xssf 自定义颜色
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创建新的workbooktry {// Workbook wb1=new XSSFWorkbook("oldPath");Workbook wb = new XSSFWorkbook(new FileInputStream("c:\\germmy\\template.xlsx"));Map<String, CellStyle> styles = createStyles(wb);// create a new sheetSheet 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 B4POIExcelUtil.writeDataToExcel(3, 1, s, b4_db,Cell.CELL_TYPE_NUMERIC, styles.get("normalcell"));//sytle用null,表明利用原有样式//2.2 C4POIExcelUtil.writeDataToExcel(3, 2, s, c4_db,Cell.CELL_TYPE_NUMERIC, styles.get("normalcell"));//sytle用normalcell//2.3 B5POIExcelUtil.writeDataToExcel(4, 1, s, b5_db,Cell.CELL_TYPE_NUMERIC, styles.get("normalcell"));////sytle用normalcell//3.G4,B41的公式，//3.1 g4String 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 b41POIExcelUtil.writeDataToExcel(40, 1, s, b41,Cell.CELL_TYPE_FORMULA, styles.get("formula_h"));////sytle用formula// SaveString 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/
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?