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 methods
* ************************************************************************
*/
@Override
public void open(String filepath) throws Exception {
this.filepath = filepath;
String author = ini.getValue("Excel", "Author", "Toël Hartmann");
String keywords = ini.getValue("Excel", "Keywords", "");
wb = new HSSFWorkbook();
// Set some properties
wb.createInformationProperties();
wb.getSummaryInformation().setAuthor(author);
wb.getSummaryInformation().setKeywords(keywords);
wb.getSummaryInformation().setCreateDateTime(new Date());
// Create the cell style for column titles
titleStyle = wb.createCellStyle();
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
titleStyle.setFont(font);
String sheetName = getConfigValue("Excel", "sheet", null);
if (sheetName == null || sheetName.isEmpty())
sheetName = FileUtils.getFileNameWithoutExtention(filepath);
sheet = wb.createSheet(sheetName);
if (getConfigValue("Excel", "freezeFirstRow", "true").equalsIgnoreCase("true")) {
sheet.createFreezePane(0, 1);
}
}
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"));
}
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);
}
/**
* 生成文件
*
* @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);
}
/* } */
}
/**
* 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);
}
/**
* 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("rawtypes")
private void generateHeaderRow(
HSSFWorkbook workBook, Sheet sheet, JSONArray columnConfigJSONObject) {
CellStyle center = workBook.createCellStyle();
center.setAlignment(HSSFCellStyle.ALIGN_CENTER);
int columnType = Cell.CELL_TYPE_STRING;
String col = null, text = null;
short width = 15;
sheet.setDefaultColumnWidth(width);
Row headerRow = createRow(sheet, headerRowNum);
for (short i = 0; i < columnConfigJSONObject.length(); i++) {
JSONObject headerObj = columnConfigJSONObject.getJSONObject(i);
for (Iterator iterator = headerObj.keys(); iterator.hasNext(); ) {
String key = (String) iterator.next();
if ("columnType".equals(key)) {
// if(isNumber(headerObj.getString(key))) {
// columnType = Cell.CELL_TYPE_NUMERIC;
// }
} else {
col = key;
text = headerObj.getString(key);
}
}
headerIdArray.add(col);
Cell cell = createCell(headerRow, i, columnType);
cell.setCellValue(new HSSFRichTextString(text));
cell.setCellStyle(center);
}
}
private void writeHeaderRow(Entity entity, HSSFSheet sheet) {
HSSFRow headerRow = sheet.createRow(0);
int colnum = 0;
for (Map.Entry<String, Object> component : getComponents(entity)) {
String componentName = component.getKey();
headerRow.createCell(colnum).setCellValue(new HSSFRichTextString(componentName));
ComponentDescriptor cd = entity.descriptor().getComponent(componentName);
PrimitiveType primitiveType;
if (cd.getTypeDescriptor() instanceof SimpleTypeDescriptor)
primitiveType = ((SimpleTypeDescriptor) cd.getTypeDescriptor()).getPrimitiveType();
else
throw new UnsupportedOperationException(
"Can only export simple type attributes, "
+ "failed to export "
+ entity.type()
+ '.'
+ cd.getName());
Class<?> javaType = (primitiveType != null ? primitiveType.getJavaType() : String.class);
String formatString = null;
if (BeanUtil.isIntegralNumberType(javaType)) formatString = getIntegralPattern();
else if (BeanUtil.isDecimalNumberType(javaType)) formatString = getDecimalPattern();
else if (Time.class.isAssignableFrom(javaType)) formatString = getTimePattern();
else if (Timestamp.class.isAssignableFrom(javaType)) formatString = getTimestampPattern();
else if (Date.class.isAssignableFrom(javaType)) formatString = getDatePattern();
if (formatString != null) {
HSSFDataFormat dataFormat = workbook.createDataFormat();
CellStyle columnStyle = workbook.createCellStyle();
columnStyle.setDataFormat(dataFormat.getFormat(formatString));
sheet.setDefaultColumnStyle(colnum, columnStyle);
}
colnum++;
}
}
private void createWorkbook() {
this.workbook = new HSSFWorkbook();
this.dateCellStyle = workbook.createCellStyle();
HSSFDataFormat format = workbook.createDataFormat();
short dateFormat = format.getFormat(getDatePattern());
this.dateCellStyle.setDataFormat(dateFormat);
}
/**
* 测试单元格样式
*
* @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);
}
/**
* 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);
}
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);
}
/**
* 摘要： @说明：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;
}
/**
* 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));
}
}
public ExcelUtils(List items, ItemSearch itemSearch) {
this.wb = new HSSFWorkbook();
this.sheet = wb.createSheet("jtrac");
this.sheet.setDefaultColumnWidth((short) 12);
HSSFFont fBold = wb.createFont();
fBold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
this.csBold = wb.createCellStyle();
this.csBold.setFont(fBold);
this.csDate = wb.createCellStyle();
this.csDate.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
this.items = items;
this.itemSearch = itemSearch;
}
@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);
}
private CellStyle getDateFormatStyle(HSSFCell poiCell) {
final HSSFWorkbook poiWorkbook = poiCell.getRow().getSheet().getWorkbook();
CellStyle dateFormatStyle = poiWorkbook.createCellStyle();
CreationHelper createHelper = poiWorkbook.getCreationHelper();
dateFormatStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy/mm/dd hh:MM:ss"));
dateFormatStyle.setAlignment(CellStyle.ALIGN_LEFT);
return dateFormatStyle;
}
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);
}
/**
* 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 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 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);
}
@Override
public void setDateTimePattern(String dateTimePattern) {
this.dateTimePattern = dateTimePattern;
DateTimeConverter dtConverter = (DateTimeConverter) converters.lookup(Date.class);
dtConverter.setDateTimePattern(dateTimePattern);
dateCellStyle = workbook.createCellStyle();
CreationHelper helper = workbook.getCreationHelper();
dateCellStyle.setDataFormat(helper.createDataFormat().getFormat(dateTimePattern));
}
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 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);
}
// 默认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;
}
