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));
}
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"));
}
public void gerar(String caminho, int qtdLinhas, int qtdColunas) throws IOException {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet1 = wb.createSheet("planilha um");
for (int i = 0; i < qtdLinhas; i++) {
HSSFRow row = sheet1.createRow(i);
HSSFCellStyle style = wb.createCellStyle();
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_JUSTIFY);
for (int j = 0; j < qtdColunas; j++) {
HSSFRichTextString strValor = new HSSFRichTextString("Linha: " + i + " Coluna " + j);
HSSFCell cell = row.createCell(j);
cell.setCellStyle(style);
cell.setCellValue(strValor);
}
}
FileOutputStream stream = new FileOutputStream(caminho + "/planilha1.xls");
wb.write(stream);
}
/**
* 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());
}
private void writeTotals(
HSSFSheet sheet,
String apiTotalLabel,
int apiTotal,
String coverageTotalLabel,
String coverageTotal) {
leftAlignmentCellStyle.setFont(boldFont);
rightAlignmentCellStyle.setFont(boldFont);
HSSFRow row;
HSSFCell cell;
row = sheet.getRow(0);
cell = row.createCell(6);
cell.setCellValue(apiTotalLabel);
cell.setCellStyle(leftAlignmentCellStyle);
cell = row.createCell(7);
cell.setCellValue(apiTotal);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellStyle(rightAlignmentCellStyle);
cell = row.createCell(9);
cell.setCellValue(coverageTotalLabel);
cell.setCellStyle(leftAlignmentCellStyle);
cell = row.createCell(10);
cell.setCellFormula(coverageTotal);
cell.setCellType(Cell.CELL_TYPE_FORMULA);
cell.setCellStyle(rightAlignmentCellStyle);
}
/**
* Insert a bulleted multi-level list into a cell.
*
* @param workbook A reference to the HSSFWorkbook that 'contains' the cell.
* @param multiLevelListItems An ArrayList whose elements contain instances of the
* MultiLevelListItem class. Each element encapsulates the text for the high level item along
* with an ArrayList. Each element of this ArrayList encapsulates the text for a lower level
* item.
* @param cell An instance of the HSSFCell class that encapsulates a reference to the spreadsheet
* cell into which the list will be written.
*/
public void multiLevelBulletedListInCell(
HSSFWorkbook workbook, ArrayList<MultiLevelListItem> multiLevelListItems, HSSFCell cell) {
StringBuffer buffer = new StringBuffer();
ArrayList<String> lowerLevelItems = null;
// Note that again, an HSSFCellStye object is required and that
// it's wrap text property should be set to 'true'
HSSFCellStyle wrapStyle = workbook.createCellStyle();
wrapStyle.setWrapText(true);
// Step through the ArrayList of MultilLevelListItem instances.
for (MultiLevelListItem multiLevelListItem : multiLevelListItems) {
// For each element in the ArrayList, get the text for the high
// level list item......
buffer.append(InCellLists.BULLET_CHARACTER);
buffer.append(" ");
buffer.append(multiLevelListItem.getItemText());
buffer.append("\n");
// and then an ArrayList whose elements encapsulate the text
// for the lower level list items.
lowerLevelItems = multiLevelListItem.getLowerLevelItems();
if (!(lowerLevelItems == null) && !(lowerLevelItems.isEmpty())) {
for (String item : lowerLevelItems) {
buffer.append(InCellLists.TAB);
buffer.append(InCellLists.BULLET_CHARACTER);
buffer.append(" ");
buffer.append(item);
buffer.append("\n");
}
}
}
// The StringBuffer's contents are the source for the contents
// of the cell.
cell.setCellValue(new HSSFRichTextString(buffer.toString().trim()));
cell.setCellStyle(wrapStyle);
}
/**
* 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);
}
/**
* Fills given row with headers
*
* @param wb
* @param sheet
* @param row
*/
private void fillHeaderRow(HSSFWorkbook wb, HSSFSheet sheet, HSSFRow row) {
// create style of header font
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontHeightInPoints((short) 11);
HSSFCellStyle style = wb.createCellStyle();
style.setFont(font);
// here we could create array of strings, I mean headers
String[] headers = {
this.iwrb.getLocalizedString("school.child", "Child"),
this.iwrb.getLocalizedString("school.personal_id", "Personal ID"),
this.iwrb.getLocalizedString("school.e-mail", "Email"),
this.iwrb.getLocalizedString("school.address", "Address"),
this.iwrb.getLocalizedString("school.zip_code", "Zip code"),
this.iwrb.getLocalizedString("school.city", "City"),
this.iwrb.getLocalizedString("school.phone", "Phone"),
this.iwrb.getLocalizedString("school.gender", "Gender"),
this.iwrb.getLocalizedString("school.last_provider", "Last provider"),
this.iwrb.getLocalizedString("school.rejection_date", "Rejection date")
};
int[] headerWidths = {30, 14, 25, 25, 10, 16, 16, 8, 30, 16};
HSSFCell cell;
for (int i = 0; i < headers.length; i++) {
cell = row.createCell((short) i);
cell.setCellValue(headers[i]);
cell.setCellStyle(style);
sheet.setColumnWidth((short) i, (short) (headerWidths[i] * 256));
}
}
/**
* 测试单元格样式
*
* @author David
* @param wb
* @param cell
* @param td
*/
private static void setType(HSSFWorkbook wb, HSSFCell cell, Element td) {
Attribute typeAttr = td.getAttribute("type");
String type = typeAttr.getValue();
HSSFDataFormat format = wb.createDataFormat();
HSSFCellStyle cellStyle = wb.createCellStyle();
if ("NUMERIC".equalsIgnoreCase(type)) {
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
Attribute formatAttr = td.getAttribute("format");
String formatValue = formatAttr.getValue();
formatValue = StringUtils.isNotBlank(formatValue) ? formatValue : "#,##0.00";
cellStyle.setDataFormat(format.getFormat(formatValue));
} else if ("STRING".equalsIgnoreCase(type)) {
cell.setCellValue("");
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cellStyle.setDataFormat(format.getFormat("@"));
} else if ("DATE".equalsIgnoreCase(type)) {
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cellStyle.setDataFormat(format.getFormat("yyyy-m-d"));
} else if ("ENUM".equalsIgnoreCase(type)) {
CellRangeAddressList regions =
new CellRangeAddressList(
cell.getRowIndex(), cell.getRowIndex(), cell.getColumnIndex(), cell.getColumnIndex());
Attribute enumAttr = td.getAttribute("format");
String enumValue = enumAttr.getValue();
// 加载下拉列表内容
DVConstraint constraint = DVConstraint.createExplicitListConstraint(enumValue.split(","));
// 数据有效性对象
HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
wb.getSheetAt(0).addValidationData(dataValidation);
}
cell.setCellStyle(cellStyle);
}
/**
* Inserts a numbered list into a single cell.
*
* @param workbook A reference to the HSSFWorkbook that 'contains' the cell.
* @param listItems An ArrayList whose elements encapsulate the text for the lists items.
* @param cell An instance of the HSSFCell class that encapsulates a reference to the spreadsheet
* cell into which the list will be written.
* @param startingValue A primitive int containing the number for the first item in the list.
* @param increment A primitive int containing the value that should be used to calculate
* subsequent item numbers.
*/
public void numberedListInCell(
HSSFWorkbook workbook,
ArrayList<String> listItems,
HSSFCell cell,
int startingValue,
int increment) {
StringBuffer buffer = new StringBuffer();
int itemNumber = startingValue;
// Note that again, an HSSFCellStye object is required and that
// it's wrap text property should be set to 'true'
HSSFCellStyle wrapStyle = workbook.createCellStyle();
wrapStyle.setWrapText(true);
// Note that the basic method is identical to the listInCell() method
// with one difference; a number prefixed to the items text.
for (String listItem : listItems) {
buffer.append(String.valueOf(itemNumber) + ". ");
buffer.append(listItem);
buffer.append("\n");
itemNumber += increment;
}
// The StringBuffer's contents are the source for the contents
// of the cell.
cell.setCellValue(new HSSFRichTextString(buffer.toString().trim()));
cell.setCellStyle(wrapStyle);
}
@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);
}
public void initStyles() {
// HSSFDataFormat format = workbook.createDataFormat();
redStyle = workbook.createCellStyle();
setBorder(redStyle, 1);
redStyle.setFillForegroundColor(HSSFColor.RED.index);
yellowStyle = workbook.createCellStyle();
setBorder(yellowStyle, 1);
yellowStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
blueStyle = workbook.createCellStyle();
setBorder(blueStyle, 1);
blueStyle.setFillForegroundColor(HSSFColor.BLUE_GREY.index);
whiteStyle = workbook.createCellStyle();
setBorder(whiteStyle, 1);
whiteStyle.setFillForegroundColor(HSSFColor.WHITE.index);
normalStyle = workbook.createCellStyle();
setBorder(normalStyle, 0);
// normalStyle.setFillForegroundColor(HSSFColor.WHITE.index);
normalPStyle = workbook.createCellStyle();
setBorder(normalPStyle, 0);
normalPStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
// normalPStyle.setFillForegroundColor(HSSFColor.WHITE.index);
headerStyle = workbook.createCellStyle();
setBorder(headerStyle, 0);
HSSFFont font = workbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerStyle.setFont(font);
redPStyle = workbook.createCellStyle();
setBorder(redPStyle, 1);
redPStyle.setFillForegroundColor(HSSFColor.RED.index);
// redPStyle.setDataFormat(format.getFormat(NUMBER_FORMAT));
redPStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
yellowPStyle = workbook.createCellStyle();
setBorder(yellowPStyle, 1);
yellowPStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
// yellowPStyle.setDataFormat(format.getFormat(NUMBER_FORMAT));
yellowPStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
_fonts = new HashMap<String, HSSFFont>(2);
HSSFFont t13y_red = workbook.createFont();
t13y_red.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
t13y_red.setColor(HSSFFont.COLOR_RED);
_fonts.put("t13yr", t13y_red);
HSSFFont t13y_blue = workbook.createFont();
t13y_blue.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
t13y_blue.setColor(HSSFColor.BLUE_GREY.index);
_fonts.put("t13yb", t13y_blue);
}
/**
* 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);
}
public void setCell(int index, Calendar value) {
HSSFCell cell = this.row.createCell((short) index);
// cell.setEncoding(XLS_ENCODING);
cell.setCellValue(value.getTime());
// for new cell style
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(DATE_FORMAT));
cell.setCellStyle(cellStyle);
}
public static HSSFCellStyle rowStyle2(final HSSFSheet sheet) {
HSSFCellStyle style = sheet.getWorkbook().createCellStyle();
style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
return style;
}
public void setValueAndColor(int rowIndex, int columnIndex, String value, ExcelColor color) {
HSSFFont font = createFont();
font.setColor(ExcelColor.getColorIndex(color));
HSSFCellStyle cellStyle = createCellStyle();
cellStyle.cloneStyleFrom(getCellStyle(rowIndex, columnIndex));
cellStyle.setFont(font);
setValueAndStyle(rowIndex, columnIndex, value, cellStyle);
}
// 默认cell样式
public HSSFCellStyle getDefaultStyle(HSSFWorkbook wb) {
HSSFCellStyle style = wb.createCellStyle();
// 设置边框样式
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
return style;
}
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;
}
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;
}
@SuppressWarnings("unchecked")
private void createBodyRow(Row currentRow, Object[] bodyRow, int[] types) {
Cell currentCell;
// write the section attributes to the row
for (int index = 0; index < bodyRow.length; index++) {
// create the cell
currentCell = currentRow.createCell(index);
// set the format of the cells
currentCell.setCellStyle(bodyStyle);
if (types[index] != Integer.MAX_VALUE) {
currentCell.setCellType(types[index]);
}
// put the value into the cell
switch (types[index]) {
case Cell.CELL_TYPE_NUMERIC:
currentCell.setCellValue((double) bodyRow[index]);
break;
case Cell.CELL_TYPE_FORMULA:
currentCell.setCellFormula(String.valueOf(bodyRow[index]));
break;
case Integer.MAX_VALUE:
HashMap<String, String> cellInfo = (HashMap<String, String>) bodyRow[index];
// sets the hyperlink to be blue and underline
HSSFCellStyle hyperlinkStyle = (HSSFCellStyle) GetBodyCellStyle(workbook);
Font hyperlinkFont = workbook.createFont();
hyperlinkFont.setUnderline(Font.U_SINGLE);
hyperlinkFont.setColor(HSSFColor.BLUE.index);
hyperlinkStyle.setFont(hyperlinkFont);
currentCell.setCellStyle(hyperlinkStyle);
currentCell.setCellValue(cellInfo.get("Label"));
currentCell.setHyperlink(LinkDataToMap(cellInfo));
break; // MAX_VALUE integer will be used to denote hyperlinks
case Cell.CELL_TYPE_STRING:
currentCell.setCellValue(String.valueOf(bodyRow[index]));
break;
default:
throw new UnsupportedOperationException(
"The type "
+ types[index]
+ " is not supported."
+ " Only types 0, 1, 2, and Integer.MAX_VALUE (for hyperlinks)"
+ " are supported. See the javadoc for org.apache.poi.ss.usermodel.Cell "
+ "for more information on the types of cells.");
}
}
}
/**
* Inserts a list of plain items - that is items that are neither numbered or bulleted - into a
* single cell.
*
* @param workbook A reference to the HSSFWorkbook that 'contains' the cell.
* @param listItems An ArrayList whose elements encapsulate the text for the list's items.
* @param cell An instance of the HSSFCell class that encapsulates a reference to the spreadsheet
* cell into which the list will be written.
*/
public void listInCell(HSSFWorkbook workbook, ArrayList<String> listItems, HSSFCell cell) {
StringBuffer buffer = new StringBuffer();
HSSFCellStyle wrapStyle = workbook.createCellStyle();
wrapStyle.setWrapText(true);
for (String listItem : listItems) {
buffer.append(listItem);
buffer.append("\n");
}
// The StringBuffer's contents are the source for the contents
// of the cell.
cell.setCellValue(new HSSFRichTextString(buffer.toString().trim()));
cell.setCellStyle(wrapStyle);
}
private HSSFWorkbook getWorkbook() throws IOException {
if (this.workBook == null) {
// create a new workbook
this.workBook = new HSSFWorkbook();
// init default column and create the cell style
style = workBook.createCellStyle();
style.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
this.rowCount = 0;
}
return workBook;
}
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;
}
public void decorateRowWithBoldCellYellowBackground(int cellId, HSSFRow row, Object value) {
HSSFCellStyle boldStyleBlueBground = workbook.createCellStyle();
boldStyleBlueBground.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
boldStyleBlueBground.setFont(boldFont);
boldStyleBlueBground.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
boldStyleBlueBground.setFillForegroundColor(new HSSFColor.LIGHT_YELLOW().getIndex());
// boldStyleBlueBground.setBorderBottom(HSSFCellStyle.BORDER_HAIR);
// boldStyleBlueBground.setBorderLeft(HSSFCellStyle.BORDER_HAIR);
// boldStyleBlueBground.setBorderRight(HSSFCellStyle.BORDER_HAIR);
// boldStyleBlueBground.setBorderTop(HSSFCellStyle.BORDER_HAIR);
this.decorateRowWithCell((short) cellId, row, value, boldStyleBlueBground);
}
/**
* 生成文件
*
* @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();
}
@Override
protected void buildExcelDocument(
Map<String, Object> model,
HSSFWorkbook workbook,
HttpServletRequest request,
HttpServletResponse response)
throws Exception {
/*
* if (workbook.getNumberOfSheets() == 0) { setUrl("/resources/xls/Product_Locator_API_Usage_Report"); renderMergedOutputModel(model, request, response); } else {
*/
HSSFSheet sheet = workbook.getSheetAt(0);
HSSFCellStyle ordinStyle = workbook.createCellStyle();
ordinStyle.setBorderRight(CellStyle.BORDER_THIN);
ordinStyle.setBorderLeft(CellStyle.BORDER_THIN);
HSSFCellStyle lineStyle = workbook.createCellStyle();
lineStyle.setBorderTop(CellStyle.BORDER_THIN);
short currentRow = 1;
Report report = (Report) model.get(SEARCH_LIST_KEY);
if (report != null) {
List<ReportAPIUsage> list = report.getRepotAPIUsageList();
HSSFRow row = sheet.getRow(0);
row.createCell(0)
.setCellValue("Date Range: " + report.getDateFrom() + " - " + report.getDateTo());
for (ReportAPIUsage object : list) {
currentRow++;
row = sheet.createRow(currentRow);
HSSFCell cellBrName = row.createCell(BRAND_NAME_COLUMN);
cellBrName.setCellValue(object.getClientKey());
cellBrName.setCellStyle(ordinStyle);
HSSFCell cellAnyPac = row.createCell(ANY_PACKAGE_COLUMN);
cellAnyPac.setCellValue(object.getClientName());
cellAnyPac.setCellStyle(ordinStyle);
HSSFCell cellSpecPac = row.createCell(SPEC_PACKAGE_COLUMN);
cellSpecPac.setCellValue(object.getCountOfQueries());
cellSpecPac.setCellStyle(ordinStyle);
}
currentRow++;
row = sheet.createRow(currentRow);
HSSFCell cellBrName = row.createCell(BRAND_NAME_COLUMN);
cellBrName.setCellStyle(lineStyle);
HSSFCell cellAnyPac = row.createCell(ANY_PACKAGE_COLUMN);
cellAnyPac.setCellStyle(lineStyle);
HSSFCell cellSpecPac = row.createCell(SPEC_PACKAGE_COLUMN);
cellSpecPac.setCellStyle(lineStyle);
}
/* } */
}
@Test
public void testReadCell() throws Exception {
when(mockCell.getCellType()).thenReturn(Cell.CELL_TYPE_NUMERIC);
final HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
final HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
final Date currentDate = new Date();
cellStyle.setDataFormat(
hssfWorkbook.getCreationHelper().createDataFormat().getFormat("m/d/yyyy h:mm"));
when(mockCell.getCellStyle()).thenReturn(cellStyle);
when(mockCell.getDateCellValue()).thenReturn(currentDate);
final Calendar calendar = calendarConverter.readCell(mockCell);
assertNotNull(calendar);
assertEquals(currentDate.getTime(), calendar.getTimeInMillis());
}
public void postProcessXLS(Object document) {
HSSFWorkbook wb = (HSSFWorkbook) document;
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow header = sheet.getRow(0);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillForegroundColor(HSSFColor.GREEN.index);
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
HSSFCell cell = header.getCell(i);
cell.setCellStyle(cellStyle);
}
}
// 创建长字符串样式
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;
}