POI学习笔记 自定义颜色
来源:互联网 发布:社交网络百度云盘 编辑:程序博客网 时间:2024/05/29 23:47
http://yunzhongxia.iteye.com/blog/561425
http://yunzhongxia.iteye.com/blog/558998
项目中经常要解析和生成Excel文件,最常用的开源组件有poi与jxl。jxl是韩国人开发的,发行较早,但是更新的很慢,目前似乎还不支持excel2007。poi是apache下的一个子项目,poi应该是处理ms的office系列文档最好的组件了。poi3.6版本已经开始支持excel2007了。但是由于excel2007底层的实现似乎变成xml与excel2003底层存储发生了本质的变化,因此poi解析excel的类就存在差异了。
现在简单的介绍下poi常用的接口。
经常用的类一般都在org.apache.poi.hssf.usermodel(excel2003)或org.apache.poi.xssf.usermodel
(excel2007)。
- 工作薄: WorkBook是操作Excel的入口,HSSFWorkbook, XSSFWorkbook实现了该接口。
- 页:Sheet表示工作薄的分页。HSSFSheet, XSSFChartSheet, XSSFDialogsheet, XSSFSheet实现了该接口。
- Row:表示页中的一行。HSSFRow, XSSFRow实现了该接口。
- Cell:行中的一个单元格。HSSFCell, XSSFCell实现了该接口。
从上面的介绍得知:页是通过工作薄对象创建的,行是通过页对象创建的,单元格是通过行对象创建的。接下来,我们就开始发掘poi的强大功能吧。
- 创建一个空白的工作薄
- import java.io.FileOutputStream;
- import org.apache.poi.hssf.usermodel.HSSFWorkbook;
- import org.apache.poi.ss.usermodel.Workbook;
- import org.apache.poi.xssf.usermodel.XSSFWorkbook;
- //2003版本
- Workbook wb = new HSSFWorkbook();
- FileOutputStream fileOut = new FileOutputStream("workbook.xls");
- wb.write(fileOut);
- fileOut.close();
- //2007版本
- // Workbook wb = new XSSFWorkbook();
- // FileOutputStream fileOut = new FileOutputStrea("workbook.xlsx");
- // wb.write(fileOut);
- // fileOut.close();
import java.io.FileOutputStream;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;//2003版本Workbook wb = new HSSFWorkbook();FileOutputStream fileOut = new FileOutputStream("workbook.xls");wb.write(fileOut); fileOut.close();//2007版本// Workbook wb = new XSSFWorkbook();// FileOutputStream fileOut = new FileOutputStrea("workbook.xlsx");// wb.write(fileOut);// fileOut.close();
注意:Workbook 是org.apache.poi.ss.usermodel包下的一个接口,注意与以前版本的不同,HSSFWorkbook和XSSFWorkbook实现了该接口,这样就达到了面前接口编程。下面的excel工具类中要用到此点知识。
2. 创建两个空白页
- Workbook wb = new HSSFWorkbook();
- //Workbook wb = new XSSFWorkbook();
- Sheet sheet1 = wb.createSheet("new sheet");
- Sheet sheet2 = wb.createSheet("second sheet");
- FileOutputStream fileOut = new FileOutputStream("workbook.xls");
- wb.write(fileOut);
- fileOut.close();
Workbook wb = new HSSFWorkbook();//Workbook wb = new XSSFWorkbook();Sheet sheet1 = wb.createSheet("new sheet");Sheet sheet2 = wb.createSheet("second sheet");FileOutputStream fileOut = new FileOutputStream("workbook.xls");wb.write(fileOut);fileOut.close();
3. 创建单元格
- public void createRow() throws Exception {
- Workbook wb = new HSSFWorkbook();
- // Workbook wb = new XSSFWorkbook();
- CreationHelper createHelper = wb.getCreationHelper();
- Sheet sheet = wb.createSheet("new sheet");
- //创建一行并放一些单元格到该行中,行的索引是以0开始的
- Row row = sheet.createRow((short) 0);
- // 创建一个单元格并填充一个整数的值
- Cell cell = row.createCell(0);
- cell.setCellValue(1);
- //链式写法
- row.createCell(1).setCellValue(1.2);
- row.createCell(2).setCellValue(
- createHelper.createRichTextString("This is a string"));
- row.createCell(3).setCellValue(true);
- //输出文件
- FileOutputStream fileOut = new FileOutputStream("workbook.xls");
- wb.write(fileOut);
- fileOut.close();
- }
public void createRow() throws Exception {Workbook wb = new HSSFWorkbook();// Workbook wb = new XSSFWorkbook();CreationHelper createHelper = wb.getCreationHelper();Sheet sheet = wb.createSheet("new sheet");//创建一行并放一些单元格到该行中,行的索引是以0开始的Row row = sheet.createRow((short) 0);// 创建一个单元格并填充一个整数的值Cell cell = row.createCell(0);cell.setCellValue(1);//链式写法row.createCell(1).setCellValue(1.2);row.createCell(2).setCellValue(createHelper.createRichTextString("This is a string"));row.createCell(3).setCellValue(true);//输出文件FileOutputStream fileOut = new FileOutputStream("workbook.xls");wb.write(fileOut);fileOut.close();}
4. 创建日期单元格
- public void createDateCell() throws Exception {
- Workbook wb = new HSSFWorkbook();
- // Workbook wb = new XSSFWorkbook();
- CreationHelper createHelper = wb.getCreationHelper();
- Sheet sheet = wb.createSheet("new sheet");
- // Create a row and put some cells in it. Rows are 0 based.
- Row row = sheet.createRow(0);
- // Create a cell and put a date value in it. The first cell is not
- // styled
- // as a date.
- Cell cell = row.createCell(0);
- cell.setCellValue(new Date());
- // we style the second cell as a date (and time). It is important to
- // create a new cell style from the workbook otherwise you can end up
- // modifying the built in style and effecting not only this cell but
- // other cells.
- CellStyle cellStyle = wb.createCellStyle();
- cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(
- "yyyy/MM/dd hh:mm"));
- cell = row.createCell(1);
- // cell.setCellValue(new Date());
- Date date = new Date();
- cell.setCellValue(createHelper.createRichTextString(date.toString()));
- cell.setCellStyle(cellStyle);
- // you can also set date as java.util.Calendar
- cell = row.createCell(2);
- cell.setCellValue(Calendar.getInstance());
- cell.setCellStyle(cellStyle);
- // Write the output to a file
- FileOutputStream fileOut = new FileOutputStream("workbook.xls");
- wb.write(fileOut);
- fileOut.close();
- }
public void createDateCell() throws Exception {Workbook wb = new HSSFWorkbook();// Workbook wb = new XSSFWorkbook();CreationHelper createHelper = wb.getCreationHelper();Sheet sheet = wb.createSheet("new sheet");// Create a row and put some cells in it. Rows are 0 based.Row row = sheet.createRow(0);// Create a cell and put a date value in it. The first cell is not// styled// as a date.Cell cell = row.createCell(0);cell.setCellValue(new Date());// we style the second cell as a date (and time). It is important to// create a new cell style from the workbook otherwise you can end up// modifying the built in style and effecting not only this cell but// other cells.CellStyle cellStyle = wb.createCellStyle();cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy/MM/dd hh:mm"));cell = row.createCell(1);// cell.setCellValue(new Date());Date date = new Date();cell.setCellValue(createHelper.createRichTextString(date.toString()));cell.setCellStyle(cellStyle);// you can also set date as java.util.Calendarcell = row.createCell(2);cell.setCellValue(Calendar.getInstance());cell.setCellStyle(cellStyle);// Write the output to a fileFileOutputStream fileOut = new FileOutputStream("workbook.xls");wb.write(fileOut);fileOut.close();}
5. 创建不同的单元格样式
- public void createCellType() throws Exception{
- Workbook wb = new HSSFWorkbook();
- Sheet sheet = wb.createSheet("new sheet");
- Row row = sheet.createRow((short)2);
- row.createCell(0).setCellValue(1.1);
- row.createCell(1).setCellValue(new Date());
- row.createCell(2).setCellValue(Calendar.getInstance());
- row.createCell(3).setCellValue("a string");
- row.createCell(4).setCellValue(true);
- row.createCell(5).setCellType(HSSFCell.CELL_TYPE_ERROR);
- // Write the output to a file
- FileOutputStream fileOut = new FileOutputStream("workbook.xls");
- wb.write(fileOut);
- fileOut.close();
- }
public void createCellType() throws Exception{Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("new sheet"); Row row = sheet.createRow((short)2); row.createCell(0).setCellValue(1.1); row.createCell(1).setCellValue(new Date()); row.createCell(2).setCellValue(Calendar.getInstance()); row.createCell(3).setCellValue("a string"); row.createCell(4).setCellValue(true); row.createCell(5).setCellType(HSSFCell.CELL_TYPE_ERROR); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close();}
6. 设置单元格水平垂直对齐方式
- public static void main(String[] args) throws Exception {
- Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
- Sheet sheet = wb.createSheet();
- Row row = sheet.createRow((short) 2);
- row.setHeightInPoints(30);
- createCell(wb, row, (short) 0, XSSFCellStyle.ALIGN_CENTER, XSSFCellStyle.VERTICAL_BOTTOM);
- createCell(wb, row, (short) 1, XSSFCellStyle.ALIGN_CENTER_SELECTION, XSSFCellStyle.VERTICAL_BOTTOM);
- createCell(wb, row, (short) 2, XSSFCellStyle.ALIGN_FILL, XSSFCellStyle.VERTICAL_CENTER);
- createCell(wb, row, (short) 3, XSSFCellStyle.ALIGN_GENERAL, XSSFCellStyle.VERTICAL_CENTER);
- createCell(wb, row, (short) 4, XSSFCellStyle.ALIGN_JUSTIFY, XSSFCellStyle.VERTICAL_JUSTIFY);
- createCell(wb, row, (short) 5, XSSFCellStyle.ALIGN_LEFT, XSSFCellStyle.VERTICAL_TOP);
- createCell(wb, row, (short) 6, XSSFCellStyle.ALIGN_RIGHT, XSSFCellStyle.VERTICAL_TOP);
- // Write the output to a file
- FileOutputStream fileOut = new FileOutputStream("xssf-align.xlsx");
- wb.write(fileOut);
- fileOut.close();
- }
- /**
- * Creates a cell and aligns it a certain way.
- *
- * @param wb the workbook
- * @param row the row to create the cell in
- * @param column the column number to create the cell in
- * @param halign the horizontal alignment for the cell.
- */
- private static void createCell(Workbook wb, Row row, short column, short halign, short valign) {
- Cell cell = row.createCell(column);
- cell.setCellValue(new XSSFRichTextString("Align It"));
- CellStyle cellStyle = wb.createCellStyle();
- cellStyle.setAlignment(halign);
- cellStyle.setVerticalAlignment(valign);
- cell.setCellStyle(cellStyle);
- }
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();
- }
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 fileFileOutputStream 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
- }
- }
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
- }
- }
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();
- }
- }
- }
- }
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);
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();
- }
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();
- }
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 fileFileOutputStream fileOut = new FileOutputStream("workbook.xls");wb.write(fileOut);fileOut.close();}
13. 设置字体
- public void createFont() 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 new font and alter it.
- Font font = wb.createFont();
- font.setFontHeightInPoints((short)24);
- font.setFontName("Courier New");
- font.setItalic(true);
- font.setStrikeout(true);
- // Fonts are set into a style so create a new one to use.
- CellStyle style = wb.createCellStyle();
- style.setFont(font);
- // Create a cell and put a value in it.
- Cell cell = row.createCell(1);
- cell.setCellValue("This is a test of fonts");
- cell.setCellStyle(style);
- // Write the output to a file
- FileOutputStream fileOut = new FileOutputStream("workbook.xls");
- wb.write(fileOut);
- fileOut.close();
- }
public void createFont() 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 new font and alter it. Font font = wb.createFont(); font.setFontHeightInPoints((short)24); font.setFontName("Courier New"); font.setItalic(true); font.setStrikeout(true); // Fonts are set into a style so create a new one to use. CellStyle style = wb.createCellStyle(); style.setFont(font); // Create a cell and put a value in it. Cell cell = row.createCell(1); cell.setCellValue("This is a test of fonts"); cell.setCellStyle(style); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close();}
注意:一个工作薄最多只能创建32767 个不同的字体样式,因此你应该重用字体样式而不应该每创建一个单元格就创建一个单元格字体样式。
错误写法:
- for (int i = 0; i < 10000; i++) {
- Row row = sheet.createRow(i);
- Cell cell = row.createCell((short) 0);
- CellStyle style = workbook.createCellStyle();
- Font font = workbook.createFont();
- font.setBoldweight(Font.BOLDWEIGHT_BOLD);
- style.setFont(font);
- cell.setCellStyle(style);
- }
for (int i = 0; i < 10000; i++) { Row row = sheet.createRow(i); Cell cell = row.createCell((short) 0); CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); cell.setCellStyle(style); }
正确写法:
- Font font = workbook.createFont();
- font.setBoldweight(Font.BOLDWEIGHT_BOLD);
- style.setFont(font);
- for (int i = 0; i < 10000; i++) {
- Row row = sheet.createRow(i);
- Cell cell = row.createCell((short) 0);
- cell.setCellStyle(style);
- }
14. 自定义颜色
HSSF:
- HSSFWorkbook wb = new HSSFWorkbook();
- HSSFSheet sheet = wb.createSheet();
- HSSFRow row = sheet.createRow((short) 0);
- HSSFCell cell = row.createCell((short) 0);
- cell.setCellValue("Default Palette");
- //apply some colors from the standard palette,
- // as in the previous examples.
- //we'll use red text on a lime background
- HSSFCellStyle style = wb.createCellStyle();
- style.setFillForegroundColor(HSSFColor.LIME.index);
- style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
- HSSFFont font = wb.createFont();
- font.setColor(HSSFColor.RED.index);
- style.setFont(font);
- cell.setCellStyle(style);
- //save with the default palette
- FileOutputStream out = new FileOutputStream("default_palette.xls");
- wb.write(out);
- out.close();
- //now, let's replace RED and LIME in the palette
- // with a more attractive combination
- // (lovingly borrowed from freebsd.org)
- cell.setCellValue("Modified Palette");
- //creating a custom palette for the workbook
- HSSFPalette palette = wb.getCustomPalette();
- //replacing the standard red with freebsd.org red
- palette.setColorAtIndex(HSSFColor.RED.index,
- (byte) 153, //RGB red (0-255)
- (byte) 0, //RGB green
- (byte) 0 //RGB blue
- );
- //replacing lime with freebsd.org gold
- palette.setColorAtIndex(HSSFColor.LIME.index, (byte) 255, (byte) 204, (byte) 102);
- //save with the modified palette
- // note that wherever we have previously used RED or LIME, the
- // new colors magically appear
- out = new FileOutputStream("modified_palette.xls");
- wb.write(out);
- out.close();
HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); HSSFRow row = sheet.createRow((short) 0); HSSFCell cell = row.createCell((short) 0); cell.setCellValue("Default Palette"); //apply some colors from the standard palette, // as in the previous examples. //we'll use red text on a lime background HSSFCellStyle style = wb.createCellStyle(); style.setFillForegroundColor(HSSFColor.LIME.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont font = wb.createFont(); font.setColor(HSSFColor.RED.index); style.setFont(font); cell.setCellStyle(style); //save with the default palette FileOutputStream out = new FileOutputStream("default_palette.xls"); wb.write(out); out.close(); //now, let's replace RED and LIME in the palette // with a more attractive combination // (lovingly borrowed from freebsd.org) cell.setCellValue("Modified Palette"); //creating a custom palette for the workbook HSSFPalette palette = wb.getCustomPalette(); //replacing the standard red with freebsd.org red palette.setColorAtIndex(HSSFColor.RED.index, (byte) 153, //RGB red (0-255) (byte) 0, //RGB green (byte) 0 //RGB blue ); //replacing lime with freebsd.org gold palette.setColorAtIndex(HSSFColor.LIME.index, (byte) 255, (byte) 204, (byte) 102); //save with the modified palette // note that wherever we have previously used RED or LIME, the // new colors magically appear out = new FileOutputStream("modified_palette.xls"); wb.write(out); out.close();
XSSF:
- XSSFWorkbook wb = new XSSFWorkbook();
- XSSFSheet sheet = wb.createSheet();
- XSSFRow row = sheet.createRow(0);
- XSSFCell cell = row.createCell( 0);
- cell.setCellValue("custom XSSF colors");
- XSSFCellStyle style1 = wb.createCellStyle();
- style1.setFillForegroundColor(new XSSFColor(new java.awt.Color(128, 0, 128)));
- style1.setFillPattern(CellStyle.SOLID_FOREGROUND);
XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(); XSSFRow row = sheet.createRow(0); XSSFCell cell = row.createCell( 0); cell.setCellValue("custom XSSF colors"); XSSFCellStyle style1 = wb.createCellStyle(); style1.setFillForegroundColor(new XSSFColor(new java.awt.Color(128, 0, 128))); style1.setFillPattern(CellStyle.SOLID_FOREGROUND);
15. 读取和重写工作薄
- InputStream inp = new FileInputStream("workbook.xls");
- //InputStream inp = new FileInputStream("workbook.xlsx");
- Workbook wb = WorkbookFactory.create(inp);
- Sheet sheet = wb.getSheetAt(0);
- Row row = sheet.getRow(2);
- Cell cell = row.getCell(3);
- if (cell == null)
- cell = row.createCell(3);
- cell.setCellType(Cell.CELL_TYPE_STRING);
- cell.setCellValue("a test");
- // Write the output to a file
- FileOutputStream fileOut = new FileOutputStream("workbook.xls");
- wb.write(fileOut);
- fileOut.close();
InputStream inp = new FileInputStream("workbook.xls"); //InputStream inp = new FileInputStream("workbook.xlsx"); Workbook wb = WorkbookFactory.create(inp); Sheet sheet = wb.getSheetAt(0); Row row = sheet.getRow(2); Cell cell = row.getCell(3); if (cell == null) cell = row.createCell(3); cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue("a test"); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close();
16. 单元格内容换行
- Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
- Sheet sheet = wb.createSheet();
- Row row = sheet.createRow(2);
- Cell cell = row.createCell(2);
- cell.setCellValue("Use \n with word wrap on to create a new line");
- //to enable newlines you need set a cell styles with wrap=true
- CellStyle cs = wb.createCellStyle();
- cs.setWrapText(true);
- cell.setCellStyle(cs);
- //increase row height to accomodate two lines of text
- row.setHeightInPoints((2*sheet.getDefaultRowHeightInPoints()));
- //adjust column width to fit the content
- sheet.autoSizeColumn((short)2);
- FileOutputStream fileOut = new FileOutputStream("ooxml-newlines.xlsx");
- wb.write(fileOut);
- fileOut.close();
Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); Sheet sheet = wb.createSheet(); Row row = sheet.createRow(2); Cell cell = row.createCell(2); cell.setCellValue("Use \n with word wrap on to create a new line"); //to enable newlines you need set a cell styles with wrap=true CellStyle cs = wb.createCellStyle(); cs.setWrapText(true); cell.setCellStyle(cs); //increase row height to accomodate two lines of text row.setHeightInPoints((2*sheet.getDefaultRowHeightInPoints())); //adjust column width to fit the content sheet.autoSizeColumn((short)2); FileOutputStream fileOut = new FileOutputStream("ooxml-newlines.xlsx"); wb.write(fileOut); fileOut.close();
换行的步骤:
- 在需要换行的地方加上\n cell.setCellValue("Use \n with word wrap on to create a new line");
- 设置行高 row.setHeightInPoints((2*sheet.getDefaultRowHeightInPoints()));
- 设置列只适应宽度 sheet.autoSizeColumn((short)2);
17. 数据格式化
- public void dataFormat() throws Exception{
- Workbook wb = new HSSFWorkbook();
- Sheet sheet = wb.createSheet("format sheet");
- CellStyle style;
- DataFormat format = wb.createDataFormat();
- Row row;
- Cell cell;
- short rowNum = 0;
- short colNum = 0;
- row = sheet.createRow(rowNum++);
- cell = row.createCell(colNum);
- cell.setCellValue(11111.25);
- style = wb.createCellStyle();
- style.setDataFormat(format.getFormat("0.0"));
- cell.setCellStyle(style);
- row = sheet.createRow(rowNum++);
- cell = row.createCell(colNum);
- cell.setCellValue(1111.25);
- style = wb.createCellStyle();
- style.setDataFormat(format.getFormat("#,###.0000"));
- cell.setCellStyle(style);
- FileOutputStream fileOut = new FileOutputStream("workbook.xls");
- wb.write(fileOut);
- fileOut.close();
- }
public void dataFormat() throws Exception{Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("format sheet"); CellStyle style; DataFormat format = wb.createDataFormat(); Row row; Cell cell; short rowNum = 0; short colNum = 0; row = sheet.createRow(rowNum++); cell = row.createCell(colNum); cell.setCellValue(11111.25); style = wb.createCellStyle(); style.setDataFormat(format.getFormat("0.0")); cell.setCellStyle(style); row = sheet.createRow(rowNum++); cell = row.createCell(colNum); cell.setCellValue(1111.25); style = wb.createCellStyle(); style.setDataFormat(format.getFormat("#,###.0000")); cell.setCellStyle(style); FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close();}
18. 设置打印区域
- Workbook wb = new HSSFWorkbook();
- Sheet sheet = wb.createSheet("Sheet1");
- //sets the print area for the first sheet
- wb.setPrintArea(0, "$A$1:$C$2");
- //Alternatively:
- wb.setPrintArea(
- 0, //sheet index
- 0, //start column
- 1, //end column
- 0, //start row
- 0 //end row
- );
- FileOutputStream fileOut = new FileOutputStream("workbook.xls");
- wb.write(fileOut);
- fileOut.close();
Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("Sheet1"); //sets the print area for the first sheet wb.setPrintArea(0, "$A$1:$C$2"); //Alternatively: wb.setPrintArea( 0, //sheet index 0, //start column 1, //end column 0, //start row 0 //end row ); FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close();
19. 设置页脚
- HSSFWorkbook wb = new HSSFWorkbook();
- HSSFSheet sheet = wb.createSheet("format sheet");
- HSSFFooter footer = sheet.getFooter();
- footer.setRight( "Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages() );
- // Create various cells and rows for spreadsheet.
- FileOutputStream fileOut = new FileOutputStream("workbook.xls");
- wb.write(fileOut);
- fileOut.close();
HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("format sheet"); HSSFFooter footer = sheet.getFooter(); footer.setRight( "Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages() ); // Create various cells and rows for spreadsheet. FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close();
备注:只有在打印预览的时候页脚才显示出来。
20. 选中一个Sheet
- Workbook wb = new HSSFWorkbook();
- Sheet sheet = wb.createSheet("row sheet");
- sheet.setSelected(true);
Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("row sheet"); sheet.setSelected(true);
21. 设置方法倍率
- Workbook wb = new HSSFWorkbook();
- Sheet sheet1 = wb.createSheet("new sheet");
- sheet1.setZoom(3,4); // 75 percent magnification
Workbook wb = new HSSFWorkbook(); Sheet sheet1 = wb.createSheet("new sheet"); sheet1.setZoom(3,4); // 75 percent magnification
Zoom是一个分数,例如如果方法75%,那么分子为3,分母为4。
22. 设置打印时的页宽和页高
- Workbook wb = new HSSFWorkbook();
- Sheet sheet = wb.createSheet("format sheet");
- PrintSetup ps = sheet.getPrintSetup();
- sheet.setAutobreaks(true);
- ps.setFitHeight((short)1);
- ps.setFitWidth((short)1);
- // Create various cells and rows for spreadsheet.
- FileOutputStream fileOut = new FileOutputStream("workbook.xls");
- wb.write(fileOut);
- fileOut.close();
Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("format sheet"); PrintSetup ps = sheet.getPrintSetup(); sheet.setAutobreaks(true); ps.setFitHeight((short)1); ps.setFitWidth((short)1); // Create various cells and rows for spreadsheet. FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close();
23. 移动行
- public void shiftRow() throws Exception{
- Workbook wb = new HSSFWorkbook();
- Sheet sheet = wb.createSheet("row sheet");
- for(int i=0;i<20;i++){
- Row row=sheet.createRow(i);
- Cell cell=row.createCell(0);
- cell.setCellValue(""+i);
- }
- // Shift rows 6 - 11 on the spreadsheet to the top (rows 0 - 5)
- //把第6-11行向上移动5行
- sheet.shiftRows(5, 10,-5);
- FileOutputStream fileOut = new FileOutputStream("workbook.xls");
- wb.write(fileOut);
- fileOut.close();
- }
public void shiftRow() throws Exception{Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("row sheet"); for(int i=0;i<20;i++){ Row row=sheet.createRow(i); Cell cell=row.createCell(0); cell.setCellValue(""+i); } // Shift rows 6 - 11 on the spreadsheet to the top (rows 0 - 5) //把第6-11行向上移动5行 sheet.shiftRows(5, 10,-5); FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close();}
POI学习笔记四将会介绍poi更高级的一些知识点。
- POI学习笔记 自定义颜色
- JAVA POI 自定义颜色
- POI-----自定义颜色
- 【POI】自定义cell的颜色
- Java poi 自定义RGB颜色
- POI生成excel文件,自定义单元格颜色
- POI生成excel文件,自定义单元格颜色
- POI生成excel文件,自定义单元格颜色
- POI之Excel自定义单元格背景颜色
- java POI实现Excel单元格自定义颜色
- POI 自定义16进制颜色导出
- POI设置自定义的RGB背景颜色
- POI学习笔记
- apache POI 学习笔记
- poi 颜色
- POI颜色
- Excel笔记:POI的学习
- poi.jar包学习笔记
- GitLab5.2 创建项目故障
- maven3和maven2不兼容引发的一次血案
- iOS 关于音频开发
- 你必须知道的5个JVM命令行标志
- Snow Patrol的chasing cars歌词大意
- POI学习笔记 自定义颜色
- 几个Eclipse超cool插件
- hdu_1278 漂亮面料的设计
- 云端BPM学习指南
- PS总结
- 基于chromium24的HTML5特性实现(input type = week date datetime datetimelocal month time)
- Linux内核的同步机制
- winform 未知的错误
- Extjs4.0.7 Formpanel 中各组件属性详解--(持续更新)