环境:poi3.9+xssf+excel2013+template.xlsx文件
背景:在用poi写excel时,单元格的颜色需求是必须要模板中的一样。
思路:先用【颜色摘取器】将颜色的RGB摘下来,然后用XSSF实现自定义颜色即可。
1、主函数类
package com.poi.excel.client;
import java.awt.Color;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.examples.CellTypes;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.poi.excel.util.POIExcelUtil;
public class ExcelTestClient {
static org.apache.log4j.Logger logger = org.apache.log4j.Logger
.getLogger(ExcelTestClient.class);
public static void main(String[] args) {
new ExcelTestClient().writeWithExcelUtil();
}
private void writeWithExcelUtil() {
// 如何通过一个已有的workbook创建新的workbook
try {
// Workbook wb1=new XSSFWorkbook("oldPath");
Workbook wb = new XSSFWorkbook(new FileInputStream(
"c:\\germmy\\template.xlsx"));
Map<String, CellStyle> styles = createStyles(wb);
// create a new sheet
Sheet s = wb.getSheet("机构呼入量报表");
//1.先写入左上方标题
String content="20151013-20151019电销呼入量报表";
POIExcelUtil.writeDataToExcel(0, 0, s, content,
Cell.CELL_TYPE_STRING, null);//sytle用null,表明利用原有样式
//2.B4,C4,B5的值
double b4_db=1000,c4_db=1500,b5_db=2000;
//2.1 B4
POIExcelUtil.writeDataToExcel(3, 1, s, b4_db,
Cell.CELL_TYPE_NUMERIC, styles.get("normalcell"));//sytle用null,表明利用原有样式
//2.2 C4
POIExcelUtil.writeDataToExcel(3, 2, s, c4_db,
Cell.CELL_TYPE_NUMERIC, styles.get("normalcell"));//sytle用normalcell
//2.3 B5
POIExcelUtil.writeDataToExcel(4, 1, s, b5_db,
Cell.CELL_TYPE_NUMERIC, styles.get("normalcell"));sytle用normalcell
//3.G4,B41的公式,
//3.1 g4
String g4="SUM(B4:F4)",b41="SUM(B4:B40)";
POIExcelUtil.writeDataToExcel(3, 6, s, g4,
Cell.CELL_TYPE_FORMULA, styles.get("formula_v"));sytle用formula
//3.2 b41
POIExcelUtil.writeDataToExcel(40, 1, s, b41,
Cell.CELL_TYPE_FORMULA, styles.get("formula_h"));sytle用formula
// Save
String filename = "c:/germmy/workbook.xls";
if (wb instanceof XSSFWorkbook) {
filename = filename + "x";
}
FileOutputStream out = new FileOutputStream(filename);
wb.write(out);
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* Create a library of cell styles
*/
private static Map<String, CellStyle> createStyles(Workbook wb) {
Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
CellStyle style;
Font titleFont = wb.createFont();
titleFont.setFontHeightInPoints((short) 18);
titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFont(titleFont);
styles.put("title", style);
Font monthFont = wb.createFont();
monthFont.setFontHeightInPoints((short) 11);
monthFont.setColor(IndexedColors.WHITE.getIndex());
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setFont(monthFont);
style.setWrapText(true);
styles.put("header", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setWrapText(true);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
styles.put("cell", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
styles.put("formula", style);
//*********************add by germmy@20131013 start************************
//普通单元格,四周有黑线
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_RIGHT);
style.setWrapText(true);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setDataFormat(wb.createDataFormat().getFormat("#,##0"));//这样写百分百变成货币
styles.put("normalcell", style);
//横向求和公式,粗体,有淡紫色背景,四周有黑色
style = wb.createCellStyle();
XSSFCellStyle styleTemp=((XSSFCellStyle)style);
styleTemp.setAlignment(CellStyle.ALIGN_RIGHT);
styleTemp.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
Font formulaFont = wb.createFont();
formulaFont.setFontName("宋体");
formulaFont.setFontHeightInPoints((short) 11);
formulaFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
styleTemp.setFont(formulaFont);
//控制颜色
styleTemp.setFillForegroundColor(new XSSFColor( new Color(220, 230, 241)));
// style.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE.getIndex());
styleTemp.setFillPattern(CellStyle.SOLID_FOREGROUND);
styleTemp.setBorderRight(CellStyle.BORDER_THIN);
styleTemp.setRightBorderColor(IndexedColors.BLACK.getIndex());
styleTemp.setBorderLeft(CellStyle.BORDER_THIN);
styleTemp.setLeftBorderColor(IndexedColors.BLACK.getIndex());
styleTemp.setBorderTop(CellStyle.BORDER_THIN);
styleTemp.setTopBorderColor(IndexedColors.BLACK.getIndex());
styleTemp.setBorderBottom(CellStyle.BORDER_THIN);
styleTemp.setBottomBorderColor(IndexedColors.BLACK.getIndex());
styleTemp.setDataFormat(wb.createDataFormat().getFormat("#,##0"));
styles.put("formula_h", styleTemp);//横向的公式颜色
//纵向求和公式,四周有黑线
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_RIGHT);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setDataFormat(wb.createDataFormat().getFormat("#,##0"));
styles.put("formula_v", style);//纵向的公式颜色
//*********************add by germmy@20131013 end************************
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
styles.put("formula_2", style);
return styles;
}
}
2、ExcelUtil文件:
package com.poi.excel.util;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
public class POIExcelUtil {
/**
*
* @param row
* @param column
* @param sheet
* @param content
* @param cellTypes
* @param cellStyle
* @throws Exception
*/
public static void writeDataToExcel(int row, int column, Sheet sheet,
Object content, int cellType, CellStyle cellStyle) throws Exception {
Row r1 = sheet.getRow(row);
Cell c1 = r1.getCell(column);
if (null != cellStyle) {
c1.setCellStyle(cellStyle);
}
switch (cellType) {
case Cell.CELL_TYPE_NUMERIC:
c1.setCellValue((double) content);
break;
case Cell.CELL_TYPE_STRING:
c1.setCellValue((String) content);
break;
case Cell.CELL_TYPE_FORMULA:
c1.setCellFormula((String) content);
break;
default:
c1.setCellValue((String) content);//默认的先暂时全用这个
System.out.println("未匹配到东西!");
break;
}
}
}
3、JAR包下载地址:http://www.apache.org/dyn/closer.cgi/poi/release/bin/poi-bin-3.9-20121203.zip
-----------------------------------------------------------------------------------------------------------------
其他参考文档:
1、http://poi.apache.org/spreadsheet/examples.html
2、http://blog.sina.com.cn/s/blog_62c89b450100lxnh.html
3、http://kxjhlele.iteye.com/blog/321392
4、http://www.docin.com/p-69674027.html
5、http://bbs.csdn.net/topics/360031211
6、http://fangwei.iteye.com/blog/1161085
--------------------------------------------------------------------------------------------------------------
ps:颜色提取器 http://www.douban.com/group/topic/6338619/