POI 读取EXCEL文件及一些简单的格式设置。
public class ExcelBook?
{
??? private java.io.FileInputStream inputStream;
??? private FileOutputStream outputStream;
??? private org.apache.poi.hssf.usermodel.HSSFWorkbook workBook;
??? private org.apache.poi.hssf.usermodel.HSSFSheet workSheet;
??? public ExcelBook()
??? {
?????? inputStream = new java.io.FileInputStream(templateFileName);
?????? org.apache.poi.poifs.filesystem.POIFSFileSystem fileSystem = new? org.apache.poi.poifs.filesystem.POIFSFileSystem(inputStream);
?????? try
?????? {
???????? //得到Excel工作簿对象
//??????? workBook = (org.apache.poi.hssf.usermodel.HSSFWorkbook)org.apache.poi.ss.usermodel.WorkbookFactory.create(fileSystem);
????????? workBook = new org.apache.poi.hssf.usermodel.HSSFWorkbook(fileSystem);
?????? }
?????? catch(java.io.IOException e)
?????? {
????????? System.out.println();
?????? }
?????? finally
?????? {
????????? if(inputStream != null)
????????? {
???????????? inputStream.close();?????
????????? }
?????? }
???????? //得到Excel工作表对象
//?????? workSheet = getSheet(java.lang.String name);
?????? workSheet = getSheetAt(int index);
??????//得到Excel工作表的行
????? HSSFRow row =?workSheet.getRow(i);
????? //得到Excel工作表指定行的单元格
????? HSSFCell cell = row.getCell(j);
????? //取得有效行数
???? int? rowCount = workSheet.getLastRowNum();
???? //取得一行的有效单元格数
????? int? cellCount?= row.getLastCellNum();
????? //设定行高，列宽
???? workSheet.setColumnWidth((short)column,(short)width);
???? row.setHeight((short)heigtt);
?????? ......
????? HSSFCellStyle style = workBook.createCellStyle();//创建样式对象
??? ?//设置字体
????? HSSFFont font = workBook.createFont();//创建字体对象
????? font.setFontHeightInPoints((short)15);//设置字体大小
????? font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//设置粗体
????? font.setFontName("黑体");//设置为黑体字
????? style.setFont(font);//将字体加入到样式对象
????? //设置对齐方式
????? style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);//水平居中
????? style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
???? ?//设置边框
????? style.setBorderTop(HSSFCellStyle.BORDER_THICK);//顶部边框粗线
????? style.setTopBorderColor(HSSFColor.RED.index);//设置为红色
????? style.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);//底部边框双线
????? style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);//左边边框
????? style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);//右边边框
????? //格式化日期
????? style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
????? HSSFCell cell = row.createCell(1);//创建单元格
????? cell.setCellValue(new Date());//写入当前日期
????? cell.setCellStyle(style);//应用样式对象
????? //添加区域，合并单元格
????? Region region = new Region(rowFrom,columnFrom,rowTo,columnTo);
???? //工作表的放大，缩小
????? workSheet.setZoom(3,4) ; //75%
//????? Excel中公式不能自动计算出来的问题
//????? ?HSSFCell cell1 = row.getCell((short)1);
//????? ?if (HSSFCell.CELL_TYPE_FORMULA == cell1.getCellType()) {
//??? ??//取得公式单元格的公式,重新设置
//????? ??cell1.setCellFormula(cell1.getCellFormula());
//????? ?}
????? //强制刷新单元格公式
????? HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(workBook);
??????eval.evaluateFormulaCell(Cell cell); //记算公式，保存结果，但不改变公式。
????? //强制刷新Excel文件的所有公式
????? HSSFFormulaEvaluator.evaluateAllFormulaCells(workBook);
????? ......
?????//文件输出流
????? workSheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 7));
????? workSheet.addMergedRegion(new CellRangeAddress(2, 2, 5, 7));
????? workSheet.addMergedRegion(new CellRangeAddress(3, 3, 1, 3));
?
?????? //TODO
?????? ......
?????? try
?????? {
????????? outputStream = new java.io.FileOutputStream(outFileName);
????????? workBook.write(java.io.OutputStream stream)?
?????? }
?????? catch(java.io.IOException e)
?????? {
????????? System.out.println();
?????? }
?????? finally
?????? {
????????? if(outputStream != null)
????????? {
???????????? outputStream.close();?????
????????? }
?????? }
??? }
}
?
转载自：http://hi.baidu.com/%D3%A3%BB%A8%D4%B0_%C9%BD%CE%F7/blog/item/42ac6ed6ff9484ce8d10291b.html
