欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

xssf 自定义颜色

程序员文章站 2022-07-13 14:28:17
...

环境：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 [email protected] 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 [email protected] 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/

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

上一篇： JXL自定义颜色

下一篇： Echarts 自定义颜色

推荐阅读