private void createStyles(HSSFWorkbook workBook, HSSFSheet workSheet) {
/*Font bold = workBook.createFont();
bold.setBoldweight(Font.BOLDWEIGHT_BOLD);
bold.setFontHeightInPoints((short) 10);
bold.setColor(Font.COLOR_RED);*/
HSSFFont hssfFont = workBook.createFont();
hssfFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
hssfFont.setColor(Font.COLOR_RED);
boldStyle = workBook.createCellStyle();
boldStyle.setBorderBottom(CellStyle.BORDER_THIN);
boldStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
boldStyle.setFont(hssfFont);
boldStyle.setFillBackgroundColor(HSSFColor.BLUE.index);
defaultFont = workBook.createFont();
defaultFont.setFontHeightInPoints((short) 10);
defaultFont.setFontName("Arial");
defaultFont.setColor(IndexedColors.BLACK.getIndex());
defaultFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
defaultFont.setItalic(true);
newStyle = workBook.createCellStyle();
// newStyle.setFillBackgroundColor(IndexedColors.DARK_GREEN.getIndex());
// newStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
newStyle.setAlignment(CellStyle.ALIGN_CENTER);
newStyle.setFont(defaultFont);
/* hssfCellStyle = workBook.createCellStyle();
hssfCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
hssfCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
hssfCellStyle.setFont(bold);*/
}
public HSSFCellStyle createLastCellStyle(boolean white) {
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.DARK_BLUE.index);
style.setLeftBorderColor(HSSFColor.DARK_BLUE.index);
style.setBottomBorderColor(HSSFColor.DARK_BLUE.index);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
if (white) {
style.setFillBackgroundColor(HSSFColor.WHITE.index);
style.setFillForegroundColor(HSSFColor.WHITE.index);
} else {
style.setFillBackgroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
style.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
}
HSSFFont font = workbook.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 12);
style.setFont(font);
return style;
}
private CoverageResults() {
fileName = "Coverage-results.xls";
workbook = new HSSFWorkbook();
allElementsSheet = workbook.createSheet("All Elements");
packagesSheet = workbook.createSheet("Packages");
boundClassesSheet = workbook.createSheet("Bound Classes");
allClassesSheet = workbook.createSheet("All Classes");
boundMethodsSheet = workbook.createSheet("Bound Methods");
allMethodsSheet = workbook.createSheet("All Methods");
normalFont = workbook.createFont();
normalFont.setFontName(HSSFFont.FONT_ARIAL);
normalFont.setFontHeightInPoints((short) 10);
boldFont = workbook.createFont();
boldFont.setFontName(HSSFFont.FONT_ARIAL);
boldFont.setFontHeightInPoints((short) 10);
boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
centerAlignmentCellStyle = workbook.createCellStyle();
centerAlignmentCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
leftAlignmentCellStyle = workbook.createCellStyle();
leftAlignmentCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
rightAlignmentCellStyle = workbook.createCellStyle();
rightAlignmentCellStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
dataFormatCellStyle = workbook.createCellStyle();
CreationHelper createHelper = workbook.getCreationHelper();
dataFormatCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd/MM/yyyy"));
}
private static void buildTitle(HSSFSheet worksheet, int startRowIndex, int startColIndex) {
// 报表标题字体
Font fontTitle = worksheet.getWorkbook().createFont();
fontTitle.setBoldweight((short) Font.BOLDWEIGHT_BOLD);
fontTitle.setFontHeight((short) 280);
// 标题单元格格式
HSSFCellStyle cellStyleTitle = worksheet.getWorkbook().createCellStyle();
cellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER);
cellStyleTitle.setWrapText(true);
cellStyleTitle.setFont(fontTitle);
HSSFRow rowTitle = worksheet.createRow((short) startRowIndex);
rowTitle.setHeight((short) 500);
HSSFCell cellTitle = rowTitle.createCell(startColIndex);
cellTitle.setCellValue("学生列表");
cellTitle.setCellStyle(cellStyleTitle);
worksheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2)); // 标题合并列
Date date = new Date();
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
HSSFRow dateTitle = worksheet.createRow((short) startRowIndex + 1);
HSSFCell cellDate = dateTitle.createCell(startColIndex);
cellDate.setCellValue("这个报表创建于: " + dateFormat.format(date));
}
public void applyTo(HSSFCellStyle style, HSSFWorkbook workbook) {
style.setAlignment(this.alignment);
style.setBorderBottom(this.borderBottom);
style.setBorderLeft(this.borderLeft);
style.setBorderRight(this.borderRight);
style.setBorderTop(this.borderTop);
style.setBottomBorderColor(this.bottomBorderColor);
style.setDataFormat(this.dataFormat);
style.setFillPattern(this.fillPattern);
style.setFillForegroundColor(this.fillForegroundColor);
style.setFillBackgroundColor(this.fillBackgroundColor);
style.setFont(workbook.getFontAt(this.fontIndex));
style.setHidden(this.hidden);
style.setIndention(this.indention);
style.setLeftBorderColor(this.leftBorderColor);
style.setLocked(this.locked);
style.setRightBorderColor(this.rightBorderColor);
style.setRotation(this.rotation);
style.setTopBorderColor(this.topBorderColor);
style.setVerticalAlignment(this.verticalAlignment);
style.setWrapText(this.wrapText);
}
/**
* Builds the report title and the date header
*
* @param worksheet
* @param startRowIndex starting row offset
* @param startColIndex starting column offset
*/
public static void buildTitle(HSSFSheet worksheet, int startRowIndex, int startColIndex) {
// Create font style for the report title
Font fontTitle = worksheet.getWorkbook().createFont();
fontTitle.setBoldweight(Font.BOLDWEIGHT_BOLD);
fontTitle.setFontHeight((short) 280);
// Create cell style for the report title
HSSFCellStyle cellStyleTitle = worksheet.getWorkbook().createCellStyle();
cellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER);
cellStyleTitle.setWrapText(true);
cellStyleTitle.setFont(fontTitle);
// Create report title
HSSFRow rowTitle = worksheet.createRow((short) startRowIndex);
rowTitle.setHeight((short) 500);
HSSFCell cellTitle = rowTitle.createCell(startColIndex);
cellTitle.setCellValue("Compensation Report");
cellTitle.setCellStyle(cellStyleTitle);
// Create merged region for the report title
worksheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 14));
// Create date header
HSSFRow dateTitle = worksheet.createRow((short) startRowIndex + 1);
HSSFCell cellDate = dateTitle.createCell(startColIndex);
cellDate.setCellValue("This report was generated at " + new Date());
}
/**
* Creates a cell and aligns it a certain way.
*
* @param wb the workbook
* @param row the row to create the cell in
* @param column the column number to create the cell in
* @param align the alignment for the cell.
*/
private static void createCell(HSSFWorkbook wb, HSSFRow row, int column, int align) {
HSSFCell cell = row.createCell(column);
cell.setCellValue("Align It");
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment((short) align);
cell.setCellStyle(cellStyle);
}
/**
* 摘要： @说明：Excel样式 @创建：作者:yxy 创建时间：2011-8-17
*
* @param workBook
* @param cellStyle 样式模型
* @return @修改历史： [序号](yxy 2011-8-17)<修改说明>
*/
public static HSSFCellStyle getNewStyle(HSSFWorkbook workBook, CellStyle cellStyle) {
HSSFCellStyle style = workBook.createCellStyle();
// 对齐方式
style.setAlignment(cellStyle.getAlignment());
style.setVerticalAlignment(cellStyle.getVAlignment());
// 设置背景颜色
// 最好的设置Pattern
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 单元格背景的显示模式
style.setFillForegroundColor(cellStyle.getColor()); // 单元格背景的显示模式．
// 设置边框
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());
return style;
}
@SuppressWarnings("deprecation")
public void Daochu() throws Exception {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("库存盘点表");
HSSFRow row = sheet.createRow((int) 0);
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFCell cell = row.createCell((short) 0);
cell.setCellValue("快递编号");
cell.setCellStyle(style);
cell = row.createCell((short) 1);
cell.setCellValue("目的地");
cell.setCellStyle(style);
cell = row.createCell((short) 2);
cell.setCellValue("入库日期");
cell.setCellStyle(style);
cell = row.createCell((short) 3);
cell.setCellValue("区号");
cell.setCellStyle(style);
cell = row.createCell((short) 4);
cell.setCellValue("排号");
cell.setCellStyle(style);
cell = row.createCell((short) 5);
cell.setCellValue("架号");
cell.setCellStyle(style);
cell = row.createCell((short) 6);
cell.setCellValue("位号");
cell.setCellStyle(style);
cell = row.createCell((short) 7);
cell.setCellValue("中转中心");
cell.setCellStyle(style);
FileInputStream fis = new FileInputStream("src/main/java/data/save/instock.txt");
ObjectInputStream ois = new ObjectInputStream(fis);
@SuppressWarnings("unchecked")
List<InStoringpo> list = (List<InStoringpo>) ois.readObject();
ois.close();
InStoringpo[] sp = new InStoringpo[list.size()];
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow((int) i + 1);
sp[i] = list.get(i);
// 第四步，创建单元格，并设置值
row.createCell((short) 0).setCellValue(sp[i].bianhao);
row.createCell((short) 1).setCellValue(sp[i].destination);
row.createCell((short) 2).setCellValue(sp[i].year + "-" + sp[i].month + "-" + sp[i].day);
row.createCell((short) 3).setCellValue(sp[i].quhao);
row.createCell((short) 4).setCellValue(sp[i].paihao);
row.createCell((short) 5).setCellValue(sp[i].jiahao);
row.createCell((short) 6).setCellValue(sp[i].weihao);
row.createCell((short) 7).setCellValue(sp[i].zhongzhuan);
}
try {
FileOutputStream fout = new FileOutputStream("C:/大作业代码/kucundaochu.xls");
wb.write(fout);
fout.close();
} catch (Exception e) {
e.printStackTrace();
}
System.out.println("导出成功！");
}
/**
* Creates a cell and aligns it a certain way.
*
* @param wb the workbook
* @param row the row to create the cell in
* @param column the column number to create the cell in
* @param align the alignment for the cell.
*/
@SuppressWarnings("deprecation")
private static void createCell(HSSFWorkbook wb, HSSFRow row, short column, short align) {
HSSFCell cell = row.createCell(column);
cell.setCellValue("Align It");
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(align);
cell.setCellStyle(cellStyle);
}
/**
* to build the response as excel file.
*
* @see org.springframework.web.servlet.view.document.AbstractExcelView#
* buildExcelDocument(java.util.Map, org.apache.poi.hssf.usermodel.HSSFWorkbook,
* javax.servlet.http.HttpServletRequest, javax.servlet.http.HttpServletResponse)
*/
@SuppressWarnings(AppConstants.SUPPRESS_WARNINGS_UNCHECKED)
@Override
protected void buildExcelDocument(
Map<String, Object> model,
HSSFWorkbook myWorkBook,
HttpServletRequest arg2,
HttpServletResponse arg3)
throws Exception {
List<QuestionDTO> questionDTOs = (List<QuestionDTO>) model.get("questions");
HSSFSheet questionsSheet = myWorkBook.createSheet(AppConstants.EXCEL_SHEET_NAME);
HSSFRow headerRow = questionsSheet.createRow(0);
HSSFCellStyle wrapCellStyle = myWorkBook.createCellStyle();
wrapCellStyle.setWrapText(true);
wrapCellStyle.setAlignment(CellStyle.ALIGN_JUSTIFY);
createNewCell(headerRow, QUESTIONID_CELLNUM, "Question ID", wrapCellStyle);
createNewCell(headerRow, QUESTION_CELLNUM, "Question", wrapCellStyle);
createNewCell(headerRow, ANSWER_CELLNUM, "Answer", wrapCellStyle);
createNewCell(
headerRow, CATEGORIES_CELLNUM, "Categories (Category seperated by comma)", wrapCellStyle);
HSSFRow dataRow = null;
if (!CoreUtil.isEmpty(questionDTOs)) {
int i = 0;
for (QuestionDTO questionDTO : questionDTOs) {
dataRow = questionsSheet.createRow(i + 1);
createNewCell(dataRow, QUESTIONID_CELLNUM, Long.toString(questionDTO.getId()), null);
createNewCell(dataRow, QUESTION_CELLNUM, questionDTO.getQuestion(), wrapCellStyle);
createNewCell(dataRow, ANSWER_CELLNUM, questionDTO.getAnswer(), wrapCellStyle);
createNewCell(
dataRow,
CATEGORIES_CELLNUM,
parseCategoryDTOs(questionDTO.getCategoryDTOs()),
wrapCellStyle);
// Increment row
i = i + 1;
}
}
questionsSheet.autoSizeColumn(QUESTIONID_CELLNUM);
questionsSheet.autoSizeColumn(QUESTION_CELLNUM);
questionsSheet.autoSizeColumn(ANSWER_CELLNUM);
questionsSheet.autoSizeColumn(CATEGORIES_CELLNUM);
questionsSheet.setDefaultColumnStyle(QUESTION_CELLNUM, wrapCellStyle);
questionsSheet.setDefaultColumnStyle(ANSWER_CELLNUM, wrapCellStyle);
questionsSheet.setDefaultColumnStyle(CATEGORIES_CELLNUM, wrapCellStyle);
}
private void initMessageStyle() {
messageStyle = wb.createCellStyle();
messageStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
messageStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
messageStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
messageStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
messageStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
messageStyle.setWrapText(true);
messageStyle.setFont(normalFont);
}
private void initEmptyStyle() {
emptyStyle = wb.createCellStyle();
emptyStyle.setFillForegroundColor(HSSFColor.RED.index);
emptyStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
emptyStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
emptyStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
emptyStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
emptyStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
emptyStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
}
private void initKeyStyle() {
keyStyle = wb.createCellStyle();
keyStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
keyStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
keyStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
keyStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
keyStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
keyStyle.setWrapText(true);
keyStyle.setFont(normalFont);
}
private void initNormalStyle() {
normalStyle = wb.createCellStyle();
normalStyle.setFillForegroundColor(HSSFColor.WHITE.index);
normalStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
normalStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
normalStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
normalStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
normalStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
normalStyle.setWrapText(true);
normalStyle.setFont(normalFont);
}
public HSSFCellStyle createScriptStyle() {
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
HSSFFont font = workbook.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 12);
font.setColor(HSSFColor.BLACK.index);
style.setFont(font);
return style;
}
private void initChangedOrEmptyStyle() {
changedOrEmptyStyle = wb.createCellStyle();
changedOrEmptyStyle.setFillForegroundColor(HSSFColor.PINK.index);
changedOrEmptyStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
changedOrEmptyStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
changedOrEmptyStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
changedOrEmptyStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
changedOrEmptyStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
changedOrEmptyStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
changedOrEmptyStyle.setWrapText(true);
changedOrEmptyStyle.setFont(normalFont);
}
public HSSFCellStyle createInfoCellStyle() {
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont font = workbook.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setColor(HSSFColor.BLACK.index);
style.setFont(font);
return style;
}
public HSSFCellStyle createTitleCellStyle() {
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont font = workbook.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 16);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setUnderline(HSSFFont.U_SINGLE);
font.setColor(HSSFColor.GREY_80_PERCENT.index);
style.setFont(font);
return style;
}
/**
* 生成文件
*
* @throws IOException
*/
public byte[] run() throws IOException {
// 初始化参数
initParameter();
// 实例化decorator
initDecorator();
// 初始化
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
sheet.setDefaultColumnWidth((short) 20); // 设置默认宽度
try {
wb.setSheetName(0, sheetName, HSSFWorkbook.ENCODING_UTF_16);
} catch (Exception ex) {
logger.error("生成表单时出错：怀疑是表单名导致。说明：poi对中文的支持不好，可能会有问题（仅表单名）如中文?－－中文字加英文标点的情况");
}
baseCellStyle = wb.createCellStyle();
baseCellStyle.setAlignment(align);
baseCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
dateCellStyle = wb.createCellStyle();
dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(dateFormat));
// 创建标题行
HSSFRow row = sheet.createRow((short) 0);
// 创建标题
for (int i = 0; i < nameList.size(); i++) {
HSSFCell cell = row.createCell((short) i);
writeCell(cell, (String) nameList.get(i));
}
// 创建数据
for (int i = 0; i < dataList.size(); i++) {
row = sheet.createRow((short) i + 1);
for (int j = 0; j < fieldList.size(); j++) {
HSSFCell cell = row.createCell((short) j);
Object value = getValue(dataList.get(i), (String) fieldList.get(j));
writeCell(cell, value);
}
}
// 写入文件
// FileOutputStream fileOut = new FileOutputStream(fileName);
ByteArrayOutputStream out = new ByteArrayOutputStream();
wb.write(out);
return out.toByteArray();
// fileOut.close();
}
private HSSFCellStyle getHeaderStyle0(
final HSSFWorkbook workbook,
final boolean shouldRed,
final boolean shouldBackground,
final boolean shouldleft,
final Font font) {
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setWrapText(true);
font.setFontName(HSSFFont.FONT_ARIAL);
font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
font.setFontHeightInPoints((short) 7);
font.setColor(HSSFFont.COLOR_NORMAL);
style.setFont(font);
if (shouldRed) {
font.setColor(HSSFFont.COLOR_RED);
style.setFont(font);
}
if (shouldBackground) {
style.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
}
if (shouldleft) {
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
}
return style;
}
public HSSFCellStyle createSubTitleCellStyle() {
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setFillBackgroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
style.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
HSSFFont font = workbook.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 14);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setColor(HSSFColor.DARK_BLUE.index);
style.setFont(font);
return style;
}
// 创建长字符串样式
public static HSSFCellStyle createLongStringStyle(HSSFWorkbook wb) {
HSSFCellStyle normalStyle = wb.createCellStyle();
// normalStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
// normalStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
// normalStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
// normalStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
// HSSFCell.CELL_TYPE_STRING
// normalStyle.set
normalStyle.setAlignment(align);
// normalStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// normalStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
normalStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
// normalStyle.setWrapText(true);
return normalStyle;
}
/**
* Builds the column headers
*
* @param worksheet
* @param startRowIndex starting row offset
* @param startColIndex starting column offset
*/
public static void buildHeaders(
HSSFSheet worksheet, int startRowIndex, int startColIndex, List<Production> datasource) {
// Create font style for the headers
Font font = worksheet.getWorkbook().createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
// Create cell style for the headers
HSSFCellStyle headerCellStyle = worksheet.getWorkbook().createCellStyle();
headerCellStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
headerCellStyle.setFillPattern(CellStyle.FINE_DOTS);
headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
headerCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
headerCellStyle.setWrapText(true);
headerCellStyle.setFont(font);
headerCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
// Create the column headers
HSSFRow rowHeader = worksheet.createRow((short) startRowIndex + 2);
rowHeader.setHeight((short) 500);
HSSFCell cell0 = rowHeader.createCell(startColIndex + 0);
cell0.setCellValue("Shift");
cell0.setCellStyle(headerCellStyle);
int dyn = 1;
List<Compensation> compensations = datasource.get(0).getCompensations();
for (Compensation compensation : compensations) {
HSSFCell cellc1 = rowHeader.createCell(startColIndex + 0 + dyn);
cellc1.setCellValue(compensation.getElectricmeter().getName());
cellc1.setCellStyle(headerCellStyle);
dyn++;
HSSFCell cellc2 = rowHeader.createCell(startColIndex + 0 + dyn);
cellc2.setCellValue(compensation.getElectricmeter().getName() + "\nkW·h");
cellc2.setCellStyle(headerCellStyle);
dyn++;
}
HSSFCell cell5 = rowHeader.createCell(startColIndex + 0 + dyn + 0);
cell5.setCellValue("580 İnd/Reak");
cell5.setCellStyle(headerCellStyle);
HSSFCell cell6 = rowHeader.createCell(startColIndex + 0 + dyn + 1);
cell6.setCellValue("880 Kap/Reak");
cell6.setCellStyle(headerCellStyle);
}
protected HSSFCellStyle createInternalCellStyle() {
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.DARK_BLUE.index);
style.setLeftBorderColor(HSSFColor.DARK_BLUE.index);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setFillBackgroundColor(HSSFColor.WHITE.index);
style.setFillForegroundColor(HSSFColor.WHITE.index);
HSSFFont font = workbook.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 12);
style.setFont(font);
return style;
}
private void initContentCellStyle() {
// 生成并设置另一个样式
style2 = workbook.createCellStyle();
style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 生成另一个字体
HSSFFont font2 = workbook.createFont();
font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
// 把字体应用到当前的样式
style2.setFont(font2);
}
private void initHeadCellStyle() {
style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 生成一个字体
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
}
/**
* Fills the report with content
*
* @param worksheet
* @param startRowIndex starting row offset
* @param startColIndex starting column offset
* @param datasource the data source
*/
public static void fillReport(
HSSFSheet worksheet, int startRowIndex, int startColIndex, List<Expense> datasource) {
// Row offset
startRowIndex += 2;
// Create cell style for the body
HSSFCellStyle bodyCellStyle = worksheet.getWorkbook().createCellStyle();
bodyCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
bodyCellStyle.setWrapText(true);
HSSFCellStyle numericStyle = worksheet.getWorkbook().createCellStyle();
numericStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
// Create body
for (int i = startRowIndex; i + startRowIndex - 2 < datasource.size() + 2; i++) {
// Create a new row
HSSFRow row = worksheet.createRow((short) i + 1);
HSSFCell cell0 = row.createCell(startColIndex + 0);
cell0.setCellValue(datasource.get(i - 2).getMember().getUsername());
cell0.setCellStyle(bodyCellStyle);
HSSFCell cell1 = row.createCell(startColIndex + 1);
cell1.setCellValue(datasource.get(i - 2).getExpensetype().getCode());
cell1.setCellStyle(bodyCellStyle);
HSSFCell cell2 = row.createCell(startColIndex + 2);
cell2.setCellValue(datasource.get(i - 2).getDocumentDate());
HSSFCell cell3 = row.createCell(startColIndex + 3);
cell3.setCellValue(datasource.get(i - 2).getCompany());
cell3.setCellStyle(bodyCellStyle);
HSSFCell cell4 = row.createCell(startColIndex + 4);
cell4.setCellValue(datasource.get(i - 2).getDescription());
cell4.setCellStyle(bodyCellStyle);
HSSFCell cell5 = row.createCell(startColIndex + 5);
cell5.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell5.setCellValue(datasource.get(i - 2).getAmount().doubleValue());
cell5.setCellStyle(numericStyle);
}
}
private void writeSheet(
List<Map<String, String>> valueMaps, String worksheetName, HSSFWorkbook wb) {
// Set column widths
HSSFSheet sheet = wb.createSheet(worksheetName);
sheet.setColumnWidth(Short.parseShort("0"), Short.parseShort("15000"));
sheet.setColumnWidth(Short.parseShort("1"), Short.parseShort("30000"));
// header style
HSSFCellStyle headerStyle;
HSSFFont headerFont = wb.createFont();
headerFont.setFontHeightInPoints((short) 11);
headerStyle = wb.createCellStyle();
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
headerStyle.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index);
headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
headerStyle.setFont(headerFont);
headerStyle.setWrapText(true);
// header row
HSSFRow headerRow = sheet.createRow(0);
headerRow.setHeightInPoints(30);
HSSFCell headerCell0 = headerRow.createCell((short) 0);
HSSFCell headerCell1 = headerRow.createCell((short) 1);
headerCell0.setCellStyle(headerStyle);
setText(headerCell0, "Layer Name");
headerCell1.setCellStyle(headerStyle);
setText(headerCell1, "Message");
int counter = 1;
for (Map<String, String> valueMap : valueMaps) {
HSSFRow dataRow = sheet.createRow(counter);
String layer = valueMap.get("layer");
String status = valueMap.get("status");
status = HtmlUtils.htmlUnescape(status);
HSSFCell currentCell0 = dataRow.createCell((short) 0);
HSSFCell currentCell1 = dataRow.createCell((short) 1);
setText(currentCell0, layer);
setText(currentCell1, status);
counter++;
}
}
public static HSSFCellStyle getNewStyles(
HSSFWorkbook workBook, CellStyle cellStyle, HSSFFont font) {
HSSFCellStyle style = workBook.createCellStyle();
// 对齐方式
style.setAlignment(cellStyle.getAlignment());
style.setVerticalAlignment(cellStyle.getVAlignment());
// 设置自动换行
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;
}
