- package excelUtils;
-
- import java.awt.Color;
- import java.io.File;
- import java.io.FileInputStream;
- import java.io.FileOutputStream;
- import java.io.IOException;
- import java.util.HashMap;
- import java.util.Map;
-
- import org.apache.poi.ss.usermodel.FillPatternType;
- import org.apache.poi.ss.usermodel.HorizontalAlignment;
- import org.apache.poi.ss.usermodel.VerticalAlignment;
- import org.apache.poi.xssf.usermodel.XSSFSheet;
- import org.apache.poi.xssf.usermodel.XSSFWorkbook;
- 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.XSSFFont;
- import org.apache.poi.xssf.usermodel.XSSFRow;
-
- public class ExcelStyle {
-
- private static XSSFWorkbook workbook = null;
-
- private static Color light_green = new Color(198, 224, 180);
- private static Color light_orange = new Color(248, 203, 173);
- private static Color light_blue = new Color(180, 198, 231);
- private static Color light_yellow = new Color(255, 230, 153);
- private static Color light_gray = new Color(217, 217, 217);
-
- public static void main(String[] args){
- String fileDir = "D:/test.xlsx";
- String sheetName = "sheet1";
-
- int[] columnWidth = { 10, 20, 30, 10, 20, 30 };//每列列宽
- setExcelSimpleStyle(fileDir, sheetName, columnWidth);
- }
-
- /**
- *只设置列宽, 颜色按默认绿橙蓝排列,居中
- *
- *@param fileDir
- *@param sheetName
- *@param columnWidth
- *@return
- */
- public static boolean setExcelSimpleStyle(String fileDir, String sheetName, int[] columnWidth) {
- String[] colors = { "light_green", "light_orange", "light_blue" };
- boolean inCenter = true;
- if (!ExcelCreater.fileExist(fileDir)) {
- return false;
- }
- if (!ExcelCreater.sheetExist(fileDir, sheetName)) {
- return false;
- }
- setStyle(fileDir, sheetName, columnWidth, colors, inCenter);
- return true;
- }
-
- /**
- *判断是否存在,设置列宽,颜色,居中,返回是否设置成功
- *
- *@param fileDir
- *@param sheetName
- *@param columnWidth
- *@param colors
- *@param inCenter
- */
- public static boolean setExcelStyle(String fileDir, String sheetName, int[] columnWidth, String[] colors,
- boolean inCenter) {
-
- if (!ExcelCreater.fileExist(fileDir)) {
- return false;
- }
- if (!ExcelCreater.sheetExist(fileDir, sheetName)) {
- return false;
- }
- setStyle(fileDir, sheetName, columnWidth, colors, inCenter);
- return true;
- }
-
- /**
- *设置列宽,颜色,居中
- *
- *@param fileDir
- *@param sheetName
- *@param columnWidth
- *@param colors
- *@param inCenter
- */
- public static void setStyle(String fileDir, String sheetName, int[] columnWidth, String[] colors,
- boolean inCenter) {
-
- FileOutputStream out = null;
- File file = new File(fileDir);
- try {
- workbook = new XSSFWorkbook(new FileInputStream(file));
- XSSFSheet sheet = workbook.getSheet(sheetName);
- setColumnWidth(sheet, columnWidth);
-
- int rowCount = sheet.getLastRowNum() + 1;
- int columnCount = sheet.getRow(0).getLastCellNum();
-
- Map<Integer, XSSFCellStyle> styleMap = new HashMap<>();
- for (int colorIndex = 0; colorIndex < colors.length; colorIndex++) {
- XSSFCellStyle style = getStyle(colors[colorIndex], inCenter, null, 11);
- styleMap.put(colorIndex, style);
- }
-
- for (int rowIndex = 0; rowIndex < rowCount; rowIndex++) {
- XSSFRow newRow = sheet.getRow(rowIndex);
- for (int columnIndex = 0; columnIndex < columnCount; columnIndex++) {
- XSSFCell cell = newRow.getCell(columnIndex);
- cell.setCellStyle(styleMap.get(columnIndex % styleMap.size()));
- }
- }
-
- out = new FileOutputStream(fileDir);
- workbook.write(out);
- } catch (Exception e) {
- e.printStackTrace();
- } finally {
- try {
- if (out != null) {
- out.close();
- }
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
- }
-
- /**
- *设置每行高度
- *
- *@param sheet
- *@param rowHeight
- */
- public static void setRowHeight(XSSFSheet sheet, int rowHeight) {
- sheet.setDefaultRowHeight((short) rowHeight);
- }
-
- /**
- *分别设置每一列宽度,若只有一个值,全部设为此值
- *
- *@param sheet
- *@param columnWidth
- */
- public static void setColumnWidth(XSSFSheet sheet, int[] columnWidth) {
- if (columnWidth.length == 1) {
- sheet.setDefaultColumnWidth(columnWidth[0]);
- } else {
- for (int i = 0; i < columnWidth.length; i++) {
- sheet.setColumnWidth(i, columnWidth[i] * 256);
- }
- }
- }
-
- /**
- *获得制定颜色字体居中的style
- *
- *@param color
- *@param inCenter
- *@param fontName
- *@param fontHeight
- */
- public static XSSFCellStyle getStyle(String color, boolean inCenter, String fontName, int fontHeight) {
- XSSFCellStyle style = workbook.createCellStyle();
- if (color != null && !color.equals("")) {
- setStyleColor(style, color);
- }
- if (inCenter == true) {
- setAlignment(style);
- }
- if (fontName != null && !fontName.equals("")) {
- setStyleFont(style, fontName, fontHeight);
- }
- return style;
- }
-
- /**
- *设置style的颜色,目前五种颜色可选
- *
- *@param style
- *@param color
- */
- public static void setStyleColor(XSSFCellStyle style, String color) {
- if (color.equals("light_green")) {
- style.setFillForegroundColor(new XSSFColor(light_green));
- }
- if (color.equals("light_orange")) {
- style.setFillForegroundColor(new XSSFColor(light_orange));
- }
- if (color.equals("light_blue")) {
- style.setFillForegroundColor(new XSSFColor(light_blue));
- }
- if (color.equals("light_yellow")) {
- style.setFillForegroundColor(new XSSFColor(light_yellow));
- }
- if (color.equals("light_gray")) {
- style.setFillForegroundColor(new XSSFColor(light_gray));
- }
- style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
- }
-
- /**
- *设置style字体
- *
- *@param style
- *@param fontName
- *@param fontHeight
- */
- public static void setStyleFont(XSSFCellStyle style, String fontName, int fontHeight) {
- XSSFFont font = workbook.createFont();
- font.setFontName(fontName);
- font.setFontHeight(fontHeight);
- style.setFont(font);
- }
-
- /**
- *设置style水平垂直居中
- *
- *@param style
- */
- public static void setAlignment(XSSFCellStyle style) {
- style.setAlignment(HorizontalAlignment.CENTER);// 水平居中
- style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
- }
-
- }
|