private void createSummerySheet() {
sheet0 = workbook.createSheet("Summary");
PrintSetup printSetup = sheet0.getPrintSetup();
printSetup.setLandscape(true);
sheet0.setFitToPage(true);
sheet0.setHorizontallyCenter(true);
// title row
Row titleRow = sheet0.createRow(0);
titleRow.setHeightInPoints(45);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellValue("File Health Report");
titleCell.setCellStyle(styles.get("title"));
sheet0.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));
for (int i = 0; i < titles.length; i++) {
Row _row = sheet0.createRow(i + 1);
Cell headerCell = _row.createCell(0);
headerCell.setCellValue(titles[i]);
headerCell.setCellStyle(styles.get("header"));
_row.setHeightInPoints(20);
}
// finally set column widths, the width is measured in units of 1/256th
// of a character width
sheet0.setColumnWidth(0, 50 * 256); // 30 characters wide
}
/**
* @param SheetName excel里面的sheet的名字 （可以任意写，一般和excelname一样）
* @param response 要写的流
* @param HeadName 数据的表头 即列名
* @param data 数据 二维数组
* @return
* @throws IOException
*/
public static OutputStream creatExcel(
String SheetName, File file, List<String> HeadName, String[][] data, Integer[] ColumnWidth)
throws IOException {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(SheetName);
CellStyle s = new CellStyle();
Font f12 = new Font();
HSSFFont font12 = ExcelUtil.getHSSFFont(workbook, f12);
HSSFCellStyle style = ExcelUtil.getNewStyle(workbook, s, font12);
// sheet.setDefaultColumnWidth(50);
sheet.setDefaultRowHeight((short) 400);
sheet.setColumnWidth(0, 18 * 256);
OutputStream os = new FileOutputStream(file);
// 设置excel的表头
for (int i = 0; i < HeadName.size(); i++) {
ExcelUtil.setCells(0, i, HeadName.get(i), sheet, style);
}
// 设置excel数据
for (int i = 0; i < data.length; i++) {
for (int j = 0; j < data[i].length; j++) {
ExcelUtil.setCells(i + 1, j, data[i][j], sheet, style);
}
}
// 设置excel 表格的列宽
for (int i = 0; i < HeadName.size(); i++) {
sheet.setColumnWidth(i, ColumnWidth[i]);
}
workbook.write(os);
return os;
}
private void initSheet() {
String[] messageFiles = MessageAssistant.getInstance().getMessageFiles();
sheet.setColumnWidth((short) 0, (short) 10000);
for (short iColumn = 1; iColumn <= messageFiles.length; iColumn++) {
sheet.setColumnWidth(iColumn, (short) 12000);
}
}
public static void buildReport(HSSFSheet worksheet, int startRowIndex, int startColIndex) {
// 设置列宽
worksheet.setColumnWidth(0, 5200);
worksheet.setColumnWidth(1, 5200);
worksheet.setColumnWidth(2, 5200);
buildTitle(worksheet, startRowIndex, startColIndex);
buildHeaders(worksheet, startRowIndex, startColIndex);
}
/**
* 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));
}
}
/**
* Builds the report layout.
*
* <p>This doesn't have any data yet. This is your template.
*/
public static void buildReport(
HSSFSheet worksheet, int startRowIndex, int startColIndex, List<Production> datasource) {
// Set column widths
worksheet.setColumnWidth(0, 5000);
// Build the title and date headers
buildTitle(worksheet, startRowIndex, startColIndex);
// Build the column headers
buildHeaders(worksheet, startRowIndex, startColIndex, datasource);
}
public static OutputStream creatExcel(
String SheetName,
String excelName,
HttpServletResponse response,
List<String> HeadName,
String[][] data,
List<Integer> colunmWidth)
throws IOException {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(SheetName);
CellStyle s = new CellStyle();
Font f12 = new Font();
HSSFFont font12 = ExcelUtil.getHSSFFont(workbook, f12);
HSSFCellStyle style = ExcelUtil.getNewStyles(workbook, s, font12);
sheet.setDefaultColumnWidth(50);
sheet.setDefaultRowHeight((short) 400);
response.setContentType("application/ms-excel;");
response.setHeader(
"Content-Disposition", "attachment;Filename=" + ExcelUtil.toUtf8String(excelName));
sheet.setColumnWidth(0, (HeadName.size()) * 256);
OutputStream os = response.getOutputStream();
// 设置excel的表头
for (int i = 0; i < HeadName.size(); i++) {
ExcelUtil.setCells(0, i, HeadName.get(i), sheet, style);
}
// 设置excel数据
for (int i = 0; i < data.length; i++) {
for (int j = 0; j < data[i].length; j++) {
ExcelUtil.setCells(i + 1, j, data[i][j], sheet, style);
}
}
// 设置excel 表格的列宽
for (int i = 0; i < HeadName.size(); i++) {
sheet.setColumnWidth(i, colunmWidth.get(i));
}
workbook.write(os);
return os;
}
/**
* 设置列宽
*
* @author David
* @param sheet
* @param colgroup
*/
private static void setColumnWidth(HSSFSheet sheet, Element colgroup) {
List<Element> cols = colgroup.getChildren("col");
for (int i = 0; i < cols.size(); i++) {
Element col = cols.get(i);
Attribute width = col.getAttribute("width");
String unit = width.getValue().replaceAll("[0-9,\\.]", "");
String value = width.getValue().replaceAll(unit, "");
int v = 0;
if (StringUtils.isBlank(unit) || "px".endsWith(unit)) {
v = Math.round(Float.parseFloat(value) * 37F);
} else if ("em".endsWith(unit)) {
v = Math.round(Float.parseFloat(value) * 267.5F);
}
sheet.setColumnWidth(i, v);
}
}
private void fixWidthAndPopulate(HSSFCell cell, double numeric, String value) {
int valWidth = 0;
if (numeric != NON_NUMERIC) {
cell.setCellValue(numeric);
valWidth = (cell.getNumericCellValue() + "$,.").length() * WIDTH_MULT;
} else {
cell.setCellValue(value);
valWidth = (cell.getStringCellValue() + "").length() * WIDTH_MULT;
if (valWidth < (WIDTH_MULT * MIN_CHARS)) {
valWidth = WIDTH_MULT * MIN_CHARS;
}
}
if (valWidth > sheet.getColumnWidth(cell.getCellNum())) {
sheet.setColumnWidth(cell.getCellNum(), (short) valWidth);
}
}
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++;
}
}
private void createHeader(TableModel model) {
rownum = 0;
cellnum = 0;
HSSFRow row = sheet.createRow(rownum);
List columns = model.getColumnHandler().getHeaderColumns();
for (Iterator iter = columns.iterator(); iter.hasNext(); ) {
Column column = (Column) iter.next();
String title = column.getCellDisplay();
HSSFCell cell = row.createCell(cellnum);
// modify by springside
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
// end of modify by springside
cell.setCellStyle((HSSFCellStyle) styles.get("titleStyle"));
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(title);
int valWidth = (title + "").length() * WIDTH_MULT;
sheet.setColumnWidth(cell.getCellNum(), (short) valWidth);
cellnum++;
}
}
public static boolean writeMultArray2ExcelFile1(
String fileName,
List<List<String[]>> arrys,
List<String> sheetLabels,
boolean hasHead,
int indexKey)
throws Exception {
if (arrys.isEmpty()) {
System.err.println("No input data!");
return false;
}
HSSFWorkbook wb = new HSSFWorkbook();
HSSFCellStyle headStyle = wb.createCellStyle();
// apply custom font to the text in the comment
HSSFFont font = wb.createFont();
font.setFontName("Courier New");
font.setFontHeightInPoints((short) 10);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setColor(HSSFColor.RED.index);
headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
headStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headStyle.setLocked(true);
headStyle.setFont(font);
HSSFCellStyle bodyStyle = wb.createCellStyle();
bodyStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
bodyStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
bodyStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFCellStyle markedBodyStyle = wb.createCellStyle();
markedBodyStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
markedBodyStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
markedBodyStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
markedBodyStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
markedBodyStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
String lastKey = null;
int switcher = -1;
HSSFCell cell = null;
String[] titleNames = null;
int d = 0;
for (List<String[]> arry : arrys) {
HSSFSheet sheet1 = wb.createSheet(sheetLabels.get(d));
if (hasHead) {
titleNames = (String[]) arry.get(0);
}
int columnNum = ((String[]) arry.get(0)).length;
for (int i = 0; i < columnNum; i++) {
sheet1.setColumnWidth(i, (int) ((30 * 6) / ((double) 1 / 20)));
}
int rowIndex = 0;
// create titile row
HSSFRow row = sheet1.createRow(rowIndex);
if (titleNames != null) {
for (int i = 0; i < columnNum; i++) {
cell = row.createCell(i);
cell.setCellValue(titleNames[i]);
cell.setCellStyle(headStyle);
}
rowIndex++;
}
int rowNum = arry.size();
for (int i = rowIndex; i < rowNum; i++) {
row = sheet1.createRow((i));
String[] line = (String[]) arry.get(i);
columnNum = line.length;
if (indexKey >= 0) {
if (lastKey == null && line[indexKey] != null) {
lastKey = line[indexKey];
switcher *= -1;
} else if (lastKey != null && line[indexKey] == null) {
lastKey = line[indexKey];
switcher *= -1;
} else if (lastKey == null && line[indexKey] == null) {
} else {
if (!lastKey.equals(line[indexKey])) {
switcher *= -1;
lastKey = line[indexKey];
}
}
} else {
switcher = 1;
}
for (int j = 0; j < columnNum; j++) {
cell = row.createCell(j);
if (switcher > 0) {
cell.setCellStyle(bodyStyle);
} else {
cell.setCellStyle(markedBodyStyle);
}
if (line[j] != null) {
if (Util.isNumeric(line[j])) {
// org.?apache.?poi.?hssf.?usermodel.?HSSFCell.CELL_TYPE_NUMERIC
cell.setCellType(0);
cell.setCellValue(Double.parseDouble(line[j]));
} else {
cell.setCellValue(line[j]);
}
} else {
cell.setCellValue(".");
}
}
}
d++;
}
// Write the output to a inFile
FileOutputStream fileOut = new FileOutputStream(fileName);
wb.write(fileOut);
fileOut.close();
return true;
}
/**
* 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
*
* @param headers 表格属性列名数组
* @param dataset 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
* javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
*/
@SuppressWarnings("unchecked")
public Workbook exportExcel(String[] headers, Collection<T> dataset) {
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(sheetName);
// 设置表格默认列宽度为15个字节
if (useStyle) sheet.setDefaultColumnWidth(defaultColumnWidth);
// 生成一个样式
// 声明一个画图的顶级管理器
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
// 定义注释的大小和位置,详见文档
HSSFComment comment =
patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5));
// 设置注释内容
comment.setString(new HSSFRichTextString(docPrse));
// 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
comment.setAuthor(author);
// 产生表格标题行
HSSFRow row = sheet.createRow(startRow);
int colNum1 = startCol;
for (short i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(colNum1);
if (useStyle) cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
colNum1++;
}
// 遍历集合数据，产生数据行
Iterator<T> it = dataset.iterator();
int index = startRow;
while (it.hasNext()) {
index++;
row = sheet.createRow(index);
T t = (T) it.next();
// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
Field[] fields = t.getClass().getDeclaredFields();
int colRow2 = startCol;
for (short i = 0; i < fields.length; i++) {
HSSFCell cell = row.createCell(colRow2);
if (useStyle) cell.setCellStyle(style2);
Field field = fields[i];
String fieldName = field.getName();
String getMethodName =
"get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
try {
Class tCls = t.getClass();
Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
Object value = getMethod.invoke(t, new Object[] {});
// 判断值的类型后进行强制类型转换
String textValue = null;
if (value instanceof Boolean) {
boolean bValue = (Boolean) value;
textValue = "男";
if (!bValue) {
textValue = "女";
}
} else if (value instanceof Date) {
Date date = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat(dateFormat);
textValue = sdf.format(date);
} else if (value instanceof byte[]) {
// 有图片时，设置行高为60px;
row.setHeightInPoints(picCellHeight);
// 设置图片所在列宽度为80px,注意这里单位的一个换算
sheet.setColumnWidth(i, picCellWidth);
// sheet.autoSizeColumn(i);
byte[] bsValue = (byte[]) value;
HSSFClientAnchor anchor =
new HSSFClientAnchor(
0, 0, 1023, 255, (short) colRow2, index, (short) colRow2, index);
anchor.setAnchorType(2);
patriarch.createPicture(
anchor, workbook.addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
} else {
// 其它数据类型都当作字符串简单处理
textValue = value.toString();
}
// 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
if (textValue != null) {
Pattern p = Pattern.compile("^//d+(//.//d+)?$");
Matcher matcher = p.matcher(textValue);
if (matcher.matches()) {
// 是数字当作double处理
cell.setCellValue(Double.parseDouble(textValue));
} else {
HSSFRichTextString richString = new HSSFRichTextString(textValue);
HSSFFont font3 = workbook.createFont();
font3.setColor(HSSFColor.BLUE.index);
richString.applyFont(font3);
cell.setCellValue(richString);
}
}
} catch (SecurityException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} finally {
// 清理资源
}
colRow2++;
}
}
return workbook;
}
@Override
protected void buildExcelDocument(
Map<String, Object> model,
HSSFWorkbook workbook,
HttpServletRequest request,
HttpServletResponse response)
throws Exception {
// 取得模型数据
SqlRowSet table = (SqlRowSet) model.get("table");
String title = model.get("title").toString();
// 创建工作表和标题
HSSFSheet sheet = workbook.createSheet(title); // 创建工作区
HSSFRow row_title = sheet.createRow(0); // 创建一行引用对象
HSSFFont title_font = workbook.createFont(); // 创建标题的字体
title_font.setFontHeightInPoints((short) 8);
title_font.setFontHeight((short) HSSFFont.BOLDWEIGHT_NORMAL);
title_font.setColor((short) (HSSFFont.COLOR_RED));
HSSFCellStyle title_style = workbook.createCellStyle(); // 创建样式
title_style.setFont(title_font);
HSSFCell cell_title = row_title.createCell(1); // 创建单元格引用对象
cell_title.setCellStyle(title_style);
cell_title.setCellValue(title);
// 创建数据表头
String titles[] = {"学生姓名", "性别", "年龄", "身份证号", "出生日期", "政治面貌", "家庭电话", "家庭地址", "健康状况"};
HSSFRow row = sheet.createRow((short) 1);
HSSFCellStyle items_style = workbook.createCellStyle();
items_style.setAlignment((short) HSSFCellStyle.ALIGN_CENTER);
HSSFFont celltbnamefont = workbook.createFont();
celltbnamefont.setFontHeightInPoints((short) 10);
celltbnamefont.setColor((short) (HSSFFont.COLOR_RED));
items_style.setFont(celltbnamefont);
items_style.setWrapText(true);
for (int i = 0; i < titles.length; i++) {
HSSFCell cell = row.createCell(i);
if (i == 3 || i == 6 || i == 2) {
sheet.setColumnWidth(i, 5335);
} else {
sheet.setColumnWidth(i, 3335);
}
cell.setCellValue(titles[i]);
cell.setCellStyle(items_style);
}
HSSFCellStyle datestyle = workbook.createCellStyle();
HSSFDataFormat df = workbook.createDataFormat();
datestyle.setDataFormat(df.getFormat("yyyy-mm-dd"));
int i = 0;
while (table.next()) {
HSSFRow dataRow = sheet.createRow((short) (i + 2));
for (int j = 0; j < 9; j++) {
HSSFCell cell = dataRow.createCell(j);
String data = table.getString(j + 2);
cell.setCellStyle(datestyle);
cell.setCellValue(data);
}
i++;
}
}
// 导出报表到 excel
public void toExcel() throws Exception {
String date = Common.getSystemDate();
// 定义excel以及sheet
HSSFWorkbook workBook = new HSSFWorkbook();
HSSFSheet sheet = workBook.createSheet(date + "学工部设备表");
// header
sheet.setColumnWidth((short) 0, (short) (8 * 256));
sheet.setColumnWidth((short) 1, (short) (20 * 256));
sheet.setColumnWidth((short) 2, (short) (20 * 256));
sheet.setColumnWidth((short) 3, (short) (16 * 256));
sheet.setColumnWidth((short) 4, (short) (20 * 256));
sheet.setColumnWidth((short) 5, (short) (20 * 256));
sheet.setColumnWidth((short) 6, (short) (20 * 256));
sheet.setColumnWidth((short) 7, (short) (16 * 256));
sheet.setColumnWidth((short) 8, (short) (20 * 256));
sheet.setColumnWidth((short) 9, (short) (20 * 256));
sheet.setColumnWidth((short) 10, (short) (20 * 256));
sheet.setColumnWidth((short) 11, (short) (20 * 256));
HSSFRow titleRow = sheet.createRow((short) 0);
HSSFCell titleCell = titleRow.createCell((short) 0);
titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
titleCell.setCellValue("重庆邮电大学学工部设备表");
// 设置表头样式
HSSFCellStyle titleStyle = workBook.createCellStyle();
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 表头字体
HSSFFont titleFont = workBook.createFont();
titleFont.setFontHeightInPoints((short) 16);
titleFont.setFontName("黑体");
titleStyle.setFont(titleFont);
// 表头边框
titleStyle.setBorderBottom(CellStyle.BORDER_THIN);
// titleStyle.setBorderLeft(CellStyle.BORDER_THIN);
titleStyle.setBorderRight(CellStyle.BORDER_THIN);
// titleStyle.setBorderTop(CellStyle.BORDER_THIN);
titleCell.setCellStyle(titleStyle);
titleCell = titleRow.createCell((short) 9);
titleCell.setCellStyle(titleStyle);
// 设置单元格边框
titleRow = sheet.createRow((short) 1);
titleCell = titleRow.createCell((short) 0);
titleCell.setCellStyle(titleStyle);
titleCell = titleRow.createCell((short) 1);
titleCell.setCellStyle(titleStyle);
titleCell = titleRow.createCell((short) 2);
titleCell.setCellStyle(titleStyle);
titleCell = titleRow.createCell((short) 3);
titleCell.setCellStyle(titleStyle);
titleCell = titleRow.createCell((short) 4);
titleCell.setCellStyle(titleStyle);
titleCell = titleRow.createCell((short) 5);
titleCell.setCellStyle(titleStyle);
titleCell = titleRow.createCell((short) 6);
titleCell.setCellStyle(titleStyle);
titleCell = titleRow.createCell((short) 7);
titleCell.setCellStyle(titleStyle);
titleCell = titleRow.createCell((short) 8);
titleCell.setCellStyle(titleStyle);
titleCell = titleRow.createCell((short) 9);
titleCell.setCellStyle(titleStyle);
titleCell = titleRow.createCell((short) 10);
titleCell.setCellStyle(titleStyle);
titleCell = titleRow.createCell((short) 11);
titleCell.setCellStyle(titleStyle);
// 合并单元格 0行0列 1行9列
sheet.addMergedRegion(new Region(0, (short) 0, 1, (short) 11));
// 内容样式
HSSFCellStyle contentStyle = workBook.createCellStyle();
contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 内容字体
HSSFFont contentFont = workBook.createFont();
contentFont.setFontHeightInPoints((short) 11);
contentFont.setFontName("宋体");
contentStyle.setFont(contentFont);
contentStyle.setBorderBottom(CellStyle.BORDER_THIN);
contentStyle.setBorderRight(CellStyle.BORDER_THIN);
// contentStyle.setBorderLeft(CellStyle.BORDER_THIN);
// contentStyle.setBorderTop(CellStyle.BORDER_THIN);
// 第二行表头
titleRow = sheet.createRow((short) 2);
titleCell = titleRow.createCell((short) 0);
titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
titleCell.setCellValue("编号");
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 1);
titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
titleCell.setCellValue("名称");
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 2);
titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
titleCell.setCellValue("设备价值（元）");
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 3);
titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
titleCell.setCellValue("申请部门");
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 4);
titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
titleCell.setCellValue("申请人");
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 5);
titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
titleCell.setCellValue("购买时间");
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 6);
titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
titleCell.setCellValue("购买地点");
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 7);
titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
titleCell.setCellValue("设备状态");
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 8);
titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
titleCell.setCellValue("生产厂商");
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 9);
titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
titleCell.setCellValue("使用部门");
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 10);
titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
titleCell.setCellValue("使用人");
titleCell.setCellStyle(contentStyle);
List<Equipment> equipmentListtemp = new ArrayList<Equipment>();
equipmentListtemp = this.equipmentService.getEquipmentBycondtion("where 1=1");
int i = 0;
Double totalMoney = 0d;
String totalString = "";
for (i = 0; i < equipmentListtemp.size(); ++i) {
titleRow = sheet.createRow((short) (i + 3));
// 第一列 编号
titleCell = titleRow.createCell((short) 0);
titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
if (equipmentListtemp.get(i).getEquipmentcode() != null)
titleCell.setCellValue(equipmentListtemp.get(i).getEquipmentcode());
else titleCell.setCellValue(i + 1);
titleCell.setCellStyle(contentStyle);
// 第二列 名称
titleCell = titleRow.createCell((short) 1);
titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
if (equipmentListtemp.get(i).getName() != null)
titleCell.setCellValue(equipmentListtemp.get(i).getName());
else titleCell.setCellValue("");
titleCell.setCellStyle(contentStyle);
// 第三列 设备价值
titleCell = titleRow.createCell((short) 2);
titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
if (equipmentListtemp.get(i).getValueof().toString() != null) {
titleCell.setCellValue(equipmentListtemp.get(i).getValueof().toString());
totalMoney += equipmentListtemp.get(i).getValueof();
} else {
titleCell.setCellValue("0");
totalMoney += 0;
}
titleCell.setCellStyle(contentStyle);
// 第四列 申请部门
titleCell = titleRow.createCell((short) 3);
titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
String officeIdTemp = equipmentListtemp.get(i).getDepartment();
if (officeIdTemp != null) {
Studentoffice studentofficetemp = this.studentofficeService.findById(officeIdTemp);
if (studentofficetemp != null) {
titleCell.setCellValue(studentofficetemp.getName());
}
} else {
titleCell.setCellValue("请检查办公室");
}
titleCell.setCellStyle(contentStyle);
// 第五列 申请人
titleCell = titleRow.createCell((short) 4);
titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
if (equipmentListtemp.get(i).getBuyers() != null)
titleCell.setCellValue(equipmentListtemp.get(i).getBuyers());
else titleCell.setCellValue("");
titleCell.setCellStyle(contentStyle);
// 第六列 购买时间
titleCell = titleRow.createCell((short) 5);
titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
if (equipmentListtemp.get(i).getPurchasetime() != null)
titleCell.setCellValue(equipmentListtemp.get(i).getPurchasetime().trim());
else titleCell.setCellValue("");
titleCell.setCellStyle(contentStyle);
// 第七列 购买地点
titleCell = titleRow.createCell((short) 6);
titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
if (equipmentListtemp.get(i).getAddress() != null)
titleCell.setCellValue(equipmentListtemp.get(i).getAddress());
else titleCell.setCellValue("请检查岗位名称");
titleCell.setCellStyle(contentStyle);
// 第八列 设备状态
titleCell = titleRow.createCell((short) 7);
titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
if (equipmentListtemp.get(i).getStates() == 1) {
titleCell.setCellValue("正常");
} else titleCell.setCellValue("报废");
titleCell.setCellStyle(contentStyle);
// 第九列 生产厂商
titleCell = titleRow.createCell((short) 8);
titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
if (equipmentListtemp.get(i).getFactory() != null) {
titleCell.setCellValue(equipmentListtemp.get(i).getFactory());
} else titleCell.setCellValue("");
titleCell.setCellStyle(contentStyle);
// 第十列 使用部门
titleCell = titleRow.createCell((short) 9);
titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
Studentoffice Studentofficenow = new Studentoffice();
List<Equipmentdepartment> equipmentdepartmentlisttemp =
this.equipmentDepartmentService.findByEquipmentId(equipmentListtemp.get(i).getId());
Equipmentdepartment Equipmentdepartmentnow = new Equipmentdepartment();
for (int j = 0; j < equipmentdepartmentlisttemp.size(); j++) {
if (equipmentdepartmentlisttemp.get(j).getFlowouttime() == null
|| equipmentdepartmentlisttemp.get(j).getFlowouttime().length() == 0) {
Equipmentdepartmentnow = equipmentdepartmentlisttemp.get(j);
Studentofficenow =
this.studentofficeService.findById(
equipmentdepartmentlisttemp.get(j).getDepartment());
break;
}
}
if (Studentofficenow.getName() != null) {
titleCell.setCellValue(Studentofficenow.getName());
} else titleCell.setCellValue("");
titleCell.setCellStyle(contentStyle);
// 第十一列 使用人
titleCell = titleRow.createCell((short) 10);
titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
Officeteacher officeteachernow = new Officeteacher();
if (Equipmentdepartmentnow != null) {
if (Equipmentdepartmentnow != null) {
List<Equipmentuser> equipmentUserNowList =
this.equipmentuserService.findByEquipmentdepId(Equipmentdepartmentnow.getId());
for (int j = 0; j < equipmentUserNowList.size(); j++) {
if (equipmentUserNowList.get(j).getFlowouttime2() == null
|| equipmentUserNowList.get(j).getFlowouttime2().length() == 0) {
officeteachernow =
this.officeteacherService.findById(
equipmentUserNowList.get(j).getEquipmentuser());
break;
}
}
}
}
if (officeteachernow != null) {
if (officeteachernow.getTeacher() != null) {
if (officeteachernow.getTeacher().getName() != null) {
titleCell.setCellValue(officeteachernow.getTeacher().getName());
}
}
} else titleCell.setCellValue("");
titleCell.setCellStyle(contentStyle);
}
titleRow = sheet.createRow((short) (i + 3));
titleCell = titleRow.createCell((short) 0);
titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
titleCell.setCellValue("合计");
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 1);
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 2);
titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
titleCell.setCellStyle(contentStyle);
titleCell.setCellValue("¥" + totalMoney + "元");
titleCell = titleRow.createCell((short) 11);
titleCell.setCellStyle(contentStyle);
// 设置单元格边框
titleRow = sheet.createRow((short) (i + 4));
titleCell = titleRow.createCell((short) 0);
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 1);
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 2);
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 3);
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 4);
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 5);
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 6);
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 7);
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 8);
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 9);
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 10);
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 11);
titleCell.setCellStyle(contentStyle);
sheet.addMergedRegion(new Region((i + 3), (short) 0, (i + 4), (short) 1));
sheet.addMergedRegion(new Region((i + 3), (short) 2, (i + 4), (short) 11));
// 设置单元格边框
titleRow = sheet.createRow((short) (i + 5));
titleCell = titleRow.createCell((short) 0);
titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
titleCell.setCellValue("大写");
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 1);
titleCell.setCellStyle(contentStyle);
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 2);
titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
// 转换大写方式
totalString = szTzf(totalMoney);
titleCell.setCellValue(totalString);
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 11);
titleCell.setCellStyle(contentStyle);
// 设置单元格边框
titleRow = sheet.createRow((short) (i + 6));
titleCell = titleRow.createCell((short) 0);
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 1);
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 2);
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 3);
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 4);
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 5);
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 6);
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 7);
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 8);
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 9);
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 10);
titleCell.setCellStyle(contentStyle);
titleCell = titleRow.createCell((short) 11);
titleCell.setCellStyle(contentStyle);
sheet.addMergedRegion(new Region((i + 5), (short) 0, (i + 6), (short) 1));
sheet.addMergedRegion(new Region((i + 5), (short) 2, (i + 6), (short) 11));
// 表尾样式
HSSFCellStyle endStyle = workBook.createCellStyle();
endStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
endStyle.setBorderBottom(CellStyle.BORDER_THIN);
endStyle.setBorderRight(CellStyle.BORDER_THIN);
endStyle.setFont(contentFont);
// sheet.getRow(0).getCell((short)0).setCellStyle(titleStyle);
// sheet.getRow(1).getCell((short)0).setCellStyle(contentStyle);
// sheet.getRow(1).getCell((short)1).setCellStyle(contentStyle);
// sheet.getRow(2).getCell((short)1).setCellStyle(contentStyle);
// sheet.getRow(2).getCell((short)1).setCellStyle(contentStyle);
// 导出
HttpServletResponse resp = ServletActionContext.getResponse();
resp.setContentType("application/vnd.ms-excel");
resp.setCharacterEncoding("UFT-8");
String fileName = date + "学工部设备表.xls";
fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");
resp.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
ServletOutputStream op = resp.getOutputStream();
op.flush();
workBook.write(op);
op.close();
// return "toExcel";
/*
* String fileName = date.toLocaleString().substring(0, 10)+
* "勤工助学酬劳发放表.xls"; FileOutputStream fileOut = new
* FileOutputStream(fileName); workBook.write(fileOut); fileOut.close();
*/
}
/**
* Call each of the list creation methods.
*
* @param outputFilename A String that encapsulates the name of and path to the Excel spreadsheet
* file this code will create.
*/
public void demonstrateMethodCalls(String outputFilename) {
HSSFWorkbook workbook = null;
HSSFSheet sheet = null;
HSSFRow row = null;
HSSFCell cell = null;
File outputFile = null;
FileOutputStream fos = null;
ArrayList<MultiLevelListItem> multiLevelListItems = null;
ArrayList<String> listItems = null;
String listItem = null;
try {
workbook = new HSSFWorkbook();
sheet = workbook.createSheet("In Cell Lists");
row = sheet.createRow(0);
// Create a cell at A1 and insert a single, bulleted, item into
// that cell.
cell = row.createCell(0);
this.bulletedItemInCell(workbook, "List Item", cell);
// Create a cell at A2 and insert a plain list - that is one
// whose items are neither bulleted or numbered - into that cell.
row = sheet.createRow(1);
cell = row.createCell(0);
listItems = new ArrayList<String>();
listItems.add("List Item One.");
listItems.add("List Item Two.");
listItems.add("List Item Three.");
listItems.add("List Item Four.");
this.listInCell(workbook, listItems, cell);
// The row height and cell width are set here to ensure that the
// list may be seen.
row.setHeight((short) 1100);
sheet.setColumnWidth(0, 9500);
// Create a cell at A3 and insert a numbered list into that cell.
// Note that a couple of items have been added to the listItems
// ArrayList
row = sheet.createRow(2);
cell = row.createCell(0);
listItems.add("List Item Five.");
listItems.add("List Item Six.");
this.numberedListInCell(workbook, listItems, cell, 1, 2);
row.setHeight((short) 1550);
// Create a cell at A4 and insert a numbered list into that cell.
// Note that a couple of items have been added to the listItems
// ArrayList
row = sheet.createRow(3);
cell = row.createCell(0);
listItems.add("List Item Seven.");
listItems.add("List Item Eight.");
listItems.add("List Item Nine.");
listItems.add("List Item Ten.");
this.bulletedListInCell(workbook, listItems, cell);
row.setHeight((short) 2550);
// Insert a plain, multi-level list into cell A5. Note that
// the major difference here is that the list items are passed as
// an ArrayList of MultiLevelListItems. Note that an ArrayList
// of instances of an inner class was used here in preference to
// a Hashtable or HashMap as the ArrayList will preserve the
// ordering of the items added to it; the first item added will
// be the first item recovered and the last item added, the last
// item recovered.
row = sheet.createRow(4);
cell = row.createCell(0);
multiLevelListItems = new ArrayList<MultiLevelListItem>();
listItems = new ArrayList<String>();
listItems.add("ML List Item One - Sub Item One.");
listItems.add("ML List Item One - Sub Item Two.");
listItems.add("ML List Item One - Sub Item Three.");
listItems.add("ML List Item One - Sub Item Four.");
multiLevelListItems.add(new MultiLevelListItem("List Item One.", listItems));
// Passing either null or an empty ArrayList will signal that
// there are no lower level items associated with the top level
// item
multiLevelListItems.add(new MultiLevelListItem("List Item Two.", null));
multiLevelListItems.add(new MultiLevelListItem("List Item Three.", null));
listItems = new ArrayList<String>();
listItems.add("ML List Item Four - Sub Item One.");
listItems.add("ML List Item Four - Sub Item Two.");
listItems.add("ML List Item Four - Sub Item Three.");
multiLevelListItems.add(new MultiLevelListItem("List Item Four.", listItems));
this.multiLevelListInCell(workbook, multiLevelListItems, cell);
row.setHeight((short) 2800);
// Insert a numbered multi-level list into cell A6. Note that the
// same ArrayList as constructed for the above plain multi-level
// list example will be re-used
row = sheet.createRow(5);
cell = row.createCell(0);
this.multiLevelNumberedListInCell(workbook, multiLevelListItems, cell, 1, 1, 1, 2);
row.setHeight((short) 2800);
// Insert a numbered multi-level list into cell A7. Note that the
// same ArrayList as constructed for the plain multi-level list
// example will be re-used
row = sheet.createRow(6);
cell = row.createCell(0);
this.multiLevelBulletedListInCell(workbook, multiLevelListItems, cell);
row.setHeight((short) 2800);
// Save the completed workbook
outputFile = new File(outputFilename);
fos = new FileOutputStream(outputFile);
workbook.write(fos);
} catch (FileNotFoundException fnfEx) {
System.out.println("Caught a: " + fnfEx.getClass().getName());
System.out.println("Message: " + fnfEx.getMessage());
System.out.println("Stacktrace follows...........");
fnfEx.printStackTrace(System.out);
} catch (IOException ioEx) {
System.out.println("Caught a: " + ioEx.getClass().getName());
System.out.println("Message: " + ioEx.getMessage());
System.out.println("Stacktrace follows...........");
ioEx.printStackTrace(System.out);
} finally {
if (fos != null) {
try {
fos.close();
} catch (IOException ioEx) {
}
}
}
}
public void generateExcel(OutputStream out, XLSCallBack<T> xlscaCallBack)
throws RuntimeException, IOException {
HSSFWorkbook workbook = new HSSFWorkbook();
// create a sheet with specified name
HSSFSheet sheet = workbook.createSheet(xlscaCallBack.getSheetName());
for (int i = 0; i < xlscaCallBack.getColumnWidth().length; i++) {
sheet.setColumnWidth(i, xlscaCallBack.getColumnWidth()[i] * 256);
}
// create a title for sheet title
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, xlscaCallBack.getHeaders().length - 1));
HSSFCellStyle titleCellStyle = workbook.createCellStyle(); // create titleCellStyle for cell
titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont titleFont = workbook.createFont(); // set font
titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
titleFont.setFontName("宋体");
titleFont.setFontHeight((short) (240));
titleFont.setColor(HSSFColor.AUTOMATIC.index);
titleCellStyle.setFont(titleFont);
titleCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border
titleCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
titleCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
titleCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
titleCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // set background
titleCellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
titleCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
titleCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
HSSFRow title = sheet.createRow(0);
title.setHeight((short) 300);
for (int i = 0; i < xlscaCallBack.getHeaders().length; i++) { // create tytle for title
HSSFCell cell = title.createCell(i);
cell.setCellValue(xlscaCallBack.getTitle());
cell.setCellStyle(titleCellStyle);
}
HSSFCellStyle headerCellStyle = workbook.createCellStyle(); // create headerCellStyle for cell
headerCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont headerFont = workbook.createFont(); // set font
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerFont.setFontName("幼圆");
headerFont.setColor(HSSFColor.AUTOMATIC.index);
headerCellStyle.setFont(headerFont);
headerCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border
headerCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
headerCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
headerCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // set background
headerCellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
headerCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
HSSFRow header = sheet.createRow(1);
for (int i = 0; i < xlscaCallBack.getHeaders().length; i++) { // create tytle for header
HSSFCell cell = header.createCell(i);
cell.setCellValue(xlscaCallBack.getHeaders()[i]);
cell.setCellStyle(headerCellStyle);
}
HSSFCellStyle rowCellStyle = workbook.createCellStyle(); // create headerCellStyle for cell
rowCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont rowFont = workbook.createFont(); // set font
rowFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
rowFont.setFontName("幼圆");
rowFont.setColor(HSSFColor.AUTOMATIC.index);
rowCellStyle.setFont(rowFont);
rowCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border
rowCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
rowCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
rowCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
for (int i = 0; i < lists.size(); i++) {
HSSFRow row = sheet.createRow(i + 2);
String values[] = xlscaCallBack.getValue(lists.get(i));
for (int j = 0; j < values.length; j++) {
HSSFCell cell = row.createCell(j);
cell.setCellValue(values[j]);
cell.setCellStyle(rowCellStyle);
}
}
HSSFCellStyle bottomCellStyle = workbook.createCellStyle(); // create titleCellStyle for cell
bottomCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
HSSFFont bottomFont = workbook.createFont(); // set font
bottomFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
bottomFont.setFontName("幼圆");
bottomFont.setColor(HSSFColor.AUTOMATIC.index);
bottomCellStyle.setFont(bottomFont);
bottomCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border
bottomCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
bottomCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
bottomCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
bottomCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // set background
bottomCellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
bottomCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
bottomCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
sheet.addMergedRegion(
new CellRangeAddress(
lists.size() + 2,
lists.size() + 2,
0,
xlscaCallBack.getHeaders().length - 1)); // creaet bootom for xls
HSSFRow bottomRow = sheet.createRow(lists.size() + 2);
for (int i = 0; i < xlscaCallBack.getHeaders().length; i++) { // create tytle for title
HSSFCell cell = bottomRow.createCell(i);
SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
cell.setCellValue("共计导出 " + lists.size() + " 条记录 ,导出日期:" + sf.format(new Date()));
cell.setCellStyle(bottomCellStyle);
}
try {
workbook.write(out);
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException("create Excel failed due to some unkonw reasion~");
}
}
public IndicatorEntryExcelTemplate(final IndicatorEntryData data, final HSSFWorkbook wb) {
this.data = data;
this.wb = wb;
final HSSFSheet sheet =
wb.createSheet(data.getLocalizedVersion("flexibleElementIndicatorsList"));
utils = new ExcelUtils(wb);
int rowIndex = -1;
int cellIndex = 0;
// empty row
utils.putEmptyRow(sheet, ++rowIndex, 8.65f);
// title
utils.putMainTitle(
sheet,
++rowIndex,
data.getLocalizedVersion("flexibleElementIndicatorsList").toUpperCase(),
data.getNumbOfCols());
// empty row
utils.putEmptyRow(sheet, ++rowIndex, ExportConstants.EMPTY_ROW_HEIGHT);
// column headers
row = sheet.createRow(++rowIndex);
row.setHeightInPoints(ExportConstants.TITLE_ROW_HEIGHT);
cellIndex = 0;
utils.putHeader(row, ++cellIndex, data.getLocalizedVersion("name"));
utils.putHeader(row, ++cellIndex, data.getLocalizedVersion("code"));
utils.putHeader(row, ++cellIndex, data.getLocalizedVersion("targetValue"));
utils.putHeader(row, ++cellIndex, data.getLocalizedVersion("value"));
// empty row
utils.putEmptyRow(sheet, ++rowIndex, 8.65f);
// freeze pane
sheet.createFreezePane(0, rowIndex);
for (final IndicatorGroup group : data.getIndicators().getGroups()) {
row = sheet.createRow(++rowIndex);
row.setHeightInPoints(ExportConstants.TITLE_ROW_HEIGHT);
putGroupCell(sheet, rowIndex, group.getName());
for (final IndicatorDTO indicator : group.getIndicators()) {
// indicator's detail sheet
createDetailSheet(indicator);
row = sheet.createRow(++rowIndex);
row.setHeightInPoints(ExportConstants.TITLE_ROW_HEIGHT);
// ind name
utils.createLinkCell(
row.createCell(1),
indicator.getName(),
ExportConstants.INDICATOR_SHEET_PREFIX + indicator.getName(),
true);
// code
utils.putBorderedBasicCell(sheet, rowIndex, 2, indicator.getCode());
// target
putRightAlignedCell(sheet, rowIndex, 3, indicator.getObjective());
// current value
putRightAlignedCell(sheet, rowIndex, 4, data.getFormattedValue(indicator));
}
}
sheet.setColumnWidth(0, 256 * 2);
sheet.setColumnWidth(1, 256 * 45);
sheet.setColumnWidth(2, 256 * 27);
sheet.setColumnWidth(3, 256 * 27);
sheet.setColumnWidth(4, 256 * 27);
}
private void createDetailSheet(final IndicatorDTO indicator) {
final boolean isQualitative = indicator.getAggregation() == IndicatorDTO.AGGREGATE_MULTINOMIAL;
final HSSFSheet sheetEx =
wb.createSheet(
utils.normalizeAsLink(ExportConstants.INDICATOR_SHEET_PREFIX + indicator.getName()));
int rowIndex = -1;
final List<PivotTableData.Axis> leaves =
data.getEntryMap().get(indicator.getId()).getRootColumn().getLeaves();
int numbOfLeaves = leaves.size();
int numbOfCols = 4;
// back to list link
row = sheetEx.createRow(++rowIndex);
utils.createLinkCell(
row.createCell(1),
data.getLocalizedVersion("goToIndicatorsList"),
data.getLocalizedVersion("flexibleElementIndicatorsList"),
false);
sheetEx.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, numbOfCols));
// title
utils.putMainTitle(sheetEx, ++rowIndex, indicator.getName(), numbOfCols);
// empty row
utils.putEmptyRow(sheetEx, ++rowIndex, ExportConstants.EMPTY_ROW_HEIGHT);
sheetEx.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, numbOfCols));
// put details
putBasicInfo(
sheetEx, ++rowIndex, data.getLocalizedVersion("code"), indicator.getCode(), numbOfCols);
putBasicInfo(
sheetEx,
++rowIndex,
data.getLocalizedVersion("group"),
data.getGroupMap().get(indicator.getGroupId()),
numbOfCols);
// type
String type = null;
;
if (isQualitative) {
// qualitative
type = data.getLocalizedVersion("qualitative");
} else {
// quantitative
type = data.getLocalizedVersion("quantitative");
}
putBasicInfo(sheetEx, ++rowIndex, data.getLocalizedVersion("type"), type, numbOfCols);
// conditional
if (isQualitative) {
// qualitative
// possible values
row = sheetEx.createRow(++rowIndex);
// key
cell = utils.putHeader(row, 1, data.getLocalizedVersion("possibleValues"));
cell.getCellStyle().setAlignment(CellStyle.ALIGN_RIGHT);
// value
final MultiItemText itemText = data.formatPossibleValues(indicator.getLabels());
utils.putBorderedBasicCell(sheetEx, rowIndex, 2, itemText.text);
row.setHeightInPoints(itemText.lineCount * defHeight);
region = new CellRangeAddress(rowIndex, rowIndex, 2, numbOfCols);
sheetEx.addMergedRegion(utils.getBorderedRegion(region, sheetEx, wb));
} else {
// quantitative
// aggregation method
String aggrMethod = null;
if (indicator.getAggregation() == IndicatorDTO.AGGREGATE_AVG)
aggrMethod = data.getLocalizedVersion("average");
else aggrMethod = data.getLocalizedVersion("sum");
putBasicInfo(
sheetEx,
++rowIndex,
data.getLocalizedVersion("aggregationMethod"),
aggrMethod,
numbOfCols);
// units
putBasicInfo(
sheetEx, ++rowIndex, data.getLocalizedVersion("units"), indicator.getUnits(), numbOfCols);
// target value
putBasicInfo(
sheetEx,
++rowIndex,
data.getLocalizedVersion("targetValue"),
indicator.getObjective(),
numbOfCols);
}
// source of ver
putBasicInfo(
sheetEx,
++rowIndex,
data.getLocalizedVersion("sourceOfVerification"),
indicator.getSourceOfVerification(),
numbOfCols);
row.setHeightInPoints(
utils.calculateLineCount(indicator.getSourceOfVerification(), 3 * 18) * defHeight);
// comment
putBasicInfo(
sheetEx,
++rowIndex,
data.getLocalizedVersion("indicatorComments"),
indicator.getDescription(),
numbOfCols);
row.setHeightInPoints(utils.calculateLineCount(indicator.getDescription(), 3 * 18) * defHeight);
// value
putBasicInfo(
sheetEx,
++rowIndex,
data.getLocalizedVersion("value"),
data.getFormattedValue(indicator),
numbOfCols);
// empty row
utils.putEmptyRow(sheetEx, ++rowIndex, ExportConstants.EMPTY_ROW_HEIGHT);
sheetEx.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, numbOfCols));
// data entry
// header
row = sheetEx.createRow(++rowIndex);
row.setHeightInPoints(defHeight);
int cellIndex = 0;
utils.putHeader(row, ++cellIndex, data.getLocalizedVersion("sideAndMonth"));
Map<String, Integer> columnIndexMap = new HashMap<String, Integer>();
for (PivotTableData.Axis axis : leaves) {
utils.putHeader(row, ++cellIndex, axis.getLabel());
columnIndexMap.put(axis.getLabel(), cellIndex);
}
// rows
for (PivotTableData.Axis axis :
data.getEntryMap().get(indicator.getId()).getRootRow().getChildren()) {
row = sheetEx.createRow(++rowIndex);
row.setHeightInPoints(defHeight);
utils.putHeader(row, 1, axis.getLabel());
// populate empty cells
for (int i = 0; i < numbOfLeaves; i++) {
cell = utils.putBorderedBasicCell(sheetEx, rowIndex, i + 2, "");
}
// insert values
for (Map.Entry<PivotTableData.Axis, PivotTableData.Cell> entry : axis.getCells().entrySet()) {
cellIndex = columnIndexMap.get(entry.getKey().getLabel());
Object value = null;
boolean rightAligned = false;
if (isQualitative) {
value = data.getLabelByIndex(indicator.getLabels(), entry.getValue().getValue());
} else {
value = new Long(Math.round(entry.getValue().getValue()));
rightAligned = true;
}
putValueCell(sheetEx, rowIndex, cellIndex, value, rightAligned);
}
}
// col width
sheetEx.setColumnWidth(0, 256 * 2);
sheetEx.autoSizeColumn(1);
for (int i = 2; i < 2 + numbOfLeaves; i++) {
sheetEx.setColumnWidth(i, 256 * 16);
}
}
public void setColumnWidth(int columnIndex, int width) {
sheet.setColumnWidth(columnIndex, width);
}
/**
* 写入工作表
*
* @param wb Excel工作簿
* @param title Sheet工作表名称
* @param styles 表头样式
* @param creator 创建人
* @param tableData 表格数据
* @throws Exception
*/
public HSSFWorkbook writeSheet(
HSSFWorkbook wb,
HashMap<String, HSSFCellStyle> styles,
String creator,
List<TableData> tableDataLst)
throws Exception {
SimpleDateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分");
String create_time = formater.format(new Date());
int cnt = 1;
for (TableData tableData : tableDataLst) {
String sheetTitle = tableData.getSheetTitle();
sheetTitle = sheetTitle == null || sheetTitle.equals("") ? "sheet" + cnt : sheetTitle;
cnt++;
TableHeaderMetaData headerMetaData = tableData.getTableHeader(); // 获得HTML的表头元素
HSSFSheet sheet = wb.createSheet(sheetTitle); // 在Excel工作簿中建一工作表
sheet.setDisplayGridlines(false); // 设置表标题是否有表格边框
wb.cloneSheet(0);
// 创建标题
HSSFRow row = sheet.createRow(0); // 创建新行
HSSFCell cell = row.createCell(0); // 创建新列
int rownum = 0;
cell.setCellValue(new HSSFRichTextString(sheetTitle));
HSSFCellStyle style = styles.get("TITLE"); // 设置标题样式
if (style != null) cell.setCellStyle(style);
sheet.addMergedRegion(
new CellRangeAddress(
0, 0, 0, headerMetaData.getColumnCount() - 1)); // 合并标题行：起始行号，终止行号， 起始列号，终止列号
// 创建副标题
row = sheet.createRow(1);
cell = row.createCell(0);
cell.setCellValue(new HSSFRichTextString("创建人:"));
style = styles.get("SUB_TITLE");
if (style != null) cell.setCellStyle(style);
cell = row.createCell(1);
cell.setCellValue(new HSSFRichTextString(creator));
style = styles.get("SUB_TITLE2");
if (style != null) cell.setCellStyle(style);
cell = row.createCell(2);
cell.setCellValue(new HSSFRichTextString("创建时间:"));
style = styles.get("SUB_TITLE");
if (style != null) cell.setCellStyle(style);
cell = row.createCell(3);
style = styles.get("SUB_TITLE2");
cell.setCellValue(new HSSFRichTextString(create_time));
if (style != null) cell.setCellStyle(style);
rownum = 3; // 如果rownum = 1，则去掉创建人、创建时间等副标题；如果rownum = 0， 则把标题也去掉
HSSFCellStyle headerstyle = styles.get("TABLE_HEADER");
int colnum = 0;
for (int i = 0; i < headerMetaData.getOriginColumns().size(); i++) {
TableColumn tc = headerMetaData.getOriginColumns().get(i);
if (i != 0) {
colnum += headerMetaData.getOriginColumns().get(i - 1).getLength();
}
generateColumn(sheet, tc, headerMetaData.maxlevel, rownum, colnum, headerstyle);
}
rownum += headerMetaData.maxlevel;
List<TableDataRow> dataRows = tableData.getRows();
int index = 0;
for (TableDataRow dataRow : dataRows) {
row = sheet.createRow(rownum);
List<TableDataCell> dataCells = dataRow.getCells();
int size = headerMetaData.getColumns().size();
index = -1;
for (int i = 0; i < size; i++) {
TableColumn tc = headerMetaData.getColumns().get(i);
if (!tc.isVisible()) continue;
index++;
createCell(row, tc, dataCells, i, index, styles);
}
rownum++;
}
// 设置前两列根据数据自动列宽
for (int c = 0; c < headerMetaData.getColumns().size(); c++) {
sheet.autoSizeColumn((short) c);
String t = headerMetaData.getColumns().get(c).getDisplay();
if (sheet.getColumnWidth(c) < t.length() * 256 * 3)
sheet.setColumnWidth(c, t.length() * 256 * 3);
}
sheet.setGridsPrinted(true);
}
return wb;
}
/**
* 写入工作表
*
* @param wb Excel工作簿
* @param title Sheet工作表名称
* @param styles 表头样式
* @param creator 创建人
* @param tableData 表格数据
* @throws Exception
*/
public HSSFWorkbook writeSheet(
HSSFWorkbook wb,
String title,
HashMap<String, HSSFCellStyle> styles,
String creator,
TableData tableData,
String subTitleName)
throws Exception {
TableHeaderMetaData headerMetaData = tableData.getTableHeader(); // 获得HTML的表头元素
SimpleDateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分");
String create_time = formater.format(new Date());
HSSFSheet sheet = wb.createSheet(title); // 在Excel工作簿中建一工作表
sheet.setDisplayGridlines(false); // 设置表标题是否有表格边框
// 创建标题
HSSFRow row = sheet.createRow(0); // 创建新行
HSSFCell cell = row.createCell(0); // 创建新列
int rownum = 0;
cell.setCellValue(new HSSFRichTextString(title));
HSSFCellStyle style = styles.get("TITLE"); // 设置标题样式
if (style != null) cell.setCellStyle(style);
sheet.addMergedRegion(
new CellRangeAddress(
0, 0, 0, headerMetaData.getColumnCount() - 1)); // 合并标题行：起始行号，终止行号， 起始列号，终止列号
// 创建副标题
row = sheet.createRow(1);
cell = row.createCell(0);
cell.setCellValue(new HSSFRichTextString(subTitleName)); // updated by zdwang 2014-02-21
style = styles.get("SUB_TITLE");
if (style != null) cell.setCellStyle(style);
cell = row.createCell(1);
cell.setCellValue(new HSSFRichTextString(creator));
style = styles.get("SUB_TITLE2");
if (style != null) cell.setCellStyle(style);
cell = row.createCell(2);
cell.setCellValue(new HSSFRichTextString("创建时间:"));
style = styles.get("SUB_TITLE");
if (style != null) cell.setCellStyle(style);
cell = row.createCell(3);
style = styles.get("SUB_TITLE2");
cell.setCellValue(new HSSFRichTextString(create_time));
if (style != null) cell.setCellStyle(style);
rownum = 3; // 如果rownum = 1，则去掉创建人、创建时间等副标题；如果rownum = 0， 则把标题也去掉
HSSFCellStyle headerstyle = styles.get("TABLE_HEADER");
int colnum = 0;
for (int i = 0; i < headerMetaData.getOriginColumns().size(); i++) {
TableColumn tc = headerMetaData.getOriginColumns().get(i);
if (i != 0) {
colnum += headerMetaData.getOriginColumns().get(i - 1).getLength();
}
generateColumn(sheet, tc, headerMetaData.maxlevel, rownum, colnum, headerstyle);
}
rownum += headerMetaData.maxlevel;
List<TableDataRow> dataRows = tableData.getRows();
HashMap<Integer, Integer> counter = new HashMap<Integer, Integer>();
HashMap<Integer, String> word = new HashMap<Integer, String>();
int index = 0;
for (TableDataRow dataRow : dataRows) {
row = sheet.createRow(rownum);
List<TableDataCell> dataCells = dataRow.getCells();
int size = headerMetaData.getColumns().size();
index = -1;
for (int i = 0; i < size; i++) {
TableColumn tc = headerMetaData.getColumns().get(i);
if (!tc.isVisible()) continue;
index++;
String value = dataCells.get(i).getValue();
if (tc.isGrouped()) {
String w = word.get(index);
if (w == null) {
word.put(index, value);
counter.put(index, 1);
createCell(row, tc, dataCells, i, index, styles);
} else {
if (w.equals(value)) {
counter.put(index, counter.get(index) + 1);
} else {
stopGrouping(sheet, word, counter, index, size, rownum, styles.get("STRING"));
word.put(index, value);
counter.put(index, 1);
createCell(row, tc, dataCells, i, index, styles);
}
}
} else {
createCell(row, tc, dataCells, i, index, styles);
}
}
rownum++;
}
stopGrouping(sheet, word, counter, 0, index, rownum, styles.get("STRING"));
// 设置前两列根据数据自动列宽
for (int c = 0; c < headerMetaData.getColumns().size(); c++) {
sheet.autoSizeColumn((short) c);
String t = headerMetaData.getColumns().get(c).getDisplay();
if (sheet.getColumnWidth(c) < t.length() * 256 * 3)
sheet.setColumnWidth(c, t.length() * 256 * 3);
}
sheet.setGridsPrinted(true);
return wb;
}
public static boolean writeArray2ExcelSheet1(
HSSFSheet sheet1, HSSFWorkbook wb, List<String[]> arry, boolean hasHead) throws Exception {
int rowNum = arry.size();
if (rowNum == 0) {
System.err.println("No input data!");
return false;
}
String[] titleNames = null;
if (hasHead) {
titleNames = (String[]) arry.get(0);
}
int columnNum = ((String[]) arry.get(0)).length;
for (int i = 0; i < columnNum; i++) {
sheet1.setColumnWidth(i, (int) ((30 * 8) / ((double) 1 / 20)));
}
HSSFFont font = wb.createFont();
font.setFontName("Courier New");
font.setFontHeightInPoints((short) 10);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setColor(HSSFColor.RED.index);
HSSFCellStyle headStyle = wb.createCellStyle();
headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
headStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headStyle.setLocked(true);
headStyle.setFont(font);
HSSFCellStyle bodyStyle = wb.createCellStyle();
bodyStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
bodyStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
bodyStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFCellStyle markedBodyStyle = wb.createCellStyle();
markedBodyStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
markedBodyStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
markedBodyStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
markedBodyStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
markedBodyStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
int rowIndex = 0;
// create titile row
HSSFRow row = sheet1.createRow(rowIndex);
HSSFCell cell = null;
if (titleNames != null) {
for (int i = 0; i < columnNum; i++) {
cell = row.createCell(i);
cell.setCellValue(titleNames[i]);
cell.setCellStyle(headStyle);
}
rowIndex++;
}
for (int i = rowIndex; i < rowNum; i++) {
row = sheet1.createRow(i);
String[] line = (String[]) arry.get(i);
columnNum = line.length;
for (int j = 0; j < columnNum; j++) {
cell = row.createCell(j);
if (line[0] != null) {
cell.setCellStyle(markedBodyStyle);
} else {
cell.setCellStyle(bodyStyle);
}
if (line[j] != null) {
if (Util.isNumeric(line[j])) {
// org.?apache.?poi.?hssf.?usermodel.?HSSFCell.CELL_TYPE_NUMERIC
cell.setCellType(0);
cell.setCellValue(Double.parseDouble(line[j]));
} else {
cell.setCellValue(line[j]);
}
} else {
cell.setCellValue("");
}
}
}
return true;
}
public void exportJgjExcel(
String title,
List<String[]> header,
List<Object[]> dataset,
OutputStream out,
short[] width,
List<int[]> merge) {
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(title);
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short) 15);
// 生成一个样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.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);
// 生成并设置另一个样式
HSSFCellStyle style2 = workbook.createCellStyle();
style2.setFillForegroundColor(HSSFColor.WHITE.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);
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
for (int i = 0; i < header.size(); i++) {
row = sheet.createRow(i);
for (int m = 0; m < header.get(i).length; m++) {
HSSFCell cell = row.createCell(m);
cell.setCellStyle(style);
String headValue = header.get(i)[m];
// HSSFRichTextString text = new HSSFRichTextString(header.get(i)[m]);
cell.setCellValue(headValue);
}
}
if (merge != null && merge.size() > 0) {
for (int m = 0; m < merge.size(); m++) {
sheet.addMergedRegion(
new CellRangeAddress(
merge.get(m)[0], merge.get(m)[1], merge.get(m)[2], merge.get(m)[3])); // 合并行
}
}
// sheet.addMergedRegion(new CellRangeAddress(0,0,9,10));
// sheet.addMergedRegion(new CellRangeAddress(0,0,11,12));
for (int j = 0; j < dataset.size(); j++) {
row = sheet.createRow(j + header.size());
for (short i = 0; i < dataset.get(j).length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style2);
HSSFRichTextString richString =
new HSSFRichTextString(String.valueOf(dataset.get(j)[i]).replace("null", ""));
// HSSFFont font3 = workbook.createFont();
// font3.setColor(HSSFColor.BLACK.index);
// richString.applyFont(font3);
cell.setCellValue(richString);
}
}
if (width != null) {
for (short i = 0; i < width.length; i++) {
sheet.setColumnWidth(i, width[i]);
}
}
try {
workbook.write(out);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void exportExcel(
String headline,
String title,
String[] headers,
List<Object[]> dataset,
OutputStream out,
short[] width) {
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(title);
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short) 15);
// 生成一个样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.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);
// 生成并设置另一个样式
HSSFCellStyle style2 = workbook.createCellStyle();
style2.setFillForegroundColor(HSSFColor.WHITE.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);
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
if (row != null) {
HSSFCell cell = row.createCell(0);
HSSFFont f = workbook.createFont();
f.setColor(HSSFColor.DARK_BLUE.index);
f.setFontHeightInPoints((short) 24);
f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
HSSFCellStyle s = workbook.createCellStyle();
s.setAlignment(HSSFCellStyle.ALIGN_CENTER);
s.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
s.setFont(f);
cell.setCellStyle(s);
cell.setCellValue(headline);
}
sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) (headers.length - 1)));
row = sheet.createRow(1);
for (short i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
for (int j = 0; j < dataset.size(); j++) {
row = sheet.createRow(j + 2);
for (short i = 0; i < dataset.get(j).length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style2);
HSSFRichTextString richString =
new HSSFRichTextString(String.valueOf(dataset.get(j)[i]).replace("null", ""));
// HSSFFont font3 = workbook.createFont();
// font3.setColor(HSSFColor.BLACK.index);
// richString.applyFont(font3);
cell.setCellValue(richString);
}
}
if (width != null) {
for (short i = 0; i < width.length; i++) {
sheet.setColumnWidth(i, width[i]);
}
}
try {
workbook.write(out);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Override
protected void buildExcelDocument(
Map<String, Object> model,
HSSFWorkbook workbook,
HttpServletRequest request,
HttpServletResponse response)
throws Exception {
// get data model which is passed by the Spring container
List<Object> errorList = (List<Object>) model.get("errorList");
List<DonVi> donViError = (List<DonVi>) errorList.get(0);
List<String> statusError = (List<String>) errorList.get(1);
// create a new Excel sheet
HSSFSheet sheet = workbook.createSheet("Bộ phận sử dụng bị lỗi import");
// create style for header cells
CellStyle style = workbook.createCellStyle();
Font font = workbook.createFont();
font.setFontName("Times New Roman");
font.setFontHeight((short) 260);
style.setFont(font);
sheet.setDefaultRowHeight((short) 400);
sheet.setColumnWidth(0, 3000);
sheet.setColumnWidth(1, 12000);
sheet.setColumnWidth(2, 12000);
sheet.setColumnWidth(3, 4000);
sheet.setColumnWidth(4, 6000);
sheet.setColumnWidth(5, 5000);
sheet.setDefaultColumnStyle(0, style);
sheet.setDefaultColumnStyle(1, style);
sheet.setDefaultColumnStyle(2, style);
sheet.setDefaultColumnStyle(3, style);
sheet.setDefaultColumnStyle(4, style);
sheet.setDefaultColumnStyle(5, style);
// create header row
CellStyle style2 = workbook.createCellStyle();
Font font2 = workbook.createFont();
font2.setFontName("Times New Roman");
font2.setBoldweight(Font.BOLDWEIGHT_BOLD);
font2.setFontHeight((short) 260);
style2.setFont(font2);
style2.setAlignment(CellStyle.ALIGN_CENTER);
HSSFRow row2 = sheet.createRow(0);
row2.createCell(0).setCellValue("Công ty điện lực thành phố Cần Thơ");
row2.getCell(0).setCellStyle(style);
row2.createCell(1).setCellValue("Kho Công ty Điện Lực Cần Thơ");
row2.getCell(1).setCellStyle(style);
// create header row
HSSFRow header = sheet.createRow(1);
response.setHeader("Content-Disposition", "inline; filename=" + "BophansudungError.xls");
header.createCell(0).setCellValue("Mã BPSD");
header.getCell(0).setCellStyle(style2);
header.createCell(1).setCellValue("Tên BPSD");
header.getCell(1).setCellStyle(style2);
header.createCell(2).setCellValue("Địa chỉ");
header.getCell(2).setCellStyle(style2);
header.createCell(3).setCellValue("Email");
header.getCell(3).setCellStyle(style2);
header.createCell(4).setCellValue("Số điện thoại");
header.getCell(4).setCellStyle(style2);
header.createCell(5).setCellValue("Lỗi");
header.getCell(5).setCellStyle(style2);
// create data rows
int rowCount = 2;
int i = 0;
for (DonVi dv : donViError) {
HSSFRow aRow = sheet.createRow(rowCount++);
aRow.createCell(0).setCellValue(dv.getDvMa());
aRow.createCell(1).setCellValue(dv.getDvTen());
aRow.createCell(2).setCellValue(dv.getDiaChi());
aRow.createCell(3).setCellValue(dv.getEmail());
aRow.createCell(4).setCellValue(dv.getSdt());
aRow.createCell(5).setCellValue(statusError.get(i++));
}
}
