我自己的jar包下载
一、介绍
常常有客户这样子要求：你要把我们的报表直接用Excel打开(电信系统、银行系统)。或者是：我们已经习惯用Excel打印。这样在我们实际的开发中，很多时候需要实现导入、导出Excel的应用。
目前，比较常用的实现Java导入、导出Excel的技术有:Jakarta POI、freemarker和Java Excel
下面我就分别讲解一下如何使用这些技术实现导入、导出Excel
二、使用Jakarta POI导出Excel
Jakarta POI 是一套用于访问微软格式文档的Java API。Jakarta POI有很多组件组成，其中有用于操作Excel格式文件的HSSF和用于操作Word的HWPF，
在各种组件中目前只有用于操作Excel的HSSF相对成熟。官方主页http://poi.apache.org/index.html，API文档http://poi.apache.org/apidocs/index.html
1.环境配置
下载jar
官方下载：http://poi.apache.org/download.html这里可以下载到它的最新版本和文档，目前最新版本是3.7，这里使用比较稳定的3.6版。
加入jar包
将根目录下的poi-3.6-20091214.jar和Lib目录下三个通用包 commons-logging-1.1.jar junit-3.8.1.jar log4j-1.2.13.jar拷贝到项目的Lib下
2.Jakarta POI HSSF API组件
HSSF（用于操作Excel的组件）提供给用户使用的对象在rg.apache.poi.hssf.usermodel包中,主要部分包括Excel对象，样式和格式，还有辅助操作。有以下几种对象：
常用组件：
HSSFWorkbook excel的文档对象
HSSFSheet excel的表单
HSSFRow excel的行
HSSFCell excel的格子单元
HSSFFont excel字体
HSSFDataFormat 日期格式
HSSFHeader sheet头
HSSFFooter sheet尾（只有打印的时候才能看到效果）
样式：
HSSFCellStyle cell样式
辅助操作包括：
HSSFDateUtil 日期
HSSFPrintSetup 打印
HSSFErrorConstants 错误信息表
3. 基本操作步骤
首先，理解一下一个Excel的文件的组织形式，一个Excel文件对应于一个workbook(HSSFWorkbook)，一个workbook可以有多个sheet（HSSFSheet）组成，一个sheet是由多个row（HSSFRow）组成，一个row是由多个cell（HSSFCell）组成。
基本操作步骤：
a、用HSSFWorkbook打开或者创建“Excel文件对象”
b、用HSSFWorkbook对象返回或者创建Sheet对象
c、用Sheet对象返回行对象，用行对象得到Cell对象
d、对Cell对象读写。
4.我自己写的测试用例
import java.io.FileOutputStream; import java.util.ArrayList; import java.util.List; 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.hssf.util.Region; public class POIExportExcel { public HSSFWorkbook generateExcel1(List list, HSSFWorkbook wb, String title) { //行的数量 int rows = 0; HSSFSheet sheet = wb.createSheet("Sheet1"); // 数据格样式 HSSFCellStyle dataCellStyle = createDataCellStyle(wb); // 小标题样式 HSSFCellStyle cellStyle = createCellStyle(wb); sheet.setDefaultRowHeight((short) 400);//设置所有行高为400 //设置列宽 sheet.setColumnWidth((short) 1, (short) 2800); sheet.setColumnWidth((short) 2, (short) 2800); sheet.setColumnWidth((short) 3, (short) 2800); sheet.setColumnWidth((short) 4, (short) 2800); sheet.setColumnWidth((short) 5, (short) 2800); sheet.setColumnWidth((short) 6, (short) 2800); sheet.setColumnWidth((short) 7, (short) 2800); sheet.setColumnWidth((short) 8, (short) 2800); sheet.setColumnWidth((short) 9, (short) 2800); sheet.setColumnWidth((short) 10, (short) 3200); sheet.setColumnWidth((short) 11, (short) 3200); sheet.setColumnWidth((short) 12, (short) 3200); sheet.setColumnWidth((short) 13, (short) 3200); //标题行 HSSFRow row1 = sheet.createRow((short) (rows++)); row1.setHeight((short) 500); sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) 13));//横跨1-14列 createCell(row1, (short) 0, cellStyle, title); //表头行(1,2列合并；3,4,5,6,7列合并；8,9列合并) HSSFRow row2 = sheet.createRow((short) (rows++)); sheet.addMergedRegion(new Region(1, (short) 0, 1, (short) 1)); sheet.addMergedRegion(new Region(1, (short) 2, 1, (short) 6)); sheet.addMergedRegion(new Region(1, (short) 7, 1, (short) 8)); sheet.addMergedRegion(new Region(1, (short) 9, 1, (short) 9)); sheet.addMergedRegion(new Region(1, (short) 10, 1, (short) 10)); sheet.addMergedRegion(new Region(1, (short) 11, 1, (short) 11)); sheet.addMergedRegion(new Region(1, (short) 12, 1, (short) 12)); sheet.addMergedRegion(new Region(1, (short) 13, 1, (short) 13)); createCell(row2, (short) 0, dataCellStyle, "name1"); createNullCell(row2, (short) 1, dataCellStyle); createCell(row2, (short) 2, dataCellStyle, "name2"); createNullCell(row2, (short) 3, dataCellStyle); createNullCell(row2, (short) 4, dataCellStyle); createNullCell(row2, (short) 5, dataCellStyle); createNullCell(row2, (short) 6, dataCellStyle); createCell(row2, (short) 7, dataCellStyle, "name3"); createNullCell(row2, (short) 8, dataCellStyle); createCell(row2, (short) 9, dataCellStyle, "name4"); createCell(row2, (short) 10, dataCellStyle, "name5"); createCell(row2, (short) 11, dataCellStyle, "name6"); createCell(row2, (short) 12, dataCellStyle, "name7"); createCell(row2, (short) 13, dataCellStyle, "name8"); //遍历集合,保存数据到单元格 for(int i=0;i<list.size();i++){ Object javaBean = list.get(i); //具体的数据值通过对象去获取，这里简单给值 String variable1 = "value1"+"_"+i; String variable2 = "value2"+"_"+i; String variable3 = "value3"+"_"+i; String variable4 = "value4"+"_"+i; String variable5 = "value5"+"_"+i; String variable6 = "value6"+"_"+i; String variable7 = "value7"+"_"+i; String variable8 = "value8"+"_"+i; HSSFRow rowi = sheet.createRow((short) (rows++)); sheet.addMergedRegion(new Region(i+2, (short) 0, i+2, (short) 1)); sheet.addMergedRegion(new Region(i+2, (short) 2, i+2, (short) 6)); sheet.addMergedRegion(new Region(i+2, (short) 7, i+2, (short) 8)); sheet.addMergedRegion(new Region(i+2, (short) 9, i+2, (short) 9)); sheet.addMergedRegion(new Region(i+2, (short) 10, i+2, (short) 10)); sheet.addMergedRegion(new Region(i+2, (short) 11, i+2, (short) 11)); sheet.addMergedRegion(new Region(i+2, (short) 12, i+2, (short) 12)); sheet.addMergedRegion(new Region(i+2, (short) 13, i+2, (short) 13)); createCell(rowi, (short) 0, dataCellStyle, variable1); createNullCell(rowi, (short) 1, dataCellStyle); createCell(rowi, (short) 2, dataCellStyle, variable2); createNullCell(rowi, (short) 3, dataCellStyle); createNullCell(rowi, (short) 4, dataCellStyle); createNullCell(rowi, (short) 5, dataCellStyle); createNullCell(rowi, (short) 6, dataCellStyle); createCell(rowi, (short) 7, dataCellStyle, variable3); createNullCell(rowi, (short) 8, dataCellStyle); createCell(rowi, (short) 9, dataCellStyle, variable4); createCell(rowi, (short) 10, dataCellStyle, variable5); createCell(rowi, (short) 11, dataCellStyle, variable6); createCell(rowi, (short) 12, dataCellStyle, variable7); createCell(rowi, (short) 13, dataCellStyle, variable8); } return wb; } /** * 创建单元格 * @param row * @param id * @param style * @param value */ private void createCell(HSSFRow row, short id, HSSFCellStyle style, String value) { HSSFCell cell = row.createCell((short) id); // cell.setEncoding(HSSFCell.ENCODING_UTF_16); cell.setCellValue(value); cell.setCellStyle(style); } /** * 创建空的单元格 */ private HSSFCell createNullCell(HSSFRow row, short id, HSSFCellStyle style) { HSSFCell null1Cell = row.createCell(id); null1Cell.setCellStyle(style); return null1Cell; } /** * 设置数据单元格样式 * @param wb * @return */ private HSSFCellStyle createDataCellStyle(HSSFWorkbook wb) { HSSFCellStyle dataCellStyle = wb.createCellStyle(); // 水平居中 dataCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); dataCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); dataCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); dataCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); dataCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); return dataCellStyle; } /** * 设置单元格样式 * @param wb * @return */ private HSSFCellStyle createCellStyle(HSSFWorkbook wb) { // 小标题样式 HSSFCellStyle cellStyle = wb.createCellStyle(); // 水平居中 cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 字体 HSSFFont littleFont = wb.createFont(); // 设置字体为粗体 littleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 然后将字体关联到样式 cellStyle.setFont(littleFont); cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); return cellStyle; } public static void main(String[] args){ /*String serverPath = request.getSession().getServletContext().getRealPath("/")+"exportExcel"; //在服务器端创建文件夹 File file = new File(serverPath+path); if(!file.exists()){ file.mkdir(); } SimpleDateFormat sfm = new SimpleDateFormat("yyyy-MM-dd"); String filename = "excel" + "_" + sfm.format(new java.util.Date()); String encodedfileName = new String(filename.getBytes(), "GBK"); FileOutputStream out = new FileOutputStream(serverPath+path+"/"+encodedfileName+".xls"); */ new POIExportExcel(); String title = "POI导出excel"; List list = new ArrayList<String>(); //加入两条做个测试 list.add(new String()); list.add(new String()); try { FileOutputStream out = new FileOutputStream("D:/"+title+".xls"); HSSFWorkbook wb =new HSSFWorkbook(); wb = new POIExportExcel().generateExcel1(list,wb,title); wb.write(out); /*List srcfile=new ArrayList(); srcfile.add(new File(serverPath+path+"/"+encodedfileName+".xls")); //将服务器上存放Excel的文件夹打成zip包 File zipfile = new File(serverPath+path+".zip"); ZipUtil.zipFiles(srcfile, zipfile); //弹出下载框供用户下载 this.downFile(response,serverPath, path+".zip");*/ } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } }
结果如下图：
三、使用Jakarta POI读取(导入)Excel
依赖dom4j.jar和xmlbeans.jar
没有的话会抛出：Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlObject
import java.io.File; import java.io.FileInputStream; import java.sql.Date; import java.text.DecimalFormat; import java.util.ArrayList; import java.util.List; import org.apache.commons.lang.StringUtils; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.RichTextString; 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.xssf.usermodel.XSSFWorkbook; public class MyExcelReader { /** * 分析获取上传的excel文件中的数据信息。 仅对Excel文件的第一张表进行分析，分析过程中如果遇到空行就结束。 * @param file * excel * @return excel中对应的数据以二维数组形式返回 * @throws Exception */ private String[][] readExcel(File file) { String[][] data = null; Workbook wb = null; try { // 解析excel2003 if (file.getName().endsWith(".xls")) { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream( file.getPath())); // excel数据对象 wb = new HSSFWorkbook(fs); } // 解析excel2007及以上 if (file.getName().endsWith(".xlsx")) { wb = new XSSFWorkbook(new FileInputStream(file.getPath())); } // excel中对应的表单 Sheet sheet = wb.getSheetAt(0); // excel中的有效列数 int colNum = getValidColNum(sheet); // 只有excel的有效列数大于零才对数据进行分析 if (colNum > 0) { // 从Excel中获取到的有效数据 data = getExcelData(sheet, colNum); } } catch (Exception e) { e.printStackTrace(); } return data; } /** * 得到表单的有效列数. 获取表单第一行开始位置连续非空单元格的个数 * * @param sheet * Excel的表单 * @return 列数 * @throws Exception */ private int getValidColNum(Sheet sheet) throws Exception { // 判断文件是否为空文件 if (sheet.getRow(0) == null) { System.out.println("文件为空"); throw new Exception("error.importExcel.blankExcelFile"); } // 表单总列数 int colNum = sheet.getRow(0).getPhysicalNumberOfCells(); Row row = sheet.getRow(0); // 如果表单第一行即表头位置出现空单元格，该单元格后面的信息不在导入 for (int i = 0; i < colNum; i++) { if (row.getCell((short) i) != null) { Cell cell = row.getCell((short) i); String value = this.getCellValue(cell); if (StringUtils.isEmpty(value)) { colNum = i; break; } } // 如果列表头信息为空，不读取后面的数据 else { colNum = i; } } return colNum; } /** * 读取Excel数据. 读取过程中，遇到空行自动停止，舍弃空行后面的数据。 可根据数组信息获取Excel的有效行数 * * @param sheet * 表单对象 * @param colNum * 有效列数 * @return 数据二维数组 */ private String[][] getExcelData(Sheet sheet, int colNum) { int tempRowNum = sheet.getPhysicalNumberOfRows(); // 存储从Excel表中读取的数据信息 List<List<String>> dataList = new ArrayList<List<String>>(); // 读取Excel数据，将其存储在可变长的List容器中，同时得到Excel文件的有效数据行数 int rowNum = 0; for (; rowNum < tempRowNum && sheet.getRow(rowNum) != null; rowNum++) { List<String> rowData = new ArrayList<String>(); // 对行信息进行累加，判断该行是否为空行 String rowValue = ""; for (int j = 0; j < colNum; j++) { if (sheet.getRow(rowNum).getCell((short) j) != null) { Cell cell = sheet.getRow(rowNum).getCell((short) j); String value = getCellValue(cell); rowData.add(value); rowValue += value; } else { rowData.add(null); } } // 读取信息时如遇到空行，结束读入 if (rowValue.length() == 0) { break; } else { dataList.add(rowData); } } // 将Excel数据转存到数组对象中 String[][] data = new String[rowNum][colNum]; for (int i = 0; i < dataList.size(); i++) { for (int j = 0; j < dataList.get(i).size(); j++) { data[i][j] = dataList.get(i).get(j); } } return data; } /** * 得到Excel单元格的数据内容 * * @param cell * Excel单元格对象 * @return 单元格的内容 */ public String getCellValue(Cell cell) { // excel的日期格式和java的有很大区别 if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC && DateUtil.isCellDateFormatted(cell)) { return new Date(cell.getDateCellValue().getTime()).toString(); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { double value = cell.getNumericCellValue(); return new DecimalFormat("###.####").format(value); } else { String result = ""; try { RichTextString textString = cell.getRichStringCellValue(); if (textString != null) { result = textString.getString().trim(); result = this.subStr(result); if (result.length() > 0) { char firstChar = result.charAt(0); // 如果信息不是以英文字符或数字开始，替换掉信息中的所有中英文空格，以英文或数字开始的信息不处理 if (!(firstChar >= 'a' && firstChar <= 'z' || firstChar >= 'A' && firstChar <= 'Z' || firstChar >= '0' && firstChar <= '9')) { result = result.replaceAll(" ", "").replaceAll(" ", ""); } } } } catch (Exception e) { e.printStackTrace(); } return result; } } /** * 截取字符串中的内码 * * @param str * @return */ public String subStr(String str) { for (int i = 0; i < str.length(); i++) { // 韩文等乱码,均以&#开头以；结束的字符 if (str.indexOf("&#") >= 0 && str.indexOf(";", str.indexOf("&#")) > 0) { String s2 = str.substring(str.indexOf("&#"), str.indexOf(";", str.indexOf("&#")) + 1); s2 = this.cov(s2); str = str.substring(0, str.indexOf("&#")) + s2 + str.substring( str.indexOf(";", str.indexOf("&#")) + 1, str .length()); } } return str; } /** * 转化编码，韩文等乱码 * * @param string * @return */ public String cov(String string) { String str = string.replaceAll("&#", ",").replaceAll(";", ""); String[] s2 = str.split(","); String s1 = ""; for (int i = 1; i < s2.length; i++) { int a = Integer.parseInt(s2[i], 10); s1 = s1 + (char) a; } return s1; } /** * 测试 * @param args */ public static void main(String[] args) { File file = new File("D:/test.xlsx"); // 保存Excel文件到服务器 String[][] dataArray = new MyExcelReader().readExcel(file); // System.out.println("标题："+dataArray[0].toString()); for (int i = 0; i < dataArray.length; i++) { String[] rowData = dataArray[i]; for (int j = 0; j < rowData.length; j++) { System.out.println("第" + (i + 1) + "行第" + (j + 1) + "列：" + dataArray[i][j]); } } } }
三、使用freemarker导出excel
貌似是只支持office2007之后的版本，wps也会出问题。
先放两个链接供参考：
http://blog.csdn.net/zgf19930504/article/details/50804122
https://my.oschina.net/u/1765449/blog/272334
四、第三方插件Java Excel(jxl)操作excel
http://blog.csdn.net/jerehedu/article/details/45195359
参考文章: