Java uses Apache POI to Operate excel files, apachepoi

Java uses Apache POI to Operate excel files, apachepoi

• Official introduction

HSSF is the POI Project's pure Java implementation of the Excel '97 (-2007) file format. XSSF is the POI Project's pure Java implementation of the Excel 2007 OOXML (.xlsx) file format. according to the official documents, the HSSF package provided by POI is used to operate the .xls file of Excel '97(-2007), and the xssf package is used to operate the files after Excel2007. xslx file.

• Required jar package

POI official site to download the package and decompress get java excel files required jar package: Where dom4j-1.6.1.jar and xbean. jar (: http://mirror.bjtu.edu.cn/apache/xmlbeans/binaries/ site: http://xmlbeans.apache.org)
) Is not included in the jar package provided by POI and needs to be downloaded separately. Otherwise, the program will throw an exception: java. lang. ClassNotFoundException: org. apache. xmlbeans. XmlOptions.

• Code

Create a java project in Eclipse and add the jar packages listed above to the classpath of the project. Otherwise, an error is returned if the jar package is not referenced. Directly go to the Code (the basic framework of the code is from the Apache POI official website, and you can adjust the part by yourself): Create an excel file and write the content:

public static void createWorkbook() throws IOException {        Workbook wb = new HSSFWorkbook();        String safeName1 = WorkbookUtil.createSafeSheetName("[O'sheet1]");        Sheet sheet1 = wb.createSheet(safeName1);        CreationHelper createHelper = wb.getCreationHelper();        // Create a row and put some cells in it. Rows are 0 based.        Row row = sheet1.createRow((short) 0);        // Create a cell and put a value in it.        Cell cell = row.createCell(0);        cell.setCellValue(1234);        // Or do it on one line.        row.createCell(2).setCellValue(                createHelper.createRichTextString("This is a string"));        row.createCell(3).setCellValue(true);        // we style the second cell as a date (and time). It is important to        // create a new cell style from the workbook otherwise you can end up        // modifying the built 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);        // you can also set date as java.util.Calendar        CellStyle cellStyle1 = wb.createCellStyle();        cellStyle1.setDataFormat(createHelper.createDataFormat().getFormat(                "yyyyMMdd HH:mm:ss"));        cellStyle1.setBorderBottom(CellStyle.BORDER_THIN);        cellStyle1.setBottomBorderColor(IndexedColors.BLACK.getIndex());        cellStyle1.setBorderLeft(CellStyle.BORDER_THIN);        cellStyle1.setLeftBorderColor(IndexedColors.GREEN.getIndex());        cellStyle1.setBorderRight(CellStyle.BORDER_THIN);        cellStyle1.setRightBorderColor(IndexedColors.BLUE.getIndex());        cellStyle1.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED);        cellStyle1.setTopBorderColor(IndexedColors.BLACK.getIndex());        cell = row.createCell(4);        cell.setCellValue(Calendar.getInstance());        cell.setCellStyle(cellStyle1);        FileOutputStream fileOut = new FileOutputStream("e:/test/workbook.xls");        wb.write(fileOut);        fileOut.close();    }

Read the content of an excel file:

public static void readExcel() throws InvalidFormatException, IOException {        // Use a file        Workbook wb1 = WorkbookFactory.create(new File("e:/test/userinfo.xls"));        Sheet sheet = wb1.getSheetAt(0);        // Decide which rows to process        // int rowStart = Math.min(10, sheet.getFirstRowNum());        // int rowEnd = Math.max(40, sheet.getLastRowNum());        int rowStart = sheet.getLastRowNum();        int rowEnd = sheet.getLastRowNum() + 1;        logger.info(sheet.getFirstRowNum());        logger.info(sheet.getLastRowNum());        for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {            Row r = sheet.getRow(rowNum);            int lastColumn = Math.max(r.getLastCellNum(), 10);            logger.info(lastColumn);            // To get the contents of a cell, you first need to know what kind            // of cell it is (asking a string cell for its numeric contents will            // get you a NumberFormatException for example). So, you will want            // to switch on the cell's type, and then call the appropriate            // getter for that cell.            for (int cn = 0; cn < lastColumn; cn++) {                // Cell cell = r.getCell(cn, Row.RETURN_BLANK_AS_NULL);                Cell cell = r.getCell(cn);                switch (cell.getCellType()) {                case Cell.CELL_TYPE_STRING:                    logger.info(cell.getRichStringCellValue().getString());                    break;                case Cell.CELL_TYPE_NUMERIC:                    if (DateUtil.isCellDateFormatted(cell)) {                        logger.info(cell.getDateCellValue());                    } else {                        logger.info(cell.getNumericCellValue());                    }                    break;                case Cell.CELL_TYPE_BOOLEAN:                    logger.info(cell.getBooleanCellValue());                    break;                case Cell.CELL_TYPE_FORMULA:                    logger.info(cell.getCellFormula());                    break;                default:                    logger.info("empty");                }            }        }    }

 

The following is a specific example. The excel File Content in the instance is as follows:

What our program needs to do is read the content of each file in the order of the first line of titles. The actual order of titles and content is uncertain, however, we need to output the file content in the given order. The Code is as follows:

Public static void readUserInfo () throws InvalidFormatException, IOException {String [] titles = {"billing ID", "billing type", "name", "residential address ", "Work Unit", "phone", "Mobile Phone", "residential building number", "unit number", "floor", "room number", "building area (㎡ )", "area basis", "A area", "A super", "A light", "B area", "B", "B light", "User ID ", "Table number in front of the building"}; // map used to store the title and sequence. key is the title and value is the sequence number Map <String, Integer> titleMap = new HashMap <String, integer> (); // write the specified sequence to map for (int I = 0; I <titles. length; I + +) {TitleMap. put (titles [I], I);} Workbook wb = WorkbookFactory. create (new File ("e:/test/userinfo.xls"); for (int numSheet = 0; numSheet <wb. getNumberOfSheets (); numSheet ++) {Sheet xSheet = wb. getSheetAt (numSheet); if (xSheet = null) {continue;} // obtain the header content of the first Row tRow = xSheet. getRow (0); // array that stores the Title Order Integer [] titleSort = new Integer [tRow. getLastCellNum ()]; // loop title for (int titleNum = 0; titl ENum <tRow. getLastCellNum (); titleNum ++) {Cell tCell = tRow. getCell (titleNum); String title = ""; if (tCell = null | "". equals (tCell) {} else if (tCell. getCellType () = XSSFCell. CELL_TYPE_BOOLEAN) {// boolean type processing // logger.info (xCell. getBooleanCellValue ();} else if (tCell. getCellType () = XSSFCell. CELL_TYPE_NUMERIC) {// title = doubleToString (tCell. getNumericCellValue ();} else {// other types Processing title = tCell. getStringCellValue () ;}// read the forward message number from the map using the obtained title, and write the array Integer ts = titleMap that saves the forward Number of the title. get (title); if (ts! = Null) {titleSort [titleNum] = ts ;}// loop Row for (int rowNum = 1; rowNum <xSheet. getLastRowNum () + 1; rowNum ++) {Row xRow = xSheet. getRow (rowNum); if (xRow = null) {continue;} // Cell String [] v = new String [titleSort. length]; for (int cellNum = 0; cellNum <titleSort. length; cellNum ++) {Cell xCell = xRow. getCell (cellNum); String value = ""; if (xCell = null | "". equals (xCell) {} else if (xCell. getCellType () = XSSFCell. CELL_TYPE_BOOLEAN) {// logger.info (xCell. getBooleanCellValue ();} else if (xCell. getCellType () = XSSFCell. CELL_TYPE_NUMERIC) {// numeric type processing value = doubleToString (xCell. getNumericCellValue ();} else {// other types of processing value = xCell. getStringCellValue () ;}// the number in the order of titles to store each row of records v [titleSort [cellNum] = value; // logger.info ("v [" + titleSort [cellNum] + "] =" + v [titleSort [cellNum]);} // array of cyclic results, the obtained for (int I = 0; I <v. length; I ++) {logger.info (v [I]) ;}}}

The tool class doubleToString used in the previous section (convert the double type in excel to the String type and process the number in the scientific notation form ):

private static String doubleToString(double d) {        String str = Double.valueOf(d).toString();        // System.out.println(str);        String result = "";        if (str.indexOf("E") > 2) {            int index = str.indexOf("E");            int power = Integer.parseInt(str.substring(index + 1));            BigDecimal value = new BigDecimal(str.substring(0, index));            value = value.movePointRight(power);            result = value.toString();        } else {            if (str.indexOf(".0") > 0)                result = str.substring(0, str.indexOf(".0"));            else                result = str;        }        return result;    }

Currently, the application of POI is limited to this and has not been further explored. New related content will be added later. Please criticize and correct it!

Reprinted please indicate the source: http://www.cnblogs.com/bxljoy/p/3939409.html

Who can give me a detailed Java Excel export method through Apache POI, it is best to give the complete code

This is the most common problem during development, such as operating excel. Today I would like to share with you the Apache POI Excel export method. ExportExcel can be directly copied and changed in the past.
The Code is as follows:
01. package com. smnpc. util;

02.

03. import java. io. FileOutputStream;

04. import java. io. IOException;

05. import java. util. Calendar;

06.

07. import org. apache. poi. hssf. usermodel. HSSFCell;

08. import org. apache. poi. hssf. usermodel. HSSFCellStyle;

09. import org. apache. poi. hssf. usermodel. HSSFDataFormat;

10. import org. apache. poi. hssf. usermodel. HSSFRow;

11. import org. apache. poi. hssf. usermodel. HSSFSheet;

12. import org. apache. poi. hssf. usermodel. HSSFWorkbook;

13.

14 ./**

15. * generate an Excel file export object

16 .*
17. * @ author Robert

18 .*
19 .*/

20. public class ExportExcel {

21. // set cell encoding to solve Chinese High-byte Truncation

22. // private static short XLS_ENCODING = HSSFWorkbook. ENCODING_UTF_16;

23. // customize the Date Format

24. private static String DATE_FORMAT = "m/d/yy"; // "m/d/yy h: mm"

25. // custom floating point format

26. private static String NUMBER_FORMAT = "#,## 0.00 ";

27.

28. private String xlsFileName;

29.

30. private HSSFWorkbook workbook;

31.

32. private HSSFSheet sheet;

33.

34. private HSSFRow row;

35.

36 ./**

37. * initialize Excel

38 .*
39. * @ param fileName

40. * export file name

41. * @ return

42 .*/

43. public void XLSExport (String fileName ){

44.this.xls FileName = fileName;

45. this. workbook = new HSSFWorkbook ();

46. this. sheet = workbook. createSheet ();

47 .}

48.

49 ./**

50. * export an Excel file

51 .*
52. * @ throws IO ...... remaining full text>

How can I use java poi to write code to set whether Excel cells are locked?

Import org. apache. poi. hssf. usermodel. HSSFCell;
Import org. apache. poi. hssf. usermodel. HSSFCellStyle;
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;
/**
* @ Param inputFile: Enter the path of the template file.
* @ Param outputFile: the input file is stored in the server path.
* @ Param dataList: Data to be exported
* @ Throws Exception
* @ Roseuid:
*/
Public void exportExcelFile (String inputFile, String outputFile, List dataList) throws Exception
{
// Use a template file to construct a poi
POIFSFileSystem fs = new POIFSFileSystem (new FileInputStream (inputFile ));
// Create a template Worksheet
HSSFWorkbook templatewb = new HSSFWorkbook (fs );
// Directly retrieve the first sheet object of the template
HSSFSheet templateSheet = templatewb. getSheetAt (1 );
// Obtain the first row object of the first sheet of the template in order to get the template Style
HSSFRow templateRow = templateSheet. getRow (0 );

// HSSFSheet timplateSheet = templatewb. getSheetAt (1 );
// Obtain the total number of columns in an Excel file
Int columns = templateSheet. getRow (short) 0). getPhysicalNumberOfCells ();
Debug. println ("columns is:" + columns );
// Create a style array
HSSFCellStyle styleArray [] = new HSSFCellStyle [columns];

// Create all the column styles at one time and put them in the array
For (int s =... the remaining full text>