Use poi to read and write Excel and poi to read and write excel

Use poi to read and write Excel and poi to read and write excel

For a programmer, file operations are common, especially for Excel files.

Here I will introduce a POI tool used in the project to Operate Excel. There are many concepts about POI on the Internet. For more information, you can use Baidu. Here I will just give a brief introduction. POI is an apache class library that provides java developers with support for Office file processing (word, ppt, and Excel. Here are some examples to illustrate.

Preparations

The version I used is 3.10.1. The required jar files include:

• Dom4j. jar
• Log4j-1.2.13.jar
• Poi-3.10.1-20140818.jar
• Poi-ooxml-3.10.1-20140818.jar
• Poi-ooxml-schemas-3.10.1-20140818.jar
• Poi-scratchpad-3.10.1-20140818.jar

Code example 1. Read Excel

Public void testReadExcel () {try {// read the Excel Workbook wb = new HSSFWorkbook (new FileInputStream ("d :\\ 2.xls ")); // obtain the number of sheet for (int t = 0; t <wb. getNumberOfSheets (); t ++) {Sheet sheet = wb. getSheetAt (t); Row row = null; int lastRowNum = sheet. getLastRowNum (); // cyclically read for (int I = 0; I <= lastRowNum; I ++) {row = sheet. getRow (I); if (row! = Null) {// obtain the value of each column for (int j = 0; j <row. getLastCellNum (); j ++) {Cell cell = row. getCell (j); String value = getCellValue (cell); if (! Value. equals ("") {System. out. print (value + "|") ;}} System. out. println () ;}}} catch (Exception e) {e. printStackTrace ();}}

A method is used:

/***** Read the cell value ** @ Title: getCellValue * @ Date: 10:52:07 * @ param Cell * @ return */private String getCellValue (cell) {Object result = ""; if (cell! = Null) {switch (cell. getCellType () {case Cell. CELL_TYPE_STRING: result = cell. getStringCellValue (); break; case Cell. CELL_TYPE_NUMERIC: result = cell. getNumericCellValue (); break; case Cell. CELL_TYPE_BOOLEAN: result = cell. getBooleanCellValue (); break; case Cell. CELL_TYPE_FORMULA: result = cell. getCellFormula (); break; case Cell. CELL_TYPE_ERROR: result = cell. getErrorCellValue (); break; case Cell. CELL_TYPE_BLANK: break; default: break;} return result. toString ();}

First, read the file into the Workbook. The Workbook interface has two implementations: HSSFWorkbook and XSSFWorkbook. The former is used to read the Excel of version 97-03 with the extension of xls, and the latter is used to read the 07 and later versions with the extension of xlsx. Read to the workbook, And Then loop all the sheet, all the valid rows and columns in the sheet loop. Here, sheet. getLastRowNum () obtains the index value of the last row (starting from 0), while sheet. getPhysicalNumberOfRows () is the row number of the last row obtained (starting from 1 ). It should be noted that the loop column does not loop in sheet, but loops in row.
As follows:


Code Example 2. Write an Excel file

Public void testWriteExcel () {String excelPath = "d:/3.xls"; Workbook workbook = null; try {// XSSFWorkbook used. running X (> = 2007), HSSWorkbook for 03. xsl workbook = new HSSFWorkbook (); // XSSFWorkbook (); // WorkbookFactory. create (inputStream);} catch (Exception e) {System. out. println ("Excel creation failed:"); e. printStackTrace ();} if (workbook! = Null) {Sheet sheet = workbook. createSheet ("Test Data"); Row row0 = sheet. createRow (0); for (int I = 0; I <6; I ++) {Cell cell = row0.createCell (I, Cell. CELL_TYPE_STRING); cell. setCellValue ("column title" + I); // sheet. autoSizeColumn (I); // Auto Adjust width} for (int rowNum = 1; rowNum <10; rowNum ++) {Row row Row = sheet. createRow (rowNum); for (int I = 0; I <6; I ++) {Cell cell = row. createCell (I, Cell. CELL_TYPE_STRING); cell. setCellValue ("cell" + String. valueOf (rowNum + 1) + String. valueOf (I + 1);} try {FileOutputStream outputStream = new FileOutputStream (excelPath); workbook. write (outputStream); outputStream. flush (); outputStream. close ();} catch (Exception e) {System. out. println ("failed to write to Excel:"); e. printStackTrace ();}}}

As follows:
It's easy. As long as you use poi, whether you have installed the Office or not, you can operate the Office file perfectly, friends, come and try it.

Use the java poi class to read the content of an excel table and then write the complete code into a new excel table.

You want to read an excel file, integrate the data in it, and then generate a new excel file.

Package aa;

Import java. io. FileInputStream;
Import java. io. FileNotFoundException;
Import java. io. FileOutputStream;
Import java. io. InputStream;

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;

// You want to read an excel file, integrate the data in it, and generate a new excel file.

Public class {

Public static void main (String [] args) throws Exception {

// Read excel:
InputStream is = new FileInputStream ("d :\\ test2.xls ");
POIFSFileSystem fs = new POIFSFileSystem (is );
HSSFWorkbook wb = new HSSFWorkbook (fs );

// Read the sheet and read the first column of the First row in the sheet.
HSSFSheet sheet = wb. getSheetAt (0 );
HSSFRow row = sheet. getRow (0 );
HSSFCell cell = row. getCell (short) 0 );
String s = cell. getStringCellValue ();

System. out. println (s );

//... Content combination to play by yourself ~

// For example, a String S2 is combined above.

// Write an excel file, such as test3.xls.
FileOutputStream fos = new FileOutputStream ("d: \ test3.xls ");

HSSFWorkbook wb2 = new HSSFWorkbook ();
HSSFSheet hsSheet = wb2.createSheet ("TestReport ");
HSSFRow hsRow1 = hsSheet. createRow (0); // China Construction Bank
HsRow1.setHeight (short) 500 );
HSSFCell hsCell11 = hsRow1.createCell (short) 0 );
HsCell11.setEncoding (HSSFCell. ENCODING_UTF_16 );
HsCell11.setCellValue (s + "TESTVALUE & quo ...... remaining full text>

Which jar packages need to be imported to read and write excel using the poi package?

First and third