学会简单使用poi进行excel有关操作
直接上代码：
官网上的抄的api例子：
package com.test; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.util.Calendar; import java.util.Date; import org.apache.poi.EncryptedDocumentException; import org.apache.poi.hssf.extractor.ExcelExtractor; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.usermodel.HeaderFooter; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.openxml4j.opc.OPCPackage; import org.apache.poi.poifs.filesystem.NPOIFSFileSystem; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.CreationHelper; import org.apache.poi.ss.usermodel.DataFormat; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.Footer; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.PrintSetup; 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.usermodel.WorkbookFactory; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.CellReference; import org.apache.poi.ss.util.CellUtil; import org.apache.poi.ss.util.RegionUtil; import org.apache.poi.ss.util.WorkbookUtil; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFColor; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.junit.Test; /** * 根据api一些简单操作 * @author Administrator * */ public class CreateExcel { String str="C:\\Users\\Administrator.PMIMP83HWNIKWOV\\Desktop\\poi\\create workbook"; public static void main(String[] args) { CreateExcel ce=new CreateExcel(); // String str="C:\\Users\\Administrator.PMIMP83HWNIKWOV\\Desktop\\poi\\create workbook"; // ce.createSheet(new HSSFWorkbook(), str+"\\demo0.xls"); // ce.createCell(); ce.createDateCell(); } /** * 创建Workbook */ public void createWorkbook(){ Workbook wb=null; FileOutputStream fileOut=null; try { wb=new HSSFWorkbook(); fileOut=new FileOutputStream(str+"\\demo.xls"); wb.write(fileOut); fileOut.close(); wb=new XSSFWorkbook(); fileOut=new FileOutputStream(str+"\\demo1.xlsx"); wb.write(fileOut); fileOut.close(); } catch (IOException e) { e.printStackTrace(); } } //建表，不要问我为什么注释有些是英文的，官网上扣下来的 public void createSheet(Workbook wb,String filename){ Sheet sheet1=wb.createSheet("sheet1"); // Note that sheet name is Excel must not exceed 31 characters // and must not contain any of the any of the following characters: // 0x0000 // 0x0003 // colon (:) // backslash (\) // asterisk (*) // question mark (?) // forward slash (/) // opening square bracket ([) // closing square bracket (]) // You can use org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)} // for a safe way to create valid names, this utility replaces invalid characters with a space (' ') String safeName=WorkbookUtil.createSafeSheetName("[O'Briend's sales*?]");//这个方法会去除名称字符串中的不合法字符 Sheet sheet3=wb.createSheet(safeName); try { FileOutputStream fileOut=new FileOutputStream(filename, true); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } /** * 创建细胞，也就是创建格子 */ public void createCell(){ Workbook wb=new HSSFWorkbook();//xsl //Workbook wb=new XSSFWorkbook(); CreationHelper createHelper=wb.getCreationHelper(); Sheet sheet=wb.createSheet("sheet3"); //create a row and put some cells in it.Rows are 0 based Row row=sheet.createRow((short)0); //create a cell and put a value in it Cell cell=row.createCell(0);//不填写类型的话，默认的类型是this.createCell(column,HSSFCell.CELL_TYPE_BLANK); cell.setCellValue(1); //or do it on one line row.createCell(1).setCellValue(1.2);//创建和赋值连成一行 row.createCell(2).setCellValue(createHelper.createRichTextString("This is a string")); row.createCell(3).setCellValue(true); row.createCell(4).setCellValue("nihao"); //write the output to a file try { FileOutputStream fileOut=new FileOutputStream("C:\\Users\\Administrator.PMIMP83HWNIKWOV\\Desktop\\poi\\create workbook\\demo.xls",true); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } //创建日期类型cell public void createDateCell(){ Workbook wb=new HSSFWorkbook(); //Workbook wb=new XSSFWorkbook(); CreationHelper createHelper=wb.getCreationHelper(); Sheet sheet=wb.createSheet("new sheet"); Row row=sheet.createRow(0); //create a row and put a date value in it.The first cell is not styled as a date. Cell cell=row.createCell(0); cell.setCellValue(new Date()); //we style the second cell as a date (and time).It is important to create a new //cell style from the workbook oterwise you can end up modifying the builting in style and effecting not only this cell but other cells //设置日期的格式是分厂重要的 CellStyle cellStyle=wb.createCellStyle(); cellStyle.setDataFormat( createHelper.createDataFormat().getFormat("m/d/yy h:mm") ) ; cell=row.createCell(1); cell.setCellValue(new Date()); cell.setCellStyle(cellStyle); //write the output to a file FileOutputStream fileOut; try { fileOut = new FileOutputStream(str+"\\demo4.xls"); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } //使用不同类型的cell @Test public void useOtherTypeCell(){ Workbook wb=new HSSFWorkbook(); Sheet sheet=wb.createSheet("new sheet"); Row row=sheet.createRow((short)2); row.createCell(0).setCellValue(1.1); row.createCell(1).setCellValue(new Date()); row.createCell(2).setCellValue(Calendar.getInstance()); row.createCell(3).setCellValue("a string"); row.createCell(4).setCellValue(true); row.createCell(5).setCellType(Cell.CELL_TYPE_ERROR); //write the output to a file FileOutputStream fileOut; try { fileOut = new FileOutputStream(str+"\\demo5.xls"); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } //文件VS输入流 @Test public void FileVSStream(){ //打开工作薄时，要么.xsl HSSFWorkbook或.xlsx XSSFWorkbook,可以从一个工作薄 文件 或者一个InputStream，使用一个文件对象允许更低的 //内存消耗，而一个InputStream需要更多的内存缓冲区 //使用WorkbookFactory获取Workbook try { Workbook wb=WorkbookFactory.create(new File(str+"\\MyExcel.xlsx")); } catch (EncryptedDocumentException e) { e.printStackTrace(); } catch (InvalidFormatException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } //使用InputStream获取Workbook对象 try { Workbook wb=WorkbookFactory.create(new FileInputStream(str+"\\MyExcel.xlsx")); } catch (EncryptedDocumentException e) { e.printStackTrace(); } catch (InvalidFormatException e) { e.printStackTrace(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } //如果使用HSSFWorkbook或者XSSFWorkbook直接，通常应该使用NPOIFSFileSystem或OPCPackage 有完全控制的生命周期（包括完成后关闭该文件） try { NPOIFSFileSystem fs=new NPOIFSFileSystem(new File(str+"\\MyExcel.xls")); HSSFWorkbook wb=new HSSFWorkbook(fs.getRoot(),true); //... fs.close(); } catch (IOException e) { e.printStackTrace(); } try { NPOIFSFileSystem fs=new NPOIFSFileSystem(new FileInputStream(str+"\\MyExcel.xls")); HSSFWorkbook wb=new HSSFWorkbook(fs.getRoot(),true); //... fs.close(); } catch (IOException e) { e.printStackTrace(); } //XXSFWorkbook try { OPCPackage pkg=OPCPackage.open(new File(str+"\\MyExcel.xlsx")); XSSFWorkbook wb=new XSSFWorkbook(pkg); //... pkg.close(); } catch (InvalidFormatException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } try { OPCPackage pkg=OPCPackage.open(new FileInputStream(str+"\\MyExcel.xlsx")); XSSFWorkbook wb=new XSSFWorkbook(pkg); //... pkg.close(); } catch (InvalidFormatException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } //演示各种对齐选项 @Test public void alignTest(){ Workbook wb=new XSSFWorkbook(); Sheet sheet=wb.createSheet(); Row row=sheet.createRow((short)2); row.setHeightInPoints(30);//设置行高 createCell(wb,row,(short)0,CellStyle.ALIGN_CENTER,CellStyle.VERTICAL_BOTTOM); createCell(wb,row,(short)1,CellStyle.ALIGN_CENTER_SELECTION,CellStyle.VERTICAL_BOTTOM); createCell(wb,row,(short)2,CellStyle.ALIGN_FILL,CellStyle.VERTICAL_CENTER); createCell(wb,row,(short)3,CellStyle.ALIGN_GENERAL, CellStyle.VERTICAL_CENTER); createCell(wb, row, (short) 4, CellStyle.ALIGN_JUSTIFY, CellStyle.VERTICAL_JUSTIFY); createCell(wb, row, (short) 5, CellStyle.ALIGN_LEFT, CellStyle.VERTICAL_TOP); createCell(wb, row, (short) 6, CellStyle.ALIGN_RIGHT, CellStyle.VERTICAL_TOP); //write the output to a file FileOutputStream fileOut; try { fileOut = new FileOutputStream(str+"\\xssf-align.xlsx"); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } public void createCell(Workbook wb,Row row,short column,short halign,short valign){ Cell cell=row.createCell(column); cell.setCellValue("Align It"); CellStyle cellStyle=wb.createCellStyle(); cellStyle.setAlignment(halign); cellStyle.setVerticalAlignment(valign); cell.setCellStyle(cellStyle); } //使用边界 @Test public void usrBorder(){ Workbook wb=new HSSFWorkbook(); Sheet sheet=wb.createSheet("new sheet"); Row row=sheet.createRow(1); Cell cell=row.createCell(1); cell.setCellValue(4); //style the cell with borders all around CellStyle style=wb.createCellStyle(); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.GREEN.getIndex()); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLUE.getIndex()); style.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); cell.setCellStyle(style); FileOutputStream fileOut; try { fileOut = new FileOutputStream(str+"\\demo7.xls"); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } @Test public void IteratorRowAndCell(){ //有时候，你想遍历workbook，一个表中的所有行或者所有的cell /* *workbook.sheetIterator(),sheet.rowIterator(),row.cellIterator() ,或者隐式地使用forEach *注意使用迭代器等遍历注意间隔和空缝隙 */ } public void IteratorWhiteCell(Sheet sheet){ int rowStart=Math.min(15,sheet.getFirstRowNum()); int rowEnd=Math.max(1400, sheet.getLastRowNum()); for(int rowNum=rowStart;rowNum<rowEnd;rowNum++){ Row r=sheet.getRow(rowNum); if(r==null){ //这行是空的 continue; } int lastColumn=r.getLastCellNum(); for(int cn=0;cn<lastColumn;cn++){ Cell c=r.getCell(cn, Row.RETURN_BLANK_AS_NULL); if(c==null){ //the spreadsheet is empty in this cell }else{ //Do something useful with the cell's contents } } } } //获取单元格内容 /* *To get the contents of a cell,you first need to know what kind of the cell is(asking a string cell for its numeric will *ge you a NumberFormatException for example) * * 得到单元格内容，你首先需要知道它是什么类型，否则错误的类型可能会出现NumberFormatException */ @Test public void getContent(){ Workbook wb=null; try { wb = new XSSFWorkbook(new File(str+"\\订餐统计.xlsx")); } catch (InvalidFormatException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } Sheet sheet1=wb.getSheetAt(0); for(Row row:sheet1){ for(Cell cell:row){ CellReference cellRef=new CellReference(row.getRowNum(),cell.getColumnIndex()); System.out.print(cellRef.formatAsString()); System.out.println(" - "); switch(cell.getCellType()){ case Cell.CELL_TYPE_STRING: System.out.println(cell.getRichStringCellValue().getString()); break; case Cell.CELL_TYPE_NUMERIC: if(DateUtil.isCellDateFormatted(cell)){ System.out.println(cell.getDateCellValue()); }else{ System.out.println(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: System.out.println(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: System.out.println(cell.getBooleanCellValue()); break; default: System.out.println(); } } } } //文本提取 //对于大多数文本提取要求 @Test public void getText(){ InputStream inp=null; HSSFWorkbook wb=null; try { inp = new FileInputStream(str+"\\demo.xls"); wb=new HSSFWorkbook(new POIFSFileSystem(inp)); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } ExcelExtractor extractor=new ExcelExtractor(wb); extractor.setFormulasNotResults(true); extractor.setIncludeSheetNames(false); String text=extractor.getText(); System.out.println(text); } //填充和颜色 @Test public void paddingAndColor(){ Workbook wb=new XSSFWorkbook(); Sheet sheet=wb.createSheet("new sheet"); //create a row and put some cells in it.Row are 0 based Row row=sheet.createRow((short)1); //Aqua background aqua:浅绿色 CellStyle style=wb.createCellStyle(); style.setFillBackgroundColor(IndexedColors.AQUA.getIndex()); style.setFillPattern(CellStyle.BIG_SPOTS); Cell cell=row.createCell((short)1); cell.setCellValue("X"); cell.setCellStyle(style); //Orange "foreground",foreground beging the fill foreground not the font color . foreground:前景 style=wb.createCellStyle(); style.setFillForegroundColor(IndexedColors.ORANGE.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell=row.createCell((short)2); cell.setCellValue("X"); cell.setCellStyle(style); //write to output to a file FileOutputStream fileOut; try { fileOut = new FileOutputStream(str+"\\demo8.xlsx"); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } //合并单元格 @Test public void combineCell(){ Workbook wb=new HSSFWorkbook(); Sheet sheet=wb.createSheet("new sheet"); Row row=sheet.createRow((short)1); Cell cell=row.createCell((short)1); cell.setCellValue("this is a test of merging"); sheet.addMergedRegion(new CellRangeAddress( 1, // firstRow, 全部从0为基数 1, // lastRow, 1,// firstCol, 2// lastCol )); FileOutputStream fileOut; try { fileOut = new FileOutputStream(str+"\\demo9.xls"); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } //设置字体 @Test public void useFont(){ Workbook wb=new HSSFWorkbook(); Sheet sheet=wb.createSheet("new sheet"); Row row=sheet.createRow(1); //create a new font and alert it Font font=wb.createFont(); font.setFontHeight((short)24); font.setFontName("Courier New"); font.setItalic(true); font.setStrikeout(true); CellStyle style=wb.createCellStyle(); style.setFont(font); Cell cell=row.createCell(1); cell.setCellValue("This is a test of fonts"); cell.setCellStyle(style); FileOutputStream fileOut; try { fileOut = new FileOutputStream(str+"\\demo10.xls"); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } //Note ,the maximun number of unique fonts in a workbooks is limited 32767,you should reuse fonts in you applications //instead of creating a font for each cell //在一个workbook中，每一个font是唯一的，好像在32767之内。你应该使用同一个Font然后循环给单元格设置样式而不是在循环中初始化多个font /*//错误的： for(int i=0;i<10000;i++){ row=sheet.createRow(i); cell=row.createCell((short)0); style=wb.createCellStyle(); font=wb.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); cell.setCellStyle(style); } //正确的： style=wb.createCellStyle(); font=wb.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); for(int i=0;i<10000;i++){ row=sheet.createRow(i); cell=row.createCell((short)0); cell.setCellStyle(style); }*/ } //自定义颜色 @Test public void customColor(){ // //HSSF中 // HSSFWorkbook wb=new HSSFWorkbook(); // HSSFSheet sheet=wb.createSheet(); // HSSFRow row=sheet.createRow((short)0); // @SuppressWarnings("deprecation") // HSSFCell cell=row.createCell((short)0); // cell.setCellValue("Default Palette"); // // //apply some colors from the standard palette,as in the previous examples // //we will use red text on a lime background // HSSFCellStyle style=wb.createCellStyle(); // style.setFillForegroundColor(HSSFColor.LIME.index); // style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // // HSSFFont font=wb.createFont(); // font.setColor(HSSFColor.RED.index);//字体颜色 // style.setFont(font); // // cell.setCellStyle(style); // // //save with the default palette // try { // FileOutputStream out=new FileOutputStream(str+"\\default_patte.xls"); // wb.write(out); // wb.close(); // } catch (FileNotFoundException e) { // e.printStackTrace(); // } catch (IOException e) { // e.printStackTrace(); // } // // //now let's replace RED and LIMIT in the palette with a more attractive combination // //(lovingly borrowed from freebsd.org) // cell.setCellValue("Modified Palette"); // // //creating a custom palette for the workbook // HSSFPalette palette=wb.getCustomPalette(); // // //replacing the standard red with freebsd.org red // palette.setColorAtIndex(HSSFColor.LIME.index, // (byte) 153,//red, // (byte) 0,//green, // (byte) 0//blue // ); // //replacing lime with freebsd.org gold // palette.setColorAtIndex(HSSFColor.LIME.index, (byte)255, (byte)204, (byte)102); // // //save with the modified palette // //note that wherever we have previously used RED or LIME,the new colors magically appear // try { // FileOutputStream out=new FileOutputStream(str+"\\modified_palette.xls"); // wb.write(out); // out.close(); // } catch (FileNotFoundException e) { // e.printStackTrace(); // } catch (IOException e) { // e.printStackTrace(); // } // //在XSSF中 XSSFWorkbook wb1=new XSSFWorkbook(); XSSFSheet sheet1=wb1.createSheet(); XSSFRow row1=sheet1.createRow(0); XSSFCell cell1=row1.createCell(0); cell1.setCellValue("custom XSSF colors"); XSSFCellStyle style1=wb1.createCellStyle(); style1.setFillForegroundColor(new XSSFColor(new java.awt.Color(128,0,128)));//设置背景颜色 style1.setFillPattern(CellStyle.SOLID_FOREGROUND); cell1.setCellStyle(style1); try { FileOutputStream out=new FileOutputStream(str+"\\modified_palette1.xlsx"); wb1.write(out); out.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } //读和修改 @Test public void readAndRewrite(){ try { InputStream inp=new FileInputStream(str+"\\cs.xlsx"); //InputStream inp=new FileInputStream("workbook.xlsx"); Workbook wb=WorkbookFactory.create(inp); Sheet sheet=wb.getSheetAt(0); Row row=sheet.getRow(2); Cell cell=row.getCell(3); if(cell==null){ cell=row.createCell(3); } cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue("a test"); FileOutputStream fileOut=new FileOutputStream(str+"\\cs.xlsx"); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (EncryptedDocumentException e) { e.printStackTrace(); } catch (InvalidFormatException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } //在单元格内使用文本换行 @Test public void useNewLine(){ Workbook wb=new XSSFWorkbook();//or new HSSFWorkbook(); Sheet sheet=wb.createSheet(); Row row=sheet.createRow(2); Cell cell=row.createCell(2); cell.setCellValue("use \n with word warp on to create a new line"); //to enable newlines you need set a cell styles with warp==true CellStyle cs=wb.createCellStyle(); cs.setWrapText(true); cell.setCellStyle(cs); //increase row height to accomodate two lines of text //设置行高为两个行高 row.setHeightInPoints(2*sheet.getDefaultRowHeightInPoints()); //adjust column width to fit the content sheet.autoSizeColumn((short)2); FileOutputStream fileOut; try { fileOut = new FileOutputStream(str+"\\ooxml-newlines.xlsx"); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } //数据格式 @Test public void formatData(){ Workbook wb=new HSSFWorkbook(); Sheet sheet=wb.createSheet("format sheet"); CellStyle style; DataFormat format=wb.createDataFormat(); Row row; Cell cell; short rowNum=0; short colNum=0; row=sheet.createRow(rowNum++); cell=row.createCell(colNum); cell.setCellValue(11111.25); style=wb.createCellStyle(); style.setDataFormat(format.getFormat("0.0")); cell.setCellStyle(style); row=sheet.createRow(rowNum++); cell=row.createCell(colNum); cell.setCellValue(11111.25); style=wb.createCellStyle(); style.setDataFormat(format.getFormat("#,##0.0000")); cell.setCellStyle(style); FileOutputStream fileOut; try { fileOut = new FileOutputStream(str+"\\demo11.xls"); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } //有道翻译感觉不太准确 //Fix Sheet to One Page @Test public void fixToOnePage(){ Workbook wb=new HSSFWorkbook(); Sheet sheet=wb.createSheet("format sheet"); PrintSetup ps=sheet.getPrintSetup(); sheet.setAutobreaks(true); ps.setFitHeight((short)1); ps.setFitWidth((short)1); //create various cells and rows for spreadsheet Row row=sheet.createRow(1); Cell cell=row.createCell(0); cell.setCellValue("nihao"); FileOutputStream fileOut; try { fileOut = new FileOutputStream(str+"\\demo12.xls"); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } //Set Print Area 设置打印区域 @Test public void setPrintArea(){//其实我感觉不出什么东西来，不过api上有就写一下 Workbook wb=new HSSFWorkbook(); Sheet sheet=wb.createSheet("sheet1"); //sets the print area for the first sheet wb.setPrintArea(0, "$A$1:$C$2"); //Alternatibely wb.setPrintArea( 0,//sheetIndex, 0,//startColumn, 0,//endColumn, 0,//startRow, 0//endRow ); FileOutputStream fileOut; try { fileOut = new FileOutputStream(str+"\\demo13.xls"); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } //Set Page Numbers on Footer @Test public void setFooter(){ Workbook wb=new HSSFWorkbook();//or new XSSFWorkbook(); Sheet sheet=wb.createSheet("format sheet"); Footer footer=sheet.getFooter(); footer.setRight("Page "+HeaderFooter.page()+" of "+HeaderFooter.numPages()); //Create various cells and rows for spreadsheet Row row=sheet.createRow(1); Cell cell=row.createCell(0); cell.setCellValue("haha"); FileOutputStream fileOut; try { fileOut = new FileOutputStream(str+"\\demo14.xls"); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } //使用一些函数 @Test public void useSomeFunction(){ Workbook wb=new HSSFWorkbook();//new XSSFWorkbook(); Sheet sheet1=wb.createSheet("new sheet"); //create a merged region 合并区域 Row row=sheet1.createRow(1); Row row2=sheet1.createRow(2); Cell cell=row.createCell(1); cell.setCellValue("This is a test of merging"); CellRangeAddress region=CellRangeAddress.valueOf("B2:E5"); sheet1.addMergedRegion(region); //Set the border and border colors final short borderMediumDashed=CellStyle.BORDER_MEDIUM_DASHED; RegionUtil.setBorderTop(borderMediumDashed, region, sheet1, wb); RegionUtil.setBorderLeft(borderMediumDashed, region, sheet1, wb); RegionUtil.setBorderBottom(borderMediumDashed, region, sheet1, wb); RegionUtil.setRightBorderColor(borderMediumDashed, region, sheet1, wb); RegionUtil.setBottomBorderColor(IndexedColors.AQUA.getIndex(), region, sheet1, wb); RegionUtil.setTopBorderColor(IndexedColors.AQUA.getIndex(), region, sheet1, wb); RegionUtil.setRightBorderColor(IndexedColors.AQUA.getIndex(), region, sheet1, wb); RegionUtil.setLeftBorderColor(IndexedColors.AQUA.getIndex(), region, sheet1, wb); //shows some usages of HSSFCellUtil CellStyle style=wb.createCellStyle(); style.setIndention((short)4); CellUtil.createCell(row, 8, "this is the value of the cell",style); Cell cell2=CellUtil.createCell(row2, 8, "this is the value of the cell"); CellUtil.setAlignment(cell2,wb,CellStyle.ALIGN_CENTER); //Write out the workbook FileOutputStream fileOut; try { fileOut = new FileOutputStream(str+"\\demo15.xls"); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } //重复的行和列 @Test public void repeatingRowAndCol(){ Workbook wb=new HSSFWorkbook(); Sheet sheet1=wb.createSheet("Sheet1"); Sheet sheet2=wb.createSheet("Sheet2"); //set the rows to repeat from 4 to 5 on the first sheet sheet1.setRepeatingRows(CellRangeAddress.valueOf("4:5")); //set the columns to repeat from column A to C on the secend sheet sheet2.setRepeatingColumns(CellRangeAddress.valueOf("A:C")); FileOutputStream fileOut; try { fileOut = new FileOutputStream(str+"\\demo16.xls"); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } }
简单解析excel：
package com.test; import java.io.File; import java.io.IOException; import org.apache.poi.EncryptedDocumentException; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; 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; import org.apache.poi.ss.usermodel.WorkbookFactory; /* static int CELL_TYPE_BLANK Blank Cell type (3) static int CELL_TYPE_BOOLEAN Boolean Cell type (4) static int CELL_TYPE_ERROR Error Cell type (5) static int CELL_TYPE_FORMULA Formula Cell type (2) static int CELL_TYPE_NUMERIC Numeric Cell type (0) static int CELL_TYPE_STRING String Cell type (1) */ public class ParseexcelDemo { public static void main(String[] args) { ParseexcelDemo pxd=new ParseexcelDemo(); pxd.parseXml("C:\\Users\\Administrator.PMIMP83HWNIKWOV\\Desktop\\poi\\create workbook\\学员烧烤通讯录表.xlsx"); } public void parseXml(String filename){ Workbook wb=null; try { wb=WorkbookFactory.create(new File(filename)); Sheet sheet=wb.getSheetAt(0); for(Row row:sheet){ for(Cell cell:row){ System.out.print(getCellValue(cell)+"---"); } System.out.println(); } } catch (EncryptedDocumentException e) { e.printStackTrace(); } catch (InvalidFormatException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } public Object getCellValue(Cell cell){ int type=cell.getCellType(); String show=null; switch(type){ case Cell.CELL_TYPE_BLANK: show=null;break; case Cell.CELL_TYPE_BOOLEAN: show= String.valueOf(cell.getBooleanCellValue());break; case Cell.CELL_TYPE_ERROR: show=String.valueOf(cell.getErrorCellValue());break; case Cell.CELL_TYPE_FORMULA: show=cell.getCellFormula();break; case Cell.CELL_TYPE_NUMERIC: show=String.valueOf(cell.getNumericCellValue());break; case Cell.CELL_TYPE_STRING: show=cell.getStringCellValue();break; default: show=null; } return show; } }
简单创建excel：
package com.listenerVedio; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.junit.Test; public class PotTest1 { @Test public void TestWrite01(){ Workbook wb=new HSSFWorkbook(); FileOutputStream fos=null; try { fos=new FileOutputStream("d:/test/poi/w1.xls"); Sheet sheet=wb.createSheet("测试01"); Row row=sheet.createRow(0); row.setHeightInPoints(30);//设置行高 CellStyle cs=wb.createCellStyle();//新建单元格样式对象 //我们可以使用CellStyle设置单元格的一些关于美化方向的属性，比如颜色，边框，背景等 cs.setAlignment(CellStyle.ALIGN_CENTER);//设置水平居中 cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//设置垂直居中 /* cs.setBorderBottom((short)1);//设置边框样式 这里发现边框设置样式失败。原来设置边框中使用的是样式的常量 cs.setBorderTop((short)1); cs.setBorderLeft((short)1); cs.setBorderRight((short)1); */ cs.setBorderBottom(CellStyle.BORDER_DASH_DOT); cs.setBorderLeft(CellStyle.BORDER_MEDIUM_DASH_DOT_DOT); Cell c=row.createCell(0); c.setCellStyle(cs);//给单元格设置样式 c.setCellValue("标识"); c=row.createCell(1); c.setCellStyle(cs); c.setCellValue("用户名"); wb.write(fos); wb.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } /** * 发现这样写是不现实的，因为设置一个单元格样式太过麻烦。虽然使用for、循环可以达到效果 * 我 们习惯于创建一个模板，模板里面将样式定好，然后我们填充模板里面的元素，然后用来完成数据的导出 * 能够根据excel转换成对象，能够将对象导成excel，能够带模板的方式导出excel。终极目标 * * 一些思路：如何将对象导出成excel，首先需要有对象，对象一般不是你的实体类而是专门用来导出的自定义对象，应为导出对象的列应该不是所有的 * 如何确定顶部的标题：使用注解实现。创建Annotation，在其中说明这是excel的resource，顺序也是使用注解实现 * */ } }
使用模板简单创建excel：
package com.util; import java.io.File; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.util.Calendar; import java.util.Date; import java.util.HashMap; import java.util.Map; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; 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.ss.usermodel.WorkbookFactory; /** * Excel模板 * @author Administrator * */ public class ExcelTemplate { private static ExcelTemplate et=new ExcelTemplate(); public final static String DATA_LINE="datas"; public final static String DEFAULT_STYLE="defaultStyle"; public final static String OWNSTYLE="style"; private Workbook wb; private Sheet sheet; /** * 数据的初始化列 */ private int initColIndex; /** * 数据的初始化行 */ private int initRowIndex; /** * 数据的当前列 */ private int curColIndex; /** * 数据的当前行 */ private int curRowIndex; /** * 数据的最后一行 */ private int lastRowIndex; /** * 当前行对象 */ private Row curRow; /** * 默认样式 */ private CellStyle defaultStyle; /** * 自定义的样式的集合 */ private Map<Integer,CellStyle> styles; /** * 数据列行高 */ private float dataLineHeight; /** * 单例获取et * @return */ public static ExcelTemplate getInstance(){ return et; } /** * 根据classPath读取模板 * @param classPath * @return */ public ExcelTemplate readTemplateByClasspath(String classPath){ try { wb=WorkbookFactory.create(ExcelTemplate.class.getResourceAsStream(classPath)); } catch (InvalidFormatException e) { e.printStackTrace(); throw new RuntimeException("取读模板格式有误！请检查"); } catch (IOException e) { e.printStackTrace(); throw new RuntimeException("取读的模板不存在！请检查"); } initTemp(); return et; } /** * 根据filePath读取模板 * @param filePath * @return */ public ExcelTemplate readTemplateByFilepath(String filePath){ try { wb=WorkbookFactory.create(new File(filePath)); }catch (InvalidFormatException e) { e.printStackTrace(); throw new RuntimeException("取读模板格式有误！请检查"); } catch (IOException e) { e.printStackTrace(); throw new RuntimeException("取读的模板不存在！请检查"); } initTemp(); return et; } /** * 将Workbook写入文件 * @param filePath */ public void writeToFile(String filePath){ FileOutputStream fos=null; try { fos=new FileOutputStream(filePath); wb.write(fos); } catch (FileNotFoundException e) { e.printStackTrace(); throw new RuntimeException("写入的文件不存在！"+e.getMessage()); } catch (IOException e) { e.printStackTrace(); throw new RuntimeException("写入数据失败！"+e.getMessage()); }finally{ try { if(fos!=null){ fos.close(); } } catch (IOException e) { e.printStackTrace(); } } } /** * 将Workbook写入输出流 * @param os */ public void writeToStream(OutputStream os){ try { wb.write(os); } catch (IOException e) { e.printStackTrace(); throw new RuntimeException("写入流失败！"+e.getMessage()); } } /** * 初始化模板 */ public void initTemp(){ sheet=wb.getSheetAt(0); initConfigData(); curRow=sheet.getRow(initRowIndex); } /** * 获取初始化配置数据 */ private void initConfigData() { lastRowIndex=sheet.getLastRowNum(); styles=new HashMap<Integer,CellStyle>(); int styleinRow=0; for(Row row:sheet){ for(Cell cell:row){ if(cell.getCellType()!=Cell.CELL_TYPE_STRING) continue; String str=cell.getStringCellValue().trim(); if(str.equals(DATA_LINE)){ curColIndex=initColIndex=cell.getColumnIndex(); curRowIndex=initRowIndex=cell.getRowIndex(); dataLineHeight=row.getHeightInPoints(); } if(str.equals(DEFAULT_STYLE)){ defaultStyle=cell.getCellStyle(); styleinRow=cell.getRowIndex(); } if(str.equals(OWNSTYLE)){ styles.put(cell.getColumnIndex(), cell.getCellStyle()); styleinRow=cell.getRowIndex(); } /** * 后面可以附加好多其他东西 */ } } //sheet.removeRow(sheet.getRow(styleinRow));//这里将设置样式的一行删除，样式已经完成了 /*直接删除不能实现下面的行自动往上顶，使用移动下面的行将前面的行覆盖实现*/ sheet.shiftRows(styleinRow+1,lastRowIndex--, -1, true, true); } /** * 创建新行 */ public void createNewRow(){ moveData(); curRow=sheet.createRow(++curRowIndex); curRow.setHeightInPoints(dataLineHeight); curColIndex=initColIndex; } /** * 创建新列并为新列赋值 * @param value */ public void createCell(String value){ Cell c=curRow.createCell(curColIndex); if(styles.containsKey(curColIndex)) c.setCellStyle(styles.get(curColIndex)); else c.setCellStyle(defaultStyle); c.setCellValue(value); curColIndex++; } public void createCell(int value){ Cell c=curRow.createCell(curColIndex); if(styles.containsKey(curColIndex)) c.setCellStyle(styles.get(curColIndex)); else c.setCellStyle(defaultStyle); c.setCellValue(value); curColIndex++; } public void createCell(double value){ Cell c=curRow.createCell(curColIndex); if(styles.containsKey(curColIndex)) c.setCellStyle(styles.get(curColIndex)); else c.setCellStyle(defaultStyle); c.setCellValue(value); curColIndex++; } public void createCell(boolean value){ Cell c=curRow.createCell(curColIndex); if(styles.containsKey(curColIndex)) c.setCellStyle(styles.get(curColIndex)); else c.setCellStyle(defaultStyle); c.setCellValue(value); curColIndex++; } public void createCell(Date value){ Cell c=curRow.createCell(curColIndex); if(styles.containsKey(curColIndex)) c.setCellStyle(styles.get(curColIndex)); else c.setCellStyle(defaultStyle); c.setCellValue(value); curColIndex++; } public void createCell(Calendar value){ Cell c=curRow.createCell(curColIndex); if(styles.containsKey(curColIndex)) c.setCellStyle(styles.get(curColIndex)); else c.setCellStyle(defaultStyle); c.setCellValue(value); curColIndex++; } public void createCell(RichTextString value){ Cell c=curRow.createCell(curColIndex); if(styles.containsKey(curColIndex)) c.setCellStyle(styles.get(curColIndex)); else c.setCellStyle(defaultStyle); c.setCellValue(value); curColIndex++; } /** * 将excel尾部描述列往后移动 */ private void moveData(){ sheet.shiftRows(curRowIndex+1, lastRowIndex++, 1,true, true); } /** * 设置序号 可选 */ public void insertSerial(){ int serialRow=-1; int serialCol=-1; boolean findSerial=false; for(Row row:sheet){ if(findSerial) break; for(Cell cell:row){ if(cell.getCellType()!=Cell.CELL_TYPE_STRING) continue; String str=cell.getStringCellValue().trim(); if(str.equals("serial")){ serialRow=cell.getRowIndex(); serialCol=cell.getColumnIndex(); findSerial=true; break; } } } if(serialRow==-1 || serialCol==-1){ return; } int count=curRowIndex-initRowIndex+1; for(int i=1;i<=count;i++){ Cell c=sheet.getRow(serialRow++).createCell(serialCol); c.setCellValue(i); } } /** * 为一些变量赋值 * @param datas */ public void replaceVariable(Map<String,String> datas){ for(Row row:sheet){ for(Cell cell:row){ if(cell.getCellType()!=Cell.CELL_TYPE_STRING) continue; String str=cell.getStringCellValue().trim(); if(!str.startsWith("#")) continue; if(datas.containsKey(str.substring(1))){ cell.setCellValue(datas.get(str.substring(1))); } } } } }
实现excel和对象之间的转换：
package com.ann; import java.lang.annotation.ElementType; import java.lang.annotation.Retention; import java.lang.annotation.RetentionPolicy; import java.lang.annotation.Target; @Retention(RetentionPolicy.RUNTIME) @Target({ElementType.FIELD}) public @interface Column { String title(); int order() default 9999; } package com.entity; import com.ann.Column; /** * 用户类 * @author Administrator * */ public class User{ @Column(title="编号",order=1) private int id; @Column(title="姓名",order=2) private String name; @Column(title="年龄",order=3) private int age; @Column(title="邮箱",order=4) private String email; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public User() { super(); } public User(int id, String name, int age, String email) { super(); this.id = id; this.name = name; this.age = age; this.email = email; } @Override public String toString() { return "User [id=" + id + ", name=" + name + ", age=" + age + ", email=" + email + "]"; } } package com.util; /** * 列标题列顺序和对应对象字段组成的自定义对象 * excel和Object的中间对象 * @author Administrator * */ public class ExcelHeader implements Comparable<ExcelHeader>{ /** * excel的标题名 */ private String title; /** * 每一个标题的顺序 */ private int order; /** * 每一个列对应的字段名 * @return */ private String fieldName; public String getTitle() { return title; } public void setTitle(String title) { this.title = title; } public ExcelHeader(String title, int order,String filedName) { super(); this.title = title; this.order = order; this.fieldName=filedName; } public ExcelHeader() { super(); } @Override public String toString() { return "ExcelHeader [title=" + title + ", order=" + order + ", fieldName=" + fieldName + "]"; } public int getOrder() { return order; } public void setOrder(int order) { this.order = order; } @Override public int compareTo(ExcelHeader o) { return order>o.order?1:(order==o.order?0:-1); } public void setFieldName(String fieldName) { this.fieldName = fieldName; } public String getFieldName() { return fieldName; } } package com.util; import java.io.File; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.util.Calendar; import java.util.Date; import java.util.HashMap; import java.util.Map; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; 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.ss.usermodel.WorkbookFactory; /** * Excel模板 * @author Administrator * */ public class ExcelTemplate { private static ExcelTemplate et=new ExcelTemplate(); public final static String DATA_LINE="datas"; public final static String DEFAULT_STYLE="defaultStyle"; public final static String OWNSTYLE="style"; private Workbook wb; private Sheet sheet; /** * 数据的初始化列 */ private int initColIndex; /** * 数据的初始化行 */ private int initRowIndex; /** * 数据的当前列 */ private int curColIndex; /** * 数据的当前行 */ private int curRowIndex; /** * 数据的最后一行 */ private int lastRowIndex; /** * 当前行对象 */ private Row curRow; /** * 默认样式 */ private CellStyle defaultStyle; /** * 自定义的样式的集合 */ private Map<Integer,CellStyle> styles; /** * 数据列行高 */ private float dataLineHeight; /** * 单例获取et * @return */ public static ExcelTemplate getInstance(){ return et; } /** * 根据classPath读取模板 * @param classPath * @return */ public ExcelTemplate readTemplateByClasspath(String classPath){ try { wb=WorkbookFactory.create(ExcelTemplate.class.getResourceAsStream(classPath)); } catch (InvalidFormatException e) { e.printStackTrace(); throw new RuntimeException("取读模板格式有误！请检查"); } catch (IOException e) { e.printStackTrace(); throw new RuntimeException("取读的模板不存在！请检查"); } initTemp(); return et; } /** * 根据filePath读取模板 * @param filePath * @return */ public ExcelTemplate readTemplateByFilepath(String filePath){ try { wb=WorkbookFactory.create(new File(filePath)); }catch (InvalidFormatException e) { e.printStackTrace(); throw new RuntimeException("取读模板格式有误！请检查"); } catch (IOException e) { e.printStackTrace(); throw new RuntimeException("取读的模板不存在！请检查"); } initTemp(); return et; } /** * 将Workbook写入文件 * @param filePath */ public void writeToFile(String filePath){ FileOutputStream fos=null; try { fos=new FileOutputStream(filePath); wb.write(fos); } catch (FileNotFoundException e) { e.printStackTrace(); throw new RuntimeException("写入的文件不存在！"+e.getMessage()); } catch (IOException e) { e.printStackTrace(); throw new RuntimeException("写入数据失败！"+e.getMessage()); }finally{ try { if(fos!=null){ fos.close(); } } catch (IOException e) { e.printStackTrace(); } } } /** * 将Workbook写入输出流 * @param os */ public void writeToStream(OutputStream os){ try { wb.write(os); } catch (IOException e) { e.printStackTrace(); throw new RuntimeException("写入流失败！"+e.getMessage()); } } /** * 初始化模板 */ public void initTemp(){ sheet=wb.getSheetAt(0); initConfigData(); curRow=sheet.getRow(initRowIndex); } /** * 获取初始化配置数据 */ private void initConfigData() { lastRowIndex=sheet.getLastRowNum(); styles=new HashMap<Integer,CellStyle>(); int styleinRow=0; for(Row row:sheet){ for(Cell cell:row){ if(cell.getCellType()!=Cell.CELL_TYPE_STRING) continue; String str=cell.getStringCellValue().trim(); if(str.equals(DATA_LINE)){ curColIndex=initColIndex=cell.getColumnIndex(); curRowIndex=initRowIndex=cell.getRowIndex(); dataLineHeight=row.getHeightInPoints(); } if(str.equals(DEFAULT_STYLE)){ defaultStyle=cell.getCellStyle(); styleinRow=cell.getRowIndex(); } if(str.equals(OWNSTYLE)){ styles.put(cell.getColumnIndex(), cell.getCellStyle()); styleinRow=cell.getRowIndex(); } /** * 后面可以附加好多其他东西 */ } } //sheet.removeRow(sheet.getRow(styleinRow));//这里将设置样式的一行删除，样式已经完成了 /*直接删除不能实现下面的行自动往上顶，使用移动下面的行将前面的行覆盖实现*/ sheet.shiftRows(styleinRow+1,lastRowIndex--, -1, true, true); } /** * 创建新行 */ public void createNewRow(){ moveData(); curRow=sheet.createRow(++curRowIndex); curRow.setHeightInPoints(dataLineHeight); curColIndex=initColIndex; } /** * 创建新列并为新列赋值 * @param value */ public void createCell(String value){ Cell c=curRow.createCell(curColIndex); if(styles.containsKey(curColIndex)) c.setCellStyle(styles.get(curColIndex)); else c.setCellStyle(defaultStyle); c.setCellValue(value); curColIndex++; } public void createCell(int value){ Cell c=curRow.createCell(curColIndex); if(styles.containsKey(curColIndex)) c.setCellStyle(styles.get(curColIndex)); else c.setCellStyle(defaultStyle); c.setCellValue(value); curColIndex++; } public void createCell(double value){ Cell c=curRow.createCell(curColIndex); if(styles.containsKey(curColIndex)) c.setCellStyle(styles.get(curColIndex)); else c.setCellStyle(defaultStyle); c.setCellValue(value); curColIndex++; } public void createCell(boolean value){ Cell c=curRow.createCell(curColIndex); if(styles.containsKey(curColIndex)) c.setCellStyle(styles.get(curColIndex)); else c.setCellStyle(defaultStyle); c.setCellValue(value); curColIndex++; } public void createCell(Date value){ Cell c=curRow.createCell(curColIndex); if(styles.containsKey(curColIndex)) c.setCellStyle(styles.get(curColIndex)); else c.setCellStyle(defaultStyle); c.setCellValue(value); curColIndex++; } public void createCell(Calendar value){ Cell c=curRow.createCell(curColIndex); if(styles.containsKey(curColIndex)) c.setCellStyle(styles.get(curColIndex)); else c.setCellStyle(defaultStyle); c.setCellValue(value); curColIndex++; } public void createCell(RichTextString value){ Cell c=curRow.createCell(curColIndex); if(styles.containsKey(curColIndex)) c.setCellStyle(styles.get(curColIndex)); else c.setCellStyle(defaultStyle); c.setCellValue(value); curColIndex++; } /** * 将excel尾部描述列往后移动 */ private void moveData(){ sheet.shiftRows(curRowIndex+1, lastRowIndex++, 1,true, true); } /** * 设置序号 可选 */ public void insertSerial(){ int serialRow=-1; int serialCol=-1; boolean findSerial=false; for(Row row:sheet){ if(findSerial) break; for(Cell cell:row){ if(cell.getCellType()!=Cell.CELL_TYPE_STRING) continue; String str=cell.getStringCellValue().trim(); if(str.equals("serial")){ serialRow=cell.getRowIndex(); serialCol=cell.getColumnIndex(); findSerial=true; break; } } } if(serialRow==-1 || serialCol==-1){ return; } int count=curRowIndex-initRowIndex+1; for(int i=1;i<=count;i++){ Cell c=sheet.getRow(serialRow++).createCell(serialCol); c.setCellValue(i); } } /** * 为一些变量赋值 * @param datas */ public void replaceVariable(Map<String,String> datas){ for(Row row:sheet){ for(Cell cell:row){ if(cell.getCellType()!=Cell.CELL_TYPE_STRING) continue; String str=cell.getStringCellValue().trim(); if(!str.startsWith("#")) continue; if(datas.containsKey(str.substring(1))){ cell.setCellValue(datas.get(str.substring(1))); } } } } } package com.util; import java.io.File; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.lang.reflect.Field; import java.lang.reflect.InvocationTargetException; import java.util.ArrayList; import java.util.Collections; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Properties; import java.util.Set; import org.apache.commons.beanutils.BeanUtils; import org.apache.poi.EncryptedDocumentException; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; 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; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import com.ann.Column; /** * 对象和excel之间的互相转换 * 使用模板写入excel与不使用模板写入 * 读取excel数据到对象集合 * @author Administrator * */ @SuppressWarnings({"rawtypes","unchecked"}) public class ExcelUtil { private static ExcelUtil eu=new ExcelUtil(); /** * 单例 */ private ExcelUtil(){} /** * excel字段内容对象集合 */ private List<ExcelHeader> headers; public static ExcelUtil getInstance(){ return eu; } /** * 模板对象 */ private ExcelTemplate et; /** * excel工作薄对象 */ private Workbook wb; /** * 将对象插入到excel表格中，使用模板，目标是文件 * @param datas * @param alertableValues * @param objectClassType * @param path * @param isFilePath * @param outPath */ public void Obj2ExcelUseTemplate(List<Object> datas,Map<String,String> alertableValues,Class objectClassType,String path,boolean isFilePath,String outPath){ et=isFilePath?ExcelTemplate.getInstance().readTemplateByFilepath(path):ExcelTemplate.getInstance().readTemplateByClasspath(path); try { handerObj2ExcelUseTemplate(datas,alertableValues,objectClassType); et.writeToFile(outPath); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } catch (NoSuchMethodException e) { e.printStackTrace(); } catch (InstantiationException e) { e.printStackTrace(); } } /** * 将对象插入到excel表格中，使用模板，目标是输出流 * @param datas * @param alertableValues * @param objectClassType * @param path * @param isFilePath * @param os */ public void Obj2ExcelUseTemplate(List<Object> datas,Map<String,String> alertableValues,Class objectClassType,String path,boolean isFilePath,OutputStream os){ et=isFilePath?ExcelTemplate.getInstance().readTemplateByFilepath(path):ExcelTemplate.getInstance().readTemplateByClasspath(path); try { handerObj2ExcelUseTemplate(datas,alertableValues,objectClassType); et.writeToStream(os); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } catch (NoSuchMethodException e) { e.printStackTrace(); } catch (InstantiationException e) { e.printStackTrace(); } } /** * 将对象插入到excel表格中，使用模板，目标是文件 * @param datas * @param alertableValues * @param objectClassType * @param path * @param isFilePath * @param outPath */ public void Obj2ExcelUseTemplate(List<Object> datas,Properties prop,Class objectClassType,String path,boolean isFilePath,String outPath){ et=isFilePath?ExcelTemplate.getInstance().readTemplateByFilepath(path):ExcelTemplate.getInstance().readTemplateByClasspath(path); try { Map maps=new HashMap<String,String>(); if(!(prop==null || prop.size()==0)) maps.putAll(prop); handerObj2ExcelUseTemplate(datas,maps,objectClassType); et.writeToFile(outPath); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } catch (NoSuchMethodException e) { e.printStackTrace(); } catch (InstantiationException e) { e.printStackTrace(); } } /** * 将对象插入到excel表格中，使用模板，目标是输出流 * @param datas * @param alertableValues * @param objectClassType * @param path * @param isFilePath * @param os */ public void Obj2ExcelUseTemplate(List<Object> datas,Properties prop,Class objectClassType,String path,boolean isFilePath,OutputStream os){ et=isFilePath?ExcelTemplate.getInstance().readTemplateByFilepath(path):ExcelTemplate.getInstance().readTemplateByClasspath(path); try { Map maps=new HashMap<String,String>(); if(!(prop==null || prop.size()==0)) maps.putAll(prop); handerObj2ExcelUseTemplate(datas,maps,objectClassType); et.writeToStream(os); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } catch (NoSuchMethodException e) { e.printStackTrace(); } catch (InstantiationException e) { e.printStackTrace(); } } /** * 使用模板将对象插入Excel中的转换工作的方法 * @param datas * @param alertableValues * @param objectClassType * @throws IllegalAccessException * @throws InvocationTargetException * @throws NoSuchMethodException * @throws InstantiationException */ private void handerObj2ExcelUseTemplate(List<Object> datas,Map<String,String> alertableValues,Class objectClassType) throws IllegalAccessException, InvocationTargetException, NoSuchMethodException, InstantiationException{ headers=getHeaderList(objectClassType); Collections.sort(headers); for(ExcelHeader eh:headers){//设置标题 et.createCell(eh.getTitle()); } for(Object o:datas){ et.createNewRow(); for(ExcelHeader eh:headers){ et.createCell(BeanUtils.getProperty(o, eh.getFieldName())); } } if(alertableValues==null || alertableValues.size()==0) return; et.replaceVariable(alertableValues); } /** * 获取excel字段内容对象集合 * @param clz * @return */ private List<ExcelHeader> getHeaderList(Class clz) { List<ExcelHeader> headers=new ArrayList<ExcelHeader>(); Field[] fields=clz.getDeclaredFields(); for(Field f:fields){ if(f.isAnnotationPresent(Column.class)){ Column c=f.getAnnotation(Column.class); headers.add(new ExcelHeader(c.title(), c.order(), f.getName())); } } return headers; } /** * 对象写入excel文件 * @param isXSSF * @param datas * @param objectClassType * @param outPath */ public void Obj2Excel(boolean isXSSF,List<Object> datas,Class objectClassType,String outPath){ wb=isXSSF?(new XSSFWorkbook()):(new HSSFWorkbook()); try { Obj2Excel(datas,objectClassType); writeToFile(outPath); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } catch (NoSuchMethodException e) { e.printStackTrace(); } } /** * 对象写入excel文件 流 * @param isXSSF * @param datas * @param objectClassType * @param os */ public void Obj2Excel(boolean isXSSF,List<Object> datas,Class objectClassType,OutputStream os){ wb=isXSSF?(new XSSFWorkbook()):(new HSSFWorkbook()); try { Obj2Excel(datas,objectClassType); writeToStream(os); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } catch (NoSuchMethodException e) { e.printStackTrace(); } } /** * 将Workbook写入文件 * @param outPath */ private void writeToFile(String outPath) { FileOutputStream fos=null; try { fos = new FileOutputStream(outPath); wb.write(fos); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); }finally{ try { if(fos!=null) fos.close(); } catch (IOException e) { e.printStackTrace(); } } } /** * 将Workbook写入输出流 * @param os */ private void writeToStream(OutputStream os){ try { wb.write(os); } catch (IOException e) { e.printStackTrace(); } } /** * 处理将对象转换成excel的具体实现，不使用模板 * @param datas * @param objectClassType * @throws IllegalAccessException * @throws InvocationTargetException * @throws NoSuchMethodException */ private void Obj2Excel(List<Object> datas,Class objectClassType) throws IllegalAccessException, InvocationTargetException, NoSuchMethodException{ Sheet sheet=wb.createSheet(); Row r=sheet.createRow(0); //插入表头 headers=getHeaderList(objectClassType); Collections.sort(headers); for(int i=0;i<headers.size();i++){ Cell c=r.createCell(i); c.setCellValue(headers.get(i).getTitle()); } //插入数据内容 for(int i=0;i<datas.size();i++){ r=sheet.createRow(i+1); for(int j=0;j<headers.size();j++){ Cell c=r.createCell(j); c.setCellValue(BeanUtils.getProperty(datas.get(i),headers.get(j).getFieldName())); } } } /** * 从流中读取表格信息到对象集合 * @param ins * @param objectClassType * @param readLine * @param tailLine * @return */ public List<Object> readExcel2Objs(InputStream ins,Class objectClassType,int readLine,int tailLine){ try { wb=WorkbookFactory.create(ins); return handerReadExcel2Objs(objectClassType,readLine,tailLine); } catch (EncryptedDocumentException e) { e.printStackTrace(); } catch (InvalidFormatException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } catch (InstantiationException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } return null; } /** * 从文件中读取表格信息到对象集合 * @param filePath * @param objectClassType * @param readLine * @param tailLine * @return */ public List<Object> readExcel2Objs(String filePath,Class objectClassType,int readLine,int tailLine){ try { wb=WorkbookFactory.create(new File(filePath)); return handerReadExcel2Objs(objectClassType,readLine,tailLine); } catch (EncryptedDocumentException e) { e.printStackTrace(); } catch (InvalidFormatException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } catch (InstantiationException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } return null; } /** * 转换表格信息到对象集合的实现方法 * @param objectClassType * @param readLine * @param tailLine * @return * @throws InstantiationException * @throws IllegalAccessException * @throws InvocationTargetException */ private List<Object> handerReadExcel2Objs(Class objectClassType,int readLine,int tailLine) throws InstantiationException, IllegalAccessException, InvocationTargetException{ List<Object> objs=new ArrayList<Object>(); Sheet sheet=wb.getSheetAt(0); Map<Integer,String> headerMap=getHeaderMap(sheet.getRow(readLine),objectClassType);//列数和字段的map集合 for(int i=readLine+1;i<sheet.getLastRowNum()-tailLine+1;i++){ Row r=sheet.getRow(i); Object o=objectClassType.newInstance(); Set<Integer> cols=headerMap.keySet(); for(int x:cols){ Cell c=r.getCell(x); BeanUtils.copyProperty(o, headerMap.get(x), getCellValue(c)); } objs.add(o); } return objs; } /** * 获取不同值的方法 * @param cell * @return */ private Object getCellValue(Cell cell){ int type=cell.getCellType(); String show=null; switch(type){ case Cell.CELL_TYPE_BLANK: show=null;break; case Cell.CELL_TYPE_BOOLEAN: show= String.valueOf(cell.getBooleanCellValue());break; case Cell.CELL_TYPE_ERROR: show=String.valueOf(cell.getErrorCellValue());break; case Cell.CELL_TYPE_FORMULA: show=cell.getCellFormula();break; case Cell.CELL_TYPE_NUMERIC: show=String.valueOf(cell.getNumericCellValue());break; case Cell.CELL_TYPE_STRING: show=cell.getStringCellValue();break; default: show=null; } return show; } /** * 获取列和对应字段的映射集合 */ public Map<Integer,String> getHeaderMap(Row r,Class objectClassType){ headers=getHeaderList(objectClassType); Collections.sort(headers); Map<Integer,String> maps=new HashMap<Integer,String>(); for(Cell c:r){ String title=c.getStringCellValue(); for(ExcelHeader eh:headers){ if(title.equals(eh.getTitle())){ maps.put(c.getColumnIndex(), eh.getFieldName()); break; } } } return maps; } } 一个用来测试的properties： title="User Message List" date="2016-7-11" author="guodaxia"
