Apache POI HSSF和XSSF读写EXCEL总结
Apache POI HSSF和XSSF读写EXCEL总结
HSSF是指2007年以前的,XSSF是指2007年版本以上的
这个还是比较好用的,这些总结来自Apache的官方向导的点点滴滴
还有好多没有没有写的，详细的请参考http://poi.apache.org/spreadsheet/quick-guide.html
还有LOG也比较好看
Java代码
public class SummaryHSSF {public static void main(String[] args) throws IOException {//创建Workbook对象（这一个对象代表着对应的一个Excel文件） //HSSFWorkbook表示以xls为后缀名的文件Workbook wb = new HSSFWorkbook();//获得CreationHelper对象,这个应该是一个帮助类CreationHelper helper = wb.getCreationHelper();//创建Sheet并给名字(表示Excel的一个Sheet)Sheet sheet1 = wb.createSheet("HSSF_Sheet_1");Sheet sheet2 = wb.createSheet("HSSF_Sheet_2");//Row表示一行Cell表示一列Row row = null;Cell cell = null;for(int i=0;i<60;i=i+2){//获得这个sheet的第i行row = sheet1.createRow(i);//设置行长度自动//row.setHeight((short)500);row.setHeightInPoints(20);//row.setZeroHeight(true);for(int j=0;j<25;j++){//设置每个sheet每一行的宽度,自动,根据需求自行确定sheet1.autoSizeColumn(j+1, true);//创建一个基本的样式CellStyle cellStyle = SummaryHSSF.createStyleCell(wb);//获得这一行的每j列cell = row.createCell(j);if(j==0){//设置文字在单元格里面的位置cellStyle = SummaryHSSF.setCellStyleAlignment(cellStyle, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);//先创建字体样式,并把这个样式加到单元格的字体里面cellStyle.setFont(createFonts(wb));//把这个样式加到单元格里面cell.setCellStyle(cellStyle);//给单元格设值cell.setCellValue(true);}else if(j==1){//设置文字在单元格里面的位置cellStyle = SummaryHSSF.setCellStyleAlignment(cellStyle, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);//设置这个样式的格式(Format)cellStyle = SummaryHSSF.setCellFormat(helper,cellStyle, "#,##0.0000");//先创建字体样式,并把这个样式加到单元格的字体里面cellStyle.setFont(createFonts(wb));//把这个样式加到单元格里面cell.setCellStyle(cellStyle);//给单元格设值cell.setCellValue(new Double(2008.2008));}else if(j==2){cellStyle = SummaryHSSF.setCellStyleAlignment(cellStyle, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);cellStyle.setFont(createFonts(wb));cell.setCellStyle(cellStyle);cell.setCellValue(helper.createRichTextString("RichString"+i+j));}else if(j==3){cellStyle = SummaryHSSF.setCellStyleAlignment(cellStyle, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);cellStyle = SummaryHSSF.setCellFormat(helper,cellStyle, "MM-yyyy-dd");cell.setCellStyle(cellStyle);cell.setCellValue(new Date());}else if(j==24){cellStyle = SummaryHSSF.setCellStyleAlignment(cellStyle, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);cellStyle.setFont(createFonts(wb));//设置公式cell.setCellFormula("SUM(E"+(i+1)+":X"+(i+1)+")");}else{cellStyle = SummaryHSSF.setCellStyleAlignment(cellStyle, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);cellStyle = SummaryHSSF.setFillBackgroundColors(cellStyle,IndexedColors.ORANGE.getIndex(),IndexedColors.ORANGE.getIndex(),CellStyle.SOLID_FOREGROUND);cell.setCellStyle(cellStyle);cell.setCellValue(1);}}}//输出OutputStream os = new FileOutputStream(new File("c://SummaryHSSF.xls"));wb.write(os);os.close();}/** * 边框 * @param wb * @return */public static CellStyle createStyleCell(Workbook wb){CellStyle cellStyle = wb.createCellStyle();//设置一个单元格边框颜色cellStyle.setBorderBottom(CellStyle.BORDER_THIN);cellStyle.setBorderTop(CellStyle.BORDER_THIN);cellStyle.setBorderLeft(CellStyle.BORDER_THIN);cellStyle.setBorderRight(CellStyle.BORDER_THIN);//设置一个单元格边框颜色cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());return cellStyle;}/** * 设置文字在单元格里面的位置 * CellStyle.ALIGN_CENTER * CellStyle.VERTICAL_CENTER * @param cellStyle * @param halign * @param valign * @return */public static CellStyle setCellStyleAlignment(CellStyle cellStyle,short halign,short valign){//设置上下cellStyle.setAlignment(CellStyle.ALIGN_CENTER);//设置左右cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);return cellStyle;}/** * 格式化单元格 * 如#,##0.00,m/d/yy去HSSFDataFormat或XSSFDataFormat里面找 * @param cellStyle * @param fmt * @return */public static CellStyle setCellFormat(CreationHelper helper,CellStyle cellStyle,String fmt){//还可以用其它方法创建formatcellStyle.setDataFormat(helper.createDataFormat().getFormat(fmt));return cellStyle;}/** * 前景和背景填充的着色 * @param cellStyle * @param bg IndexedColors.ORANGE.getIndex(); * @param fg IndexedColors.ORANGE.getIndex(); * @param fp CellStyle.SOLID_FOREGROUND * @return */public static CellStyle setFillBackgroundColors(CellStyle cellStyle,short bg,short fg,short fp){//cellStyle.setFillBackgroundColor(bg);cellStyle.setFillForegroundColor(fg);cellStyle.setFillPattern(fp);return cellStyle;}/** * 设置字体 * @param wb * @return */public static Font createFonts(Workbook wb){//创建Font对象Font font = wb.createFont();//设置字体font.setFontName("黑体");//着色font.setColor(HSSFColor.BLUE.index);//斜体font.setItalic(true);//字体大小font.setFontHeight((short)300);return font;}}public class ReadExcel {public static void main(String[] args) throws Exception {InputStream is = new FileInputStream(new File("c://SummaryHSSF.xls"));//根据输入流创建Workbook对象Workbook wb = WorkbookFactory.create(is);//get到Sheet对象Sheet sheet = wb.getSheetAt(0);//这个必须用接口for(Row row : sheet){for(Cell cell : row){//cell.getCellType是获得cell里面保存的值的type//如Cell.CELL_TYPE_STRINGswitch(cell.getCellType()){case Cell.CELL_TYPE_BOOLEAN://得到Boolean对象的方法System.out.print(cell.getBooleanCellValue()+" ");break;case Cell.CELL_TYPE_NUMERIC://先看是否是日期格式if(DateUtil.isCellDateFormatted(cell)){//读取日期格式System.out.print(cell.getDateCellValue()+" ");}else{//读取数字System.out.print(cell.getNumericCellValue()+" ");}break;case Cell.CELL_TYPE_FORMULA://读取公式System.out.print(cell.getCellFormula()+" ");break;case Cell.CELL_TYPE_STRING://读取StringSystem.out.print(cell.getRichStringCellValue().toString()+" ");break;}}System.out.println("");}}}Sheet sheet = wb.getSheetAt(0);for (Iterator rit = sheet.rowIterator(); rit.hasNext(); ) {Row row = (Row)rit.next();for (Iterator cit = row.cellIterator(); cit.hasNext(); ) {Cell cell = (Cell)cit.next();// Do something here}}HSSFSheet sheet = wb.getSheetAt(0);for (Iterator<HSSFRow> rit = (Iterator<HSSFRow>)sheet.rowIterator(); rit.hasNext(); ) {HSSFRow row = rit.next();for (Iterator<HSSFCell> cit = (Iterator<HSSFCell>)row.cellIterator(); cit.hasNext(); ) {HSSFCell cell = cit.next();// Do something here}}