本文介绍了如何使用Apache POI 3.9库创建和操作Excel文件，包括创建Workbook和添加行、单元格等步骤。
项目下载地址：http://download.youkuaiyun.com/detail/a875881240/67022371. POI 3.9(org.apache.poi)
Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。Apache POI - Component POI组件
1)HSSF(Microsoft Excel 97(-2007)) and XSSF(2007+) for Excel Documents
2)HWPF(Microsoft Word 97(-2003)) and XWPF(2007+) for Word Documents
3)HSLF(Microsoft PowerPoint 97(-2003)) and XSLF(2007+) for PowerPoint Documents
4)...(http://poi.apache.org/overview.html)
2.HSSF and XSSF for Excel Documents
HSSF:Horrible SpreadSheet Format(讨厌的电子表格格式)
XSSF:2007+
SXSSF: (Since POI 3.8 beta3) 一种基于XSSF的低内存占用的API
与XSSF的对比
在一个时间点上，只可以访问一定数量的数据
不再支持Sheet.clone()
不再支持公式的求值
3.HSSF操作excel(http://poi.apache.org/spreadsheet/quick-guide.html)
org.apache.poi.hssf.usermodel包下L:(实现类)HSSFWorkbook,HSSFSheet,...
org.apache.poi.ss.usermodel包下:(接口)Workbook,Sheet,...
1) 创建一个Workbook
Workbook wb = new HSSFWorkbook();//或new XSSFWorkbook()
//WorkbookFactory.create(new File("workbook.xls"));//与InputStream相比允许较低的内存
//WorkbookFactory.create(InputStream is);//InputStream 需要更多的内存缓冲区来加载整个文件
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close(); String sheetName="new sheet";
//或者可以使用org.apache.poi.ss.util.WorkbookUtil.createSafeSheetName(String nameProposal);用来创建一个有效的名称
//如果sheetName="new sheet*?" 则改方法会将*?非法字符使用""来代替,结果为"new sheet ";
Sheet sheet = wb.createSheet(sheetName);在创建单元格之前需要先创建行
Row row=sheet.creatRow((short)0);//参数为short类型,行从0开始
//row.setZeroHeight(true);//隐藏行
Cell cell = row.creatCell((short)0);//参数为short类型,列从0开始
cell.setCellValue("value")//为单元格赋值,值可以为boolean,Date,Canlinder,Double,String,RichTextString
//CreationHelper createHelper = wb.getCreationHelper();
//createHelper.createRichTextString("This is a string \n");
或row.creatCell((short)0).setCellValue("value");
CellStyle cellStyle = wb.createCellStyle();
cellStyle.cloneStyleFrom(CellStyle cs/HSSFCellStyle hssfcs);//clone某个样式 cellStyle.setBorderBottom(CellStyle.BORDER_THIN);//设置边框样式
//org.apache.poi.ss.usermodel.CellStyle 提供样式方案
cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());//设置边框颜色
//org.apache.poi.ss.usermodel.IndexedColors 提供索引颜色方案
//或使用 org.apache.poi.hssf.util.HSSFColor 获取索引颜色方案
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
cellStyle.setLeftBorderColor(IndexedColors.GREEN.getIndex());
cellStyle.setBorderRight(CellStyle.BORDER_THIN);
cellStyle.setRightBorderColor(IndexedColors.BLUE.getIndex());
cellStyle.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED);//中虚线
cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);//填充模式,设置前景色/背景色需设置填充模式
cellStyle.setFillForegroundColor(HSSFColor.BLACK.index);//设置前景色
cellStyle.setFillBackgroundColor(HSSFColor.BLUE.index);//设置背景色
//org.apache.poi.ss.usermodel.CellStyle 提供对齐方案
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);//水平对齐方式,水平居中
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直对齐方式,垂直居中
//org.apache.poi.ss.usermodel.DataFormat
CreationHelper createHelper = wb.getCreationHelper();
DataFormat dataFormat=createHelper.createDataFormat();//或wb.createDataFormat();
format.getFormat("0.0");//数字格式化
format.getFormat("#,##0.0000");
format.getFormat("m/d/yy h:mm");//设置日期格式化
cellStyle.setDataFormat(format);
//org.apache.poi.ss.usermodel.Font
Font font = wb.createFont();//创建字体
font.setFontHeightInPoints((short)24);//设置字体大小
font.setFontName("Courier New");//黑体,微软雅黑
font.setItalic(true);//斜体
font.setStrikeout(true);//删除线
cellStyle.setFont(font);
cellStyle.setWrapText(true);//设置自动换行
cellStyle.setHidden(true);//隐藏
cellStyle.setRotation(short);
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(
1, //first row (0-based)
1, //last row (0-based)
1, //first column (0-based)
2 //last column (0-based)
));//设置单元格合并
CreationHelper createHelper = wb.getCreationHelper();
Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_URL);//url链接
link = createHelper.createHyperlink(Hyperlink.LINK_FILE);//文件链接
link = createHelper.createHyperlink(Hyperlink.LINK_EMAIL);//e-mail链接
link = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT);//在本文档的链接
link.setAddress("'Target Sheet'!A1");
cell.setHyperlink(link);
//获取所有的表:workbook.getNumberOfSheets();
Sheet sheet1 = wb.getSheetAt(0);
for (Row row : sheet1) {
for (Cell cell : row) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
System.out.println(cell.getRichStringCellValue().getString());
break;
case Cell.CELL_TYPE_NUMERIC://excel中日期是按照数值类型存放
if (DateUtil.isCellDateFormatted(cell)) {
System.out.println(cell.getDateCellValue());
} else {
System.out.println(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.println(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA://公式
System.out.println(cell.getCellFormula());
break;
default:
System.out.println();
}
}
}
sheet.createFreezePane( 1,2 );//(列,行) 1列2行冻结
sheet.createFreezePane( 1,2,1,2);前两个参数表示1列2行冻结 ,后两个参数表示1列2行的右下象限可见
sheet.createSplitPane(xSplitPos,ySplitPos,leftmostColumn,topRow,activePane);
row.setHeightInPoints(short);//-1为默认高度,行高像素
// 或 row.setHeight(short);//参数单位twips, 1/20 个像素点
sheet.setColumnWidth(columnIndex,width);//参数1:列索引,参数2:宽(int)最大列宽255个字符
Header header = sheet.getHeader();
header.setCenter("Center Header");
header.setLeft("Left Header");
Footer footer = sheet.getFooter();
footer.setRight( "Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages() );//设置页号
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();//创建一个patriarch
ClientAnchor anchor = creationHelper.createClientAnchor();//创建一个ClientAnchor
anchor.setCol1(cell.getColumnIndex());
anchor.setCol2(cell.getColumnIndex()+2);
anchor.setRow1(row.getRowNum());
anchor.setRow2(row.getRowNum()+2);
Comment comment = patr.createCellComment(anchor);
comment.setAuthor("kang");
comment.setString(creationHelper.createRichTextString("comment"));
cell.setCellComment(comment);
sheet.setAutobreaks(true);
PrintSetup ps = sheet.getPrintSetup();
ps.setFitHeight((short)1);//高自动适应
ps.setFitWidth((short)1);//宽自动适应
sheet.setSelected(true);//选中表
Sheet sheet = wb.getSheetAt(0);
for (Row row : sheet) {
for (Cell cell : row) {
// Do something here
}
}
// 或使用Iterator
for(Iterator<Row> rit = sheet.rowIterator();rit.hasNext();){
Row row=rit.next();
for(Iterator<Cell> cit=row.cellIterator();cit.hasNext();){
// Do something here
}
}
//org.apache.poi.hssf.util.HSSFCellUtil
HSSFCellUtil.createCell(HSSFRow row, int column, java.lang.String value);//创建一个单元格,设置值
HSSFCellUtil.createCell(HSSFRow row, int column, java.lang.String value, HSSFCellStyle style);//创建一个单元格,设置值,并设置样式
HSSFCellUtil.setFont(HSSFCell cell, HSSFWorkbook workbook, HSSFFont font)//设置字体
//...
FileOutputStream fileOut = new FileOutputStream(filename);
wb.write(fileOut);//OutputStream
fileOut.close();
Workbook workbook=WorkbookFactory.create(File file);
//获取所有的表:workbook.getNumberOfSheets();
Sheet sheet1 = wb.getSheetAt(0);
for (Row row : sheet1) {
for (Cell cell : row) {
}
}
/...详见7)部分
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
