/**
* 设置列边框
*
* @param style
*/
private void setColumnBorder(XSSFCellStyle style, int tableType) {
// ============================
// 设置单元格边框样式
// CellStyle.BORDER_DOUBLE 双边线
// CellStyle.BORDER_THIN 细边线
// CellStyle.BORDER_MEDIUM 中等边线
// CellStyle.BORDER_DASHED 虚线边线
// CellStyle.BORDER_HAIR 小圆点虚线边线
// CellStyle.BORDER_THICK 粗边线
// ============================
// 如果tableType为0，则为Head
// 如果为1，则为body
if (tableType == 0) {
style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); // 顶部边框粗线
style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); // 底部边框粗线
} else {
style.setBorderTop(HSSFCellStyle.BORDER_THIN); // 顶部边框细线
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 底部边框细线
}
style.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 左边边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边边框
style.setBottomBorderColor(new XSSFColor(new java.awt.Color(153, 153, 255))); // 设置底部边框颜色
style.setTopBorderColor(new XSSFColor(new java.awt.Color(153, 153, 255))); // 设置顶部边框颜色
style.setLeftBorderColor(new XSSFColor(new java.awt.Color(153, 153, 255))); // 设置左边边框颜色
style.setRightBorderColor(new XSSFColor(new java.awt.Color(153, 153, 255))); // 设置右边边框颜色
// style.setWrapText(true);
// // 设置单元格内容是否自动换行
}
// If a border style is set on a ReportBandElement we must apply it to all subreport cells
private XSSFCellStyle updateSubreportBandElementStyle(
XSSFCellStyle cellStyle,
BandElement bandElement,
Object value,
int gridRow,
int gridColumn,
int colSpan) {
if (subreportCellStyle == null) {
return cellStyle;
}
if (gridColumn == 0) {
cellStyle.setBorderLeft(subreportCellStyle.getBorderLeft());
cellStyle.setLeftBorderColor(subreportCellStyle.getLeftBorderColor());
} else if (gridColumn + colSpan - 1 == bean.getReportLayout().getColumnCount() - 1) {
cellStyle.setBorderRight(subreportCellStyle.getBorderRight());
cellStyle.setRightBorderColor(subreportCellStyle.getRightBorderColor());
}
if (pageRow == 0) {
cellStyle.setBorderTop(subreportCellStyle.getBorderTop());
cellStyle.setTopBorderColor(subreportCellStyle.getTopBorderColor());
} else if ((pageRow + 1) == getRowsCount()) {
cellStyle.setBorderBottom(subreportCellStyle.getBorderBottom());
cellStyle.setBottomBorderColor(subreportCellStyle.getBottomBorderColor());
}
return cellStyle;
}
/**
* 设置表头的单元格样式
*
* @return
*/
public XSSFCellStyle getHeadStyle() {
// 创建单元格样式
XSSFCellStyle cellStyle = wb.createCellStyle();
// 设置单元格的背景颜色为淡蓝色
cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
// 设置单元格居中对齐
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
// 设置单元格垂直居中对齐
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
// 创建单元格内容显示不下时自动换行
cellStyle.setWrapText(true);
// 设置单元格字体样式
XSSFFont font = wb.createFont();
// 设置字体加粗
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("宋体");
font.setFontHeight((short) 200);
cellStyle.setFont(font);
// 设置单元格边框为细线条
cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
return cellStyle;
}
/**
* 罫線スタイルの<b>CellStyle</b>を生成
*
* @param workbook ワークブック
* @param backgroundColor 背景色
* @return <b>CellStyle</b>
*/
public static XSSFCellStyle createTableDataCellStyle(
XSSFWorkbook workbook,
boolean isTop,
boolean isBottom,
boolean isLeft,
boolean isRight,
Color backgroundColor,
short fontSize,
boolean bold) {
XSSFCellStyle style = workbook.createCellStyle();
style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); // 必ずsetFillForegroundColorの直前に記述すること
style.setFillForegroundColor(new XSSFColor(backgroundColor));
style.setAlignment(CellStyle.ALIGN_LEFT); // 水平方法の位置
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
style.setWrapText(true); // 折り返して全体を表示する
// style.setShrinkToFit(true);//縮小して全体を表示する
// 文字サイズ設定
XSSFFont font = workbook.createFont();
font.setFontHeightInPoints(fontSize);
if (bold) {
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
}
style.setFont(font);
if (isTop) {
style.setBorderTop(XSSFCellStyle.BORDER_MEDIUM);
} else {
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
}
if (isBottom) {
style.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM);
} else {
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
}
if (isLeft) {
style.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM);
} else {
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
}
if (isRight) {
style.setBorderRight(XSSFCellStyle.BORDER_MEDIUM);
} else {
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
}
return style;
}
/**
* 罫線スタイルの<b>CellStyle</b>を生成
*
* @param workbook ワークブック
* @return <b>CellStyle</b>
*/
public static XSSFCellStyle createTableDataCellStyle(XSSFWorkbook workbook) {
XSSFCellStyle style = workbook.createCellStyle();
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
return style;
}
private void copyDefaultCellStyle(XSSFDataFormat format, Cell cell, XSSFCellStyle cs, int i) {
cs.setAlignment(XSSFCellStyle.ALIGN_CENTER_SELECTION);
cs.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
cs.setBorderTop(XSSFCellStyle.BORDER_MEDIUM);
cs.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM);
cs.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM);
cs.setBorderRight(XSSFCellStyle.BORDER_MEDIUM);
if (i == 1) cs.setDataFormat(format.getFormat("yyyy-MM-dd HH:mm:s"));
if (i == 2) cs.setDataFormat(format.getFormat("0"));
cell.setCellStyle(cs);
}
/**
* 罫線スタイルの<b>CellStyle</b>を生成
*
* @param workbook ワークブック
* @param isBorder 罫線描画フラグ
* @return <b>CellStyle</b>
*/
public static XSSFCellStyle createTableDataCellStyle(XSSFWorkbook workbook, boolean isBorder) {
XSSFCellStyle style = workbook.createCellStyle();
style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); // 必ずsetFillForegroundColorの直前に記述すること
if (isBorder) {
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
}
return style;
}
/**
* 设置列上边框粗边线
*
* @param style
*/
private XSSFCellStyle setColumnTopBorder(XSSFWorkbook workbook) {
// ============================
// 设置单元格边框样式
// CellStyle.BORDER_DOUBLE 双边线
// CellStyle.BORDER_THIN 细边线
// CellStyle.BORDER_MEDIUM 中等边线
// CellStyle.BORDER_DASHED 虚线边线
// CellStyle.BORDER_HAIR 小圆点虚线边线
// CellStyle.BORDER_THICK 粗边线
// ============================
XSSFCellStyle style = workbook.createCellStyle();
style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); // 顶部边框粗线
style.setTopBorderColor(new XSSFColor(new java.awt.Color(153, 153, 255))); // 设置顶部边框颜色
return style;
}
/**
* デフォルトのテーブルヘッダースタイルを作成
*
* @param workbook ワークブック
* @param bold 太字設定フラグ
* @param fontSize フォントサイズ
* @param backgroundColor 背景色
* @return <b>CellStyle</b>
*/
public static XSSFCellStyle createDefaultTableHeaderCellStyle(
XSSFWorkbook workbook, boolean bold, boolean center, int fontSize, Color backgroundColor) {
XSSFFont font = workbook.createFont();
if (bold) {
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
}
font.setFontHeightInPoints((short) fontSize);
font.setFontName(DEFAULT_FONT_NAME);
XSSFCellStyle style = workbook.createCellStyle();
if (center) {
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
}
style.setFont(font);
style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); // 必ずsetFillForegroundColorの直前に記述すること
style.setFillForegroundColor(new XSSFColor(backgroundColor));
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
return style;
}
/**
* 设置表体的单元格样式
*
* @return
*/
public XSSFCellStyle getBodyStyle() {
// 创建单元格样式
XSSFCellStyle cellStyle = wb.createCellStyle();
// 设置单元格居中对齐
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
// 设置单元格垂直居中对齐
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
// 创建单元格内容显示不下时自动换行
cellStyle.setWrapText(true);
// 设置单元格字体样式
XSSFFont font = wb.createFont();
// 设置字体加粗
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("宋体");
font.setFontHeight((short) 200);
cellStyle.setFont(font);
// 设置单元格边框为细线条
cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
return cellStyle;
}
public static XSSFCellStyle getNewStyle(
XSSFWorkbook workBook, CellStyle cellStyle, XSSFFont font) {
XSSFCellStyle style = workBook.createCellStyle();
// 对齐方式
style.setAlignment(cellStyle.getAlignment());
style.setVerticalAlignment(cellStyle.getVAlignment());
// 设置背景颜色
// 最好的设置Pattern
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 单元格背景的显示模式
style.setFillForegroundColor(cellStyle.getColor()); // 单元格背景的显示模式．
// style.setFillBackgroundColor(arg0);
// 设置边框
style.setBorderBottom(cellStyle.getBorderBottom()); // 下边框
style.setBorderLeft(cellStyle.getBorderLeft()); // 左边框
style.setBorderTop(cellStyle.getBorderTop()); // 上边框
style.setBorderRight(cellStyle.getBorderRight()); // 右边框
// 设置边框颜色
style.setBottomBorderColor(cellStyle.getBottomBorderColor());
style.setTopBorderColor(cellStyle.getTopBorderColor());
style.setLeftBorderColor(cellStyle.getLeftBorderColor());
style.setRightBorderColor(cellStyle.getRightBorderColor());
// 设置自动换行
style.setWrapText(cellStyle.getWrapText());
style.setHidden(cellStyle.getHidden());
// 数据格式
style.setDataFormat(cellStyle.getDataFormate());
style.setLocked(cellStyle.getLocked());
// 文本旋转 请注意，这里的Rotation取值是从-90到90，而不是0-180度
style.setRotation(cellStyle.getRotation());
// 文本缩进
style.setIndention(cellStyle.getIndention());
// 设置字体
style.setFont(font);
return style;
}
private XSSFCellStyle buildBandElementStyle(
BandElement bandElement, Object value, int gridRow, int gridColumn, int colSpan) {
Map<String, Object> style = buildCellStyleMap(bandElement, value, gridRow, gridColumn, colSpan);
XSSFCellStyle cellStyle = null;
XSSFFont cellFont = null;
int fontKey = -1;
int styleKey = -1;
// we have to create new fonts and styles if some formatting conditions are met
// also for subreports we may have a subreportCellStyle passed by ReportBandElement
boolean cacheFont = false;
boolean cacheAllFont = false;
boolean cacheStyle = false;
boolean cacheAllStyle = false;
if ((modifiedStyle[gridRow][gridColumn]) || bean.isSubreport()) {
fontKey = getFontKey(style);
if (fontKey != -1) {
cellFont = condFonts.get(fontKey);
}
if (cellFont == null) {
cellFont = wb.createFont();
cacheFont = true;
}
styleKey = getStyleKey(style, bandElement);
if (styleKey != -1) {
cellStyle = condStyles.get(styleKey);
}
if (cellStyle == null) {
cellStyle = wb.createCellStyle();
cacheStyle = true;
}
modifiedStyle[gridRow][gridColumn] = false;
} else {
fontKey = getFontKey(style);
if (fontKey != -1) {
cellFont = fonts.get(fontKey);
}
if ((cellFont == null) && (bandElement != null)) {
cellFont = wb.createFont();
cacheAllFont = true;
}
styleKey = getStyleKey(style, bandElement);
if (styleKey != -1) {
cellStyle = styles.get(styleKey);
}
if (cellStyle == null) {
cellStyle = wb.createCellStyle();
cacheAllStyle = true;
}
}
// HSSFPalette cellPal = wb.getCustomPalette();
if (style.containsKey(StyleFormatConstants.FONT_FAMILY_KEY)) {
String val = (String) style.get(StyleFormatConstants.FONT_FAMILY_KEY);
cellFont.setFontName(val);
}
if (style.containsKey(StyleFormatConstants.FONT_SIZE)) {
Float val = (Float) style.get(StyleFormatConstants.FONT_SIZE);
cellFont.setFontHeightInPoints(val.shortValue());
}
if (style.containsKey(StyleFormatConstants.FONT_COLOR)) {
Color val = (Color) style.get(StyleFormatConstants.FONT_COLOR);
cellFont.setColor(ExcelColorSupport.getNearestColor(val));
}
if (style.containsKey(StyleFormatConstants.FONT_STYLE_KEY)) {
if (StyleFormatConstants.FONT_STYLE_NORMAL.equals(
style.get(StyleFormatConstants.FONT_STYLE_KEY))) {
cellFont.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
}
if (StyleFormatConstants.FONT_STYLE_BOLD.equals(
style.get(StyleFormatConstants.FONT_STYLE_KEY))) {
cellFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
}
if (StyleFormatConstants.FONT_STYLE_ITALIC.equals(
style.get(StyleFormatConstants.FONT_STYLE_KEY))) {
cellFont.setItalic(true);
}
if (StyleFormatConstants.FONT_STYLE_BOLDITALIC.equals(
style.get(StyleFormatConstants.FONT_STYLE_KEY))) {
cellFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
cellFont.setItalic(true);
}
}
if (cacheFont && (fontKey != -1)) {
condFonts.put(fontKey, cellFont);
}
if (cacheAllFont && (fontKey != -1)) {
fonts.put(fontKey, cellFont);
}
if (style.containsKey(StyleFormatConstants.BACKGROUND_COLOR)) {
Color val = (Color) style.get(StyleFormatConstants.BACKGROUND_COLOR);
cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(ExcelColorSupport.getNearestColor(val));
}
if (style.containsKey(StyleFormatConstants.HORIZONTAL_ALIGN_KEY)) {
if (StyleFormatConstants.HORIZONTAL_ALIGN_LEFT.equals(
style.get(StyleFormatConstants.HORIZONTAL_ALIGN_KEY))) {
cellStyle.setAlignment((short) 1);
}
if (StyleFormatConstants.HORIZONTAL_ALIGN_RIGHT.equals(
style.get(StyleFormatConstants.HORIZONTAL_ALIGN_KEY))) {
cellStyle.setAlignment((short) 3);
}
if (StyleFormatConstants.HORIZONTAL_ALIGN_CENTER.equals(
style.get(StyleFormatConstants.HORIZONTAL_ALIGN_KEY))) {
cellStyle.setAlignment((short) 2);
}
}
if (style.containsKey(StyleFormatConstants.VERTICAL_ALIGN_KEY)) {
if (StyleFormatConstants.VERTICAL_ALIGN_TOP.equals(
style.get(StyleFormatConstants.VERTICAL_ALIGN_KEY))) {
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP);
}
if (StyleFormatConstants.VERTICAL_ALIGN_MIDDLE.equals(
style.get(StyleFormatConstants.VERTICAL_ALIGN_KEY))) {
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
}
if (StyleFormatConstants.VERTICAL_ALIGN_BOTTOM.equals(
style.get(StyleFormatConstants.VERTICAL_ALIGN_KEY))) {
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_BOTTOM);
}
} else {
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
}
short left = 0, right = 0, top = 0, bottom = 0;
Color leftColor = Color.BLACK,
rightColor = Color.BLACK,
topColor = Color.BLACK,
bottomColor = Color.BLACK;
if (style.containsKey(StyleFormatConstants.BORDER_LEFT)) {
Float val = (Float) style.get(StyleFormatConstants.BORDER_LEFT);
//
left = val.shortValue();
if (left == BORDER_THIN_VALUE) {
cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
}
if (left == BORDER_MEDIUM_VALUE) {
cellStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM);
}
if (left == BORDER_THICK_VALUE) {
cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THICK);
}
Color color = (Color) style.get(StyleFormatConstants.BORDER_LEFT_COLOR);
leftColor = color;
cellStyle.setLeftBorderColor(ExcelColorSupport.getNearestColor(color));
}
if (style.containsKey(StyleFormatConstants.BORDER_RIGHT)) {
Float val = (Float) style.get(StyleFormatConstants.BORDER_RIGHT);
//
right = val.shortValue();
if (right == BORDER_THIN_VALUE) {
cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
}
if (right == BORDER_MEDIUM_VALUE) {
cellStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM);
}
if (right == BORDER_THICK_VALUE) {
cellStyle.setBorderRight(XSSFCellStyle.BORDER_THICK);
}
Color color = (Color) style.get(StyleFormatConstants.BORDER_RIGHT_COLOR);
rightColor = color;
cellStyle.setRightBorderColor(ExcelColorSupport.getNearestColor(color));
}
if (style.containsKey(StyleFormatConstants.BORDER_TOP)) {
Float val = (Float) style.get(StyleFormatConstants.BORDER_TOP);
//
top = val.shortValue();
if (top == BORDER_THIN_VALUE) {
cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
}
if (top == BORDER_MEDIUM_VALUE) {
cellStyle.setBorderTop(XSSFCellStyle.BORDER_MEDIUM);
}
if (top == BORDER_THICK_VALUE) {
cellStyle.setBorderTop(XSSFCellStyle.BORDER_THICK);
}
Color color = (Color) style.get(StyleFormatConstants.BORDER_TOP_COLOR);
topColor = color;
cellStyle.setTopBorderColor(ExcelColorSupport.getNearestColor(color));
}
if (style.containsKey(StyleFormatConstants.BORDER_BOTTOM)) {
Float val = (Float) style.get(StyleFormatConstants.BORDER_BOTTOM);
//
bottom = val.shortValue();
if (bottom == BORDER_THIN_VALUE) {
cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
}
if (bottom == BORDER_MEDIUM_VALUE) {
cellStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM);
}
if (bottom == BORDER_THICK_VALUE) {
cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THICK);
}
Color color = (Color) style.get(StyleFormatConstants.BORDER_BOTTOM_COLOR);
bottomColor = color;
cellStyle.setBottomBorderColor(ExcelColorSupport.getNearestColor(color));
}
border = new Border(left, right, top, bottom);
border.setLeftColor(leftColor);
border.setRightColor(rightColor);
border.setTopColor(topColor);
border.setBottomColor(bottomColor);
if (cellFont != null) {
cellStyle.setFont(cellFont);
}
if (style.containsKey(StyleFormatConstants.PATTERN)) {
String pattern = (String) style.get(StyleFormatConstants.PATTERN);
XSSFDataFormat format = wb.createDataFormat();
cellStyle.setDataFormat(format.getFormat(pattern));
} else {
cellStyle.setDataFormat((short) 0);
}
if (bandElement != null) {
cellStyle.setWrapText(bandElement.isWrapText());
}
cellStyle =
updateSubreportBandElementStyle(
cellStyle, bandElement, value, gridRow, gridColumn, colSpan);
if (cacheStyle && (styleKey != -1)) {
condStyles.put(styleKey, cellStyle);
}
if (cacheAllStyle && (styleKey != -1)) {
styles.put(styleKey, cellStyle);
}
return cellStyle;
}
public static boolean writeMultArray2XLSXFile(
String fileName,
List<List<String[]>> arrys,
List<String> sheetLabels,
boolean hasHead,
int indexKey)
throws Exception {
if (arrys.isEmpty()) {
System.err.println("No input data!");
return false;
}
XSSFWorkbook wb = new XSSFWorkbook();
XSSFCellStyle headStyle = wb.createCellStyle();
// apply custom font to the text in the comment
XSSFFont font = wb.createFont();
font.setFontName("Courier New");
font.setFontHeightInPoints((short) 10);
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
font.setColor(HSSFColor.RED.index);
headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
headStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
headStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
headStyle.setLocked(true);
headStyle.setFont(font);
XSSFCellStyle bodyStyle = wb.createCellStyle();
bodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
bodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
bodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
XSSFCellStyle markedBodyStyle = wb.createCellStyle();
markedBodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
markedBodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
markedBodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
markedBodyStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
markedBodyStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
String lastKey = null;
int switcher = -1;
XSSFCell cell = null;
String[] titleNames = null;
int d = 0;
for (List<String[]> arry : arrys) {
XSSFSheet sheet1 = wb.createSheet(sheetLabels.get(d));
if (hasHead) {
titleNames = (String[]) arry.get(0);
}
int columnNum = ((String[]) arry.get(0)).length;
for (int i = 0; i < columnNum; i++) {
sheet1.setColumnWidth(i, (short) ((30 * 6) / ((double) 1 / 20)));
}
int rowIndex = 0;
// create titile row
XSSFRow row = sheet1.createRow(rowIndex);
if (titleNames != null) {
for (int i = 0; i < columnNum; i++) {
cell = row.createCell(i);
cell.setCellValue(titleNames[i]);
cell.setCellStyle(headStyle);
}
rowIndex++;
}
int rowNum = arry.size();
for (int i = rowIndex; i < rowNum; i++) {
row = sheet1.createRow((i));
String[] line = (String[]) arry.get(i);
columnNum = line.length;
if (indexKey >= 0) {
if (lastKey == null && line[indexKey] != null) {
lastKey = line[indexKey];
switcher *= -1;
} else if (lastKey != null && line[indexKey] == null) {
lastKey = line[indexKey];
switcher *= -1;
} else if (lastKey == null && line[indexKey] == null) {
} else {
if (!lastKey.equals(line[indexKey])) {
switcher *= -1;
lastKey = line[indexKey];
}
}
} else {
switcher = 1;
}
for (int j = 0; j < columnNum; j++) {
cell = row.createCell(j);
if (switcher > 0) {
cell.setCellStyle(bodyStyle);
} else {
cell.setCellStyle(markedBodyStyle);
}
if (line[j] != null) {
if (Util.isNumeric(line[j])) {
// org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC
cell.setCellType(0);
cell.setCellValue(Double.parseDouble(line[j]));
} else {
cell.setCellValue(line[j]);
}
} else {
cell.setCellValue(".");
}
}
}
d++;
}
// Write the output to a inFile
FileOutputStream fileOut = new FileOutputStream(fileName);
wb.write(fileOut);
fileOut.close();
return true;
}
public static boolean convertTextFile2XLSXFile(
String inFileName, String outFileName, boolean hasHead, int indexKey) throws Exception {
BufferedReader br = LocalFileFunc.getBufferedReader(inFileName);
String line = br.readLine();
if (line == null) {
return false;
}
String[] cells1 = Util.tokenize(line, '\t');
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet1 = wb.createSheet("Data");
int columnNum = cells1.length;
for (int i = 0; i < columnNum; i++) {
sheet1.setColumnWidth(i, (short) ((30 * 6) / ((double) 1 / 20)));
}
XSSFCellStyle headStyle = wb.createCellStyle();
// apply custom font to the text in the comment
XSSFFont font = wb.createFont();
font.setFontName("Courier New");
font.setFontHeightInPoints((short) 10);
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
font.setColor(HSSFColor.RED.index);
headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
headStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
headStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
headStyle.setLocked(true);
headStyle.setFont(font);
XSSFCellStyle bodyStyle = wb.createCellStyle();
bodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
bodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
bodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
XSSFCellStyle markedBodyStyle = wb.createCellStyle();
markedBodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
markedBodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
markedBodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
markedBodyStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
markedBodyStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
int rowIndex = 0;
// create titile row
XSSFRow row = sheet1.createRow(rowIndex);
String lastKey = null;
int switcher = -1;
XSSFCell cell = null;
if (hasHead) {
for (int i = 0; i < columnNum; i++) {
cell = row.createCell(i);
cell.setCellValue(cells1[i]);
cell.setCellStyle(headStyle);
}
} else {
for (int i = 0; i < columnNum; i++) {
cell = row.createCell(i);
cell.setCellValue(cells1[i]);
cell.setCellStyle(bodyStyle);
}
}
rowIndex++;
while ((line = br.readLine()) != null) {
cells1 = Util.tokenize(line, '\t');
row = sheet1.createRow((rowIndex));
columnNum = cells1.length;
if (indexKey >= 0) {
if (lastKey == null && cells1[indexKey] != null) {
lastKey = cells1[indexKey];
switcher *= -1;
} else if (lastKey != null && cells1[indexKey] == null) {
lastKey = cells1[indexKey];
switcher *= -1;
} else if (lastKey == null && cells1[indexKey] == null) {
} else {
if (!lastKey.equals(cells1[indexKey])) {
switcher *= -1;
lastKey = cells1[indexKey];
}
}
} else {
switcher = 1;
}
// System.out.println(cells1[0]);
for (int j = 0; j < columnNum; j++) {
cell = row.createCell(j);
if (switcher > 0) {
cell.setCellStyle(bodyStyle);
} else {
cell.setCellStyle(markedBodyStyle);
}
if (cells1[j] != null) {
if (Util.isNumeric(cells1[j])) {
// org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC
cell.setCellType(0);
cell.setCellValue(Double.parseDouble(cells1[j]));
} else {
cell.setCellValue(cells1[j]);
}
} else {
cell.setCellValue(".");
}
}
rowIndex++;
}
br.close();
// Write the output to a inFile
FileOutputStream fileOut = new FileOutputStream(outFileName);
wb.write(fileOut);
fileOut.close();
return true;
}
public static boolean writeArray2XLSXSheet(
XSSFSheet sheet1, XSSFWorkbook wb, List<String[]> arry, boolean hasHead) throws Exception {
int rowNum = arry.size();
if (rowNum == 0) {
System.err.println("No input data!");
return false;
}
String[] titleNames = null;
if (hasHead) {
titleNames = (String[]) arry.get(0);
}
int columnNum = ((String[]) arry.get(0)).length;
for (int i = 0; i < columnNum; i++) {
sheet1.setColumnWidth((short) i, (short) ((30 * 8) / ((double) 1 / 20)));
}
XSSFFont font = wb.createFont();
font.setFontName("Courier New");
font.setFontHeightInPoints((short) 10);
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
font.setColor(HSSFColor.RED.index);
XSSFCellStyle headStyle = wb.createCellStyle();
headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
headStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
headStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
headStyle.setLocked(true);
headStyle.setFont(font);
XSSFCellStyle bodyStyle = wb.createCellStyle();
bodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
bodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
bodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
XSSFCellStyle markedBodyStyle = wb.createCellStyle();
markedBodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
markedBodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
markedBodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
markedBodyStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
markedBodyStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
int rowIndex = 0;
// create titile row
XSSFRow row = sheet1.createRow(rowIndex);
XSSFCell cell = null;
if (titleNames != null) {
for (int i = 0; i < columnNum; i++) {
cell = row.createCell((short) i);
cell.setCellValue(titleNames[i]);
cell.setCellStyle(headStyle);
}
rowIndex++;
}
for (int i = rowIndex; i < rowNum; i++) {
row = sheet1.createRow((i));
String[] line = (String[]) arry.get(i);
columnNum = line.length;
for (int j = 0; j < columnNum; j++) {
cell = row.createCell(j);
if (line[0] != null) {
cell.setCellStyle(markedBodyStyle);
} else {
cell.setCellStyle(bodyStyle);
}
if (line[j] != null) {
if (Util.isNumeric(line[j])) {
// org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC
cell.setCellType(0);
cell.setCellValue(Double.parseDouble(line[j]));
} else {
cell.setCellValue(line[j]);
}
} else {
cell.setCellValue("");
}
}
}
return true;
}
public static boolean writeArray2XLSXFile(
String FileName, String[] titleNames, List<String[]> arry) throws Exception {
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet1 = wb.createSheet("Data");
int columnNum = titleNames.length;
int rowNum = arry.size();
// for (int i = 0; i < columnNum; i++) {
// sheet1.setColumnWidth( i, (short) ((30 * 8) / ((double) 1 / 20)));
// }
XSSFCellStyle headStyle = wb.createCellStyle();
// apply custom headFont to the text in the comment
XSSFFont headFont = wb.createFont();
headFont.setFontName("Courier New");
headFont.setFontHeightInPoints((short) 10);
headFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
headFont.setColor(HSSFColor.BLACK.index);
headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
headStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
headStyle.setFillForegroundColor(HSSFColor.BLACK.index);
headStyle.setLocked(true);
headStyle.setFont(headFont);
headStyle.setBorderTop((short) 2);
headStyle.setBorderBottom((short) 1);
XSSFCellStyle contentStyle = wb.createCellStyle();
// apply custom headFont to the text in the comment
XSSFFont contentFont = wb.createFont();
contentFont.setFontName("Courier New");
contentFont.setFontHeightInPoints((short) 9);
// headFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
contentFont.setColor(HSSFColor.BLACK.index);
contentStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
contentStyle.setFillForegroundColor(HSSFColor.BLACK.index);
contentStyle.setFont(contentFont);
// create titile row
XSSFRow row = sheet1.createRow(0);
int heandLine = 0;
XSSFCell cell = null;
if (titleNames != null) {
for (int i = 0; i < columnNum; i++) {
cell = row.createCell(i);
cell.setCellValue(titleNames[i]);
cell.setCellStyle(headStyle);
}
heandLine++;
}
for (int i = 0; i < rowNum; i++) {
row = sheet1.createRow((i + heandLine));
String[] line = (String[]) arry.get(i);
for (int j = 0; j < columnNum; j++) {
cell = row.createCell(j);
if (line[j] != null) {
if (Util.isNumeric(line[j])) {
// org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC
cell.setCellType(0);
cell.setCellValue(Double.parseDouble(line[j]));
} else {
cell.setCellValue(line[j]);
}
} else {
cell.setCellValue(".");
}
}
}
// Write the output to a inFile
FileOutputStream fileOut = new FileOutputStream(FileName);
wb.write(fileOut);
fileOut.close();
return true;
}