private void createStyles(HSSFWorkbook workBook, HSSFSheet workSheet) {
/*Font bold = workBook.createFont();
bold.setBoldweight(Font.BOLDWEIGHT_BOLD);
bold.setFontHeightInPoints((short) 10);
bold.setColor(Font.COLOR_RED);*/
HSSFFont hssfFont = workBook.createFont();
hssfFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
hssfFont.setColor(Font.COLOR_RED);
boldStyle = workBook.createCellStyle();
boldStyle.setBorderBottom(CellStyle.BORDER_THIN);
boldStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
boldStyle.setFont(hssfFont);
boldStyle.setFillBackgroundColor(HSSFColor.BLUE.index);
defaultFont = workBook.createFont();
defaultFont.setFontHeightInPoints((short) 10);
defaultFont.setFontName("Arial");
defaultFont.setColor(IndexedColors.BLACK.getIndex());
defaultFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
defaultFont.setItalic(true);
newStyle = workBook.createCellStyle();
// newStyle.setFillBackgroundColor(IndexedColors.DARK_GREEN.getIndex());
// newStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
newStyle.setAlignment(CellStyle.ALIGN_CENTER);
newStyle.setFont(defaultFont);
/* hssfCellStyle = workBook.createCellStyle();
hssfCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
hssfCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
hssfCellStyle.setFont(bold);*/
}
public HSSFCellStyle createLastCellStyle(boolean white) {
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.DARK_BLUE.index);
style.setLeftBorderColor(HSSFColor.DARK_BLUE.index);
style.setBottomBorderColor(HSSFColor.DARK_BLUE.index);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
if (white) {
style.setFillBackgroundColor(HSSFColor.WHITE.index);
style.setFillForegroundColor(HSSFColor.WHITE.index);
} else {
style.setFillBackgroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
style.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
}
HSSFFont font = workbook.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 12);
style.setFont(font);
return style;
}
private CoverageResults() {
fileName = "Coverage-results.xls";
workbook = new HSSFWorkbook();
allElementsSheet = workbook.createSheet("All Elements");
packagesSheet = workbook.createSheet("Packages");
boundClassesSheet = workbook.createSheet("Bound Classes");
allClassesSheet = workbook.createSheet("All Classes");
boundMethodsSheet = workbook.createSheet("Bound Methods");
allMethodsSheet = workbook.createSheet("All Methods");
normalFont = workbook.createFont();
normalFont.setFontName(HSSFFont.FONT_ARIAL);
normalFont.setFontHeightInPoints((short) 10);
boldFont = workbook.createFont();
boldFont.setFontName(HSSFFont.FONT_ARIAL);
boldFont.setFontHeightInPoints((short) 10);
boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
centerAlignmentCellStyle = workbook.createCellStyle();
centerAlignmentCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
leftAlignmentCellStyle = workbook.createCellStyle();
leftAlignmentCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
rightAlignmentCellStyle = workbook.createCellStyle();
rightAlignmentCellStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
dataFormatCellStyle = workbook.createCellStyle();
CreationHelper createHelper = workbook.getCreationHelper();
dataFormatCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd/MM/yyyy"));
}
public void createExcelFile(String filename) throws Exception {
out = new FileOutputStream(filename);
wb = new HSSFWorkbook();
ws = this.wb.createSheet();
cs1 = this.wb.createCellStyle();
cs2 = this.wb.createCellStyle();
cs3 = this.wb.createCellStyle();
df = this.wb.createDataFormat();
f1 = this.wb.createFont();
f2 = this.wb.createFont();
f1.setFontHeightInPoints((short) 10);
f1.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
f2.setFontHeightInPoints((short) 13);
f2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
f2.setColor(HSSFFont.COLOR_RED);
cs1.setFont(f1);
cs1.setDataFormat(df.getFormat("text"));
cs2.setFont(f2);
cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
cs3.setFont(f1);
cs3.setDataFormat(df.getFormat("#,##0.0"));
wb.setSheetName(0, "QryDetail", HSSFWorkbook.ENCODING_UTF_16);
}
/**
* 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 HSSFCellStyle createScriptStyle() {
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
HSSFFont font = workbook.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 12);
font.setColor(HSSFColor.BLACK.index);
style.setFont(font);
return style;
}
public HSSFCellStyle createInfoCellStyle() {
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont font = workbook.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setColor(HSSFColor.BLACK.index);
style.setFont(font);
return style;
}
public HSSFCellStyle createTitleCellStyle() {
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont font = workbook.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 16);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setUnderline(HSSFFont.U_SINGLE);
font.setColor(HSSFColor.GREY_80_PERCENT.index);
style.setFont(font);
return style;
}
public HSSFCellStyle createSubTitleCellStyle() {
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setFillBackgroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
style.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
HSSFFont font = workbook.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 14);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setColor(HSSFColor.DARK_BLUE.index);
style.setFont(font);
return style;
}
protected HSSFCellStyle createInternalCellStyle() {
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.DARK_BLUE.index);
style.setLeftBorderColor(HSSFColor.DARK_BLUE.index);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setFillBackgroundColor(HSSFColor.WHITE.index);
style.setFillForegroundColor(HSSFColor.WHITE.index);
HSSFFont font = workbook.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 12);
style.setFont(font);
return style;
}
private void initHeadCellStyle() {
style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 生成一个字体
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
}
private void writeSheet(
List<Map<String, String>> valueMaps, String worksheetName, HSSFWorkbook wb) {
// Set column widths
HSSFSheet sheet = wb.createSheet(worksheetName);
sheet.setColumnWidth(Short.parseShort("0"), Short.parseShort("15000"));
sheet.setColumnWidth(Short.parseShort("1"), Short.parseShort("30000"));
// header style
HSSFCellStyle headerStyle;
HSSFFont headerFont = wb.createFont();
headerFont.setFontHeightInPoints((short) 11);
headerStyle = wb.createCellStyle();
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
headerStyle.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index);
headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
headerStyle.setFont(headerFont);
headerStyle.setWrapText(true);
// header row
HSSFRow headerRow = sheet.createRow(0);
headerRow.setHeightInPoints(30);
HSSFCell headerCell0 = headerRow.createCell((short) 0);
HSSFCell headerCell1 = headerRow.createCell((short) 1);
headerCell0.setCellStyle(headerStyle);
setText(headerCell0, "Layer Name");
headerCell1.setCellStyle(headerStyle);
setText(headerCell1, "Message");
int counter = 1;
for (Map<String, String> valueMap : valueMaps) {
HSSFRow dataRow = sheet.createRow(counter);
String layer = valueMap.get("layer");
String status = valueMap.get("status");
status = HtmlUtils.htmlUnescape(status);
HSSFCell currentCell0 = dataRow.createCell((short) 0);
HSSFCell currentCell1 = dataRow.createCell((short) 1);
setText(currentCell0, layer);
setText(currentCell1, status);
counter++;
}
}
public ExcelSupport() {
super();
workbook = new HSSFWorkbook();
numericCellStyle = workbook.createCellStyle();
numericData = workbook.createDataFormat();
numericCellStyle.setDataFormat(numericData.getFormat("#,##0.00"));
percentageCellStyle = workbook.createCellStyle();
percentageCellStyle.setDataFormat((short) 4);
decimalCellStyle = workbook.createCellStyle();
decimalCellStyle.setDataFormat(workbook.createDataFormat().getFormat("#,##0.0#####"));
multiLineTextStyle = workbook.createCellStyle();
multiLineTextStyle.setWrapText(true);
multiLineTextStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
genericStyle = workbook.createCellStyle();
genericStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
dollarStyle = workbook.createCellStyle();
dollarStyle.setDataFormat((short) 8);
headlineStyle = workbook.createCellStyle();
HSSFFont headlineFont = workbook.createFont();
// set font 1 to 12 point type
headlineFont.setFontHeightInPoints((short) 24);
// make it bold
// arial is the default font
headlineFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headlineStyle.setFont(headlineFont);
boldFont = workbook.createFont();
boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
boldStyle = workbook.createCellStyle();
boldStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
boldStyle.setFont(boldFont);
}
public void setComment(int rowIndex, int columnIndex, int rows, int cols, String text) {
HSSFCell cell = getCell(rowIndex, columnIndex);
if (cell.getCellComment() != null) {
cell.removeCellComment();
}
ClientAnchor anchor = getCreationHelper().createClientAnchor();
anchor.setCol1(columnIndex + 2);
anchor.setCol2(columnIndex + 2 + cols);
anchor.setRow1(rowIndex + 1);
anchor.setRow2(rowIndex + 1 + rows);
RichTextString str = getCreationHelper().createRichTextString(text);
HSSFFont font = createFont();
font.setFontHeightInPoints((short) 10);
str.applyFont(font);
Comment comment = getDrawing().createCellComment(anchor);
comment.setString(str);
cell.setCellComment(comment);
}
/** 單元格的字体 workbook excel font --字体模型 */
public static HSSFFont getHSSFFont(HSSFWorkbook workbook, Font f) {
HSSFFont font = workbook.createFont();
// 字体名称
font.setFontName(f.getFontNm());
// 字号
font.setFontHeightInPoints(f.getFontSize());
// 字体颜色
font.setColor(f.getFontColor());
// 下划线
font.setUnderline(f.getUnderline());
// 上标下标
font.setTypeOffset(f.getOffset());
// 删除线
font.setStrikeout(f.getDelLine());
// 加粗
font.setBoldweight(f.getBold());
// 斜线
font.setItalic(f.getItalic());
// 字体高度
// font.setFontHeight(arg0);
return font;
}
protected HSSFCellStyle createSummaryCellStyle() {
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.DARK_BLUE.index);
style.setLeftBorderColor(HSSFColor.DARK_BLUE.index);
style.setTopBorderColor(HSSFColor.DARK_BLUE.index);
style.setBottomBorderColor(HSSFColor.DARK_BLUE.index);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
HSSFFont font = workbook.createFont();
font.setFontName("Courier New");
font.setFontHeightInPoints((short) 15);
font.setColor(HSSFColor.BLACK.index);
style.setFont(font);
return style;
}
/**
* 读取xml定义的模板，创建excel，感觉没啥用
*
* @author David
* @param args
*/
public static void main(String[] args) {
// 获取解析xml文件路径
String path = System.getProperty("user.dir") + "/bin/student2.xml";
File file = new File(path);
SAXBuilder builder = new SAXBuilder();
try {
// 解析xml文件
Document parse = builder.build(file);
// 创建Excel
HSSFWorkbook wb = new HSSFWorkbook();
// 创建sheet
HSSFSheet sheet = wb.createSheet("Sheet0");
// 获取xml文件跟节点
Element root = parse.getRootElement();
// 获取模板名称
String templateName = root.getAttribute("name").getValue();
int rownum = 0;
int column = 0;
// 设置列宽
Element colgroup = root.getChild("colgroup");
setColumnWidth(sheet, colgroup);
// 设置标题
Element title = root.getChild("title");
List<Element> trs = title.getChildren("tr");
for (int i = 0; i < trs.size(); i++) {
Element tr = trs.get(i);
List<Element> tds = tr.getChildren("td");
HSSFRow row = sheet.createRow(rownum);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
for (column = 0; column < tds.size(); column++) {
Element td = tds.get(column);
HSSFCell cell = row.createCell(column);
Attribute rowSpan = td.getAttribute("rowspan");
Attribute colSpan = td.getAttribute("colspan");
Attribute value = td.getAttribute("value");
if (value != null) {
String val = value.getValue();
cell.setCellValue(val);
int rspan = rowSpan.getIntValue() - 1;
int cspan = colSpan.getIntValue() - 1;
// 设置字体
HSSFFont font = wb.createFont();
font.setFontName("仿宋_GB2312");
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体加粗
// font.setFontHeight((short)12);
font.setFontHeightInPoints((short) 12);
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
// 合并单元格居中
sheet.addMergedRegion(new CellRangeAddress(rspan, rspan, 0, cspan));
}
}
rownum++;
}
// 设置表头
Element thead = root.getChild("thead");
trs = thead.getChildren("tr");
for (int i = 0; i < trs.size(); i++) {
Element tr = trs.get(i);
HSSFRow row = sheet.createRow(rownum);
List<Element> ths = tr.getChildren("th");
for (column = 0; column < ths.size(); column++) {
Element th = ths.get(column);
Attribute valueAttr = th.getAttribute("value");
HSSFCell cell = row.createCell(column);
if (valueAttr != null) {
String value = valueAttr.getValue();
cell.setCellValue(value);
}
}
rownum++;
}
// 设置数据区域样式
Element tbody = root.getChild("tbody");
Element tr = tbody.getChild("tr");
int repeat = tr.getAttribute("repeat").getIntValue();
List<Element> tds = tr.getChildren("td");
for (int i = 0; i < repeat; i++) {
HSSFRow row = sheet.createRow(rownum);
for (column = 0; column < tds.size(); column++) {
Element td = tds.get(column);
HSSFCell cell = row.createCell(column);
setType(wb, cell, td);
}
rownum++;
}
// 生成Excel导入模板
File tempFile = new File("e:/" + templateName + ".xls");
tempFile.delete();
tempFile.createNewFile();
FileOutputStream stream = FileUtils.openOutputStream(tempFile);
wb.write(stream);
stream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
private Map initStyles(HSSFWorkbook wb, short fontHeight) {
Map result = new HashMap();
HSSFCellStyle titleStyle = wb.createCellStyle();
HSSFCellStyle textStyle = wb.createCellStyle();
HSSFCellStyle boldStyle = wb.createCellStyle();
HSSFCellStyle numericStyle = wb.createCellStyle();
HSSFCellStyle numericStyleBold = wb.createCellStyle();
HSSFCellStyle moneyStyle = wb.createCellStyle();
HSSFCellStyle moneyStyleBold = wb.createCellStyle();
HSSFCellStyle percentStyle = wb.createCellStyle();
HSSFCellStyle percentStyleBold = wb.createCellStyle();
result.put("titleStyle", titleStyle);
result.put("textStyle", textStyle);
result.put("boldStyle", boldStyle);
result.put("numericStyle", numericStyle);
result.put("numericStyleBold", numericStyleBold);
result.put("moneyStyle", moneyStyle);
result.put("moneyStyleBold", moneyStyleBold);
result.put("percentStyle", percentStyle);
result.put("percentStyleBold", percentStyleBold);
HSSFDataFormat format = wb.createDataFormat();
// Global fonts
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
font.setColor(HSSFColor.BLACK.index);
font.setFontName(HSSFFont.FONT_ARIAL);
font.setFontHeightInPoints(fontHeight);
HSSFFont fontBold = wb.createFont();
fontBold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
fontBold.setColor(HSSFColor.BLACK.index);
fontBold.setFontName(HSSFFont.FONT_ARIAL);
fontBold.setFontHeightInPoints(fontHeight);
// Money Style
moneyStyle.setFont(font);
moneyStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
moneyStyle.setDataFormat(format.getFormat(moneyFormat));
// Money Style Bold
moneyStyleBold.setFont(fontBold);
moneyStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
moneyStyleBold.setDataFormat(format.getFormat(moneyFormat));
// Percent Style
percentStyle.setFont(font);
percentStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
percentStyle.setDataFormat(format.getFormat(percentFormat));
// Percent Style Bold
percentStyleBold.setFont(fontBold);
percentStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
percentStyleBold.setDataFormat(format.getFormat(percentFormat));
// Standard Numeric Style
numericStyle.setFont(font);
numericStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
// Standard Numeric Style Bold
numericStyleBold.setFont(fontBold);
numericStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
// Title Style
titleStyle.setFont(font);
titleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
titleStyle.setBottomBorderColor(HSSFColor.BLACK.index);
titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
titleStyle.setLeftBorderColor(HSSFColor.BLACK.index);
titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
titleStyle.setRightBorderColor(HSSFColor.BLACK.index);
titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
titleStyle.setTopBorderColor(HSSFColor.BLACK.index);
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// Standard Text Style
textStyle.setFont(font);
textStyle.setWrapText(true);
// Standard Text Style
boldStyle.setFont(fontBold);
boldStyle.setWrapText(true);
return result;
}
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();
}
}
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();
}
}
@SuppressWarnings("deprecation")
public Reporteconcepto(
Rgenerador objeto,
Rgenerador objeto2,
Boolean siinicial,
String partida,
String ruta,
String ruta2,
String path)
throws IOException {
this.FILE = path;
// creacion del libro que contedra nuestro reporte
libro = new HSSFWorkbook();
// cracion de la hoja que estara contenida en nuestro libro
hoja = libro.createSheet("new sheet");
// Definicion de estilo que contendra nuestro encabezado
// *****************************************************************************************************************************************************
// definicion estilos de celdas, establecimineto del tipo de fuente
fuenteen = libro.createFont();
fuenteen.setFontHeightInPoints((short) 12);
fuenteen.setFontName(fuenteen.FONT_ARIAL);
fuenteen.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// Creacion del objeto que se encargara de aplicar el estilo a la celda
esceldaen = libro.createCellStyle();
esceldaen.setWrapText(true);
esceldaen.setAlignment(HSSFCellStyle.ALIGN_LEFT);
esceldaen.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
esceldaen.setFont(fuenteen);
// establecimiento de sombreado de nuestra celda
esceldaen.setFillForegroundColor((short) 44);
esceldaen.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// Definicion de estilo que contendra los tiulos
// *****************************************************************************************************************************************************
// definicion estilos de celdas, establecimineto del tipo de fuente
fuentet = libro.createFont();
fuentet.setFontHeightInPoints((short) 11);
fuentet.setFontName(fuentet.FONT_ARIAL);
fuentet.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// Creacion del objeto que se encargara de aplicar el estilo a la celda
esceldat = libro.createCellStyle();
esceldat.setWrapText(true);
esceldat.setAlignment(HSSFCellStyle.ALIGN_CENTER);
esceldat.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
esceldat.setFont(fuentet);
// establecimiento de bordes
esceldat.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
esceldat.setBottomBorderColor((short) 8);
esceldat.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
esceldat.setLeftBorderColor((short) 8);
esceldat.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
esceldat.setRightBorderColor((short) 8);
esceldat.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
esceldat.setRightBorderColor((short) 8);
// establecimiento de sombreado de nuestra celda
esceldat.setFillForegroundColor((short) 22);
esceldat.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// *****************************************************************************************************************************************************
// Definicion del estilo de la celda de nuestros datos que contendra el
// reporte
// definicion estilos de celdas, establecimineto del tipo de fuente
fuentein = libro.createFont();
fuentein.setFontHeightInPoints((short) 10);
fuentein.setFontName(fuentein.FONT_ARIAL);
fuentein.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// Creacion del objeto que se encargara de aplicar el estilo a la celda
esceldain = libro.createCellStyle();
esceldain.setWrapText(true);
esceldain.setAlignment(HSSFCellStyle.ALIGN_CENTER);
esceldain.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
esceldain.setFont(fuentet);
// establecimiento de bordes
esceldain.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
esceldain.setBottomBorderColor((short) 8);
esceldain.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
esceldain.setLeftBorderColor((short) 8);
esceldain.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
esceldain.setRightBorderColor((short) 8);
esceldain.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
esceldain.setRightBorderColor((short) 8);
// definimos el numero de filas que contedra nuestro decumento.
encabezado = hoja.createRow((short) 5);
Cencabezado = encabezado.createCell((short) 2);
Cencabezado.setCellValue("Datos Verificados: Partida(" + partida + ")");
hoja.addMergedRegion(new Region(5, (short) 2, 6, (short) 4));
Cencabezado.setCellStyle(esceldaen);
// ********************************************************************************************************************
HSSFRow fila1 = hoja.createRow((short) 2);
HSSFCell ccontrato = fila1.createCell((short) 8);
ccontrato.setCellValue("contrato:");
ccontrato.setCellStyle(esceldain);
HSSFCell rcontrato = fila1.createCell((short) 9);
rcontrato.setCellValue(" ");
rcontrato.setCellStyle(esceldain);
HSSFCell cgenrencia = fila1.createCell((short) 10);
cgenrencia.setCellValue("Gerencia:");
cgenrencia.setCellStyle(esceldain);
HSSFCell rgerencia = fila1.createCell((short) 11);
rgerencia.setCellValue(" ");
rgerencia.setCellStyle(esceldain);
HSSFCell choja = fila1.createCell((short) 12);
choja.setCellValue("Hoja:");
choja.setCellStyle(esceldain);
HSSFCell rhoja = fila1.createCell((short) 13);
rhoja.setCellValue(" ");
rhoja.setCellStyle(esceldain);
// *******************************************************************************************************************************************************
HSSFRow fila2 = hoja.createRow((short) 3);
HSSFCell cnc = fila2.createCell((short) 8);
cnc.setCellValue("N.C. :");
cnc.setCellStyle(esceldain);
HSSFCell rcnc = fila2.createCell((short) 9);
rcnc.setCellValue(" ");
rcnc.setCellStyle(esceldain);
HSSFCell ctipo = fila2.createCell((short) 10);
ctipo.setCellValue("Tipo de obra:");
ctipo.setCellStyle(esceldain);
HSSFCell rtipo = fila2.createCell((short) 11);
rtipo.setCellValue(" ");
rtipo.setCellStyle(esceldain);
HSSFCell cunidad = fila2.createCell((short) 12);
cunidad.setCellValue("Unidad:");
cunidad.setCellStyle(esceldain);
HSSFCell runidad = fila2.createCell((short) 13);
runidad.setCellValue(" ");
runidad.setCellStyle(esceldain);
// *******************************************************************************************************************************************************
HSSFRow fila3 = hoja.createRow((short) 4);
HSSFCell clocalidad = fila3.createCell((short) 8);
clocalidad.setCellValue("localidad:");
clocalidad.setCellStyle(esceldain);
HSSFCell rlocalidad = fila3.createCell((short) 9);
rlocalidad.setCellValue(" ");
rlocalidad.setCellStyle(esceldain);
hoja.addMergedRegion(new Region(4, (short) 9, 4, (short) 11));
rlocalidad.setCellStyle(esceldain);
HSSFCell runo = fila3.createCell((short) 10);
runo.setCellValue(" ");
runo.setCellStyle(esceldain);
HSSFCell rdos = fila3.createCell((short) 11);
rdos.setCellValue(" ");
rdos.setCellStyle(esceldain);
HSSFCell cfecha = fila3.createCell((short) 12);
cfecha.setCellValue("Fecha:");
cfecha.setCellStyle(esceldain);
HSSFCell rfecha = fila3.createCell((short) 13);
rfecha.setCellValue(" ");
rfecha.setCellStyle(esceldain);
// ***************************************************************************************************************************************************************
HSSFRow fila4 = hoja.createRow((short) 5);
HSSFCell ccontratista = fila4.createCell((short) 8);
ccontratista.setCellValue("Contratista:");
ccontratista.setCellStyle(esceldain);
HSSFCell rcontratista = fila4.createCell((short) 9);
rcontratista.setCellValue(" ");
rcontratista.setCellStyle(esceldain);
hoja.addMergedRegion(new Region(5, (short) 9, 5, (short) 13));
rcontratista.setCellStyle(esceldain);
HSSFCell runoc = fila4.createCell((short) 10);
runoc.setCellValue(" ");
runoc.setCellStyle(esceldain);
HSSFCell rdosc = fila4.createCell((short) 11);
rdosc.setCellValue(" ");
rdosc.setCellStyle(esceldain);
HSSFCell rtres = fila4.createCell((short) 12);
rtres.setCellValue(" ");
rtres.setCellStyle(esceldain);
HSSFCell rcuatro = fila4.createCell((short) 13);
rcuatro.setCellValue(" ");
rcuatro.setCellStyle(esceldain);
// ***************************************************************************************************************************************************************
HSSFRow fila5 = hoja.createRow((short) 6);
HSSFCell cperiodo = fila5.createCell((short) 8);
cperiodo.setCellValue("Consultor:");
cperiodo.setCellStyle(esceldain);
HSSFCell rperiodo = fila5.createCell((short) 9);
rperiodo.setCellValue(" ");
rperiodo.setCellStyle(esceldain);
hoja.addMergedRegion(new Region(6, (short) 9, 6, (short) 13));
rperiodo.setCellStyle(esceldain);
HSSFCell runop = fila5.createCell((short) 10);
runop.setCellValue(" ");
runop.setCellStyle(esceldain);
HSSFCell rdosp = fila5.createCell((short) 11);
rdosp.setCellValue(" ");
rdosp.setCellStyle(esceldain);
HSSFCell rtresp = fila5.createCell((short) 12);
rtresp.setCellValue(" ");
rtresp.setCellStyle(esceldain);
HSSFCell rcuatrop = fila5.createCell((short) 13);
rcuatrop.setCellValue(" ");
rcuatrop.setCellStyle(esceldain);
// crear un una columna
HSSFRow row1 = hoja.createRow((short) 7);
// create de las celdas
HSSFCell cc = row1.createCell((short) 2);
HSSFCell cd = row1.createCell((short) 3);
HSSFCell cu = row1.createCell((short) 4);
HSSFCell cx = row1.createCell((short) 5);
HSSFCell cy = row1.createCell((short) 6);
HSSFCell cz = row1.createCell((short) 7);
HSSFCell ca = row1.createCell((short) 8);
HSSFCell cl = row1.createCell((short) 9);
HSSFCell cal = row1.createCell((short) 10);
HSSFCell cca = row1.createCell((short) 11);
HSSFCell cpz = row1.createCell((short) 12);
HSSFCell cim = row1.createCell((short) 13);
// writing data to the cells
cc.setCellValue("Clave");
cc.setCellStyle(esceldat);
cd.setCellValue("Descripción");
cd.setCellStyle(esceldat);
cu.setCellValue("Unidad");
cu.setCellStyle(esceldat);
cx.setCellValue("X");
cx.setCellStyle(esceldat);
cy.setCellValue("Y");
cy.setCellStyle(esceldat);
cz.setCellValue("Z");
cz.setCellStyle(esceldat);
ca.setCellValue("Alto");
ca.setCellStyle(esceldat);
cl.setCellValue("Largo");
cl.setCellStyle(esceldat);
cal.setCellValue("ancho");
cal.setCellStyle(esceldat);
cca.setCellValue("Cantidad");
cca.setCellStyle(esceldat);
cpz.setCellValue("Piezas");
cpz.setCellStyle(esceldat);
cim.setCellValue("Importe");
cim.setCellStyle(esceldat);
// crear un una columna
HSSFRow row = hoja.createRow((short) 8);
// create de las celdas
HSSFCell cc1 = row.createCell((short) 2);
HSSFCell cd1 = row.createCell((short) 3);
HSSFCell cu1 = row.createCell((short) 4);
HSSFCell cx1 = row.createCell((short) 5);
HSSFCell cy1 = row.createCell((short) 6);
HSSFCell cz1 = row.createCell((short) 7);
HSSFCell ca1 = row.createCell((short) 8);
HSSFCell cl1 = row.createCell((short) 9);
HSSFCell cal1 = row.createCell((short) 10);
HSSFCell cca1 = row.createCell((short) 11);
HSSFCell cpz1 = row.createCell((short) 12);
HSSFCell cim1 = row.createCell((short) 13);
cc1.setCellValue(objeto.getClave());
cc1.setCellStyle(esceldain);
cd1.setCellValue(objeto.getDescripcion());
cd1.setCellStyle(esceldain);
cu1.setCellValue(objeto.getUnidad());
cu1.setCellStyle(esceldain);
cx1.setCellValue(objeto.getX());
cx1.setCellStyle(esceldain);
cy1.setCellValue(objeto.getY());
cy1.setCellStyle(esceldain);
cz1.setCellValue(objeto.getZ());
cz1.setCellStyle(esceldain);
ca1.setCellValue(objeto.getAlto());
ca1.setCellStyle(esceldain);
cl1.setCellValue(objeto.getLargo());
cl1.setCellStyle(esceldain);
cal1.setCellValue(objeto.getAncho());
cal1.setCellStyle(esceldain);
cca1.setCellValue(objeto.getCantidad());
cca1.setCellStyle(esceldain);
cpz1.setCellValue(objeto.getPiezas());
cpz1.setCellStyle(esceldain);
cim1.setCellValue(objeto.getImporte());
cim1.setCellStyle(esceldain);
HSSFSheet sheet = libro.getSheetAt(0);
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
// contendor que contiene las imagenes
HSSFClientAnchor anchor;
if (ruta != null) {
File fis = new File(ruta);
anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 3, 11, (short) 5, 24);
anchor.setAnchorType(2);
HSSFPicture imagen = patriarch.createPicture(anchor, Cargarimagen(fis, libro));
}
if (ruta2 != null) {
File fis2 = new File(ruta2);
anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 9, 11, (short) 12, 24);
anchor.setAnchorType(2);
HSSFPicture imagen2 = patriarch.createPicture(anchor, Cargarimagen(fis2, libro));
}
sheet.autoSizeColumn((short) 2);
sheet.autoSizeColumn((short) 3);
sheet.autoSizeColumn((short) 4);
sheet.autoSizeColumn((short) 5);
sheet.autoSizeColumn((short) 6);
sheet.autoSizeColumn((short) 7);
sheet.autoSizeColumn((short) 8);
sheet.autoSizeColumn((short) 9);
sheet.autoSizeColumn((short) 10);
sheet.autoSizeColumn((short) 11);
sheet.autoSizeColumn((short) 12);
sheet.autoSizeColumn((short) 13);
if (siinicial == true) {
// ************************************************************************************
HSSFRow encabezado2 = hoja.createRow((short) 26);
HSSFCell Cencabezado2 = encabezado2.createCell((short) 2);
Cencabezado2.setCellValue("Datos: Partida(" + partida + ")");
hoja.addMergedRegion(new Region(26, (short) 2, 27, (short) 4));
Cencabezado2.setCellStyle(esceldaen);
// crear un una columna
HSSFRow row27 = hoja.createRow((short) 28);
// create de las celdas
HSSFCell cc2 = row27.createCell((short) 2);
HSSFCell cd2 = row27.createCell((short) 3);
HSSFCell cu2 = row27.createCell((short) 4);
HSSFCell cx2 = row27.createCell((short) 5);
HSSFCell cy2 = row27.createCell((short) 6);
HSSFCell cz2 = row27.createCell((short) 7);
HSSFCell ca2 = row27.createCell((short) 8);
HSSFCell cl2 = row27.createCell((short) 9);
HSSFCell cal2 = row27.createCell((short) 10);
HSSFCell cca2 = row27.createCell((short) 11);
HSSFCell cpz2 = row27.createCell((short) 12);
HSSFCell cim2 = row27.createCell((short) 13);
// writing data to the cells
cc2.setCellValue("Clave");
cc2.setCellStyle(esceldat);
cd2.setCellValue("Descripción");
cd2.setCellStyle(esceldat);
cu2.setCellValue("Unidad");
cu2.setCellStyle(esceldat);
cx2.setCellValue("X");
cx2.setCellStyle(esceldat);
cy2.setCellValue("Y");
cy2.setCellStyle(esceldat);
cz2.setCellValue("Z");
cz2.setCellStyle(esceldat);
ca2.setCellValue("Alto");
ca2.setCellStyle(esceldat);
cl2.setCellValue("Largo");
cl2.setCellStyle(esceldat);
cal2.setCellValue("ancho");
cal2.setCellStyle(esceldat);
cca2.setCellValue("Cantidad");
cca2.setCellStyle(esceldat);
cpz2.setCellValue("Piezas");
cpz2.setCellStyle(esceldat);
cim2.setCellValue("Importe");
cim2.setCellStyle(esceldat);
// crear un una columna
HSSFRow row28 = hoja.createRow((short) 29);
// create de las celdas
HSSFCell cc12 = row28.createCell((short) 2);
HSSFCell cd12 = row28.createCell((short) 3);
HSSFCell cu12 = row28.createCell((short) 4);
HSSFCell cx12 = row28.createCell((short) 5);
HSSFCell cy12 = row28.createCell((short) 6);
HSSFCell cz12 = row28.createCell((short) 7);
HSSFCell ca12 = row28.createCell((short) 8);
HSSFCell cl12 = row28.createCell((short) 9);
HSSFCell cal12 = row28.createCell((short) 10);
HSSFCell cca12 = row28.createCell((short) 11);
HSSFCell cpz12 = row28.createCell((short) 12);
HSSFCell cim12 = row28.createCell((short) 13);
cc12.setCellValue(objeto2.getClave());
cc12.setCellStyle(esceldain);
cd12.setCellValue(objeto2.getDescripcion());
cd12.setCellStyle(esceldain);
cu12.setCellValue(objeto2.getUnidad());
cu12.setCellStyle(esceldain);
cx12.setCellValue(objeto2.getX());
cx12.setCellStyle(esceldain);
cy12.setCellValue(objeto2.getY());
cy12.setCellStyle(esceldain);
cz12.setCellValue(objeto2.getZ());
cz12.setCellStyle(esceldain);
ca12.setCellValue(objeto2.getAlto());
ca12.setCellStyle(esceldain);
cl12.setCellValue(objeto2.getLargo());
cl12.setCellStyle(esceldain);
cal12.setCellValue(objeto2.getAncho());
cal12.setCellStyle(esceldain);
cca12.setCellValue(objeto2.getCantidad());
cca12.setCellStyle(esceldain);
cpz12.setCellValue(objeto2.getPiezas());
cpz12.setCellStyle(esceldain);
cim12.setCellValue(objeto2.getImporte());
cim12.setCellStyle(esceldain);
float uno = 0, dos = 0, resultado = 0;
uno = Float.parseFloat(objeto.getImporte());
dos = Float.parseFloat(objeto2.getImporte());
if (uno > dos) {
HSSFRow row31 = hoja.createRow((short) 33);
// create de las celdas
HSSFCell cc131 = row31.createCell((short) 3);
resultado = uno - dos;
cc131.setCellValue("Execedente: " + String.valueOf(resultado));
cc131.setCellStyle(esceldain);
} else {
if (dos > uno) {
HSSFRow row31 = hoja.createRow((short) 33);
// create de las celdas
HSSFCell cc131 = row31.createCell((short) 3);
resultado = dos - uno;
cc131.setCellValue("Restante: " + String.valueOf(resultado));
cc131.setCellStyle(esceldain);
}
}
// ****************************************************************************************
} else {
HSSFRow encabezado2 = hoja.createRow((short) 26);
HSSFCell Cencabezado2 = encabezado2.createCell((short) 2);
Cencabezado2.setCellValue(
"El aspecto:"
+ objeto.getDescripcion()
+ " no esta comtemplado en la estimacion inicial");
hoja.addMergedRegion(new Region(26, (short) 2, 27, (short) 4));
Cencabezado2.setCellStyle(esceldaen);
HSSFRow row28 = hoja.createRow((short) 29);
// create de las celdas
HSSFCell cc12 = row28.createCell((short) 3);
cc12.setCellValue("Execedente: " + objeto.getImporte());
cc12.setCellStyle(esceldain);
}
try {
FileOutputStream elFichero = new FileOutputStream(FILE);
libro.write(elFichero);
elFichero.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* Generating excel sheet
*
* @param mapping
* @param form
* @param request
* @param response
* @return
* @throws Exception
*/
@SuppressWarnings("all")
public ActionForward createExcel(
ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response)
throws Exception {
String status = null;
UserDetails userDetails = (UserDetails) request.getSession(false).getAttribute("userDetails");
// Store user details in TransactionContext object
new TransactionContext(userDetails);
AshaLogger.logInfo(logger, this.getClass().getName(), "createExcel", "executing");
// Arraylist al=(ArrayList)request.getAttribute("hidden_al");
Date d1 = new Date();
Format formatter = new SimpleDateFormat("dd-MMM-yy");
// System.out.println( "reformatted = " + sdf.format(d1) );
String count1 = request.getParameter("count");
Integer count = Integer.parseInt(count1);
String type = request.getParameter("type");
if (type.indexOf(' ') != -1) {
type = type.substring(0, type.indexOf(' '));
}
FileInputStream fstream = null;
DataInputStream in = null;
BufferedReader br = null;
String strLine = null;
String pathContext = null;
HSSFWorkbook workbook = null;
HSSFSheet firstSheet = null;
HSSFRow row = null;
HSSFCell cell = null;
StringTokenizer st = null;
Properties props = null;
HSSFCellStyle style = null;
HSSFFont font = null;
HSSFCellStyle style1 = null;
HSSFFont font1 = null;
workbook = new HSSFWorkbook();
// Heading Style
style = workbook.createCellStyle();
// style.setBorderTop((short) 6); // double lines border
// style.setBorderBottom((short) 1); // single line border
// style.setFillBackgroundColor(HSSFColor.BLACK.index);
// style.setFillForegroundColor(HSSFColor.BLACK.index);
font = workbook.createFont();
font.setFontName(HSSFFont.FONT_ARIAL);
font.setFontHeightInPoints((short) 11);
// font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setColor(HSSFColor.BLUE.index);
style.setFont(font);
// Data Style
style1 = workbook.createCellStyle();
// style.setBorderTop((short) 6); // double lines border
// style.setBorderBottom((short) 1); // single line border
// style.setFillBackgroundColor(HSSFColor.BLACK.index);
// style.setFillForegroundColor(HSSFColor.BLACK.index);
font1 = workbook.createFont();
font1.setFontName(HSSFFont.FONT_ARIAL);
font1.setFontHeightInPoints((short) 11);
// font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font1.setColor(HSSFColor.BLACK.index);
style1.setFont(font1);
int count2 = 1;
if (type.equalsIgnoreCase("advances")) {
try {
firstSheet = workbook.createSheet("Today");
row = firstSheet.createRow(0);
row.setHeight((short) 300);
cell = row.createCell(0);
cell.setCellStyle(style);
cell.setCellValue(new HSSFRichTextString("Date"));
cell = row.createCell(1);
cell.setCellStyle(style);
cell.setCellValue(new HSSFRichTextString("PID"));
cell = row.createCell(2);
cell.setCellStyle(style);
cell.setCellValue(new HSSFRichTextString("Patient Name"));
cell = row.createCell(3);
cell.setCellStyle(style);
cell.setCellValue(new HSSFRichTextString("Bill No"));
cell = row.createCell(4);
cell.setCellStyle(style);
cell.setCellValue(new HSSFRichTextString("Amount"));
for (int i = 1; i < count; i++) {
row = firstSheet.createRow(count2);
row.setHeight((short) 300);
cell = row.createCell(0);
cell.setCellStyle(style1);
try {
cell.setCellValue(new HSSFRichTextString(request.getParameter("value1" + i)));
} catch (NoSuchElementException nsee) {
cell.setCellValue(new HSSFRichTextString(""));
}
cell = row.createCell(1);
cell.setCellStyle(style1);
try {
cell.setCellValue(new HSSFRichTextString(request.getParameter("value2" + i)));
} catch (NoSuchElementException nsee) {
cell.setCellValue(new HSSFRichTextString(""));
}
cell = row.createCell(2);
cell.setCellStyle(style1);
try {
cell.setCellValue(new HSSFRichTextString(request.getParameter("value3" + i)));
} catch (NoSuchElementException nsee) {
cell.setCellValue(new HSSFRichTextString(""));
}
cell = row.createCell(3);
cell.setCellStyle(style1);
try {
cell.setCellValue(new HSSFRichTextString(request.getParameter("value4" + i)));
} catch (NoSuchElementException nsee) {
cell.setCellValue(new HSSFRichTextString(""));
}
cell = row.createCell(4);
cell.setCellStyle(style1);
try {
cell.setCellValue(new HSSFRichTextString(request.getParameter("value5" + i)));
} catch (NoSuchElementException nsee) {
cell.setCellValue(new HSSFRichTextString(""));
}
count2++;
}
} catch (Exception e) {
logger.info("e");
}
} else if (type.equalsIgnoreCase("discount(-)")) {
try {
firstSheet = workbook.createSheet("Today");
row = firstSheet.createRow(0);
row.setHeight((short) 300);
cell = row.createCell(0);
cell.setCellStyle(style);
cell.setCellValue(new HSSFRichTextString("BillNo"));
cell = row.createCell(1);
cell.setCellStyle(style);
cell.setCellValue(new HSSFRichTextString("Patient Name"));
cell = row.createCell(2);
cell.setCellStyle(style);
cell.setCellValue(new HSSFRichTextString("Billable Amount"));
cell = row.createCell(3);
cell.setCellStyle(style);
cell.setCellValue(new HSSFRichTextString("Discount"));
/*cell = row.createCell(4);
cell.setCellStyle(style);
cell.setCellValue(new HSSFRichTextString("Amount"));*/
for (int i = 1; i < count; i++) {
row = firstSheet.createRow(count2);
row.setHeight((short) 300);
cell = row.createCell(0);
cell.setCellStyle(style1);
try {
cell.setCellValue(new HSSFRichTextString(request.getParameter("value1" + i)));
} catch (NoSuchElementException nsee) {
cell.setCellValue(new HSSFRichTextString(""));
}
cell = row.createCell(1);
cell.setCellStyle(style1);
try {
cell.setCellValue(new HSSFRichTextString(request.getParameter("value2" + i)));
} catch (NoSuchElementException nsee) {
cell.setCellValue(new HSSFRichTextString(""));
}
cell = row.createCell(2);
cell.setCellStyle(style1);
try {
cell.setCellValue(new HSSFRichTextString(request.getParameter("value3" + i)));
} catch (NoSuchElementException nsee) {
cell.setCellValue(new HSSFRichTextString(""));
}
cell = row.createCell(3);
cell.setCellStyle(style1);
try {
cell.setCellValue(new HSSFRichTextString(request.getParameter("value4" + i)));
} catch (NoSuchElementException nsee) {
cell.setCellValue(new HSSFRichTextString(""));
}
/*cell = row.createCell(4);
cell.setCellStyle(style1);
try {
cell.setCellValue(new HSSFRichTextString(request
.getParameter("value5" + i)));
} catch (NoSuchElementException nsee) {
cell.setCellValue(new HSSFRichTextString(""));
}*/
count2++;
}
} catch (Exception e) {
logger.info("e");
}
}
// this is for pharmacy details
else if (type.equalsIgnoreCase("pharmacy")) {
try {
firstSheet = workbook.createSheet("Today");
row = firstSheet.createRow(0);
row.setHeight((short) 300);
cell = row.createCell(0);
cell.setCellStyle(style);
cell.setCellValue(new HSSFRichTextString("Name"));
cell = row.createCell(1);
cell.setCellStyle(style);
cell.setCellValue(new HSSFRichTextString("Mobile"));
cell = row.createCell(2);
cell.setCellStyle(style);
cell.setCellValue(new HSSFRichTextString("Bill No"));
cell = row.createCell(3);
cell.setCellStyle(style);
cell.setCellValue(new HSSFRichTextString("Discount"));
cell = row.createCell(4);
cell.setCellStyle(style);
cell.setCellValue(new HSSFRichTextString("Billable Amount"));
/*cell = row.createCell(4);
cell.setCellStyle(style);
cell.setCellValue(new HSSFRichTextString("Amount"));*/
for (int i = 1; i < count; i++) {
row = firstSheet.createRow(count2);
row.setHeight((short) 300);
cell = row.createCell(0);
cell.setCellStyle(style1);
try {
cell.setCellValue(new HSSFRichTextString(request.getParameter("value1" + i)));
} catch (NoSuchElementException nsee) {
cell.setCellValue(new HSSFRichTextString(""));
}
cell = row.createCell(1);
cell.setCellStyle(style1);
try {
cell.setCellValue(new HSSFRichTextString(request.getParameter("value2" + i)));
} catch (NoSuchElementException nsee) {
cell.setCellValue(new HSSFRichTextString(""));
}
cell = row.createCell(2);
cell.setCellStyle(style1);
try {
cell.setCellValue(new HSSFRichTextString(request.getParameter("value3" + i)));
} catch (NoSuchElementException nsee) {
cell.setCellValue(new HSSFRichTextString(""));
}
cell = row.createCell(3);
cell.setCellStyle(style1);
try {
cell.setCellValue(new HSSFRichTextString(request.getParameter("value4" + i)));
} catch (NoSuchElementException nsee) {
cell.setCellValue(new HSSFRichTextString(""));
}
cell = row.createCell(4);
cell.setCellStyle(style1);
try {
cell.setCellValue(new HSSFRichTextString(request.getParameter("value5" + i)));
} catch (NoSuchElementException nsee) {
cell.setCellValue(new HSSFRichTextString(""));
}
/*cell = row.createCell(4);
cell.setCellStyle(style1);
try {
cell.setCellValue(new HSSFRichTextString(request
.getParameter("value5" + i)));
} catch (NoSuchElementException nsee) {
cell.setCellValue(new HSSFRichTextString(""));
}*/
count2++;
}
} catch (Exception e) {
logger.info("e");
}
} else if (type.equalsIgnoreCase("refund(-)")) {
try {
firstSheet = workbook.createSheet("Today");
row = firstSheet.createRow(0);
row.setHeight((short) 300);
cell = row.createCell(0);
cell.setCellStyle(style);
cell.setCellValue(new HSSFRichTextString("Patient Name"));
cell = row.createCell(1);
cell.setCellStyle(style);
cell.setCellValue(new HSSFRichTextString("Mobile"));
cell = row.createCell(2);
cell.setCellStyle(style);
cell.setCellValue(new HSSFRichTextString("Bill No."));
cell = row.createCell(3);
cell.setCellStyle(style);
cell.setCellValue(new HSSFRichTextString("Discount"));
cell = row.createCell(4);
cell.setCellStyle(style);
cell.setCellValue(new HSSFRichTextString("Billable"));
/*cell = row.createCell(4);
cell.setCellStyle(style);
cell.setCellValue(new HSSFRichTextString("Amount"));*/
for (int i = 1; i < count; i++) {
row = firstSheet.createRow(count2);
row.setHeight((short) 300);
cell = row.createCell(0);
cell.setCellStyle(style1);
try {
cell.setCellValue(new HSSFRichTextString(request.getParameter("value1" + i)));
} catch (NoSuchElementException nsee) {
cell.setCellValue(new HSSFRichTextString(""));
}
cell = row.createCell(1);
cell.setCellStyle(style1);
try {
cell.setCellValue(new HSSFRichTextString(request.getParameter("value2" + i)));
} catch (NoSuchElementException nsee) {
cell.setCellValue(new HSSFRichTextString(""));
}
cell = row.createCell(2);
cell.setCellStyle(style1);
try {
cell.setCellValue(new HSSFRichTextString(request.getParameter("value3" + i)));
} catch (NoSuchElementException nsee) {
cell.setCellValue(new HSSFRichTextString(""));
}
cell = row.createCell(3);
cell.setCellStyle(style1);
try {
cell.setCellValue(new HSSFRichTextString(request.getParameter("value4" + i)));
} catch (NoSuchElementException nsee) {
cell.setCellValue(new HSSFRichTextString(""));
}
cell = row.createCell(4);
cell.setCellStyle(style1);
try {
cell.setCellValue(new HSSFRichTextString(request.getParameter("value5" + i)));
} catch (NoSuchElementException nsee) {
cell.setCellValue(new HSSFRichTextString(""));
}
count2++;
}
} catch (Exception e) {
logger.info("e");
}
} else if (type.equalsIgnoreCase("unrealized")) {
try {
firstSheet = workbook.createSheet("Today");
row = firstSheet.createRow(0);
row.setHeight((short) 300);
cell = row.createCell(0);
cell.setCellStyle(style);
cell.setCellValue(new HSSFRichTextString("Date"));
cell = row.createCell(1);
cell.setCellStyle(style);
cell.setCellValue(new HSSFRichTextString("PID"));
cell = row.createCell(2);
cell.setCellStyle(style);
cell.setCellValue(new HSSFRichTextString("Patient Name"));
cell = row.createCell(3);
cell.setCellStyle(style);
cell.setCellValue(new HSSFRichTextString("Bill No"));
cell = row.createCell(4);
cell.setCellStyle(style);
cell.setCellValue(new HSSFRichTextString("Amount"));
/*cell = row.createCell(4);
cell.setCellStyle(style);
cell.setCellValue(new HSSFRichTextString("Amount"));*/
for (int i = 1; i < count; i++) {
row = firstSheet.createRow(count2);
row.setHeight((short) 300);
cell = row.createCell(0);
cell.setCellStyle(style1);
try {
cell.setCellValue(new HSSFRichTextString(request.getParameter("value1" + i)));
} catch (NoSuchElementException nsee) {
cell.setCellValue(new HSSFRichTextString(""));
}
cell = row.createCell(1);
cell.setCellStyle(style1);
try {
cell.setCellValue(new HSSFRichTextString(request.getParameter("value2" + i)));
} catch (NoSuchElementException nsee) {
cell.setCellValue(new HSSFRichTextString(""));
}
cell = row.createCell(2);
cell.setCellStyle(style1);
try {
cell.setCellValue(new HSSFRichTextString(request.getParameter("value3" + i)));
} catch (NoSuchElementException nsee) {
cell.setCellValue(new HSSFRichTextString(""));
}
cell = row.createCell(3);
cell.setCellStyle(style1);
try {
cell.setCellValue(new HSSFRichTextString(request.getParameter("value4" + i)));
} catch (NoSuchElementException nsee) {
cell.setCellValue(new HSSFRichTextString(""));
}
cell = row.createCell(4);
cell.setCellStyle(style1);
try {
cell.setCellValue(new HSSFRichTextString(request.getParameter("value5" + i)));
} catch (NoSuchElementException nsee) {
cell.setCellValue(new HSSFRichTextString(""));
}
/*cell = row.createCell(4);
cell.setCellStyle(style1);
try {
cell.setCellValue(new HSSFRichTextString(request
.getParameter("value5" + i)));
} catch (NoSuchElementException nsee) {
cell.setCellValue(new HSSFRichTextString(""));
}*/
count2++;
}
} catch (Exception e) {
logger.info("e");
}
} else {
try {
firstSheet = workbook.createSheet("Today");
row = firstSheet.createRow(0);
row.setHeight((short) 300);
cell = row.createCell(0);
cell.setCellStyle(style);
cell.setCellValue(new HSSFRichTextString("Category"));
cell = row.createCell(1);
cell.setCellStyle(style);
cell.setCellValue(new HSSFRichTextString("PID"));
cell = row.createCell(2);
cell.setCellStyle(style);
cell.setCellValue(new HSSFRichTextString("Patient Name"));
cell = row.createCell(3);
cell.setCellStyle(style);
cell.setCellValue(new HSSFRichTextString("Bill No"));
cell = row.createCell(4);
cell.setCellStyle(style);
cell.setCellValue(new HSSFRichTextString("Amount"));
for (int i = 1; i < count; i++) {
row = firstSheet.createRow(count2);
row.setHeight((short) 300);
cell = row.createCell(0);
cell.setCellStyle(style1);
try {
cell.setCellValue(new HSSFRichTextString(request.getParameter("value1" + i)));
} catch (NoSuchElementException nsee) {
cell.setCellValue(new HSSFRichTextString(""));
}
cell = row.createCell(1);
cell.setCellStyle(style1);
try {
cell.setCellValue(new HSSFRichTextString(request.getParameter("value2" + i)));
} catch (NoSuchElementException nsee) {
cell.setCellValue(new HSSFRichTextString(""));
}
cell = row.createCell(2);
cell.setCellStyle(style1);
try {
cell.setCellValue(new HSSFRichTextString(request.getParameter("value3" + i)));
} catch (NoSuchElementException nsee) {
cell.setCellValue(new HSSFRichTextString(""));
}
cell = row.createCell(3);
cell.setCellStyle(style1);
try {
cell.setCellValue(new HSSFRichTextString(request.getParameter("value4" + i)));
} catch (NoSuchElementException nsee) {
cell.setCellValue(new HSSFRichTextString(""));
}
cell = row.createCell(4);
cell.setCellStyle(style1);
try {
cell.setCellValue(new HSSFRichTextString(request.getParameter("value5" + i)));
} catch (NoSuchElementException nsee) {
cell.setCellValue(new HSSFRichTextString(""));
}
count2++;
}
} catch (Exception e) {
logger.info("e");
}
}
// String filename = "E:\\ItemIssueDetails"+"_"+sdf.format(d1)+".xls";
firstSheet.autoSizeColumn((short) 0);
firstSheet.autoSizeColumn((short) 1);
firstSheet.autoSizeColumn((short) 2);
firstSheet.autoSizeColumn((short) 3);
firstSheet.autoSizeColumn((short) 4);
try {
logger.info("type in create excel is " + type);
response.setContentType("x-application/vnd.ms-excel");
response.setHeader("Pragma", "no-cache");
response.setHeader(
"Content-Disposition",
"attachment;filename=" + type.trim() + "_" + formatter.format(d1) + ".xls");
ServletOutputStream outstream = response.getOutputStream();
workbook.write(outstream);
// outstream.write(workbook.getBytes());
} catch (IOException e) {
AshaLogger.logException(
logger, this.getClass().getName(), "createExcel", "Exception Raised:", e);
} finally {
fstream = null;
in = null;
br = null;
strLine = null;
pathContext = null;
workbook = null;
firstSheet = null;
row = null;
cell = null;
st = null;
props = null;
style = null;
font = null;
style1 = null;
font1 = null;
}
request.setAttribute("status", "saved");
return mapping.findForward(null);
}
private void initNormalFont() {
normalFont = wb.createFont();
normalFont.setFontName("Arial");
normalFont.setFontHeightInPoints((short) 8);
}
public static boolean writeArray2ExcelFile1(
String FileName, String[] titleNames, List<String[]> arry) throws Exception {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet1 = wb.createSheet("Data");
int columnNum = titleNames.length;
int rowNum = arry.size();
// for (int i = 0; i < columnNum; i++) {
// sheet1.setColumnWidth( i, (short) ((30 * 8) / ((double) 1 / 20)));
// }
HSSFCellStyle headStyle = wb.createCellStyle();
// apply custom headFont to the text in the comment
HSSFFont headFont = wb.createFont();
headFont.setFontName("Courier New");
headFont.setFontHeightInPoints((short) 10);
headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headFont.setColor(HSSFColor.BLACK.index);
headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
headStyle.setFillForegroundColor(HSSFColor.BLACK.index);
headStyle.setLocked(true);
headStyle.setFont(headFont);
headStyle.setBorderTop((short) 2);
headStyle.setBorderBottom((short) 1);
HSSFCellStyle contentStyle = wb.createCellStyle();
// apply custom headFont to the text in the comment
HSSFFont contentFont = wb.createFont();
contentFont.setFontName("Courier New");
contentFont.setFontHeightInPoints((short) 9);
// headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
contentFont.setColor(HSSFColor.BLACK.index);
contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
contentStyle.setFillForegroundColor(HSSFColor.BLACK.index);
contentStyle.setFont(contentFont);
// create titile row
HSSFRow row = sheet1.createRow(0);
int heandLine = 0;
HSSFCell cell = null;
if (titleNames != null) {
for (int i = 0; i < columnNum; i++) {
cell = row.createCell(i);
cell.setCellValue(titleNames[i]);
cell.setCellStyle(headStyle);
}
heandLine++;
}
for (int i = 0; i < rowNum; i++) {
row = sheet1.createRow((i + heandLine));
String[] line = (String[]) arry.get(i);
for (int j = 0; j < columnNum; j++) {
cell = row.createCell(j);
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(".");
}
}
}
// Write the output to a inFile
FileOutputStream fileOut = new FileOutputStream(FileName);
wb.write(fileOut);
fileOut.close();
return true;
}
@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++;
}
}
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 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;
}
// 导出报表到 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();
*/
}