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

Source: Internet
Author: User
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!

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.