Poi (Apache POI)

Apache POI is an open source library of the Apache Software Foundation, where POI provides the ability for Java programs to read and write to Microsoft Office format files.

Basic functionsEditStructure: HSSF-provides the ability to read and write files in Microsoft Excel format. XSSF-Provides read-write Microsoft features of Excel ooxml format archives. HWPF-provides the ability to read and write files in Microsoft Word format. HSLF-provides the ability to read and write files in Microsoft PowerPoint format. HDGF-provides the ability to read and write Microsoft Visio format files.

12345678910111213141516171819202122232425262728293031323334353637

importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.hssf.usermodel.HSSFSheet;importorg.apache.poi.hssf.usermodel.HSSFRow;importorg.apache.poi.hssf.usermodel.HSSFCell;importjava.io.FileOutputStream;publicclassCreateXL {    /** Excel 文件要存放的位置，假定在D盘下*/    publicstaticString outputFile = "D:\\test.xls";    publicstaticvoidmain(String argv[]) {        try{            // 创建新的Excel 工作簿            HSSFWorkbook workbook = newHSSFWorkbook();            // 在Excel工作簿中建一工作表，其名为缺省值            // 如要新建一名为"效益指标"的工作表，其语句为：            // HSSFSheet sheet = workbook.createSheet("效益指标");            HSSFSheet sheet = workbook.createSheet();            // 在索引0的位置创建行（最顶端的行）            HSSFRow row = sheet.createRow((short)0);            //在索引0的位置创建单元格（左上端）            HSSFCell cell = row.createCell((short)0);            // 定义单元格为字符串类型            cell.setCellType(HSSFCell.CELL_TYPE_STRING);//已过时            // 在单元格中输入一些内容            cell.setCellValue("增加值");            // 新建一输出文件流            FileOutputStream fOut = newFileOutputStream(outputFile);            // 把相应的Excel 工作簿存盘            workbook.write(fOut);            fOut.flush();            // 操作结束，关闭文件            fOut.close();            System.out.println("文件生成...");        } catch(Exception e) {            System.out.println("已运行 xlCreate() : "+ e);        }    }}

Example 2 of reading data from an Excel document demonstrates how to read data in an Excel document. Suppose you have an Excel file named Test1.xls in the D-disk jtest directory. Example 2 program is as follows:

12345678910111213141516171819202122232425262728

importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.hssf.usermodel.HSSFSheet;importorg.apache.poi.hssf.usermodel.HSSFRow;importorg.apache.poi.hssf.usermodel.HSSFCell;importjava.io.FileInputStream;publicclassReadXL {    /** Excel文件的存放位置。注意是反斜线*/    publicstaticString fileToBeRead = "D:\\test1.xls";    publicstaticvoidmain(String argv[]) {        try{            // 创建对Excel工作簿文件的引用            HSSFWorkbook workbook = newHSSFWorkbook(newFileInputStream(fileToBeRead));            // 创建对工作表的引用。            // 本例是按名引用（让我们假定那张表有着缺省名"Sheet1"）            HSSFSheet sheet = workbook.getSheet("Sheet1");            // 也可用getSheetAt(int index)按索引引用，            // 在Excel文档中，第一张工作表的缺省索引是0，            // 其语句为：HSSFSheet sheet = workbook.getSheetAt(0);            // 读取左上端单元            HSSFRow row = sheet.getRow(0);            HSSFCell cell = row.getCell((short)0);            // 输出单元内容，cell.getStringCellValue()就是取所在单元的值            System.out.println("左上端单元是： "+ cell.getStringCellValue());        } catch(Exception e) {            System.out.println("已运行xlRead() : "+ e);        }    }}

Format cells here, we'll just cover some of the formatting-related statements, and we'll assume that workbook is a reference to a workbook. In Java, the first step is to create and format fonts and cells, and then apply these formats: 1, create fonts, set them to red, bold:

123	HSSFFont font = workbook.createFont();font.setColor(HSSFFont.COLOR_RED);font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

2. Create a format

12	HSSFCellStyle cellStyle= workbook.createCellStyle();cellStyle.setFont(font);

3. Application format

1234	HSSFCell cell = row.createCell((short) 0);cell.setCellStyle(cellStyle);cell.setCellType(HSSFCell.CELL_TYPE_STRING);cell.setCellValue("标题 ");

Working with Word documents

12345678910111213141516

importjava.io. * ;importorg.apache.poi.hwpf.extractor.WordExtractor;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.hssf.usermodel.HSSFSheet;importorg.apache.poi.hssf.usermodel.HSSFRow;importorg.apache.poi.hssf.usermodel.HSSFCell;publicclassTestPoi {    publicTestPoi() {}    publicstaticvoidmain(String args[])throwsException{        FileInputStream in = newFileInputStream("D:\\a.doc");        WordExtractor extractor = newWordExtractor();        String str = extractor.extractText(in);        //System.out.println("the result length is"+str.length());        System.out.println(str);    }}

Third, Excel file export: POI
1.jar Package Dependency
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
Structure of the 2.Excel file
Excel file
└sheet: A worksheet that controls column widths at this level
└row: Yes
└cell: Cell
3. Create a Hssfworkbook object that represents an Excel file
Hssfworkbook workbook = new Hssfworkbook ();
※ If you use the Abstractexcelview view provided by SPRINGMVC, you do not have to create it yourself.

4. Create a Hssfsheet object that represents a worksheet
Hssfsheet sheet = workbook.createsheet ("sheet name");

5. Create a Hssfrow object that represents a row
Hssfrow row = Sheet.createrow (index);
Index, which starts at 0, indicates the row

6. Create a Hssfcell object that represents a cell
Hssfcell cell = Row.createcell (index);
Index, which indicates the indexes of the cell, starts at 0

7. Writing an Excel file to the file system
① creating a file output stream object
FileOutputStream outputstream = new FileOutputStream ("File path");
② writes the contents of the file to this output stream
Workbook.write (OutputStream);

8. Adjust worksheet format
To avoid memory overflow when working with worksheet data, the related objects are reused as much as possible, rather than creating new one at a time.
Hssfdataformat format = Workbook.createdataformat ();
① Date format
Hssfcellstyle styledate = Workbook.createcellstyle ();
Styledate.setdataformat (Format.getformat ("Yyyy/mm/dd HH:mm:ss"));

② decimal Format
Hssfcellstyle styledouble = Workbook.createcellstyle ();
Styledouble.setdataformat (Format.getformat ("#,#.00000"));
"#,#.00000" means that the section and section are separated by commas, with 5 decimal places reserved

③ Wrapping Text
Hssfcellstyle Stylewraptext = Workbook.createcellstyle ();
Stylewraptext.setwraptext (TRUE);

④ Specifies the width of the column: unit 1/20 pixels
Sheet.setcolumnwidth (columnindex, width);

⑤ Auto Column width: Automatically adjusts column widths based on content, but not absolute
Sheet.autosizecolumn (columnindex);

⑥ Applying existing styles
Cell.setcellstyle (Stylewraptext);

9. Response Message Header settings
1. Generating Workbook objects
Hssfworkbook workbook = Statisticsservice.generateworkbook (Surveyid);

2. Prepare the file name string
String fileName = system.nanotime () + ". xls";

3. Set the content type of the response
Response.setcontenttype ("application/vnd.ms-excel");

4. Set the file name response message header when the browser downloads files
Response.setheader ("Content-disposition", "attachment;filename=" +filename);

5. Get the output stream
Servletoutputstream out = Response.getoutputstream ();

6. Write workbook data to the output stream
Workbook.write (out);

Poi (Apache POI)