使用POI将大量数据导入Excel中

poi

Poi常用的类 HSSF － 提供读写Microsoft Excel XLS格式档案的功能。 XSSF － 提供读写Microsoft Excel OOXML XLSX格式档案的功能。 HWPF － 提供读写Microsoft Word DOC97格式档案的功能。 XWPF － 提供读写Microsoft Word DOC2003格式档案的功能。 HSLF － 提供读写Microsoft PowerPoint格式档案的功能。 HDGF － 提供读Microsoft Visio格式档案的功能。 HPBF － 提供读Microsoft Publisher格式档案的功能。 HSMF － 提供读Microsoft Outlook格式档案的功能。 在开发中我们经常使用HSSF用来操作Excel处理表格数据。 poi简单介绍 把excel中所有的元素定义为java中的类，进而操作了类就进而操作了Excel中的元素 文件-------HSSFWorkbook 页(帧)-----HSSFSheet 行----------HSSFRow 列----------HSSFCell 样式-------HSSFCellStyle 这五个类分别对应着5个元素，进而操作这5个元素就可以操作Excel了 导出excel

package com.myjava.poi; import java.io.FileOutputStream; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; public class ExcelDemo { public static void main(String[] args) throws Exception{ /** * 第一步：定义一个新的工作簿 */ Workbook wb=new HSSFWorkbook(); /** * 第二步：创建一个Sheet页 */ Sheet sheet=wb.createSheet("第一个Sheet页"); /** * 第三步：在这个sheet页里创建一行 */ Row row=sheet.createRow(0); /** * 第四步：在该行创建一个单元格 */ Cell cell=row.createCell(0); /** * 第五步：在该单元格里设置值 */ cell.setCellValue(8); //不同类型的单元格值 row.createCell(1).setCellValue(8.8); row.createCell(2).setCellValue("你好，我是字符串类型的"); row.createCell(3).setCellValue(true); //输出 //创建一个输出流 在这里设置文件名字 FileOutputStream fileOut=new FileOutputStream("D:\\POI创造.xls"); //将这个文件写入到这个流指定的文件中 wb.write(fileOut); System.out.println("OK了！"); fileOut.close(); } }

解析excel

package cn.lizhiyu; import java.io.FileInputStream; import java.io.InputStream; import java.text.DateFormat; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.Date; import org.apache.poi.hssf.usermodel.HSSFCell; 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.poifs.filesystem.POIFSFileSystem; import org.apache.poi.ss.usermodel.DateUtil; public class asdf { public static void main(String[] args) throws Exception{ /** * 从文件系统中的“饭菜名单.xls”文件中获得输入字节 */ //首先创建一个输入流 InputStream is=new FileInputStream("D:\\aaa.xls"); //「POIFSFileSystem」类对象可以把Excel文件作为数据流来进行传入传出。 //这里可以不用这句话 HSSFWorkbook 直接传入一个输入流即可 POIFSFileSystem fs=new POIFSFileSystem(is); HSSFWorkbook wb=new HSSFWorkbook(fs); /** 获取第一个Sheet页或根据名字获得sheet页 *public HSSFSheet getSheet(java.lang.String name) *Get sheet with the given name */ HSSFSheet hssfSheet=wb.getSheetAt(0); if(hssfSheet==null){ return; } // 遍历行Row for(int rowNum=0;rowNum<=hssfSheet.getLastRowNum();rowNum++){ HSSFRow hssfRow=hssfSheet.getRow(rowNum); if(hssfRow==null){ continue; } // 遍历列Cell for(int cellNum=0;cellNum<=hssfRow.getLastCellNum();cellNum++){ HSSFCell hssfCell=hssfRow.getCell(cellNum); if(hssfCell==null){ continue; } System.out.println("第几行"+rowNum+"地激烈"+cellNum); System.out.print(" "+getValue(hssfCell)); } System.out.println(); } } //这个工具类用来获得他的存储时候的格式 //获得了他的类型，这样就可以将它存储到容器类中了 //这里这个类写的不全面 在这里都转换成了字符串 @SuppressWarnings("deprecation") private static String getValue(HSSFCell cell){ String birthdayVal=null; // return String.valueOf(hssfCell.getStringCellValue()); switch (cell.getCellTypeEnum()) { case STRING: birthdayVal = cell.getRichStringCellValue().getString(); break; case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); DateFormat formater = new SimpleDateFormat("yyyy-MM-dd"); birthdayVal = formater.format(date); //如果包含.只取整數 } else if (String.valueOf(cell.getNumericCellValue()).contains(".")) { DecimalFormat df = new DecimalFormat("#"); birthdayVal = df.format(cell.getNumericCellValue()); } else { birthdayVal = (cell + "").trim(); } break; default: birthdayVal = (cell + "").trim(); } return birthdayVal; } }

大量数据导出 导出百万数据使用SXSSFWorkbook，输出文件的扩展名为xlsx;

package com.sinosoft.report.common; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.Date; import org.apache.commons.lang.StringUtils; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.DataFormat; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.streaming.SXSSFCell; import org.apache.poi.xssf.streaming.SXSSFRow; import org.apache.poi.xssf.streaming.SXSSFSheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFFont; public class RptJob { //在创建样式的时候切记不要将创建的样式代码放到for循环中 //边框样式 private static CellStyle cellBorderStyle; //日期样式yyyy-MM-dd HH:mm:ss加边框 private static CellStyle dateCellStyleForSS; //日期样式yyyy-MM-dd加边框 private static CellStyle dateCellStyleForDD; //数字样式加边框 private static CellStyle dateCellStyleForNumber; //百分比格式加边框 private static CellStyle dateCellStyleForPer; //日期格式转换类 private static SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); public static void main(String[] args) throws IOException { //用于后面计数 short index = 0; FileOutputStream fileOut = null; //如果想要导出大量数据导excel中要使用SXSSFWorkbook这个类 SXSSFWorkbook hssfWorkbook = new SXSSFWorkbook(20000); //创建sheet SXSSFSheet sheet = hssfWorkbook.createSheet("表1"); //设置每个cell的宽度 sheet.setDefaultColumnWidth(30); //声明样式 cellBorderStyle = hssfWorkbook.createCellStyle(); dateCellStyleForSS = hssfWorkbook.createCellStyle();; dateCellStyleForDD = hssfWorkbook.createCellStyle();; dateCellStyleForNumber = hssfWorkbook.createCellStyle();; dateCellStyleForPer = hssfWorkbook.createCellStyle();; //设置日期样式 DataFormat format = hssfWorkbook.createDataFormat(); //边框样式 cellBorderStyle = hssfWorkbook.createCellStyle(); // 设置单元格边框 cellBorderStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); cellBorderStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); cellBorderStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); cellBorderStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); //声明日期样式 dateCellStyleForDD.setDataFormat(format.getFormat("yyyy-MM-dd")); // 设置单元格边框 dateCellStyleForDD.setBorderLeft(XSSFCellStyle.BORDER_THIN); dateCellStyleForDD.setBorderRight(XSSFCellStyle.BORDER_THIN); dateCellStyleForDD.setBorderTop(XSSFCellStyle.BORDER_THIN); dateCellStyleForDD.setBorderBottom(XSSFCellStyle.BORDER_THIN); dateCellStyleForDD.setAlignment(XSSFCellStyle.ALIGN_CENTER); //声明日期样式 dateCellStyleForSS.setDataFormat(format.getFormat("yyyy-MM-dd HH:mm:ss")); // 设置单元格边框 dateCellStyleForSS.setBorderLeft(XSSFCellStyle.BORDER_THIN); dateCellStyleForSS.setBorderRight(XSSFCellStyle.BORDER_THIN); dateCellStyleForSS.setBorderTop(XSSFCellStyle.BORDER_THIN); dateCellStyleForSS.setBorderBottom(XSSFCellStyle.BORDER_THIN); dateCellStyleForSS.setAlignment(XSSFCellStyle.ALIGN_CENTER); //声明百分比格式 dateCellStyleForPer.setDataFormat(format.getFormat("0.00%")); // 设置单元格边框 dateCellStyleForPer.setBorderLeft(XSSFCellStyle.BORDER_THIN); dateCellStyleForPer.setBorderRight(XSSFCellStyle.BORDER_THIN); dateCellStyleForPer.setBorderTop(XSSFCellStyle.BORDER_THIN); dateCellStyleForPer.setBorderBottom(XSSFCellStyle.BORDER_THIN); dateCellStyleForPer.setAlignment(XSSFCellStyle.ALIGN_CENTER); //声明数字格式 dateCellStyleForNumber.setDataFormat(format.getFormat("0.00")); // 设置单元格边框 dateCellStyleForNumber.setBorderLeft(XSSFCellStyle.BORDER_THIN); dateCellStyleForNumber.setBorderRight(XSSFCellStyle.BORDER_THIN); dateCellStyleForNumber.setBorderTop(XSSFCellStyle.BORDER_THIN); dateCellStyleForNumber.setBorderBottom(XSSFCellStyle.BORDER_THIN); dateCellStyleForNumber.setAlignment(XSSFCellStyle.ALIGN_CENTER); CellStyle titleStyle2 = hssfWorkbook.createCellStyle(); // 设置单元格边框 titleStyle2.setBorderLeft(XSSFCellStyle.BORDER_THIN); titleStyle2.setBorderRight(XSSFCellStyle.BORDER_THIN); titleStyle2.setBorderTop(XSSFCellStyle.BORDER_THIN); titleStyle2.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 居中 titleStyle2.setAlignment(XSSFCellStyle.ALIGN_CENTER); CellStyle titleStyle = hssfWorkbook.createCellStyle(); titleStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); titleStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); titleStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); titleStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 居中 titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); Font font = hssfWorkbook.createFont(); font.setFontName("宋体"); // 设置字体大小 font.setFontHeightInPoints((short) 22); // 粗体显示 font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); titleStyle.setFont(font); //创建第一行 SXSSFRow title = sheet.createRow(0); SXSSFCell titleCell = title.createCell(0); titleCell.setCellStyle(titleStyle); titleCell.setCellValue("标题"); //合并单元格 sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,4)); sheet.addMergedRegion(new CellRangeAddress(1, 1, 0,4)); //创建第二行 SXSSFRow titleTime = sheet.createRow(1); for(int i=0;i<5;i++){ //第一个格式格式已经设置好数据了 if(i!=0){ //给第一行设置样式 SXSSFCell titleCells = title.createCell(i); titleCells.setCellStyle(titleStyle); } SXSSFCell createCellSeconds = titleTime.createCell(i); if(i==0){ //给第二行设置值 createCellSeconds.setCellValue("开始："); } //给第二行设置样式 createCellSeconds.setCellStyle(cellBorderStyle); } //创建第三行 SXSSFRow titleRow = sheet.createRow(2); // 单元格赋值 SXSSFCell createCell = titleRow.createCell( index++); createCell.setCellValue("String"); createCell.setCellStyle(titleStyle2); SXSSFCell createCell2 = titleRow.createCell( index++); createCell2.setCellValue("date"); createCell2.setCellStyle(titleStyle2); SXSSFCell createCell3=titleRow.createCell( index++); createCell3.setCellValue("百分号"); createCell3.setCellStyle(titleStyle2); SXSSFCell createCell4=titleRow.createCell( index++); createCell4.setCellValue("数字"); createCell4.setCellStyle(titleStyle2); SXSSFCell createCell6 = titleRow.createCell( index++); createCell6.setCellValue("date"); createCell6.setCellStyle(titleStyle2); try { //之前从数据库中获得数据首先放到List<Map<String,Object>>中 数据过多会占用内存较多 //所以建议从数据库遍历出来的数据直接进行放到excel中，当导入过多数据会减轻服务起压力 for(int i=3;i<50000;i++){ SXSSFRow row = sheet.createRow(i); for (int j = 1; j <= 6; j++) { if (j==1) { setCellValue("我是字符串",row.createCell((short) 0),"String"); } if (j==2) { setCellValue("2012-12-12 12:12:12wwww",row.createCell((short) 1),"DateDD"); } if (j==3) { setCellValue("12",row.createCell((short) 2),"Per"); } if (j==4) { setCellValue("12.45",row.createCell((short) 3),"Number"); } if (j==5) { setCellValue("2012-12-12 12:12:12wwwww",row.createCell((short) 4),"DateSS"); } } } //遍历数据赋值 // 设置单元格样式 如果使用的非SXSSFWorkbook类则合并单元格方式如下 /*sheet.addMergedRegion(new Region(3 + rsList.size(), (short) 0,3 + rsList.size(), (short) 5));*/ //最后一行合并单元格 sheet.addMergedRegion(new CellRangeAddress(50000, 50000, 0, 4)); SXSSFRow endRow = sheet.createRow(50000); for(int i=0;i<5;i++){ SXSSFCell endCell = endRow.createCell(i); if(i==0){ endCell.setCellValue("提示:这里是最后一行"); } endCell.setCellStyle(cellBorderStyle); } String filePath = "d://"; File o = new File(filePath); if (!o.exists()) { o.mkdirs(); } //扩展名为这个可以存一百万多点数据，超过后会报错 //使用xls为扩展名最大数据为65536行 String fileType = ".xlsx"; String fileName="222"; //这种方法会直接将文件进行覆盖，文件中数据始终是最新的 File file = new File(filePath+fileName+fileType); fileOut = new FileOutputStream(file); hssfWorkbook.write(fileOut); } finally { try { if(fileOut != null){ fileOut.close(); } //这个也是个流要关闭 if(hssfWorkbook != null){ hssfWorkbook.close(); } } catch (Exception e) { e.printStackTrace(); } } } private static void setCellByNumer(Double value, SXSSFCell hssfCell) { hssfCell.setCellValue(value); hssfCell.setCellStyle(dateCellStyleForNumber); } private static void setCellValues(String value, SXSSFCell hssfCell) { hssfCell.setCellValue((String) value); hssfCell.setCellStyle(cellBorderStyle); } private static void setCellNumberPer(Double value, SXSSFCell hssfCell) { hssfCell.setCellValue(value); hssfCell.setCellStyle(dateCellStyleForPer); } private static void setCellValue(String value, SXSSFCell hssfCell, String type){ try{ //如果是null的Object使用 String.valueOf进行转换会转换成一个 "null"的字符串 if (null != value && !"".equals(value)) { if("String".equals(type)){ //将数据设置成字符串格式 setCellValues(value, hssfCell); }else if("Number".equals(type)){ //将数据设置成数字格式 setCellByNumer(Double.parseDouble(value), hssfCell); }else if("DateDD".equals(type)){ //将数据设置成日期格式 if(StringUtils.isNotEmpty(value) && value.length()>20){ String substring = value.substring(0,19); Date paresDate = sdf.parse(substring); hssfCell.setCellValue(paresDate); hssfCell.setCellStyle(dateCellStyleForDD); } }else if("DateSS".equals(type)){ //将数据设置成日期格式 if(StringUtils.isNotEmpty(value) && value.length()>20){ String substring = value.substring(0,19); Date paresDate; paresDate = sdf.parse(substring); hssfCell.setCellValue(paresDate); hssfCell.setCellStyle(dateCellStyleForSS); } }else if("Per".equals(type)){ //将数字设置成百分号格式 setCellNumberPer(Double.parseDouble(value)/100, hssfCell); } } else { //设置边框 hssfCell.setCellStyle(cellBorderStyle); hssfCell.setCellValue(""); } } catch (ParseException e) { e.printStackTrace(); } } }