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
* @return void
*/
private void generateColumn(
HSSFSheet sheet,
TableColumn tc,
int maxlevel,
int rownum,
int colnum,
HSSFCellStyle headerstyle) {
HSSFRow row = sheet.getRow(rownum);
if (row == null) row = sheet.createRow(rownum);
HSSFCell cell = row.createCell(colnum);
cell.setCellValue(tc.getDisplay());
if (headerstyle != null) cell.setCellStyle(headerstyle);
if (tc.isComplex()) {
CellRangeAddress address =
new CellRangeAddress(rownum, rownum, colnum, colnum + tc.getLength() - 1);
sheet.addMergedRegion(address);
fillMergedRegion(sheet, address, headerstyle);
int cn = colnum;
for (int i = 0; i < tc.getChildren().size(); i++) {
if (i != 0) {
cn = cn + tc.getChildren().get(i - 1).getLength();
}
generateColumn(sheet, tc.getChildren().get(i), maxlevel, rownum + 1, cn, headerstyle);
}
} else {
CellRangeAddress address =
new CellRangeAddress(rownum, rownum + maxlevel - tc.level, colnum, colnum);
sheet.addMergedRegion(address);
fillMergedRegion(sheet, address, headerstyle);
}
sheet.autoSizeColumn(colnum, true);
}
/**
* Builds the report title and the date header
*
* @param worksheet
* @param startRowIndex starting row offset
* @param startColIndex starting column offset
*/
public static void buildTitle(HSSFSheet worksheet, int startRowIndex, int startColIndex) {
// Create font style for the report title
Font fontTitle = worksheet.getWorkbook().createFont();
fontTitle.setBoldweight(Font.BOLDWEIGHT_BOLD);
fontTitle.setFontHeight((short) 280);
// Create cell style for the report title
HSSFCellStyle cellStyleTitle = worksheet.getWorkbook().createCellStyle();
cellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER);
cellStyleTitle.setWrapText(true);
cellStyleTitle.setFont(fontTitle);
// Create report title
HSSFRow rowTitle = worksheet.createRow((short) startRowIndex);
rowTitle.setHeight((short) 500);
HSSFCell cellTitle = rowTitle.createCell(startColIndex);
cellTitle.setCellValue("Compensation Report");
cellTitle.setCellStyle(cellStyleTitle);
// Create merged region for the report title
worksheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 14));
// Create date header
HSSFRow dateTitle = worksheet.createRow((short) startRowIndex + 1);
HSSFCell cellDate = dateTitle.createCell(startColIndex);
cellDate.setCellValue("This report was generated at " + new Date());
}
private static void buildTitle(HSSFSheet worksheet, int startRowIndex, int startColIndex) {
// 报表标题字体
Font fontTitle = worksheet.getWorkbook().createFont();
fontTitle.setBoldweight((short) Font.BOLDWEIGHT_BOLD);
fontTitle.setFontHeight((short) 280);
// 标题单元格格式
HSSFCellStyle cellStyleTitle = worksheet.getWorkbook().createCellStyle();
cellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER);
cellStyleTitle.setWrapText(true);
cellStyleTitle.setFont(fontTitle);
HSSFRow rowTitle = worksheet.createRow((short) startRowIndex);
rowTitle.setHeight((short) 500);
HSSFCell cellTitle = rowTitle.createCell(startColIndex);
cellTitle.setCellValue("学生列表");
cellTitle.setCellStyle(cellStyleTitle);
worksheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2)); // 标题合并列
Date date = new Date();
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
HSSFRow dateTitle = worksheet.createRow((short) startRowIndex + 1);
HSSFCell cellDate = dateTitle.createCell(startColIndex);
cellDate.setCellValue("这个报表创建于: " + dateFormat.format(date));
}
private void putGroupCell(HSSFSheet sheet, int rowIndex, String name) {
cell = sheet.getRow(rowIndex).createCell(1);
cell.setCellValue(name);
CellRangeAddress region = new CellRangeAddress(rowIndex, rowIndex, 1, data.getNumbOfCols());
sheet.addMergedRegion(utils.getBorderedRegion(region, sheet, wb));
cell.setCellStyle(utils.getGroupStyle(wb));
}
public void end() {
// 处理组合单元格
if (!mergeCells.isEmpty()) {
Region[] regs = new Region[mergeCells.size()];
mergeCells.values().toArray(regs);
int len = regs.length;
for (int i = 0; i < len; i++) m_sheet.addMergedRegion(regs[i]);
}
try {
flushToFile();
} catch (Exception e) {
m_spool.setErrorMsg(e.getMessage());
} finally {
if (out != null)
try {
out.close();
} catch (IOException e) {
Logger.error(e.getMessage(), e);
}
}
StringBuffer msg = new StringBuffer();
// 最后给出完整的提示
if (sheetCounter > 1) {
String filename =
m_filepath.substring(m_filepath.lastIndexOf(System.getProperty("file.separator")) + 1);
msg.append(
nc.ui.ml.NCLangRes.getInstance()
.getStrByID(
"10100108", "UPP10100108-000856") /*@res "\n您要导出的内容太多，为减小内存的使用量，它们被存储到目录 \n"*/);
msg.append(
m_filepath.substring(0, m_filepath.lastIndexOf(System.getProperty("file.separator"))));
msg.append(
nc.ui.ml.NCLangRes.getInstance()
.getStrByID("10100108", "UPP10100108-000857") /*@res " 下的多个Excel文件中，分别是：\n"*/);
msg.append(filename).append(",");
for (int i = 2; i <= sheetCounter; i++) {
if (i != sheetCounter) {
msg.append(filename.substring(0, filename.indexOf('.')) + "-" + i + ".xls").append(",");
} else {
msg.append(filename.substring(0, filename.indexOf('.')) + "-" + i + ".xls");
}
}
} else {
msg.append(
nc.ui.ml.NCLangRes.getInstance()
.getStrByID("10100108", "UPP10100108-000858") /*@res "导出内容被存放在文件 \n"*/
+ m_filepath
+ nc.ui.ml.NCLangRes.getInstance()
.getStrByID("10100108", "UPP10100108-000859") /*@res " 中."*/);
}
m_spool.setMsg(msg.toString());
mergeCells = new Hashtable(); // 存放所有组合单元格
styles = new Hashtable();
}
public void run() {
int pagesize = PAGESIZE;
try {
while (!m_spool.isHasError() && !m_spool.isEmpty()) {
if (linecounter >= pagesize) {
// 处理组合单元格
if (!mergeCells.isEmpty()) {
Region[] regs = new Region[mergeCells.size()];
mergeCells.values().toArray(regs);
int len = regs.length;
for (int i = 0; i < len; i++) m_sheet.addMergedRegion(regs[i]);
}
try {
flushToFile();
} catch (Exception e) {
Logger.error(e.getMessage(), e);
m_spool.setErrorMsg(e.getMessage());
} finally {
if (out != null)
try {
out.close();
} catch (IOException e) {
Logger.error(e.getMessage(), e);
}
}
m_workbook = new HSSFWorkbook(); // 创建工作薄
m_sheet = m_workbook.createSheet(); // 创建表单
mergeCells = new Hashtable(); // 存放所有组合单元格
styles = new Hashtable();
linecounter = 0;
sheetCounter++;
}
PrintCellData[] data = null;
data = (PrintCellData[]) m_spool.retrieve();
linecounter = linecounter + data.length; // 其实为单元格数目，非行数
fileUtil.exportSheet2(m_templateData, data, m_sheet, m_workbook, styles, mergeCells);
/* 每往Excel写入一行，进度条+1，已导出的行数+1 */
m_spool.setExportedLineCount(m_spool.getExportedLineCount() + 1);
stepProgressBar(1);
setProgressBarHintMsg();
}
} catch (Exception e) {
Logger.error(e.getMessage(), e);
m_spool.setErrorMsg(e.getMessage());
} catch (OutOfMemoryError e) {
Logger.error(e.getMessage(), e);
m_spool.setErrorMsg(
nc.ui.ml.NCLangRes.getInstance()
.getStrByID(
"10100108", "UPP10100108-000860") /*@res "内存不足，请修改Java Plugin参数，将内存调整大一些"*/);
}
}
private static void populateWorkbook(NodeList sheetList, Map cellStyles, HSSFWorkbook workbook)
throws ExcelTransformerException {
if (LOG.isLoggable(Level.FINE)) {
LOG.entering(
SimpleExcelRenderer.class.getName(),
"populateWorkbook",
String.valueOf(sheetList.getLength()));
}
for (int k = 0; k < sheetList.getLength(); k++) {
Element sheet = (Element) sheetList.item(k);
NodeList rowList = sheet.getElementsByTagName("row");
if (rowList.getLength() > 0) {
HSSFSheet hSheet = workbook.createSheet(sheet.getAttribute("name"));
for (int i = 0; i < rowList.getLength(); i++) {
Element row = (Element) rowList.item(i);
HSSFRow hRow = hSheet.createRow(i);
short cellCounter = 0;
NodeList cells = row.getChildNodes();
for (short j = 0; j < cells.getLength(); j++) {
Node cell = cells.item(j);
if (cell.getNodeType() == Node.ELEMENT_NODE && cell.getNodeName().equals("cell")) {
Element cellE = (Element) cell;
String value = XmlUtils.getElementText(cellE);
String style = cellE.getAttribute("style");
String colSpan = cellE.getAttribute("colspan");
int colSpanI = 0;
if (Utils.hasContent(colSpan)) {
colSpanI = Integer.parseInt(colSpan);
hSheet.addMergedRegion(
new Region(i, cellCounter, i, (short) (cellCounter + colSpanI - 1)));
}
HSSFCell hCell = hRow.createCell(cellCounter);
if (Utils.hasContent(style))
hCell.setCellStyle((HSSFCellStyle) cellStyles.get(style));
hCell.setCellValue(value);
cellCounter++;
}
}
}
}
}
if (LOG.isLoggable(Level.FINE)) {
LOG.exiting(SimpleExcelRenderer.class.getName(), "populateWorkbook");
}
}
private void putBasicInfo(
HSSFSheet sheet, int rowIndex, String key, Object value, int numbOfCols) {
row = sheet.createRow(rowIndex);
row.setHeightInPoints(defHeight);
// key
cell = utils.putHeader(row, 1, key);
cell.getCellStyle().setAlignment(CellStyle.ALIGN_RIGHT);
// value
utils.putBorderedBasicCell(sheet, rowIndex, 2, value);
region = new CellRangeAddress(rowIndex, rowIndex, 2, numbOfCols);
sheet.addMergedRegion(utils.getBorderedRegion(region, sheet, wb));
}
public void merge(
int startRowIndex,
int endRowIndex,
int startColumnIndex,
int endColumnIndex,
boolean center) {
if (startRowIndex <= endRowIndex && startColumnIndex <= endColumnIndex) {
sheet.addMergedRegion(
new CellRangeAddress(startRowIndex, endRowIndex, startColumnIndex, endColumnIndex));
if (center) {
CellStyle cellStyle = createCellStyle();
HSSFCell cell = getCell(startRowIndex, startColumnIndex);
cellStyle.cloneStyleFrom(cell.getCellStyle());
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
cell.setCellStyle(cellStyle);
}
}
}
/**
* @param
* @return void
*/
private void stopGrouping(
HSSFSheet sheet,
HashMap<Integer, String> word,
HashMap<Integer, Integer> counter,
int i,
int size,
int rownum,
HSSFCellStyle style) {
String w = word.get(i);
if (w != null) {
int len = counter.get(i);
CellRangeAddress address = new CellRangeAddress(rownum - len, rownum - 1, i, i);
sheet.addMergedRegion(address);
fillMergedRegion(sheet, address, style);
word.remove(i);
counter.remove(i);
}
if (i + 1 < size) {
stopGrouping(sheet, word, counter, i + 1, size, rownum, style);
}
}
/**
* 添加临时表格内容
*
* @param mainSheet -- 原表单内容
* @param subSheet -- 临时表单内容
* @return
*/
public static HSSFSheet appendSheet(HSSFSheet mainSheet, HSSFSheet subSheet) {
if (mainSheet == null || subSheet == null) return null;
// 判断报表是否允许输出
if (!isAllowOut(mainSheet)) return mainSheet;
// 原报表的最后一行
int endRowNum = mainSheet.getPhysicalNumberOfRows();
HSSFRow sourow = null, descrow = null;
HSSFCell sourcell = null, descell = null, orgcell = null;
int i = 0, offsetcnt = 0;
// 复制表格中的图片
copySheetImage(mainSheet.getWorkbook(), subSheet.getWorkbook());
// 设置以合并的单元格
CellRangeAddress range = null;
int mergedNum = subSheet.getNumMergedRegions();
for (i = 0; i < mergedNum; i++) {
range = subSheet.getMergedRegion(i);
range.setFirstRow(range.getFirstRow() + endRowNum);
range.setLastRow(range.getLastRow() + endRowNum);
mainSheet.addMergedRegion(range);
}
range = null;
// int k = 0;
// 设置相关参数
mainSheet.setAlternativeExpression(subSheet.getAlternateExpression());
mainSheet.setAlternativeFormula(subSheet.getAlternateFormula());
mainSheet.setAutobreaks(subSheet.getAutobreaks());
mainSheet.setDialog(subSheet.getDialog());
mainSheet.setDisplayGuts(subSheet.getDisplayGuts());
mainSheet.setFitToPage(subSheet.getFitToPage());
for (java.util.Iterator<Row> iterow = subSheet.rowIterator(); iterow.hasNext(); ) {
sourow = (HSSFRow) iterow.next();
offsetcnt = sourow.getRowNum() + endRowNum;
descrow = mainSheet.createRow(offsetcnt);
descrow.setHeight(sourow.getHeight());
descrow.setHeightInPoints(sourow.getHeightInPoints());
java.util.Iterator<Cell> iter = sourow.cellIterator();
while (iter.hasNext()) {
sourcell = (HSSFCell) iter.next();
int column = sourcell.getColumnIndex();
descell = descrow.createCell(column);
// 取模板中的单元格，与来源表单位置相同
int row = sourcell.getRowIndex();
orgcell = mainSheet.getRow(row).getCell(column);
if (orgcell != null) {
// 取模板中的类型赋值
descell.setCellType(orgcell.getCellType());
// 取模板中的样式赋值
descell.setCellStyle(orgcell.getCellStyle());
} else {
_log.showWarn("module xls [{0}, {1}] cell is null!", row, column);
}
if (sourcell.getCellType() == HSSFCell.CELL_TYPE_STRING)
descell.setCellValue(sourcell.getStringCellValue());
else if (sourcell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
descell.setCellValue(sourcell.getNumericCellValue());
else if (sourcell.getCellType() == HSSFCell.CELL_TYPE_BLANK) ;
}
sourow = null;
sourcell = null;
descrow = null;
orgcell = null;
}
return mainSheet;
}
@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();
}
}
// 导出报表到 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();
*/
}
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();
}
}
public void merge(int startRowIndex, int endRowIndex, int startColumnIndex, int endColumnIndex) {
if (startRowIndex <= endRowIndex && startColumnIndex <= endColumnIndex) {
sheet.addMergedRegion(
new CellRangeAddress(startRowIndex, endRowIndex, startColumnIndex, endColumnIndex));
}
}
/**
* 写入工作表
*
* @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;
}
/**
* 读取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();
}
}
/**
* 导入的模板
*
* @param request
* @param response
* @param companyId
* @return
* @throws Exception
*/
@RequestMapping("/exportTemplate")
public String exportTemplate(HttpServletRequest request, HttpServletResponse response)
throws Exception {
HSSFWorkbook wb = new HSSFWorkbook(); // excel文件对象
HSSFSheet sheet = wb.createSheet("体检报告物流信息导入模板");
CellStyle style = getStyle(wb);
CellRangeAddress range = new CellRangeAddress(0, 2, 0, 9);
sheet.addMergedRegion(range);
Row row0 = sheet.createRow(0);
Cell cell00 = row0.createCell(0, Cell.CELL_TYPE_STRING);
cell00.setCellStyle(style);
cell00.setCellValue("说明：请正确填写如下信息，如‘HR订单账号’‘兑换子订单号’‘操作时间（2015/5/20）’");
sheet.autoSizeColumn(0);
Row row1 = sheet.createRow(3);
/* Cell cell0 = row1.createCell(0, Cell.CELL_TYPE_STRING);
cell0.setCellStyle(style);
cell0.setCellValue("物流面单号");
sheet.autoSizeColumn(0);*/
Cell cell1 = row1.createCell(0, Cell.CELL_TYPE_STRING);
cell1.setCellStyle(style);
cell1.setCellValue("总订单号");
sheet.autoSizeColumn(0);
Cell cell2 = row1.createCell(1, Cell.CELL_TYPE_STRING);
cell2.setCellStyle(style);
cell2.setCellValue("兑换子订单号*");
sheet.autoSizeColumn(1);
Cell cell3 = row1.createCell(2, Cell.CELL_TYPE_STRING);
cell3.setCellStyle(style);
cell3.setCellValue("物流公司编号*");
sheet.autoSizeColumn(2);
Cell cell4 = row1.createCell(3, Cell.CELL_TYPE_STRING);
cell4.setCellStyle(style);
cell4.setCellValue("物流编号*");
sheet.autoSizeColumn(3);
Cell cell6 = row1.createCell(4, Cell.CELL_TYPE_STRING);
cell6.setCellStyle(style);
cell6.setCellValue("更新人");
sheet.autoSizeColumn(4);
Cell cell7 = row1.createCell(5, Cell.CELL_TYPE_STRING);
cell7.setCellStyle(style);
cell7.setCellValue("更新时间");
sheet.autoSizeColumn(5);
Cell cell8 = row1.createCell(6, Cell.CELL_TYPE_STRING);
cell8.setCellStyle(style);
cell8.setCellValue("操作人");
sheet.autoSizeColumn(6);
Cell cell9 = row1.createCell(7, Cell.CELL_TYPE_STRING);
cell9.setCellStyle(style);
cell9.setCellValue("导入时间");
sheet.autoSizeColumn(7);
String fileName = FileUpDownUtils.encodeDownloadFileName(request, "体检物流信息导入模板" + ".xls");
FileUpDownUtils.setDownloadResponseHeaders(response, fileName);
wb.write(response.getOutputStream());
return null;
}
/**
* 写入工作表
*
* @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;
}
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~");
}
}
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);
}
}
