// removes a column and all the contents
public boolean removeColumn(String sheetName, int colNum) {
try {
if (!isSheetExist(sheetName)) return false;
fis = new FileInputStream(path);
workBook = new XSSFWorkbook(fis);
sheet = workBook.getSheet(sheetName);
XSSFCellStyle style = workBook.createCellStyle();
style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
XSSFCreationHelper createHelper = workBook.getCreationHelper();
style.setFillPattern(HSSFCellStyle.NO_FILL);
for (int i = 0; i < getRowCount(sheetName); i++) {
row = sheet.getRow(i);
if (row != null) {
cell = row.getCell(colNum);
if (cell != null) {
cell.setCellStyle(style);
row.removeCell(cell);
}
}
}
fos = new FileOutputStream(path);
workBook.write(fos);
fos.close();
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
/**
* Read the numeric format string out of the styles table for this cell. Stores the result in the
* Cell.
*
* @param startElement
* @param cell
*/
void setFormatString(StartElement startElement, StreamingCell cell) {
Attribute cellStyle = startElement.getAttributeByName(new QName("s"));
String cellStyleString = (cellStyle != null) ? cellStyle.getValue() : null;
XSSFCellStyle style = null;
if (cellStyleString != null) {
style = stylesTable.getStyleAt(Integer.parseInt(cellStyleString));
} else if (stylesTable.getNumCellStyles() > 0) {
style = stylesTable.getStyleAt(0);
}
if (style != null) {
cell.setNumericFormatIndex(style.getDataFormat());
String formatString = style.getDataFormatString();
if (formatString != null) {
cell.setNumericFormat(formatString);
} else {
cell.setNumericFormat(BuiltinFormats.getBuiltinFormat(cell.getNumericFormatIndex()));
}
} else {
cell.setNumericFormatIndex(null);
cell.setNumericFormat(null);
}
}
/**
* 罫線スタイルの<b>CellStyle</b>を生成 1行のみ描画する
*
* @param workbook ワークブック
* @param sheet シート
* @param nRow 行
* @param nColumn 列
* @param isBold 太字フラグ
* @param fontSize 文字サイズ
* @param fontHeight 行高
*/
public static void setCellStyleForLabel(
XSSFWorkbook workbook,
XSSFSheet sheet,
int nRow,
int nColumn,
boolean isBold,
short fontSize,
float fontHeight) {
assert sheet != null;
// style設定
XSSFCellStyle style = workbook.createCellStyle();
XSSFFont font = workbook.createFont();
if (isBold) {
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); // 文字太字
}
font.setFontHeightInPoints((short) fontSize); // 文字サイズ
font.setFontName(DEFAULT_FONT_NAME);
style.setFont(font); // 文字太字 と 文字サイズ
style.setAlignment(CellStyle.ALIGN_GENERAL); // 水平方向の標準
style.setVerticalAlignment(CellStyle.VERTICAL_TOP); // 垂直方向の上詰め
style.setWrapText(true); // 折り返して全体を表示する
// セルに罫線を描画
XSSFRow row = getRowAnyway(sheet, nRow);
XSSFCell cell = getCellAnyway(row, nColumn);
cell.setCellStyle(style);
row.setHeightInPoints(fontHeight); // 行高設定
}
/**
* 设置列边框
*
* @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);
// // 设置单元格内容是否自动换行
}
/**
* 罫線スタイルの<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;
}
/**
* 创建副标题
*
* @param workbook
*/
private void createSubHeads(XSSFWorkbook workbook, EFRowSet applyForm) {
XSSFCellStyle style = null;
XSSFRow row = null;
XSSFFont font = workbook.createFont(); // 创建字体对象
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
Date date = null;
style = workbook.createCellStyle();
font.setFontHeightInPoints((short) 9); // 字号
style.setFont(font);
style.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 水平居左
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
row = workbook.getSheetAt(0).createRow(1);
createSubHeadCell(workbook, row, style, "项目编号：", 1, 0);
createSubHeadCell(workbook, row, style, applyForm.getString("F_XMBH", ""), 1, 2);
createSubHeadCell(workbook, row, style, "项目名称：", 1, 4);
createSubHeadCell(workbook, row, style, applyForm.getString("F_XMMC", ""), 1, 6);
createSubHeadCell(workbook, row, style, "申请日期：", 1, 8);
createSubHeadCell(workbook, row, style, applyForm.getString("F_SQSJ", ""), 1, 10);
createSubHeadCell(workbook, row, style, "项目单位：", 1, 12);
createSubHeadCell(workbook, row, style, applyForm.getString("F_SQDW", ""), 1, 14);
row = workbook.getSheetAt(0).createRow(2);
createSubHeadCell(workbook, row, style, "申请人：", 2, 0);
createSubHeadCell(workbook, row, style, applyForm.getString("F_SQRMC", ""), 2, 2);
createSubHeadCell(workbook, row, style, "供应中心：", 2, 4);
createSubHeadCell(workbook, row, style, applyForm.getString("F_GYZXMC", ""), 2, 6);
createSubHeadCell(workbook, row, style, "单位领导名称：", 2, 8);
createSubHeadCell(workbook, row, style, applyForm.getString("F_DWLDMC", ""), 2, 10);
createSubHeadCell(workbook, row, style, "分管领导名称：", 2, 12);
createSubHeadCell(workbook, row, style, applyForm.getString("F_FGLDMC", ""), 2, 14);
row = workbook.getSheetAt(0).createRow(3);
createSubHeadCell(workbook, row, style, "主管领导名称：", 3, 0);
createSubHeadCell(workbook, row, style, applyForm.getString("F_ZGLDMC", ""), 3, 2);
createSubHeadCell(workbook, row, style, "材料需求时间：", 3, 4);
date = (Date) applyForm.getObject("F_CLXQSJ", "");
createSubHeadCell(workbook, row, style, formatter.format(date), 3, 6);
createSubHeadCell(workbook, row, style, "项目状态：", 3, 8);
if (applyForm.getString("F_XMZT", "0").equals("0")) {
createSubHeadCell(workbook, row, style, "未完工", 3, 10);
} else {
createSubHeadCell(workbook, row, style, "已完工", 3, 10);
createSubHeadCell(workbook, row, style, "完工时间：", 3, 12);
date = (Date) applyForm.getObject("F_WGSJ", "");
createSubHeadCell(workbook, row, style, formatter.format(date), 3, 14);
}
}
/**
* デフォルトのセルスタイルを作成
*
* @param workbook ワークブック
* @param bold 太字設定フラグ
* @param centering センタリングフラグ
* @param fontSize フォントサイズ
* @return <b>CellStyle</b>
*/
public static XSSFCellStyle createDefaultCellStyle(
XSSFWorkbook workbook, boolean bold, boolean centering, int fontSize) {
XSSFFont font = workbook.createFont();
if (bold) {
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
}
font.setFontHeightInPoints((short) fontSize);
font.setFontName(DEFAULT_FONT_NAME);
XSSFCellStyle style = workbook.createCellStyle();
style.setFont(font);
if (centering) {
style.setAlignment(CellStyle.ALIGN_CENTER_SELECTION);
}
return style;
}
private void processCellStyle(Element td, XSSFCellStyle style, XSSFRichTextString rts) {
StringBuilder sb = new StringBuilder();
if (rts != null) {
XSSFFont font = rts.getFontOfFormattingRun(1);
if (font != null) {
sb.append("font-family:").append(font.getFontName()).append(";");
// sb.append("color:").append(font.getColor() ).append(";");
sb.append("font-size:").append(font.getFontHeightInPoints()).append("pt;");
if (font.getXSSFColor() != null) {
String color = font.getXSSFColor().getARGBHex().substring(2);
sb.append("color:#").append(color).append(";");
}
if (font.getItalic()) sb.append("font-style:italic;");
if (font.getBold()) sb.append("font-weight:").append(font.getBoldweight()).append(";");
if (font.getStrikeout()) {
sb.append("text-decoration:underline;");
}
}
}
if (style.getAlignment() != 1) {
switch (style.getAlignment()) {
case 2:
sb.append("text-align:").append("center;");
break;
case 3:
sb.append("text-align:").append("right;");
break;
}
}
/* if(style.getBorderBottom() != 0 )
sb.append("border-bottom:").append(style.getBorderBottom()).append("px;");
if( style.getBorderLeft() != 0 )
sb.append("border-left:").append(style.getBorderLeft()).append("px;");
if(style.getBorderTop() != 0 )
sb.append("border-top:").append(style.getBorderTop()).append("px;");
if(style.getBorderRight() != 0 )
sb.append("border-right:").append(style.getBorderRight()).append("px;");
if(style.getFillBackgroundXSSFColor()!=null){
XSSFColor color = style.getFillBackgroundXSSFColor();
}*/
// System.out.println(style.getFillBackgroundXSSFColor());
if (style.getFillBackgroundXSSFColor() != null) {
sb.append("background:#ccc;");
}
htmlDocumentFacade.addStyleClass(td, "td", sb.toString());
}
/*
* (non-Javadoc)
* @see org.xml.sax.helpers.DefaultHandler#startElement(java.lang.String, java.lang.String, java.lang.String, org.xml.sax.Attributes)
*/
public void startElement(String uri, String localName, String name, Attributes attributes)
throws SAXException {
if ("inlineStr".equals(name) || "v".equals(name)) {
vIsOpen = true;
// Clear contents cache
value.setLength(0);
}
// c => cell
else if ("c".equals(name)) {
// Get the cell reference
String r = attributes.getValue("r");
int firstDigit = -1;
for (int c = 0; c < r.length(); ++c) {
if (Character.isDigit(r.charAt(c))) {
firstDigit = c;
break;
}
}
thisColumn = nameToColumn(r.substring(0, firstDigit));
// Set up defaults.
this.nextDataType = xssfDataType.NUMBER;
this.formatIndex = -1;
this.formatString = null;
String cellType = attributes.getValue("t");
String cellStyleStr = attributes.getValue("s");
if ("b".equals(cellType)) nextDataType = xssfDataType.BOOL;
else if ("e".equals(cellType)) nextDataType = xssfDataType.ERROR;
else if ("inlineStr".equals(cellType)) nextDataType = xssfDataType.INLINESTR;
else if ("s".equals(cellType)) nextDataType = xssfDataType.SSTINDEX;
else if ("str".equals(cellType)) nextDataType = xssfDataType.FORMULA;
else if (cellStyleStr != null) {
/*
* It's a number, but possibly has a style and/or special format.
* Nick Burch said to use org.apache.poi.ss.usermodel.BuiltinFormats,
* and I see javadoc for that at apache.org, but it's not in the
* POI 3.5 Beta 5 jars. Scheduled to appear in 3.5 beta 6.
*/
int styleIndex = Integer.parseInt(cellStyleStr);
XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
this.formatIndex = style.getDataFormat();
this.formatString = style.getDataFormatString();
if (this.formatString == null)
this.formatString = BuiltinFormats.getBuiltinFormat(this.formatIndex);
}
}
}
/**
* 设置列上边框粗边线
*
* @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 nRow 行データ
* @return 有効列数
*/
public static int getRowForBold(XSSFSheet sheet, int nRow, int pageRowNum) {
int nRowIndex = nRow;
for (nRowIndex = nRow; nRowIndex > (nRow - pageRowNum); nRow--) {
XSSFRow row = OoxmlUtil.getRowAnyway(sheet, nRow);
if (row != null) {
XSSFCell cell = row.getCell(0);
XSSFCellStyle styletmp = cell.getCellStyle();
short borderTopnum = styletmp.getBorderTop();
short borderBold = XSSFCellStyle.BORDER_MEDIUM;
if (styletmp.getBorderTop() == (XSSFCellStyle.BORDER_MEDIUM)) {
break;
}
}
}
return nRowIndex;
}
/**
* 设置列下边框粗边线
*
* @param style
*/
private XSSFCellStyle setColumnButtomBorder(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.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); // 底部边框粗线
style.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边边框
style.setBottomBorderColor(new XSSFColor(new java.awt.Color(153, 153, 255))); // 设置底部边框颜色
style.setRightBorderColor(new XSSFColor(new java.awt.Color(153, 153, 255))); // 设置右边边框颜色
return style;
}
private void updateSelectedCellsFontColor(Color newColor) {
if (spreadsheet != null && newColor != null) {
List<Cell> cellsToRefresh = new ArrayList<Cell>();
for (CellReference cellRef : spreadsheet.getSelectedCellReferences()) {
Cell cell = getOrCreateCell(cellRef);
// Workbook workbook = spreadsheet.getWorkbook();
XSSFCellStyle style = (XSSFCellStyle) cloneStyle(cell);
XSSFColor color = new XSSFColor(java.awt.Color.decode(newColor.getCSS()));
XSSFFont font = (XSSFFont) cloneFont(style);
font.setColor(color);
style.setFont(font);
cell.setCellStyle(style);
cellsToRefresh.add(cell);
}
// Update all edited cells
spreadsheet.refreshCells(cellsToRefresh);
}
}
/**
* 创建标题，并设置字体、字号、加粗、颜色
*
* @param workbook
*/
private void setHeadCellFont(XSSFWorkbook workbook, String title) {
XSSFCellStyle style = workbook.createCellStyle();
XSSFRow row = (XSSFRow) workbook.getSheetAt(0).createRow(0); // 创建一个行对象
XSSFCell cell = row.createCell(0); // 创建单元格
XSSFFont font = workbook.createFont(); // 创建字体对象
font.setFontName(HSSFFont.FONT_ARIAL); // 字体
font.setFontHeightInPoints((short) 16); // 字号
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 加粗
style.setFont(font);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); // 水平居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
row.setHeightInPoints(23); // 设置行高23像素
cell.setCellStyle(style); // 应用样式对象
workbook
.getSheetAt(0)
.addMergedRegion(new CellRangeAddress(0, 0, 0, 16)); // 四个参数分别是：起始行，结束行，起始列，结束列
cell.setCellValue(title); // 写入头标题
}
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);
}
/**
* ハイパーリンクの設定
*
* @param sheet シート
* @param nRow 対象行番号
* @param nColumn 対象列番号
* @param value ハイパーリンクテキスト
* @param url ハイパーリンク先URL
*/
public static void setHyperLink(
XSSFSheet sheet, int nRow, int nColumn, String value, String url) {
assert sheet != null;
XSSFWorkbook workbook = sheet.getWorkbook();
CreationHelper helper = workbook.getCreationHelper();
Hyperlink hyperlink = helper.createHyperlink(Hyperlink.LINK_URL);
hyperlink.setAddress(url);
XSSFRow row = getRowAnyway(sheet, nRow);
XSSFCell cell = getCellAnyway(row, nColumn);
cell.setCellValue(value);
cell.setHyperlink(hyperlink);
// ハイパーリンクテキストの装飾
XSSFFont font = workbook.createFont();
XSSFCellStyle style = workbook.createCellStyle();
// font.setColor(new XSSFColor(new Color(0, 0, 255)));
font.setUnderline(XSSFFont.U_SINGLE);
style.setFont(font);
cell.setCellStyle(style);
}
private void updateSelectedCellsBackgroundColor(Color newColor) {
if (spreadsheet != null && newColor != null) {
List<Cell> cellsToRefresh = new ArrayList<Cell>();
for (CellReference cellRef : spreadsheet.getSelectedCellReferences()) {
// Obtain Cell using CellReference
Cell cell = getOrCreateCell(cellRef);
// Clone Cell CellStyle
// This cast an only be done when using .xlsx files
XSSFCellStyle style = (XSSFCellStyle) cloneStyle(cell);
XSSFColor color = new XSSFColor(java.awt.Color.decode(newColor.getCSS()));
// Set new color value
style.setFillForegroundColor(color);
cell.setCellStyle(style);
cellsToRefresh.add(cell);
}
// Update all edited cells
spreadsheet.refreshCells(cellsToRefresh);
}
}
/**
* @param oldCell
* @param newCell
* @param styleMap
*/
public static void copyCell(
XSSFCell oldCell, XSSFCell newCell, Map<Integer, XSSFCellStyle> styleMap) {
if (styleMap != null) {
if (oldCell.getSheet().getWorkbook() == newCell.getSheet().getWorkbook()) {
newCell.setCellStyle(oldCell.getCellStyle());
} else {
int stHashCode = oldCell.getCellStyle().hashCode();
XSSFCellStyle newCellStyle = styleMap.get(stHashCode);
if (newCellStyle == null) {
newCellStyle = newCell.getSheet().getWorkbook().createCellStyle();
newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
styleMap.put(stHashCode, newCellStyle);
}
newCell.setCellStyle(newCellStyle);
}
}
switch (oldCell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
newCell.setCellValue(oldCell.getStringCellValue());
break;
case HSSFCell.CELL_TYPE_NUMERIC:
newCell.setCellValue(oldCell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
newCell.setCellType(HSSFCell.CELL_TYPE_BLANK);
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
newCell.setCellValue(oldCell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_ERROR:
newCell.setCellErrorValue(oldCell.getErrorCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
newCell.setCellFormula(oldCell.getCellFormula());
break;
default:
break;
}
}
/**
* 罫線スタイルの<b>CellStyle</b>を生成
*
* @param workbook ワークブック
* @param backgroundColor 背景色
* @return <b>CellStyle</b>
*/
public static XSSFCellStyle createTableDataCellStyle(
XSSFWorkbook workbook, Color backgroundColor) {
XSSFCellStyle style = workbook.createCellStyle();
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;
}
// returns true if column is created successfully
public boolean addColumn(String sheetName, String colName) {
// System.out.println("**************addColumn*********************");
try {
fis = new FileInputStream(path);
workBook = new XSSFWorkbook(fis);
int index = workBook.getSheetIndex(sheetName);
if (index == -1) return false;
XSSFCellStyle style = workBook.createCellStyle();
style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
sheet = workBook.getSheetAt(index);
row = sheet.getRow(0);
if (row == null) row = sheet.createRow(0);
// cell = row.getCell();
// if (cell == null)
// System.out.println(row.getLastCellNum());
if (row.getLastCellNum() == -1) cell = row.createCell(0);
else cell = row.createCell(row.getLastCellNum());
cell.setCellValue(colName);
cell.setCellStyle(style);
fos = new FileOutputStream(path);
workBook.write(fos);
fos.close();
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
/**
* デフォルトのテーブルヘッダースタイルを作成
*
* @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;
}
/**
* 创建副标题
*
* @param workbook
*/
private void createSubHeads(XSSFWorkbook workbook, List subHeadList) {
XSSFCellStyle style = null;
int rowCount = subHeadList.size() / 2 + subHeadList.size() % 2; // 副标题行数
int fristCol = 0;
int columnCount = 26;
String title = "";
XSSFRow row = null;
XSSFCell cell = null;
XSSFFont font = workbook.createFont(); // 创建字体对象
/** 副标题每行放两个，一个居左，一个居右，所以除以2，多出来的一个，另起一行， */
for (int i = 0; i < rowCount; i++) {
row = workbook.getSheetAt(0).createRow(i + 1); // 创建一个行对象 ,加1是因为有一行头标题
for (int j = 0; j < 2; j++) {
if (subHeadList.size() <= (i * 2 + j)) break;
style = workbook.createCellStyle();
title = subHeadList.get(i * 2 + j).toString();
font.setFontHeightInPoints((short) 9); // 字号
style.setFont(font);
if (j == 0) {
/** 因为每行两个副标题，所以第一个标题占一半 */
fristCol = columnCount / 2;
cell = row.createCell(j); // 创建单元格
cell.setCellValue(title);
style.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 水平居左
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
cell.setCellStyle(style); // 应用样式对象
workbook
.getSheetAt(0)
.addMergedRegion(new CellRangeAddress(i + 1, i + 1, 0, fristCol - 1)); // 合并单元格
} else {
/** 因为每行两个副标题，第一个标题占一半，第二个占剩下的所有单元格 */
fristCol = columnCount - (columnCount / 2) - (columnCount % 2);
cell = row.createCell(columnCount / 2); // 创建单元格
cell.setCellValue(title);
// 字号
style.setFont(font);
style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); // 水平居右
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
cell.setCellStyle(style); // 应用样式对象
workbook
.getSheetAt(0)
.addMergedRegion(
new CellRangeAddress(i + 1, i + 1, fristCol, columnCount - 1)); // 合并单元格
}
}
}
}
/**
* 设置列对齐方式
*
* @param rowset
* @param style
* @param align
* @param columnId
*/
private void setColumnAlign(XSSFCellStyle style, String align) {
if (align.equals("LEFT")) {
style.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 水平居左
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
} else if (align.equals("RIGHT")) {
style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); // 水平居右
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
} else if (align.equals("RIGHT")) {
style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); // 水平居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
}
}
/**
* 设置表体的单元格样式
*
* @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;
}
/**
* 创建单元格的样式
*
* @param workbook 工作表
* @param font 字代
* @param alignment 水平对齐 如：CellStyle.ALIGN_CENTER
* @param verticalAlignment 垂直对齐 如：CellStyle.VERTICAL_CENTER
* @param Border 边框 如：XSSFCellStyle.BORDER_THIN
* @param foregroundColor 前置背景色 如：CellStyle.BORDER_THIN
* @param fillPattern 填充模式 如：
* @return
*/
public CellStyle createCellStyle(
Workbook workbook,
Font font,
short alignment,
short verticalAlignment,
short Border,
short foregroundColor,
short fillPattern) {
XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();
cellStyle.setFont(font);
cellStyle.setAlignment(alignment);
cellStyle.setVerticalAlignment(verticalAlignment);
cellStyle.setBorderRight(Border);
cellStyle.setFillForegroundColor(foregroundColor);
cellStyle.setFillPattern(fillPattern);
return cellStyle;
}
public void colorStyles(CellStyle style, Formatter out) {
XSSFCellStyle cs = (XSSFCellStyle) style;
styleColor(out, "background-color", cs.getFillForegroundXSSFColor());
styleColor(out, "color", cs.getFont().getXSSFColor());
}
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 Workbook handleExcel(List objs, Class clz, boolean isXssf, String message) {
XSSFWorkbook wb = null;
try {
if (isXssf) {
XSSFWorkbook w = new XSSFWorkbook();
} else {
HSSFWorkbook w = new HSSFWorkbook();
}
wb = new XSSFWorkbook();
XSSFDataFormat format = wb.createDataFormat();
XSSFSheet sheet = wb.createSheet(message + "备份记录"); // 取excel工作表对象
XSSFCellStyle cellStyle = wb.createCellStyle(); // 设置excel单元格样式
XSSFCellStyle passwordCellStyle = wb.createCellStyle(); // 设置密码单元格样式
XSSFDataFormat passwordFormat = wb.createDataFormat();
passwordCellStyle.setDataFormat(passwordFormat.getFormat(";;;"));
List<ExcelHeader> headers = getHeaderList(clz);
Collections.sort(headers);
sheet.addMergedRegion(new CellRangeAddress(0, (short) 0, 0, (short) (headers.size() - 1)));
Row r0 = sheet.createRow(0);
Cell cell = r0.createCell(0);
r0.setHeightInPoints(28);
cell.setCellValue(message + "备份记录");
Row r = sheet.createRow(1);
r.setHeightInPoints(25);
cell.setCellStyle(cellStyle);
// 输出标题
for (int i = 0; i < headers.size(); i++) {
Cell cell1 = r.createCell(i);
if (headers.get(i).getTitle().equals("密码")) cell1.setCellStyle(passwordCellStyle);
else cell1.setCellStyle(cellStyle);
cell1.setCellValue(headers.get(i).getTitle());
}
Object obj = null;
// 输出用户资料信息
if (message.indexOf("用户资料 ") > 0) {
sheet.setColumnWidth(3, 32 * 150);
sheet.setColumnWidth(4, 32 * 110);
sheet.setColumnWidth(7, 32 * 120);
for (int i = 0; i < objs.size(); i++) {
r = sheet.createRow(i + 2);
obj = objs.get(i);
for (int j = 0; j < headers.size(); j++) {
Cell cell2 = r.createCell(j);
copyDefaultCellStyle(null, cell2, cellStyle, 0);
if (getMethodName(headers.get(j)).equals("nabled"))
cell2.setCellValue(BeanUtils.getProperty(obj, "enabled"));
else if (getMethodName(headers.get(j)).equals("password")) {
cell2.setCellStyle(passwordCellStyle);
cell2.setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));
} else cell2.setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));
}
}
}
// 输出房间使用信息数据
else {
sheet.setColumnWidth(0, 32 * 80);
sheet.setColumnWidth(2, 32 * 100);
sheet.setColumnWidth(3, 32 * 190);
sheet.setColumnWidth(4, 32 * 190);
sheet.setColumnWidth(5, 32 * 190);
sheet.setColumnWidth(10, 32 * 130);
for (int i = 0; i < objs.size(); i++) {
r = sheet.createRow(i + 2);
obj = objs.get(i);
for (int j = 0; j < headers.size(); j++) {
Cell cell2 = r.createCell(j);
if (j == 3 || j == 4 || j == 5) {
XSSFCellStyle cs3 = wb.createCellStyle();
cell2.setCellValue(new Date());
copyDefaultCellStyle(format, cell2, cs3, 1);
}
if (j == 10) {
XSSFCellStyle cs2 = wb.createCellStyle();
copyDefaultCellStyle(format, cell2, cs2, 2);
}
copyDefaultCellStyle(null, cell2, cellStyle, 0);
cell2.setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));
}
}
}
// 设置行列的默认宽度和高度
} catch (IllegalAccessException e) {
e.printStackTrace();
logger.error(e);
} catch (InvocationTargetException e) {
e.printStackTrace();
logger.error(e);
} catch (NoSuchMethodException e) {
e.printStackTrace();
logger.error(e);
}
return wb;
}
/**
* 创建列
*
* @param workbook
*/
private void exportColumnData(XSSFWorkbook workbook, List subHeadList) {
XSSFCellStyle style = null;
XSSFRow row = null;
XSSFCell cell = null;
XSSFFont font = workbook.createFont(); // 创建字体对象
XSSFCellStyle topStyle = setColumnTopBorder(workbook);
XSSFCellStyle buttomStyle = setColumnButtomBorder(workbook);
int rowCount = subHeadList.size() / 2 + subHeadList.size() % 2 + 1;
row = workbook.getSheetAt(0).createRow(rowCount); // 创建一个行对象 列标题
style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); // 水平居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
font.setFontHeightInPoints((short) 10); // 字号
style.setFont(font); // 设置字体
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 设置前景填充样式
style.setFillForegroundColor(new XSSFColor(new java.awt.Color(204, 204, 255))); // 设置单元格背景色
setColumnBorder(style, 0); // 设置列边框
workbook.getSheetAt(0).setColumnWidth(0, 32 * 100); // 对列设置宽度
/** 创建单元格、设置列名称、合并单元格 */
cell = row.createCell(0);
cell.setCellValue("会计期间");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 0, 0));
cell.setCellStyle(style);
cell = row.createCell(1);
cell.setCellValue("单据编号");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 1, 1));
cell.setCellStyle(style);
cell = row.createCell(2);
cell.setCellValue("分录编号");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 2, 2));
cell.setCellStyle(style);
cell = row.createCell(3);
cell.setCellValue("材料入库信息");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount, 3, 8));
cell.setCellStyle(style);
cell = row.createCell(4);
cell.setCellStyle(topStyle);
cell = row.createCell(5);
cell.setCellStyle(topStyle);
cell = row.createCell(6);
cell.setCellStyle(topStyle);
cell = row.createCell(7);
cell.setCellStyle(topStyle);
cell = row.createCell(8);
cell.setCellStyle(topStyle);
cell = row.createCell(9);
cell.setCellValue("材料出库信息");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount, 9, 14));
cell.setCellStyle(style);
cell = row.createCell(10);
cell.setCellStyle(topStyle);
cell = row.createCell(11);
cell.setCellStyle(topStyle);
cell = row.createCell(12);
cell.setCellStyle(topStyle);
cell = row.createCell(13);
cell.setCellStyle(topStyle);
cell = row.createCell(14);
cell.setCellStyle(topStyle);
cell = row.createCell(15);
cell.setCellValue("材料编号");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 15, 15));
cell.setCellStyle(style);
cell = row.createCell(16);
cell.setCellValue("材料名称");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 16, 16));
cell.setCellStyle(style);
cell = row.createCell(17);
cell.setCellValue("规格型号");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 17, 17));
cell.setCellStyle(style);
cell = row.createCell(18);
cell.setCellValue("计量单位");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 18, 18));
cell.setCellStyle(style);
cell = row.createCell(19);
cell.setCellValue("供应商编号");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 19, 19));
cell.setCellStyle(style);
cell = row.createCell(20);
cell.setCellValue("供应商名称");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 20, 20));
cell.setCellStyle(style);
cell = row.createCell(21);
cell.setCellValue("出入方向");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 21, 21));
cell.setCellStyle(style);
cell = row.createCell(22);
cell.setCellValue("单据类型");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 22, 22));
cell.setCellStyle(style);
cell = row.createCell(23);
cell.setCellValue("材料单价");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 25, 25));
cell.setCellStyle(style);
cell = row.createCell(24);
cell.setCellValue("材料数量");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 23, 23));
cell.setCellStyle(style);
cell = row.createCell(25);
cell.setCellValue("材料总价");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 24, 24));
cell.setCellStyle(style);
row = workbook.getSheetAt(0).createRow(rowCount + 1); // 创建一个行对象 列标题
/** 创建单元格、设置列名称、合并单元格 */
// 材料入库信息
cell = row.createCell(3);
cell.setCellValue("仓库编号");
cell.setCellStyle(style);
cell = row.createCell(4);
cell.setCellValue("仓库编号");
cell.setCellStyle(style);
cell = row.createCell(5);
cell.setCellValue("仓库名称");
cell.setCellStyle(style);
cell = row.createCell(6);
cell.setCellValue("项目编号");
cell.setCellStyle(style);
cell = row.createCell(7);
cell.setCellValue("项目名称");
cell.setCellStyle(style);
cell = row.createCell(8);
cell.setCellValue("产品编号");
cell.setCellStyle(style);
// 材料出库信息
cell = row.createCell(9);
cell.setCellValue("产品名称");
cell.setCellStyle(style);
cell = row.createCell(10);
cell.setCellValue("仓库编号");
cell.setCellStyle(style);
cell = row.createCell(11);
cell.setCellValue("仓库名称");
cell.setCellStyle(style);
cell = row.createCell(12);
cell.setCellValue("项目编号");
cell.setCellStyle(style);
cell = row.createCell(13);
cell.setCellValue("项目名称");
cell.setCellStyle(style);
cell = row.createCell(14);
cell.setCellValue("产品编号");
cell.setCellStyle(style);
for (int i = 0; i < 3; i++) {
cell = row.createCell(i);
cell.setCellStyle(buttomStyle);
}
for (int i = 15; i < 26; i++) {
cell = row.createCell(i);
cell.setCellStyle(buttomStyle);
}
}
/**
* 创建列
*
* @param workbook
*/
private void exportData(XSSFWorkbook workbook, EFDataSet queryDataSet, List subHeadList) {
EFRowSet queryRS = null;
XSSFRow row = null;
XSSFFont font = workbook.createFont(); // 创建字体对象
XSSFCellStyle style = workbook.createCellStyle();
List<String> colList = new ArrayList<String>();
// 获取数据开始行，副标题信息除以2为副标题占有所有行，再加1为标题的行数，最后加一行列信息，所以最后加3
int startRow = subHeadList.size() / 2 + subHeadList.size() % 2 + 2;
// 循环数据集中每行数据
for (int i = 0; i < queryDataSet.getRowCount(); i++) {
startRow += 1; // 获取行号
row = workbook.getSheetAt(0).createRow(startRow); // 创建一个行对象
queryRS = queryDataSet.getRowSet(i);
// 循环列数据集中每个列信息
style = workbook.createCellStyle();
setColumnAlign(style, "LEFT"); // 设置列对齐方式
setColumnBorder(style, 1); // 设置列边框
font.setFontHeightInPoints((short) 10); // 字号
style.setFont(font); // 设置字体
colList = new ArrayList<String>();
colList.add("F_KJQJ");
colList.add("F_DJBH");
colList.add("F_FLBH");
colList.add("F_CKBH");
colList.add("F_CKMC");
colList.add("F_XMBH");
colList.add("F_XMMC");
colList.add("F_CPBH");
colList.add("F_CPMC");
colList.add("F_YYCKBH");
colList.add("F_YYCKMC");
colList.add("F_YYXMBH");
colList.add("F_YYXMMC");
colList.add("F_YYCPBH");
colList.add("F_YYCPMC");
colList.add("F_CLBH");
colList.add("F_CLMC");
colList.add("F_GGXH");
colList.add("F_JLDW");
colList.add("F_DWBH");
colList.add("F_DWMC");
colList.add("F_CSBH");
colList.add("F_CSMC");
colList.add("F_CRFX");
colList.add("F_DJLX");
createColumn(workbook, row, style, colList, 0, queryRS, "");
style = workbook.createCellStyle();
setColumnAlign(style, "RIGHT"); // 设置列对齐方式
setColumnBorder(style, 1); // 设置列边框
font.setFontHeightInPoints((short) 10); // 字号
style.setFont(font); // 设置字体
colList = new ArrayList<String>();
colList.add("F_CLDJ");
colList.add("F_CLSL");
colList.add("F_CLZJ");
createColumn(workbook, row, style, colList, 23, queryRS, "N");
}
}
