POI-Microsoft Office read/write support, npoi read/write word

POI-Microsoft Office read and write support, npoi read and write word

Recently, I made a small project, and there is a link in the middle is to save the data in the collection to a local Excel file and read the local Excel file into the collection


POI ’

Let's talk briefly: POI is provided by apache, and provides read and write support for Microsoft Office format documents!

HSSF-Provides functions for reading and writing Microsoft Excel format files.
XSSF-Provides the function of reading and writing Microsoft Excel OOXML format files.
HWPF-Provides the function of reading and writing Microsoft Word format files.
HSLF-Provides the function of reading and writing Microsoft PowerPoint format files.
HDGF-Provides functions for reading and writing Microsoft Visio format files.
To read Excel, you must first download the rack package: poi-**-**. Jar Take the two businesses I encountered in the project as an example: 1, import data into Excel
 1 **
 2 * excel tools
 3 * Provide: import Excel and export Excel methods
 4 * @author Marvel
 5 *
 6 * /
 7 public class ExcelTool {
 8 private static HSSFCell cell; // column
 9 private static HSSFWorkbook hwb; // Excel object
10 private static HSSFSheet sheet; // Work folder
11 private static HSSFRow row; // row
12
13
14 / **
15 * Import Excel
16 * @param strs title
17 * @param list collection
18 * @param path Save path / file name.xls
19 * @throws Exception
20 * /
21 public static Integer ToExcel (List <Object []> list, String path) throws Exception {
22 // Create an Excel document and the content is in memory
23 hwb = new HSSFWorkbook ();
24 // sheet corresponds to create a work folder;
25 sheet = hwb.createSheet ("debug.factory");
26 // print line
27 for (int i = 0; i <list.size (); i ++) {
28 // Create a line
29 row = sheet.createRow (i);
30 // Get a row of data
31 Object [] objs = (Object []) list.get (i);
32 // Print column
33 for (int a = 0; a <objs.length; a ++) {
34 // Create a column
35 cell = row.createCell (a);
36 // Set value
37 cell.setCellValue (objs [a] .toString ());
38}
39}
40 // Create a file output stream and prepare to output a spreadsheet
41 OutputStream out = new FileOutputStream (path);
42 hwb.write (out);
43 out.close ();
44 return hwb.getSheetAt (0) .getLastRowNum ();
45}
46}
Test code:

 1 package com.dev4j.debug.test;
 2 
 3 import static org.junit.Assert. *;
 4
 5 import java.util.ArrayList;
 6 import java.util.List;
 7
 8 import org.junit.Before;
 9 import org.junit.Test;
10
11 import com.dev4j.debug.tool.ExcelTool;
12
13 / **
14 *
15 * @author Marvel
16 *
17 * /
18 public class ExcelToolTest {
19
20
21 @Test
22 public void testToExcel () throws Exception {
23 List <Object []> objs = new ArrayList <Object []> ();
24 Object [] obj = new Object [] {"张三", "18", "男"};
25 objs.add (new Object [] {"Name", "Age", "Gender"});
26 objs.add (obj);
27
28 // Because getLastRowNum (); returns the index of the last row of data, starting with 0, so the index returned here is 1.
29 assertEquals ((long) 1, (long) ExcelTool.ToExcel (objs, "E: //test2.xls"));
30}
31
32}
 

2. Export Excel and append records

 1 /**
 2 * Commonly needed
 3 * @param lists
 4 * /
 5 public static void write (Integer startRow, List <Object []> lists) {
 6 for (int i = 0; i <lists.size (); i ++) {
 7 // Create a line
 8 row = sheet.createRow (startRow + i);
 9 // Get a row of data
10 Object [] objs = (Object []) lists.get (i);
11 // Print column
12 for (int a = 0; a <objs.length; a ++) {
13 // Create a column
14 cell = row.createCell (a);
15 // Set value
16 cell.setCellValue (objs [a] .toString ());
17}
18}
19
20}
twenty one     
twenty two     
twenty three     
twenty four     /**
25 * Open file and append record
26 * @param list
27 * @param path
28 * @return
29 * @throws Exception
30 * /
31 public static Integer appendExcel (List <Object []> list, String path) throws Exception {
32 // Input stream loading
33 InputStream is = new FileInputStream (path);
34 // Create hwb object
35 hwb = new HSSFWorkbook (is);
36 // Record the last line to record the subscript, +1 update the next line to write the subscript
37 int lastRow = hwb.getSheetAt (0) .getLastRowNum () + 1;
38 // Cycle creation
39 write (lastRow, list);
40 // Create a file output stream and prepare to output a spreadsheet
41 OutputStream out = new FileOutputStream (path);
42 hwb.write (out);
43 out.close ();
44 return hwb.getSheetAt (0) .getLastRowNum ();
45}
 

 There is a duplicate code segment in the previously written code, which is extracted and written as the write (Integer, List <Object []>) method

 This is a simple Excel read and write operation, get it now!