Introduction to POI
Apache POI is the Open Source Function Library of Apache Software Foundation, which provides API for Java programs to read and write files in Microsoft Office format.
II. Overview of HSSF
HSSF is the abbreviation of Horrible SpreadSheet Format. Through HSSF, you can read, write and modify Excel files with pure Java code. HSSF provides two kinds of API s for reading operations: user model and eventuser model, namely "user model" and "event-user model".
III. POI EXCEL Document Structure Class
HSSF Workbook Excel Document Object
sheet of HSSFSheet excel
Rows of HSSFRow excel
Cells of HSSFCell excel
HSSFFont excel font
HSSFName name
HSSFDataFormat date format
HSSF Header sheet header
HSSFFooter sheet tail
HSSFCellStyle cell Style
HSSFDateUtil Date
HSSFPrintSetup Printing
HSSFErrorConstants Error Information Table
4. Common operating methods of EXCEL
1. Get Excel Commonly Used Objects ____________
POIFSFileSystem fs=newPOIFSFileSystem(new FileInputStream("d:/test.xls"));
//Get Excel workbook objects
HSSFWorkbook wb = new HSSFWorkbook(fs);
//Get Excel worksheet objects
HSSFSheet sheet = wb.getSheetAt(0);
//Get rows for Excel worksheets
HSSFRow row = sheet.getRow(i);
//Get the cell for the row specified in the Excel worksheet
HSSFCell cell = row.getCell((short) j);
//Get the cell style.
cellStyle = cell.getCellStyle();2. Establishing Excel Common Objects
//Create Excel workbook objects
HSSFWorkbook wb = new HSSFWorkbook();
//Create Excel worksheet objects
HSSFSheet sheet = wb.createSheet("new sheet");
//Create rows for Excel worksheets
HSSFRow row = sheet.createRow((short)0);
//Create Cell Styles
cellStyle = wb.createCellStyle();
//Create Excel worksheet cells with specified rows
row.createCell((short)0).setCellStyle(cellStyle);
//Setting the value of Excel worksheet
row.createCell((short)0).setCellValue(1);3. Setting sheet name and cell content
wb.setSheetName(1, "First sheet",HSSFCell.ENCODING_UTF_16);
cell.setEncoding((short) 1);
cell.setCellValue("Cell content"); 4. Number of sheet s obtained
wb.getNumberOfSheets()
5. Obtaining sheet objects according to index
HSSFSheet sheet = wb.getSheetAt(0);
6. Get the number of valid rows
int rowcount = sheet.getLastRowNum();
7. Get the number of valid cells in a row
row.getLastCellNum();
8. Reading and Writing of Cell Value Types
cell.setCellType(HSSFCell.CELL_TYPE_STRING); //Set the cell to STRING type cell.getNumericCellValue();//Read the cell contents as numeric types
9. Setting column width and row height
sheet.setColumnWidth((short)column,(short)width); row.setHeight((short)height);
10. Add regions and merge cells
//Merge columns from rowFrom row columnFrom Region region = new Region((short)rowFrom,(short)columnFrom,(short)rowTo ，(short)columnTo); // The area to rowTo row columnTo sheet.addMergedRegion(region); //Get all regions sheet.getNumMergedRegions()
11. Save Excel files
FileOutputStream fileOut = new FileOutputStream(path); wb.write(fileOut);
12. Return string values based on different attributes of cells
public String getCellStringValue(HSSFCell cell) {
String cellValue = "";
switch (cell.getCellType()) {
//String type
case HSSFCell.CELL_TYPE_STRING:
cellValue = cell.getStringCellValue();
if(cellValue.trim().equals("")||cellValue.trim().length()<=0)
cellValue=" ";
break;
//value type
case HSSFCell.CELL_TYPE_NUMERIC:
cellValue = String.valueOf(cell.getNumericCellValue());
break;
//formula
case HSSFCell.CELL_TYPE_FORMULA:
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
cellValue=" ";
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
break;
case HSSFCell.CELL_TYPE_ERROR:
break;
default:
break;
}
return cellValue;
} 13. Commonly Used Cell Border Format
HSSFCellStyle style = wb.createCellStyle(); //bottom style.setBorderBottom(HSSFCellStyle.BORDER_DOTTED); //border-left style.setBorderLeft(HSSFCellStyle.BORDER_DOTTED); //border-right style.setBorderRight(HSSFCellStyle.BORDER_THIN); //border-top style.setBorderTop(HSSFCellStyle.BORDER_THIN);
14. Setting Font and Content Location
HSSFFont f = wb.createFont();
//Font size
f.setFontHeightInPoints((short) 11);
//Thickening
f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
style.setFont(f);
//Left and right center
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//Center up and down
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//Rotation angle of cell content
style.setRotation(short rotation);
HSSFDataFormat df = wb.createDataFormat();
//Setting Cell Data Format
style1.setDataFormat(df.getFormat("0.00%"));
//Formula cells
cell.setCellFormula(string);
//Rotation angle of cell content
style.setRotation(short rotation); 15. Insert pictures
//Put the read image in a ByteArray Output Stream to generate ByteArray
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
BufferedImage bufferImg = ImageIO.read(new File("ok.jpg"));
ImageIO.write(bufferImg,"jpg",byteArrayOut);
//Read in an excel template
FileInputStream fos = new FileInputStream(filePathName+"/stencil.xlt");
fs = new POIFSFileSystem(fos);
//Create A Workbook
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0,0,1023,255,(short) 0,0,(short)10,10);
patriarch.createPicture(anchor , wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYPE_JPEG));
16. Adjusting the position of the worksheet
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("format sheet");
HSSFPrintSetup ps = sheet.getPrintSetup();
sheet.setAutobreaks(true);
ps.setFitHeight((short)1);
ps.setFitWidth((short)1); 17. Setting up Printing Area
HSSFSheet sheet = wb.createSheet("Sheet1");
wb.setPrintArea(0, "$A$1:$C$2"); 19. Clean up the row data in the worksheet and adjust the row position
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("row sheet");
// Create various cells and rows for spreadsheet.
// Shift rows 6 - 11 on the spreadsheet to the top (rows 0 - 5)
sheet.shiftRows(5, 10, -5); 20. Select the specified worksheet
HSSFSheet sheet = wb.createSheet("row sheet");
heet.setSelected(true);21. Scale-up and Reduction of Worksheets
HSSFSheet sheet1 = wb.createSheet("new sheet");
// 50 percent magnification
sheet1.setZoom(1,2); 22, Headnote and Footnote
HSSFSheet sheet = wb.createSheet("new sheet");
HSSFHeader header = sheet.getHeader();
header.setCenter("Center Header");
header.setLeft("Left Header");
header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") +
HSSFHeader.fontSize((short) 16) + "Right w/ Stencil-Normal Italic font and size 16"); 23. Custom Colors
HSSFCellStyle style = wb.createCellStyle(); style.setFillForegroundColor(HSSFColor.LIME.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont font = wb.createFont(); font.setColor(HSSFColor.RED.index); style.setFont(font); cell.setCellStyle(style);
24. Filling and color settings
HSSFCellStyle style = wb.createCellStyle();
style.setFillBackgroundColor(HSSFColor.AQUA.index);
style.setFillPattern(HSSFCellStyle.BIG_SPOTS);
HSSFCell cell = row.createCell((short) 1);
cell.setCellValue("X");
style = wb.createCellStyle();
style.setFillForegroundColor(HSSFColor.ORANGE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cell.setCellStyle(style); 25. Force refresh cell formula
HSSFFormulaEvaluator eval=new HSSFFormulaEvaluator((HSSFWorkbook) wb);
private static void updateFormula(Workbook wb,Sheet s,int row){
Row r=s.getRow(row);
Cell c=null;
FormulaEcaluator eval=null;
if(wb instanceof HSSFWorkbook)
eval=new HSSFFormulaEvaluator((HSSFWorkbook) wb);
else if(wb instanceof XSSFWorkbook)
eval=new XSSFFormulaEvaluator((XSSFWorkbook) wb);
for(int i=r.getFirstCellNum();i
c=r.getCell(i);
if(c.getCellType()==Cell.CELL_TYPE_FORMULA)
eval.evaluateFormulaCell(c);
}
} Explain:
Formula Evaluator provides the evaluateFormulaCell(Cell cell) method, which calculates the formula to preserve the results, but does not change the formula. The evaluateInCell(Cell cell) method is the calculation formula, and replaces the original formula with the calculation result. That is to say, the cell type is not Cell.CELL_TYPE_FORMULA, but Cell.CELL_TYPE_NUMBERIC. HSSFFormula Evaluator provides a static method, evaluateAllFormulaCells(HSSFWorkbook wb), to calculate all the formulas of an Excel file, which is very convenient to use.
The summary of poi method - --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1. Settings do not display excel gridlines
//Where sheet is the Sheet object sheet.setDisplayGridlines(false);
2. Setting content line breaks in excel cells
cellStyle.setWrapText(true);
CellStyle is a CellStyle object created by WorkBook. CellStyle is then set to the Cell object to be replaced. Finally "/r/n" is added to the object to be replaced (usually a string). Such as
topTile.append("/r/n" +"cellContent");
3. Combination of cells
//This example combines four rows and two columns sheet.addMergedRegion(new CellRangeAddress(0, 4, 0, 2));
4. Set the number of pages in the header and footer.
HSSFHeader header = sheet.getHeader();
header.setCenter("Center Header");
header.setLeft("Left Header");
header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") +
HSSFHeader.fontSize((short) 16) + "Right w/ Stencil-Normal Italic font and size 16");
HSSFFooter footer = (HSSFFooter )sheet.getFooter()
footer.setRight( "Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages() );
5. Make a Sheet fit for a page.
sheet.setAutobreaks(true);
6. Set the zoom attribute (Zoom is specified as a score, for example, 75% of the following uses 3 as a molecule and 4 as a denominator)
sheet.setZoom(3,4);
7. Setting Print
HSSFPrintSetup print = (HSSFPrintSetup) sheet.getPrintSetup(); //Setting up horizontal printing print.setLandscape(true); //Set print zoom to 70% print.setScale((short) 70); //Set to A4 Paper print.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); //Set the printing order first row then column, default is first row print.setLeftToRight(true); //Set the zoom to 10 pages high. print.setFitHeight((short) 10); //Set zoom to width and height. print.setFitWidth((short) 10); sheet.setAutobreaks(false); //Set Page Printing Every 30 Lines if (i != 0 && i % 30 == 0) sheet.setRowBreak(i);
8. Repeated rows and columns (setting print headings)
HSSFWorkbook wb = new HSSFWorkbook(); //Set columns 1 to 12, lines 1 to 6, and print each page repeatedly. wb.setRepeatingRowsAndColumns(0, 0, 12, 1, 6);
9. Adjust cell width
sheet.setAutobreaks(true); //Set cell length sheet.setColumnWidth((short)i,colsWidth[i]); //Automatically adjust cell length according to length sheet.autoSizeColumn((short) i);
Reprint: http://blog.sina.com.cn/s/blog_91c0fdb50101kfd4.html