Java poi excel operation example

[Java] import java. io. fileOutputStream; import java. io. IOException; import java. util. calendar; import java. util. date; import java. util. iterator; import org. apache. poi. hssf. usermodel. HSSFHeader; import org. apache. poi. hssf. usermodel. HSSFWorkbook; import org. apache. poi. ss. usermodel. cell; import org. apache. poi. ss. usermodel. cellStyle; import org. apache. poi. ss. usermodel. clientAnchor; import org. apache. po I. ss. usermodel. comment; import org. apache. poi. ss. usermodel. creationHelper; import org. apache. poi. ss. usermodel. dataFormat; import org. apache. poi. ss. usermodel. dateUtil; import org. apache. poi. ss. usermodel. drawing; import org. apache. poi. ss. usermodel. font; import org. apache. poi. ss. usermodel. header; import org. apache. poi. ss. usermodel. name; import org. apache. poi. ss. usermodel. richTextString; import org. ap Ache. poi. ss. usermodel. row; import org. apache. poi. ss. usermodel. sheet; import org. apache. poi. ss. usermodel. workbook; import org. apache. poi. ss. util. cellRangeAddress; import org. apache. poi. ss. util. cellReference; import org. apache. poi. xssf. usermodel. XSSFWorkbook; // http://poi.apache.org/spreadsheet/quick-guide.html public class ExcelParseUtil {public static void test () throws IOException {Workbook wb = New HSSFWorkbook (); FileOutputStream fileOut = new FileOutputStream ("E: \ logs \ workbook.xls"); Sheet sheet1 = wb. createSheet ("new sheet"); Sheet sheet2 = wb. createSheet ("second sheet"); // set the sheet Header header = sheet1.getHeader (); header. setCenter ("Center Header"); header. setLeft ("Left Header"); header. setRight (HSSFHeader. font ("stenpencil-Normal", "Italic") + HSSFHeader. fontSize (short) 16) + "Right w/stencel-Normal Italic font and size 16"); // merge the cells sheet1.groupRow (5, 14); sheet1.groupRow (7, 14); sheet1.groupRow (16, 19 ); sheet1.groupColumn (short) 4, (short) 7); sheet1.groupColumn (short) 9, (short) 12); sheet1.groupColumn (short) 10, (short) 11 ); sheet1.addMergedRegion (new CellRangeAddress (1, // first row (0-based) 1, // last row (0-based) 1, // first column (0-based) 2 // last co Lumn (0-based); // sets the image // Create the drawing patriarch. this is the top level container for all // shapes. // Drawing drawing = sheet1.createDrawingPatriarch (); // add a picture shape // ClientAnchor anchor = helper. createClientAnchor (); // set top-left corner of the picture, /// subsequent call of Picture # resize () will operate relative to it // anchor. setCol1 (3); // anchor. setRow1 (2 ); // Picture pict = drawing. createPicture (anchor, pictureIdx); // auto-size picture relative to its top-left corner // pict. resize (); String sname = "TestSheet", cname = "TestName", cvalue = "TestVal"; Name namedCell = wb. createName (); namedCell. setNameName (cname); String reference = sname + "! A1: A1 "; // area reference namedCell. setRefersToFormula (reference); // 2. create named range for a single cell using cellreference // Name namedCel2 = wb. createName (); // namedCel2.setNameName (cname); // reference = sname + "! A1 "; // cell reference // namedCel2.setRefersToFormula (reference); // 3. create named range for an area using AreaReference // Name namedCel3 = wb. createName (); // namedCel3.setNameName (cname); // reference = sname + "! A1: C5 "; // area reference // namedCel3.setRefersToFormula (reference); // 4. create named formula // Name namedCel4 = wb. createName (); // namedCel4.setNameName ("my_sum"); // namedCel4.setRefersToFormula ("SUM (sname +! $ I $2: $ I $6 )"); // Note that sheet name is Excel must not exceed 31 characters // and must not contain any of the following characters: // 0x0000 // 0x0003 // colon (:) // backslash (\) // asterisk (*) // question mark (?) // Forward slash (/) // opening square bracket ([) // closing square bracket (]) // You can use // org. apache. poi. ss. util. workbookUtil # createSafeSheetName (String // nameProposal)} // for a safe way to create valid names, this utility replaces invalid // characters with a space ('') "// set the cell value // Create a row and put some cells in it. rows are 0 based. row row = sheet1.createRow (short) 0); // Crea Te a cell and put a value in it. cell cell = row. createCell (0); cell. setCellValue (1); // Or do it on one line. row. createCell (1 ). setCellValue (1.2); row. createCell (2 ). setCellValue ("This is a string"); row. createCell (3 ). setCellValue (true); Row row2 = sheet2.createRow (0); // Create a cell and put a date value in it. the first cell is not // styled // as a date. cell cell2 = row2.createCell (0); cell2. SetCellValue (new Date (); // 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 other ting not only this cell but // other cells. // set the cell Date Format CellStyle cellStyle = wb. createCellStyle (); DataFormat format = wb. createDataFormat (); cellStyle. setDataFormat (format. getFormat ("yyyy/mm/d D "); // style. setDataFormat (format. getFormat ("#,## 0.0000"); // set the cell Font font = wb. createFont (); font. setFontHeightInPoints (short) 24); font. setFontName ("Courier New"); font. setItalic (true); font. setStrikeout (true); font. setBoldweight (Font. BOLDWEIGHT_BOLD); cellStyle. setFont (font); cellStyle. setWrapText (true); cell2 = row2.createCell (1); cell2.setCellValue (new Date (); cell2.setCellStyle (cell Style); // you can also set date as java. util. calendar cell2 = row2.createCell (2); cell2.setCellValue (Calendar. getInstance (); cell2.setCellStyle (cellStyle); // specify parameters // Create the comment and set the text + author CreationHelper factory = wb. getCreationHelper (); // When the comment box is visible, have it show in a 1x3 space ClientAnchor anchor = factory. createClientAnchor (); anchor. setCol1 (cell. getCo LumnIndex (); anchor. setCol2 (cell. getColumnIndex () + 1); anchor. setRow1 (row. getRowNum (); anchor. setRow2 (row. getRowNum () + 3); Drawing drawing = sheet1.createDrawingPatriarch (); Comment comment = drawing. createCellComment (anchor); RichTextString str = factory. createRichTextString ("Hello, World! "); Comment. setString (str); comment. setAuthor ("Apache POI"); // Assign the comment to the cell. setCellComment (comment); // Hyperlink link = createHelper. createHyperlink (Hyperlink. LINK_URL); // link. setAddress ("http://poi.apache.org/"); // cell. setHyperlink (link); // cell. setCellStyle (cellStyle); Row row3 = sheet2.createRow (short) 2); row3.createCell (0 ). setCellValue (1.1); row3.createCell (1 ). setCellValue (new Date (); row3.createCell (2 ). setCellValue (Calendar. getInstance (); row3.createCell (3 ). setCellValue ("a string"); row3.createCell (4 ). setCellValue (true); www.2cto.com row3.createCell (5 ). setCellType (Cell. CELL_TYPE_ERROR); // obtain the value of each cell in sheet. Sheet sheet = wb. getSheetAt (0); for (Iterator <Row> rit = sheet. rowIterator (); rit. hasNext ();) {Row row4 = rit. next (); for (Iterator <Cell> cit = row4.cellIterator (); cit. hasNext ();) {Cell cell4 = cit. next (); // do something here} Sheet sheet5 = wb. getSheetAt (1); for (Row row5: sheet5) {for (Cell cell5: row5) {CellReference cellRef = new CellReference (row5.getRowNum (), cell5.getColumnIndex (); System. out. print (cellRef. formatAsString (); System. out. print ("-"); switch (cell5.getCellType () {case Cell. CELL_TYPE_STRING: System. out. println (cell5.getRichStringCellValue (). getString (); break; case Cell. CELL_TYPE_NUMERIC: if (DateUtil. isCellDateFormatted (cell5) {System. out. println (cell5.getDateCellValue ();} else {System. out. println (cell5.getNumericCellValue ();} break; case Cell. CELL_TYPE_BOOLEAN: System. out. println (cell5.getBooleanCellValue (); break; case Cell. CELL_TYPE_FORMULA: System. out. println (cell5.getCellFormula (); break; default: System. out. println () ;}} wb. write (fileOut); fileOut. close ();} public void test2 () throws IOException {Workbook wb = new XSSFWorkbook (); FileOutputStream fileOut = new FileOutputStream ("workbook.xlsx"); wb. write (fileOut); fileOut. close ();} public static void main (String [] args) throws IOException {test () ;}} output: [java] A1-41258.65454309028 B1-Sat Dec 15 15:42:32 CST 2012 C1-Sat Dec 15 15:42:32 CST 2012 A3-1.1 B3-41258.65454322917 C3-41258.65454322917 D3-a string E3-true F3-