Apache POI HSSF的简单使用
java 代码[*]1.生成(Excel)Workbook对象
[*] 创建HSSFWorkbook对象
[*] 创建 HSSFSheet 对象
[*] 创建HSSFRow对象
[*] 创建HSSFCell对象
[*]
[*]2.将Workbook对象写入文件
[*]
[*] FileOutputStream fout = new FileOutputStream("t" + "sfname"); //文件名
[*] ewb.write(fout);
[*] fout.close();
[*]
[*]
[*]
[*]//create new excel file and return the file's name
[*] public String getMyExcel(String strsql) {
[*] String sfname = "";
[*] sfname = this.getUniqueFilename();
[*]
[*] if (null == sfname || sfname.trim().equals("")) {
[*] return ""; //error:getUniqueFilename failed.
[*] }
[*]
[*] //strsql = "select aac001,aac002,aac003,aac004,aae036 from ac01 where datediff(yy,aae036,getdate()) = 0 ";
[*] if (null == strsql || strsql.trim().equals("")) {
[*] return ""; //
[*] }
[*]
[*] Connection conn = null;
[*] PreparedStatement pst = null;
[*] ResultSet rs = null;
[*]
[*] try {
[*] conn = getConnection();
[*] pst = conn.prepareStatement(strsql);
[*] rs = pst.executeQuery();
[*]
[*] if (null == rs) {
[*] return ""; //error.
[*] }
[*]
[*] boolean bHave = rs.next();
[*] if (!bHave) {
[*] return ""; //no record.
[*] }
[*]
[*] HSSFWorkbook ewb = new HSSFWorkbook();
[*] HSSFSheet esh = ewb.createSheet("list01");
[*] HSSFRow erow = null;
[*] HSSFCell ecell = null;
[*]
[*] //HSSFFont
[*] HSSFFont numFont = ewb.createFont();
[*] //font.setFontHeightInPoints((short)24);
[*] numFont.setFontName("Courier New");
[*]
[*] //song
[*] HSSFFont font = ewb.createFont();
[*] font.setFontHeightInPoints( (short) 12);
[*] font.setFontName("宋体");
[*] //font.setItalic(true);
[*]
[*] // Fonts are set into a style so create a new one to use.
[*] HSSFCellStyle style = ewb.createCellStyle();
[*] style.setFont(font);
[*]
[*] //HSSFCellStyle
[*] HSSFCellStyle cellNumStyle = ewb.createCellStyle();
[*] cellNumStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
[*] cellNumStyle.setFont(font);
[*] cellNumStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));
[*] //HSSFDataFormat df = wb.createDataFormat();
[*] //style.setDataFormat(df.getFormat("#,##0.00"));
[*]
[*] HSSFCellStyle cellTxtStyle = ewb.createCellStyle();
[*] cellTxtStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
[*] cellTxtStyle.setFont(font);
[*] cellTxtStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
[*]
[*] HSSFCellStyle cellDateStyle = ewb.createCellStyle();
[*] cellDateStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
[*] cellDateStyle.setFont(font);
[*] cellDateStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(
[*] "m/d/yy h:mm"));
[*]
[*] int currow = 0; //当前行
[*]
[*] //生成列标题
[*] int colNum = 0; //列
[*] ResultSetMetaData rsmd = rs.getMetaData();
[*] colNum = rsmd.getColumnCount();
[*]
[*] erow = esh.createRow(currow++); //标题行
[*] for (short j = 0; j < colNum; j++) {
[*] ecell = erow.createCell(j);
[*] ecell.setEncoding(HSSFCell.ENCODING_UTF_16);
[*] ecell.setCellValue(rsmd.getColumnName(j + 1));
[*] ecell.setCellStyle(cellTxtStyle);
[*]
[*] }
[*]
[*] //详细列表
[*] while (bHave) { //get row.
[*]
[*] //create row.
[*] erow = esh.createRow(currow);
[*]
[*] for (short i = 0; i < colNum; i++) {
[*] int dataType = rsmd.getColumnType(i + 1);
[*]
[*] //create cell.
[*] ecell = erow.createCell(i);
[*] ecell.setEncoding(HSSFCell.ENCODING_UTF_16);
[*]
[*] switch (dataType) {
[*] case Types.NUMERIC:
[*] ecell.setCellValue(rs.getDouble(i + 1));
[*] ecell.setCellStyle(cellNumStyle);
[*] break;
[*] case Types.CHAR:
[*] case Types.LONGVARCHAR:
[*] case Types.VARCHAR:
[*] ecell.setCellValue(rs.getString(i + 1));
[*] ecell.setCellStyle(cellTxtStyle);
[*] break;
[*] case Types.DATE:
[*] ecell.setCellValue(rs.getDate(i + 1));
[*] ecell.setCellStyle(cellDateStyle);
[*] break;
[*] case Types.TIME:
[*] ecell.setCellValue(rs.getTime(i + 1));
[*] ecell.setCellStyle(cellDateStyle);
[*] break;
[*] case Types.TIMESTAMP:
[*] ecell.setCellValue(rs.getTimestamp(i + 1));
[*] ecell.setCellStyle(cellDateStyle);
[*] break;
[*] default:
[*] ecell.setCellValue(rs.getString(i + 1));
[*] ecell.setCellStyle(cellTxtStyle);
[*]
[*] }
[*] }
[*]
[*] bHave = rs.next();
[*] currow++;
[*] }
[*]
[*] FileOutputStream fout = new FileOutputStream("t" + "sfname");
[*] ewb.write(fout);
[*] fout.close();
[*] }
[*] catch (Exception sqle) {
[*] //
[*] sqle.printStackTrace();
[*] return "";
[*] }
[*] finally {
[*] try {
[*] if (null != rs) {
[*] rs.close();
[*] rs = null;
[*] }
[*]
[*] if (null != pst) {
[*] pst.close();
[*] pst = null;
[*] }
[*] if (null != conn) {
[*] conn.close();
[*] conn = null;
[*] }
[*]
[*] }
[*] catch (Exception exx) {
[*] //
[*] }
[*] }
[*]
[*] return sfname;
[*] }
[*]
[*]
[*] 设置单元格样式
HSSFCellStyle类代表一种单元格样式。可以通过这个类来设置单元格的边框样式、背景颜色、字体、水平和垂直对齐方式等等。
HSSFCellStyle titleStyle = workbook.createCellStyle();
titleStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);
titleStyle.setBorderLeft((short)1);
titleStyle.setBorderRight((short)1);
titleStyle.setBorderTop(HSSFCellStyle.BORDER_DOUBLE);
titleStyle.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);
titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
注意：如果我们定义了一种样式，把它赋给一些单元格。然后基于新的需要，更改该样式中的某个属性，再赋给另一些单元格。那么之前单元格样式的该属性也会被同时更改。
比如我们定义了样式，设置单元格背景色为红色：
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFillForegroundColor(HSSFColor.RED.index);
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
然后把它赋给一个单元格：
HSSFCell cell1 = row.createCell((short)1);
cell1.setCellStyle(cellStyle);
然后更改样式中的背景色属性为蓝色：
cellStyle.setFillForegroundColor(HSSFColor.BLUE.index);
然后赋给另一个单元格：
HSSFCell cell2 = row.createCell((short)2);
cell2.setCellStyle(cellStyle);
想当然，我们预计在最终结果中cell1的背景色为红色，cell2的背景色为蓝色。但是结果是：两个单元格的背景色都变成了蓝色。
遇到这种情况，要预先定义两种不同的单元格样式。
当一个EXCEL文件同时需要很多大同小异的单元格样式时，这样一一定义很麻烦。POI HSSF提供了一个HSSFCellUtil类（在org.apache.poi.hssf.usermodel.contrib包），里面有几个方法可以绕过HSSFCellStyle直接设定单元格的样式，但这几个方法会抛出NestableException异常，要处理这个异常，需要引用Apache的几个Common包：
commons-beanutils.jar
commons-beanutils-bean-collections.jar
commons-beanutils-core.jar
commons-lang.jar
commons-logging-api.jar
合并单元格
HSSFSheet.addMergedRegion(new Region())方法可以合并单元格，Region()中的一个构造函数含有四个参数，分别代表起始行、起始列、结束行、结束列：
sheet.addMergedRegion(new Region(startRow,startCell,endRow,endCell)));
处理公式
HSSFCell.setCellFormula()方法用来在EXCEL单元格中写入公式。
cell = row.createCell((short)(dataFlag));
cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula("SUM("A1:D1")");
cell.setCellStyle(nameStyle);
处理链接
在POI中往单元格中写链接，是用HYPERLINK函数搞定的。
HYPERLINK函数包含两个参数，第一个参数是指向的URL地址，第二个参数是显示字串。
cell = row.createCell((short)(dataFlag));
cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula("HYPERLINK('链接地址http...')");
cell.setCellStyle(linkStyle);
为了使链接效果更好，我们可以给链接所在单元格定义一种样式，使链接显示为有下划线的蓝色字串：
HSSFCellStyle linkStyle = workbook.createCellStyle();
linkStyle.setBorderBottom((short)1);
linkStyle.setBorderLeft((short)1);
linkStyle.setBorderRight((short)1);
linkStyle.setBorderTop((short)1);
linkStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
linkStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
HSSFFont font = workbook.createFont();
font.setFontName(HSSFFont.FONT_ARIAL);
font.setUnderline((byte)1);
font.setColor(HSSFColor.BLUE.index);
linkStyle.setFont(font);
中文处理：
要在通过POI生成的EXCEL中正常显示中文，需要为单元格设置编码：
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("部门");
Java使用HSSF读取Excel表中公式列的方法
FileInputStream fis = new FileInputStream("c:/temp/test.xls");
HSSFWorkbook wb = new HSSFWorkbook(fis);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb);
// suppose your formula is in B3
CellReference cellReference = new CellReference("B3");
HSSFRow row = sheet.getRow(cellReference.getRow());
HSSFCell cell = row.getCell(cellReference.getCol());
//设置单元格所在行
evaluator.setCurrentRow(row);
HSSFFormulaEvaluator.CellValue cellValue = evaluator.evaluate(cell);
switch (cellValue.getCellType()) {
case HSSFCell.CELL_TYPE_BOOLEAN:
System.out.println(cellValue.getBooleanValue());
break;
case HSSFCell.CELL_TYPE_NUMERIC:
System.out.println(cellValue.getNumberValue());
break;
case HSSFCell.CELL_TYPE_STRING:
System.out.println(cellValue.getStringValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
break;
case HSSFCell.CELL_TYPE_ERROR:
break;
// CELL_TYPE_FORMULA will never happen
case HSSFCell.CELL_TYPE_FORMULA:
break;
}
页:
[1]