关于Java使用POI对Excel2003和2007的导入和导出

今天药忘吃喽~ 2022-06-18 04:29 153阅读 0赞

**Excel2003和2007的区别,除了不仅在外观上,还有相关的功能上也都得到很大改善,因为机制的问题(或许后缀问题把![尴尬][awkward.gif]),在文件解析上,也与之前有很大区别**

**,对于我这代码君来说,还是直接上代码,比较直观**

**1:引用相关POI.jar包,**

<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.9</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-scratchpad</artifactId>
                <version>3.9</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>3.9</version>
            </dependency>
            <dependency>
                <groupId>org.apache.xmlbeans</groupId>
                <artifactId>xmlbeans</artifactId>
                <version>2.3.0</version>
            </dependency>

**2:判断该文件是2003文件还是2007以上文件(原理通过文件后缀)**

import java.math.BigDecimal;
    import java.util.Date;
    
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFDateUtil;
    import org.apache.poi.ss.usermodel.Cell;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    
    import com.xyy.common.util.DateUtil;
    import com.xyy.common.util.StringUtil;
    
    /**
     * Excel辅助工具类
     * @ClassName: ExcelCommonUtil 
     * @author wangqinghua
     * @date 2017-3-21 上午11:07:46
     */
    public class ExcelCommonUtil {
    	private static Logger LOGGER = LoggerFactory.getLogger(ExcelCommonUtil.class);
    	/**
    	 * 判断是否为老版本的excel
    	 * @Title: isExcel2003
    	 * @param filePath   *.xls
    	 * @return
    	 * boolean
    	 * @author wangqinghua 
    	 * @date 2017-3-21 上午11:05:19
    	 */
    	public static boolean isExcel2003(String filePath){
            return StringUtil.isNotEmpty(filePath) && filePath.matches("^.+\\.(?i)(xls)$");
        }
    	
    	/**
    	 * 判断是否为新版本的excel
    	 * @Title: isExcel2007
    	 * @param filePath   *.xlsx
    	 * @return
    	 * boolean
    	 * @author wangqinghua 
    	 * @date 2017-3-21 上午11:05:43
    	 */
        public static boolean isExcel2007(String filePath){
            return StringUtil.isNotEmpty(filePath) && filePath.matches("^.+\\.(?i)(xlsx)$");
        }
    	
    	/**
    	 * 根据单元格获取内容
    	 * @Title: getCellFormatValue
    	 * @param cell
    	 * @return
    	 * String
    	 * @author wangqinghua 
    	 * @date 2017-3-10 下午10:19:04
    	 */
    	public static String getCellFormatValue(Cell cell) {
    		String result="";
    		switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING:
            	result = cell.getRichStringCellValue().getString();
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:
            	if(HSSFDateUtil.isCellDateFormatted(cell)){  
                    Date date = cell.getDateCellValue(); 
                    result =  DateUtil.date2String(date, "yyyy-MM-dd");
    	        }else{  
    	        	result = String.valueOf(new BigDecimal(cell.getNumericCellValue()));  
    	        }
                break;
            case HSSFCell.CELL_TYPE_FORMULA:
            	result = String.valueOf(cell.getNumericCellValue());
                break;
            default:
            	result = "";
                break;
            }
    		return result;
        }
    }

**3:文件判断完毕,根据POI对应的接口获取文件流,以及相关单元格值,组成对应的结果集package com.xyy.common.excel;**

import java.io.IOException;
    import java.io.InputStream;
    import java.util.Date;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    import javax.servlet.http.HttpServletRequest;
    
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFFont;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    
    import com.xyy.common.util.DateUtil;
    import com.xyy.common.util.StringUtil;
    /**
     * 导出数据表格的工具类
     * @ClassName: UtilOrganizeExcelData 
     * @author wangqinghua
     * @date 2016-2-23 下午11:08:43
     */ 
    public class UtilOrganizeExcelData {
    	private static Logger LOGGER = LoggerFactory.getLogger(ExcelCommonUtil.class);
    	/**
    	 * @param titleCn 中文标题名
    	 * @param colsCn 中文列名
    	 * @param dataFileds 数据域名称
    	 * @param styles 数据的格式 0:顺序号 1：文本 2：应用参数  3：金额（实际金额带小数点） 4:金额
    	 * @param alignStyles 对齐方式 ALIGN_LEFT = 1;ALIGN_CENTER = 2;ALIGN_RIGHT = 3;
    	 * @param widths 列的宽度 默认为4000
    	 * @param dataList 数据List
    	 * @param appParams 应用参数对应的字段
    	 * @param fileName 下载文件名称
    	 * @return
    	 */
    	public static boolean listToExcel(HttpServletRequest request, String titleCn, String colsCn, String dataFileds, String styles, String alignStyles, String widths, List dataList,String appParams,String fileName)
    	{	
    		String titleName;
    		String closName;
    		
    		String excelSplitChar=",";//域分割符号
    		String excelRowSplitChar=";";//行分割符号
    		//目前先处理中文的标题和中文的列名
    		titleName = titleCn;
    		closName = colsCn;
    		
    		//---结束处理--------------		
    		
            //---------------------------------------------------
    		String columnFiledName[] = StringUtil.splitStringToString(dataFileds,excelSplitChar);//context中的变量名
    		String columnStyles[] = StringUtil.splitStringToString(styles,excelSplitChar);//数据类型
    		String columnAlignStyles[] = StringUtil.splitStringToString(alignStyles,excelSplitChar);//对齐方式
    		String columnWidths[] = StringUtil.splitStringToString(widths,excelSplitChar);//宽度
    		String columnAppParams[] = StringUtil.splitStringToString(appParams,excelSplitChar);//参数名
    		
    		
    		int columnNum = columnFiledName.length;// 列数
    		/**
    		 * closName的格式如下 "序号,项目,年初余额,收入,-,支出,-,期末余额;|,|,|,本月,本年累计,本月,本年累计,|"
    		 * 可以多行，行与行之间用“;”隔开
    		 * “|”表示改列别合并了，不处理
    		 */
    		String columnRowName[] = StringUtil.splitStringToString(closName,excelRowSplitChar);	
    		String columnName[][] = new String[columnRowName.length][columnNum];
    		for(int i = 0; i < columnRowName.length; i++){
    			columnName[i] = StringUtil.splitStringToString(columnRowName[i], excelSplitChar);
    		}
    		
    		//创建一个工作表
    		HSSFWorkbook wb = new HSSFWorkbook();
    		
    		//设置字体位置
    		HSSFCellStyle style1_LEFT = wb.createCellStyle();//普通_左
    		HSSFCellStyle style1_CENTER = wb.createCellStyle();//普通_中
    		HSSFCellStyle style1_RIGHT = wb.createCellStyle();//普通_右
    		HSSFCellStyle style2 = wb.createCellStyle();//表头
    		HSSFCellStyle style3 = wb.createCellStyle();//标题
    
    		HSSFCellStyle styleTemp = null;
    		// 设置字体
    		HSSFFont font1 = wb.createFont();
    		HSSFFont font2 = wb.createFont();
    		HSSFFont font3 = wb.createFont();
    		
    		font1.setFontHeightInPoints((short) 11);    //字体大小
    		font1.setFontName("宋体");
    		style1_LEFT.setFont(font1);
    		style1_CENTER.setFont(font1);
    		style1_RIGHT.setFont(font1);
    		
    		font2.setFontHeightInPoints((short) 11);
    		font2.setFontName("宋体");
    		font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  //粗体
    		style2.setFont(font2);
    		
    		font3.setFontHeightInPoints((short) 15);
    		font3.setFontName("宋体");
    		font3.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    		style3.setFont(font3);
    		
    		//设置格式
    		style1_LEFT.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 水平左
    		style1_LEFT.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直举重
    		style1_LEFT.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 下边框
    		style1_LEFT.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
    		style1_LEFT.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
    		style1_LEFT.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
    		style1_LEFT.setWrapText(true);// 自动换行
    		
    		style1_CENTER.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平居中
    		style1_CENTER.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直举重
    		style1_CENTER.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 下边框
    		style1_CENTER.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
    		style1_CENTER.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
    		style1_CENTER.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
    		style1_CENTER.setWrapText(true);// 自动换行
    		
    		style1_RIGHT.setAlignment(HSSFCellStyle.ALIGN_RIGHT);// 水平居右
    		style1_RIGHT.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直举重
    		style1_RIGHT.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 下边框
    		style1_RIGHT.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
    		style1_RIGHT.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
    		style1_RIGHT.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
    		style1_RIGHT.setWrapText(true);// 自动换行
    		
    		style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平居中
    		style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直举重
    		style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 下边框
    		style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
    		style2.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
    		style2.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
    		style2.setWrapText(true);// 自动换行
    		
    		style3.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平居中
    		style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直举重
    		style3.setWrapText(true);// 自动换行
    		/*******以下开始填充数据************/
    		short rownum = 0;// 行序号
    		HSSFSheet sheet = null;// 创建表单
    		HSSFRow row = null;// 行
    		HSSFCell cell = null;// 单元格
    		
    		//创建一个Sheet
    		sheet = wb.createSheet("sheet");
    		sheet.setDefaultColumnWidth(20);
    		wb.setSheetName(0, "SHEET1");
    		//为列设置宽度
    		if(columnWidths != null && columnWidths.length == columnNum)
    		{
    			for (int j = 0; j <columnNum; j++) {
    				sheet.setColumnWidth(j, Integer.parseInt(columnWidths[j]==""?"4000":columnWidths[j]));
    			}
    		}
    		else
    		{
    			for (int j = 0; j <columnNum; j++) {
    				sheet.setColumnWidth(j, 4000);
    			}
    		}
    		//写入数据
    		row = sheet.createRow(rownum++);
    		row.setHeight((short) 800);
    		cell = row.createCell(0);
    		
    		// 大的标题
    		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    		cell.setCellValue(titleName);
    		cell.setCellStyle(style3);
    		//合并单元格
    		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (columnNum-1)));
    		// 标题栏	
    		for(int i = 0; i < columnName.length; i++){
    			row = sheet.createRow(rownum);
    			row.setHeight((short) 500);
    			for(int j = 0; j< columnNum; j++){
    				if(columnName[i][j].equals("-")){
    					sheet.addMergedRegion(new CellRangeAddress(rownum, j-1, rownum,j));
    				}
    				if(columnName[i][j].equals("|")){
    					sheet.addMergedRegion(new CellRangeAddress(rownum-1, j, rownum, j));
    				}
    				cell = row.createCell(j); 
    				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    				cell.setCellValue(columnName[i][j]);
    				cell.setCellStyle(style2);
    			}
    			rownum++;
    		}
    		//数据部分
    		//utb方法
    		try{
    			String contentTemp = null;
    			String contentTemp2 = null;
    			int size = dataList.size();
    			for (int i = 0; i < size; i++) {
    				row = sheet.createRow(rownum);//创建行
    				Object kColl = dataList.get(i);                                                     
    				for(int j = 0; j < columnNum; j++){
    					//取出数据域的值，如果含有‘|’，则处理每一个数据域（一个列里含有多个数据域）
    					String subColsFiledName[] = StringUtil.splitStringToString(columnFiledName[j], "|");
    					String subColsStyles[] = StringUtil.splitStringToString(columnStyles[j], "|");
    					String subColsAppParams[] = StringUtil.splitStringToString(columnAppParams[j], "|");
    					contentTemp = "";
    					contentTemp2 = "";
    					String gap = "";
    					if(subColsFiledName.length > 1)
    						gap = " ";
    					for(int k = 0;k < subColsFiledName.length;k++)
    					{
    						
    						
    						//如果从当前的循环kColl取不到值，则从context从去取值 add 2007-11-22
    						String valueStr = "";
    						 
    						//如果是顺序号，则不需要获取数据域值
    						if(!"0".equals(subColsStyles[k].trim()))
    						{
    							try
    							{
    								if(subColsFiledName[k].indexOf("#")!= -1){
    									String speName[] = StringUtil.splitStringToString(subColsFiledName[k],"#");
    									if(speName.length>0){
    										for(int m=0;m< speName.length;m++){
    											try{
    												valueStr = valueStr + ((Object)ReflectionUtils.getFieldValue(kColl, speName[m])).toString();
    											}catch(Exception ex){ 
    												valueStr = valueStr + ((Object)ReflectionUtils.getFieldValue(kColl, speName[m])).toString();
    											}
    										}
    									}
    								}else{
    									valueStr = ((Object)ReflectionUtils.getFieldValue(kColl, subColsFiledName[k])).toString();
    								}
    							}
    							catch(Exception oe)
    							{
    								valueStr = ((Object)ReflectionUtils.getFieldValue(kColl, subColsFiledName[k])).toString();
    							}	
    						}
    						//根据列的类型分别做处理
    						// 0:顺序号 1：文本 2：应用参数  3：金额（实际金额） 4:金额（不处理）5:日期 6：时间
    						//7:利率 8:错误信息
    						//如果是5类型，则需要输入参数
    				       	switch(Integer.parseInt(subColsStyles[k])){
    					       	case 0: //顺序号
    					       		contentTemp = contentTemp + String.valueOf(i+1) + gap;		    	   	
    					    	   	break;
    					       	case 1: //文本
    					       		contentTemp =  contentTemp + valueStr + gap;
    					    	   	break;
    					       	case 3://金额
    					       		contentTemp2 = valueStr;
    					    	   	contentTemp = contentTemp + contentTemp2 + gap;
    					    	   	break;
    					    	case 4://金额
    					       		contentTemp2 = valueStr;
    					    	   	contentTemp = contentTemp + contentTemp2 + gap;
    					    	   	break;
    					    	case 5: //日期
    					    		contentTemp2 = valueStr;
    					       		contentTemp =  contentTemp + contentTemp2 + gap;
    					    	   	break;
    					    	case 6: //时间
    					    		contentTemp2 = valueStr;
    					       		contentTemp =  contentTemp + contentTemp2 + gap;
    					    	   	break;
    					    	case 7: //利率
    					    		contentTemp2 = valueStr;
    					       		contentTemp =  contentTemp + String.valueOf(Double.parseDouble(contentTemp2)/(double)10000000) + gap;
    					    	   	break;
    					       	case 9://金额 处理   xxxx.xx这中输入，输出x,xxx.xx
    					       		if(valueStr!=null&&valueStr.startsWith("."))
    					       			valueStr="0"+valueStr;
    					       		contentTemp2 = valueStr;
    					    	   	contentTemp = contentTemp + contentTemp2 + gap;
    					    	   	break;
    				       	}
    					}
    					row.setHeight((short) 400);
    			       	cell = row.createCell(j);
    				   	cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    					//设置样式
    					switch(Integer.parseInt(columnAlignStyles[j])){
    						case 1: //左
    							styleTemp = style1_LEFT;
    							break;
    						case 2: //中
    							styleTemp = style1_CENTER;
    							break;
    						case 3: //右
    							styleTemp = style1_RIGHT;
    							break;
    					}
    					cell.setCellStyle(styleTemp);
    					cell.setCellValue(contentTemp);
    			    }
    				rownum++;
    			}
    		}catch(Exception ep){
    			ep.printStackTrace();
    			return false;
    		}
    		String fileNameStr = "";
    		//处理文件名
    		if(fileName.trim() == ""){
    			fileName = "小药药数据下载";
    		}
    		fileNameStr = fileName +"_"+ DateUtil.date2String(new Date(), "yyyy-MM-dd")+".xls";
    	    request.setAttribute("excelFileName", fileNameStr);
    		request.setAttribute("excelWorkBook", wb);
    		return true;
    	}
    	
    	public static Map<String, String> excelToList(InputStream inputStream,String fileName) {
            Map<String, String> content = new HashMap<String, String>();
            String str = "";
            Workbook wb = null;
            try {
            	if(ExcelCommonUtil.isExcel2003(fileName)){
            		wb = new HSSFWorkbook(inputStream);
            	}else if(ExcelCommonUtil.isExcel2007(fileName)){
            		wb = new XSSFWorkbook(inputStream);
            	}
            } catch (IOException e) {
                LOGGER.error("excel转换异常",e);
                return content;
            }
            Sheet sheet = wb.getSheetAt(0);
            // 得到总行数
            int rowNum = sheet.getLastRowNum();
            Row row = sheet.getRow(0);
            int colNum = row.getPhysicalNumberOfCells();
            // 正文内容应该从第二行开始,第一行为表头的标题
            for (int i = 1; i <= rowNum; i++) {
                row = sheet.getRow(i);
                int j = 0;
                while (j < colNum) {
                    str += ExcelCommonUtil.getCellFormatValue(row.getCell(j)).trim() + "|";
                    j++;
                }
                content.put(i+"", str);
                str = "";
            }
            return content;
        }
    }

上述就是整个完成的导入代码,在导出excel的上面,需要用到一些关于实体的反射原理,在这就不复制了,需要的朋友可以@我

[awkward.gif]: /images/20220618/0812819cd38242d58fdcb2c71632d16d.png