- 浏览: 83936 次
- 性别:
- 来自: 上海
最新评论
-
abc382410124:
不错呀 学习了
String.getBytes()和new String() -
lovekang89:
学习了,看起来不错
如何有效防止Java程序源码被人偷窥?
POI学习笔记(二) 转载
7. 设置单元格的边框
- public void createBorder() throws Exception {
- Workbook wb = new HSSFWorkbook();
- Sheet sheet = wb.createSheet("new sheet");
- // Create a row and put some cells in it. Rows are 0 based.
- Row row = sheet.createRow(1);
- // Create a cell and put a value in it.
- Cell cell = row.createCell(1);
- cell.setCellValue(4);
- // Style the cell with borders all around.
- CellStyle style = wb.createCellStyle();
- style.setBorderBottom(CellStyle.BORDER_THIN);
- style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
- style.setBorderLeft(CellStyle.BORDER_THIN);
- style.setLeftBorderColor(IndexedColors.GREEN.getIndex());
- style.setBorderRight(CellStyle.BORDER_THIN);
- style.setRightBorderColor(IndexedColors.BLUE.getIndex());
- style.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED);
- style.setTopBorderColor(IndexedColors.BLACK.getIndex());
- cell.setCellStyle(style);
- // Write the output to a file
- FileOutputStream fileOut = new FileOutputStream("workbook.xls");
- wb.write(fileOut);
- fileOut.close();
- }
- <SPAN style="FONT-SIZE: medium">public void createBorder() throws Exception {
- Workbook wb = new HSSFWorkbook();
- Sheet sheet = wb.createSheet("new sheet");
- // Create a row and put some cells in it. Rows are 0 based.
- Row row = sheet.createRow(1);
- // Create a cell and put a value in it.
- Cell cell = row.createCell(1);
- cell.setCellValue(4);
- // Style the cell with borders all around.
- CellStyle style = wb.createCellStyle();
- style.setBorderBottom(CellStyle.BORDER_THIN);
- style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
- style.setBorderLeft(CellStyle.BORDER_THIN);
- style.setLeftBorderColor(IndexedColors.GREEN.getIndex());
- style.setBorderRight(CellStyle.BORDER_THIN);
- style.setRightBorderColor(IndexedColors.BLUE.getIndex());
- style.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED);
- style.setTopBorderColor(IndexedColors.BLACK.getIndex());
- cell.setCellStyle(style);
- // Write the output to a file
- FileOutputStream fileOut = new FileOutputStream("workbook.xls");
- wb.write(fileOut);
- fileOut.close();
- }</SPAN>
public void createBorder() throws Exception {
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("new sheet");
// Create a row and put some cells in it. Rows are 0 based.
Row row = sheet.createRow(1);
// Create a cell and put a value in it.
Cell cell = row.createCell(1);
cell.setCellValue(4);
// Style the cell with borders all around.
CellStyle style = wb.createCellStyle();
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.GREEN.getIndex());
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLUE.getIndex());
style.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
cell.setCellStyle(style);
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
}
8. 迭代行和单元格
有时需要迭代一个页中的所有行,或者一个行中所有的单元格。一个简单的方法是循环。
幸运的是,poi知道我们所需。页可以通过sheet.rowIterator()迭代出所有的行,行可以通过row.cellIterator()迭代出所有的单元格。总之,Sheet和Row实现了java.lang.Iterable,如果你用的是jdk1.5以上的版本,你可以使用java高级for循环。
- 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
- }
- }
- <SPAN style="FONT-SIZE: medium">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
- }
- }
- </SPAN>
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
}
}
java高级for循环迭代行和单元格
- Sheet sheet = wb.getSheetAt(0);
- for (Row row : sheet) {
- for (Cell cell : row) {
- // Do something here
- }
- }
- <SPAN style="FONT-SIZE: medium">Sheet sheet = wb.getSheetAt(0);
- for (Row row : sheet) {
- for (Cell cell : row) {
- // Do something here
- }
- }
- </SPAN>
Sheet sheet = wb.getSheetAt(0);
for (Row row : sheet) {
for (Cell cell : row) {
// Do something here
}
}
9. 得到单元格的内容
想得到单元格的内容之前,首先要知道单元格的类型,因此你要先判断单元格的类型之后选择合适的方法得到单元格的值。下面的代码,循环得到一个Sheet所有的单元格。
- public void getCellValue() throws Exception {
- InputStream inp = new FileInputStream("D:\\hjn.xls");
- HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));
- Sheet sheet1 = wb.getSheetAt(0);
- for (Row row : sheet1) {
- for (Cell cell : row) {
- CellReference cellRef = new CellReference(row.getRowNum(), cell
- .getColumnIndex());
- System.out.print(cellRef.formatAsString());
- System.out.print(" - ");
- switch (cell.getCellType()) {
- case Cell.CELL_TYPE_STRING:
- System.out.println(cell.getRichStringCellValue()
- .getString());
- break;
- case Cell.CELL_TYPE_NUMERIC:
- 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();
- }
- }
- }
- }
- <SPAN style="FONT-SIZE: medium">public void getCellValue() throws Exception {
- InputStream inp = new FileInputStream("D:\\hjn.xls");
- HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));
- Sheet sheet1 = wb.getSheetAt(0);
- for (Row row : sheet1) {
- for (Cell cell : row) {
- CellReference cellRef = new CellReference(row.getRowNum(), cell
- .getColumnIndex());
- System.out.print(cellRef.formatAsString());
- System.out.print(" - ");
- switch (cell.getCellType()) {
- case Cell.CELL_TYPE_STRING:
- System.out.println(cell.getRichStringCellValue()
- .getString());
- break;
- case Cell.CELL_TYPE_NUMERIC:
- 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();
- }
- }
- }
- }</SPAN>
public void getCellValue() throws Exception {
InputStream inp = new FileInputStream("D:\\hjn.xls");
HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));
Sheet sheet1 = wb.getSheetAt(0);
for (Row row : sheet1) {
for (Cell cell : row) {
CellReference cellRef = new CellReference(row.getRowNum(), cell
.getColumnIndex());
System.out.print(cellRef.formatAsString());
System.out.print(" - ");
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
System.out.println(cell.getRichStringCellValue()
.getString());
break;
case Cell.CELL_TYPE_NUMERIC:
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();
}
}
}
}
10. 文本提取
poi的ExcelExtractor可以抽取Cell中的值。org.apache.poi.ss.extractor 为抽取类的接口,ExcelExtractor, XSSFExcelExtractor实现了该接口。
- InputStream inp = new FileInputStream("D:\\hjn.xls");
- HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));
- ExcelExtractor extractor = new ExcelExtractor(wb);
- extractor.setFormulasNotResults(true);
- extractor.setIncludeSheetNames(true);
- String text = extractor.getText();
- System.out.println(text);
- <SPAN style="FONT-SIZE: medium">InputStream inp = new FileInputStream("D:\\hjn.xls");
- HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));
- ExcelExtractor extractor = new ExcelExtractor(wb);
- extractor.setFormulasNotResults(true);
- extractor.setIncludeSheetNames(true);
- String text = extractor.getText();
- System.out.println(text);</SPAN>
InputStream inp = new FileInputStream("D:\\hjn.xls");
HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));
ExcelExtractor extractor = new ExcelExtractor(wb);
extractor.setFormulasNotResults(true);
extractor.setIncludeSheetNames(true);
String text = extractor.getText();
System.out.println(text);
11. 填充和颜色
- public void fillAndColors() throws Exception{
- Workbook wb = new HSSFWorkbook();
- Sheet sheet = wb.createSheet("new sheet");
- // Create a row and put some cells in it. Rows are 0 based.
- Row row = sheet.createRow((short) 1);
- // Aqua background
- CellStyle style = wb.createCellStyle();
- style.setFillBackgroundColor(IndexedColors.BLUE.getIndex());
- style.setFillPattern(CellStyle.ALIGN_FILL);
- Cell cell = row.createCell((short) 1);
- cell.setCellValue("X");
- cell.setCellStyle(style);
- // Orange "foreground", foreground being the fill foreground not the font color.
- style = wb.createCellStyle();
- style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
- style.setFillPattern(CellStyle.SOLID_FOREGROUND);
- cell = row.createCell((short) 2);
- cell.setCellValue("X");
- cell.setCellStyle(style);
- // Write the output to a file
- FileOutputStream fileOut = new FileOutputStream("workbook.xls");
- wb.write(fileOut);
- fileOut.close();
- }
- <SPAN style="FONT-SIZE: medium">public void fillAndColors() throws Exception{
- Workbook wb = new HSSFWorkbook();
- Sheet sheet = wb.createSheet("new sheet");
- // Create a row and put some cells in it. Rows are 0 based.
- Row row = sheet.createRow((short) 1);
- // Aqua background
- CellStyle style = wb.createCellStyle();
- style.setFillBackgroundColor(IndexedColors.BLUE.getIndex());
- style.setFillPattern(CellStyle.ALIGN_FILL);
- Cell cell = row.createCell((short) 1);
- cell.setCellValue("X");
- cell.setCellStyle(style);
- // Orange "foreground", foreground being the fill foreground not the font color.
- style = wb.createCellStyle();
- style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
- style.setFillPattern(CellStyle.SOLID_FOREGROUND);
- cell = row.createCell((short) 2);
- cell.setCellValue("X");
- cell.setCellStyle(style);
- // Write the output to a file
- FileOutputStream fileOut = new FileOutputStream("workbook.xls");
- wb.write(fileOut);
- fileOut.close();
- }</SPAN>
public void fillAndColors() throws Exception{
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("new sheet");
// Create a row and put some cells in it. Rows are 0 based.
Row row = sheet.createRow((short) 1);
// Aqua background
CellStyle style = wb.createCellStyle();
style.setFillBackgroundColor(IndexedColors.BLUE.getIndex());
style.setFillPattern(CellStyle.ALIGN_FILL);
Cell cell = row.createCell((short) 1);
cell.setCellValue("X");
cell.setCellStyle(style);
// Orange "foreground", foreground being the fill foreground not the font color.
style = wb.createCellStyle();
style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row.createCell((short) 2);
cell.setCellValue("X");
cell.setCellStyle(style);
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
}
12. 合并单元格
- public void mergingCell() throws Exception{
- Workbook wb = new HSSFWorkbook();
- Sheet sheet = wb.createSheet("new sheet");
- Row row = sheet.createRow((short) 1);
- Cell cell = row.createCell((short) 1);
- cell.setCellValue("This is a test of merging");
- sheet.addMergedRegion(new CellRangeAddress(1, // first row (0-based)
- 4, // last row (0-based)
- 1, // first column (0-based)
- 6 // last column (0-based)
- ));
- // Write the output to a file
- FileOutputStream fileOut = new FileOutputStream("workbook.xls");
- wb.write(fileOut);
- fileOut.close();
- }
- <SPAN style="FONT-SIZE: medium">public void mergingCell() throws Exception{
- Workbook wb = new HSSFWorkbook();
- Sheet sheet = wb.createSheet("new sheet");
- Row row = sheet.createRow((short) 1);
- Cell cell = row.createCell((short) 1);
- cell.setCellValue("This is a test of merging");
- sheet.addMergedRegion(new CellRangeAddress(1, // first row (0-based)
- 4, // last row (0-based)
- 1, // first column (0-based)
- 6 // last column (0-based)
- ));
-
/
发表评论
相关推荐
POI学习笔记第二版更详细的POI学习笔记第二版更详细的
"POI学习笔记" POI(Apache POI)是一款流行的Java库,用于处理Microsoft Office文件格式,包括Excel、Word、PowerPoint等。POI提供了一个简洁和灵活的API,允许开发者轻松地读取、写入和操作Office文件。 POI的...
### POI学习笔记知识点解析 #### 一、POI简介 Apache POI是一个开源的Java API,用于处理Microsoft Office格式的文件,包括Excel (.xls, .xlsx), PowerPoint (.ppt, .pptx) 和 Word (.doc, .docx)。POI提供了一套...
- 官方文档:Apache POI 提供了详尽的开发者指南和API文档,是学习的基础。 - 在线教程:许多网站提供 POI 教程,例如 Baeldung、Stack Overflow 等。 - 示例代码:GitHub 上有大量使用 POI 的开源项目,可以参考...
POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI...
2. **poi**: 这是Apache POI的核心库,主要负责处理Microsoft Office的二进制文件格式,如老版本的Excel(.xls)和PowerPoint(.ppt)。这个库提供了API,可以创建、修改和读取这些文件。 3. **poi-examples**: 这...
3. **二进制格式支持**:`poi-scratchpad.jar` 包含了处理早期的二进制Microsoft Office格式,如老版本的Excel (.xls) 和 Word (.doc) 的代码。 4. **Java对象模型支持**:`poi-ooxml-full-model.jar`, `poi-ooxml-...
- POI库的API设计相对复杂,学习曲线较陡峭,需要花时间去熟悉各种类和方法。 - 当处理复杂的公式或图表时,POI可能无法完全模拟Excel的所有特性,因此在某些情况下可能需要使用其他工具或库进行补充。 - 在更新版本...
对于更深入的学习和了解 POI 的使用方法,可以参考以下资源: - **官方文档**:[http://jakarta.apache.org/poi/](http://jakarta.apache.org/poi/) - **HSSF 快速指南**:...
这些ZIP文件包含了Apache POI的二进制发行版,包括必要的JAR文件和其他资源。开发者可以通过解压这些文件,将对应的JAR添加到项目类路径中,然后就可以在Java程序中使用Apache POI API来操作Excel、Word和PowerPoint...
poi3.8快速学习指南
poi多级表头导出模板
而“二级类别”和“一级类别”可能分别指的是POI的详细分类,例如一级类别可能是“餐饮”,二级类别则可能是“快餐”、“正餐”等更具体的分类。这样的分类体系有助于用户根据需求快速筛选和分析数据。 综合来看,...
Apache POI是一个开源项目,主要用于读取和写入Microsoft Office格式的文件,如Word(.doc/.docx)、Excel(.xls/.xlsx)和PowerPoint(.ppt/.pptx)。这个“POI中文帮助文档”提供了全面的指南,帮助开发者理解和...
关于poi ,Apache在今年,也就是在2017年9月15日 正式发布了POI 3.17版本, Apache POI团队对于3.17版本进行了一些功能的修复。主要是几个新的功能区域和许多错误修复。 POI 3.17是支持Java 6的最后一个版本。下一个...
在IT行业中,POI...免费下载的POI数据资源对于学习、研究或开发基于位置的服务非常有帮助,但使用时需要注意版权和合规性问题。通过理解并有效利用这些数据,开发者能够构建出更智能、更贴近用户需求的地图应用。
这个压缩包“poi源码和思维导图”包含了对Apache POI源代码的学习资料,以及可能的配套思维导图,对于深入理解POI的工作原理和开发技巧非常有帮助。 Apache POI的核心功能在于它允许Java开发者读写Microsoft Office...