/**
* 初始化函数
*
* @param title 表格标题，传“空值”，表示无标题
* @param headerList 表头列表
*/
private void initialize(String title, List<String> headerList) {
this.wb = new SXSSFWorkbook(500);
this.sheet = wb.createSheet("Export");
this.styles = createStyles(wb);
// Create title
if (StringUtils.isNotBlank(title)) {
Row titleRow = sheet.createRow(rownum++);
titleRow.setHeightInPoints(30);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellStyle(styles.get("title"));
titleCell.setCellValue(title);
sheet.addMergedRegion(
new CellRangeAddress(
titleRow.getRowNum(),
titleRow.getRowNum(),
titleRow.getRowNum(),
headerList.size() - 1));
}
// Create header
if (headerList == null) {
throw new RuntimeException("headerList not null!");
}
Row headerRow = sheet.createRow(rownum++);
headerRow.setHeightInPoints(16);
for (int i = 0; i < headerList.size(); i++) {
Cell cell = headerRow.createCell(i);
cell.setCellStyle(styles.get("header"));
String[] ss = StringUtils.split(headerList.get(i), "**", 2);
if (ss.length == 2) {
cell.setCellValue(ss[0]);
Comment comment =
this.sheet
.createDrawingPatriarch()
.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
comment.setString(new XSSFRichTextString(ss[1]));
cell.setCellComment(comment);
} else {
cell.setCellValue(headerList.get(i));
}
sheet.autoSizeColumn(i);
}
for (int i = 0; i < headerList.size(); i++) {
int colWidth = sheet.getColumnWidth(i) * 2;
sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
}
log.debug("Initialize success.");
}
public void addSheet(
String sheetName, List<Object[]> bodyData, int[] entryTypes, String[] header) {
Sheet addedSheet = workbook.createSheet(sheetName);
createHeaderRow(addedSheet.createRow(0), header);
// goes through each entry in the list creating the rows
for (int index = 0; index < bodyData.size(); index++) {
createBodyRow(
addedSheet.createRow(index + 1),
bodyData.get(index),
entryTypes); // the 1 accounts for the header
}
// it now auto-sizes the columns
for (int column = 0; column <= header.length; column++) {
addedSheet.autoSizeColumn(column);
}
}
public void writeDataToExcel(Sheet sheet) {
LOGGER.debug("[{}] Exporting data to excel", getText());
List<ExcelColumnRenderer> columnRendererList = new ArrayList<>();
columnRendererList.add(new TaskStatusExcelColumn());
columnRendererList.add(new TaskDescriptionExcelColumn());
TimerangeProvider timerangeProvider = fetchTimereportContext.get().getTimerangeProvider();
LocalDate startDate = timerangeProvider.getStartDate();
LocalDate endDate = timerangeProvider.getEndDate();
long amountOfDaysToDisplay = ChronoUnit.DAYS.between(startDate, endDate);
for (int days = 0; days <= amountOfDaysToDisplay; days++) {
LocalDate currentColumnDate = timerangeProvider.getStartDate().plus(days, ChronoUnit.DAYS);
String displayDate = FormattingUtil.formatDate(currentColumnDate);
columnRendererList.add(new WorklogExcelColumn(displayDate, currentColumnDate));
}
columnRendererList.add(new TaskWorklogSummaryExcelColumn());
TreeItem<DisplayRow> root = taskTableView.getRoot();
ObservableList<TreeItem<DisplayRow>> children = root.getChildren();
for (int columnIndex = 0; columnIndex < columnRendererList.size(); columnIndex++) {
ExcelColumnRenderer excelColumnRenderer = columnRendererList.get(columnIndex);
excelColumnRenderer.renderCells(
columnIndex,
sheet,
children,
fetchTimereportContext.get().getGroupByCategory().isPresent());
}
// autosize column widths
for (int i = 0; i < columnRendererList.size(); i++) {
sheet.autoSizeColumn(i);
}
}
@Test
public void generateExcelPoiReport() throws IOException {
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("Accounts");
int rowNum = 1;
Row row = sheet.createRow(rowNum++);
Font font = wb.createFont();
font.setFontHeightInPoints((short) 24);
font.setFontName(FONT_TYPE);
font.setColor(FONT_COLOR_TITLE);
/** * Header **** */
CellStyle style = wb.createCellStyle();
style.setFont(font);
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setFillForegroundColor(BACKGROUND_COLOR);
Cell cell = row.createCell((short) 1);
cell.setCellValue("Account Report per Beneficiary");
cell.setCellStyle(style);
sheet.addMergedRegion(
new CellRangeAddress(
1, // first row (0-based)
2, // last row (0-based)
1, // first column (0-based)
16 // last column (0-based)
));
/** * Body **** */
font = wb.createFont();
font.setFontHeightInPoints((short) 12);
font.setFontName(FONT_TYPE);
font.setColor(FONT_COLOR);
style = wb.createCellStyle();
style.setFont(font);
rowNum = rowNum + 3;
List<Account> accounts = accountManager.getAllAccounts();
for (Account account : accounts) {
row = sheet.createRow(rowNum++);
cell = row.createCell((short) 1);
cell.setCellStyle(style);
cell.setCellValue(account.getName());
cell = row.createCell((short) 2);
cell.setCellValue(account.getNumber());
cell.setCellStyle(style);
cell = row.createCell((short) 3);
cell.setCellValue(account.getEntityId());
cell.setCellStyle(style);
}
sheet.autoSizeColumn(1);
sheet.autoSizeColumn(2);
sheet.autoSizeColumn(3);
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("C:/Temp/workbook.xls");
wb.write(fileOut);
fileOut.close();
}
// Define A method for Creating Excel File
public String createExcel(
String titleString,
String sheetString,
String subTitleString,
List data,
String[] headerTitles,
String titleKey,
String filePath) {
String file = null;
Workbook wb;
int col = 0;
try {
// check Header Title
if (headerTitles != null && headerTitles.length > 0) col = headerTitles.length;
wb = (Workbook) new HSSFWorkbook();
// Hear we are getting whole property
List<ConfigurationUtilBean> titleMap = new CustomerCommonPropertyMap().getTitles(titleKey);
Map<String, CellStyle> styles = createStyles(wb);
Sheet sheet = wb.createSheet(sheetString);
PrintSetup printSetup = sheet.getPrintSetup();
printSetup.setLandscape(true);
sheet.setFitToPage(true);
sheet.setHorizontallyCenter(true);
Header header = sheet.getHeader();
header.setCenter("Center Header");
header.setLeft("Left Header");
header.setRight("Right Footer");
Footer footer = sheet.getFooter();
footer.setCenter("center footer");
footer.setLeft("left footer");
footer.setRight("right footer");
// Title Row....
Row titleRow = sheet.createRow(0);
titleRow.setHeightInPoints(20);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellValue(titleString);
titleCell.setCellStyle(styles.get("title"));
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col - 1));
// Sub Title Row.....
// System.out.println("Sub Title String >>>>>>"+subTitleString);
Row headerRow = null;
if (subTitleString != "") {
Row subTitleRow = sheet.createRow(1);
subTitleRow.setHeightInPoints(18);
Cell subTitleCell = subTitleRow.createCell(0);
subTitleCell.setCellValue(subTitleString);
subTitleCell.setCellStyle(styles.get("subTitle"));
sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, col - 1));
headerRow = sheet.createRow(2);
headerRow.setHeightInPoints(15);
//
} else {
headerRow = sheet.createRow(1);
headerRow.setHeightInPoints(15);
}
Cell headerCell = null;
if (headerTitles != null) {
for (ConfigurationUtilBean cell : titleMap) {
int titleIndex = 0;
for (int i = 0; i < headerTitles.length; i++) {
if (cell.getKey().equalsIgnoreCase(headerTitles[titleIndex].trim())) {
headerCell = headerRow.createCell(titleIndex);
headerCell.setCellValue(cell.getValue());
headerCell.setCellStyle(styles.get("header"));
}
titleIndex++;
}
}
}
Row dataRow = null;
Cell dataCell = null;
int rowIndex = 2;
/* List Iteration text */
try {
if (data != null && data.size() > 0) {
for (Iterator it = data.iterator(); it.hasNext(); ) {
Object[] obdata = (Object[]) it.next();
dataRow = sheet.createRow(rowIndex);
for (int cellIndex = 0; cellIndex < headerTitles.length; cellIndex++) {
dataCell = dataRow.createCell(cellIndex);
if (obdata[cellIndex] != null && !obdata[cellIndex].toString().equalsIgnoreCase("")) {
dataCell.setCellValue(obdata[cellIndex].toString());
} else {
dataCell.setCellValue("NA");
}
}
rowIndex++;
}
}
} catch (Exception e) {
// TODO: handle exception
}
for (int titleIndex = 0; titleIndex < headerTitles.length; titleIndex++)
sheet.autoSizeColumn(titleIndex); // adjust width of the column
file =
filePath
+ File.separator
+ "OpportunityReportDetail_"
+ DateUtil.getCurrentDateIndianFormat()
+ (DateUtil.getCurrentTimeHourMin()).replaceAll(":", "-")
+ ".xls";
if (wb instanceof XSSFWorkbook) file += "x";
FileOutputStream out = new FileOutputStream(file);
wb.write(out);
out.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
}
return file;
}
// Define A method for Creating Excel File
public String createExcelformate(
String titleString,
String sheetString,
String[] headerTitles,
String titleKey,
String filePath) {
String file = null;
Workbook wb;
int col = 0;
try {
// check Header Title
if (headerTitles != null && headerTitles.length > 0) col = headerTitles.length;
wb = (Workbook) new HSSFWorkbook();
// Hear we are getting whole property
List<ConfigurationUtilBean> titleMap = new CustomerCommonPropertyMap().getTitles(titleKey);
Map<String, CellStyle> styles = createStyles(wb);
Sheet sheet = wb.createSheet(sheetString);
PrintSetup printSetup = sheet.getPrintSetup();
printSetup.setLandscape(true);
sheet.setFitToPage(true);
sheet.setHorizontallyCenter(true);
Header header = sheet.getHeader();
header.setCenter("Center Header");
header.setLeft("Left Header");
header.setRight("Right Footer");
Footer footer = sheet.getFooter();
footer.setCenter("center footer");
footer.setLeft("left footer");
footer.setRight("right footer");
// Title Row....
Row titleRow = sheet.createRow(0);
titleRow.setHeightInPoints(20);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellValue(titleString);
titleCell.setCellStyle(styles.get("title"));
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col - 1));
//
Row headerRow = sheet.createRow(1);
headerRow.setHeightInPoints(15);
Cell headerCell = null;
if (headerTitles != null) {
for (ConfigurationUtilBean cell : titleMap) {
int titleIndex = 0;
for (int i = 0; i < headerTitles.length; i++) {
if (cell.getKey().equalsIgnoreCase(headerTitles[titleIndex].trim())) {
headerCell = headerRow.createCell(titleIndex);
headerCell.setCellValue(cell.getValue());
headerCell.setCellStyle(styles.get("header"));
}
titleIndex++;
}
}
}
for (int titleIndex = 0; titleIndex < headerTitles.length; titleIndex++)
sheet.autoSizeColumn(titleIndex); // adjust width of the column
file =
filePath
+ File.separator
+ "ContactReport"
+ DateUtil.getCurrentDateIndianFormat()
+ (DateUtil.getCurrentTime()).replaceAll(":", "-")
+ ".xls";
if (wb instanceof XSSFWorkbook) file += "x";
FileOutputStream out = new FileOutputStream(file);
wb.write(out);
out.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
}
return file;
}
/**
* Creates an Excel worksheet containing the metric's data (timestamps and values) for the
* specified time range. This worksheet is titled with the trhe metric's name and added to the
* specified Workbook.
*
* @param wb the workbook to add this worksheet to
* @param metricName the name of the metric whose data is being rendered in this worksheet
* @param rrdFilename the name of the RRD file to retrieve the metric's data from
* @param startTime start time, in seconds since Unix epoch, to fetch metric's data
* @param endTime end time, in seconds since Unix epoch, to fetch metric's data
* @throws IOException
* @throws MetricsGraphException
*/
private void createSheet(
Workbook wb, String metricName, String rrdFilename, long startTime, long endTime)
throws IOException, MetricsGraphException {
LOGGER.trace("ENTERING: createSheet");
MetricData metricData = getMetricData(rrdFilename, startTime, endTime);
String displayableMetricName = convertCamelCase(metricName);
String title =
displayableMetricName
+ " for "
+ getCalendarTime(startTime)
+ " to "
+ getCalendarTime(endTime);
Sheet sheet = wb.createSheet(displayableMetricName);
sheet.autoSizeColumn(0);
sheet.autoSizeColumn(1);
Font headerFont = wb.createFont();
headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
CellStyle columnHeadingsStyle = wb.createCellStyle();
columnHeadingsStyle.setFont(headerFont);
CellStyle bannerStyle = wb.createCellStyle();
bannerStyle.setFont(headerFont);
bannerStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
bannerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
int rowCount = 0;
Row row = sheet.createRow((short) rowCount);
Cell cell = row.createCell(0);
cell.setCellValue(title);
cell.setCellStyle(bannerStyle);
rowCount++;
// Blank row for spacing/readability
row = sheet.createRow((short) rowCount);
cell = row.createCell(0);
cell.setCellValue("");
rowCount++;
row = sheet.createRow((short) rowCount);
cell = row.createCell(0);
cell.setCellValue("Timestamp");
cell.setCellStyle(columnHeadingsStyle);
cell = row.createCell(1);
cell.setCellValue("Value");
cell.setCellStyle(columnHeadingsStyle);
rowCount++;
List<Long> timestamps = metricData.getTimestamps();
List<Double> values = metricData.getValues();
for (int i = 0; i < timestamps.size(); i++) {
String timestamp = getCalendarTime(timestamps.get(i));
row = sheet.createRow((short) rowCount);
row.createCell(0).setCellValue(timestamp);
// convert value to a long to prevent fractional values
row.createCell(1).setCellValue(new Double(values.get(i)).longValue());
rowCount++;
}
if (metricData.hasTotalCount()) {
// Blank row for spacing/readability
row = sheet.createRow((short) rowCount);
cell = row.createCell(0);
cell.setCellValue("");
rowCount++;
row = sheet.createRow((short) rowCount);
cell = row.createCell(0);
cell.setCellValue("Total Count: ");
cell.setCellStyle(columnHeadingsStyle);
row.createCell(1).setCellValue(metricData.getTotalCount());
}
LOGGER.trace("EXITING: createSheet");
}
@Test
public void createex() {
Workbook wb = new HSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper();
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillPattern(CellStyle.NO_FILL);
Sheet sheet = wb.createSheet("数据日志");
Sheet sheet2 = wb.createSheet("second sheet");
String safeName =
WorkbookUtil.createSafeSheetName("[O'Brien's sales*?]"); // returns " O'Brien's sales "
Sheet sheet3 = wb.createSheet(safeName);
Header header = sheet.getHeader();
header.setCenter("Center Header");
header.setLeft("Left Header");
header.setRight(
HSSFHeader.font("Stencil-Normal", "Italic")
+ HSSFHeader.fontSize((short) 16)
+ "Right w/ Stencil-Normal Italic font and size 16");
sheet.setColumnWidth(0, 15 * 256); // .autoSizeColumn(0 ); // 调整第一列宽度
sheet.setColumnWidth(1, 17 * 256); // 调整第二列宽度
sheet.setColumnWidth(3, 17 * 256); // 调整第三列宽度
sheet.autoSizeColumn(2); // 调整第四列宽度
// Create a row and put some cells in it. Rows are 0 based.
Row row = sheet.createRow((short) 1);
// Create a cell and put a value in it.
Cell cell = row.createCell(0);
cell.setCellValue("ID");
cell.setCellStyle(cellStyle);
// Or do it on one line.
row.createCell(1).setCellValue("表名");
row.createCell(2).setCellValue(createHelper.createRichTextString("操作类型"));
row.createCell(3).setCellValue("操作日期");
row.createCell(4).setCellValue("操作者");
List<Log> list = this.logList();
Row rw;
for (int i = 2; i < list.size(); i++) {
Log log = list.get(i);
rw = sheet.createRow((short) i);
rw.createCell(0).setCellValue(createHelper.createRichTextString(log.getId()));
rw.createCell(1).setCellValue(log.getDataTable());
rw.createCell(2).setCellValue(this.convertActionType(log.getActionType()));
rw.createCell(3)
.setCellValue(DateUtil.convertDateToString(log.getLogDate(), DateUtil.pattern2));
rw.createCell(4).setCellValue(log.getAdminId());
}
String bp = this.getClass().getResource("/").toString();
try {
System.out.println(bp);
File f = new File("workbook.xls");
FileOutputStream fileOut = new FileOutputStream(f);
wb.write(fileOut);
fileOut.close();
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void process2HTTP(
ExportModel expModel, UISimpleViewExport uis, HttpServletResponse hsr, DateTimeHelper dth) {
try {
String strFileName = uis.getDownloadFileName();
Workbook wbCurrent = null;
if (strFileName.toLowerCase().endsWith(".xlsx")) {
wbCurrent = new XSSFWorkbook();
} else {
wbCurrent = new HSSFWorkbook();
}
HashMap<String, CellStyle> hsCS = new HashMap<String, CellStyle>();
CreationHelper cr = wbCurrent.getCreationHelper();
CellStyle csDate = wbCurrent.createCellStyle();
csDate.setDataFormat(cr.createDataFormat().getFormat(dth.getDFDate().toPattern()));
CellStyle csDateTime = wbCurrent.createCellStyle();
csDateTime.setDataFormat(cr.createDataFormat().getFormat(dth.getDFDateTime().toPattern()));
CellStyle csTime = wbCurrent.createCellStyle();
csTime.setDataFormat(cr.createDataFormat().getFormat(dth.getDFTime().toPattern()));
hsCS.put("DATE", csDate);
hsCS.put("TIME", csTime);
hsCS.put("DATETIME", csDateTime);
Sheet sh = wbCurrent.createSheet("SVE Export");
int nRowCount = 0;
// BUILDING HEADER
if (uis.isIncludeHeader()) {
Row rw = sh.createRow(nRowCount);
int nCol = 0;
for (ExportColumn expColumn : expModel.getColumns()) {
rw.createCell(nCol).setCellValue(expColumn.getColumnName());
nCol++;
}
nRowCount++;
}
// Processing Values
for (ExportDataRow expRow : expModel.getRows()) {
Row rw = sh.createRow(nRowCount);
int nCol = 0;
for (ExportColumn expColumn : expModel.getColumns()) {
Cell clCurrent = rw.createCell(nCol);
setCellValue(expRow.getValue(expColumn.getPosition()), clCurrent, expColumn, hsCS);
nCol++;
}
nRowCount++;
}
for (int nCol = 0; nCol < expModel.getColumns().size(); nCol++) {
sh.autoSizeColumn(nCol);
}
if (strFileName.toLowerCase().endsWith(".xlsx")) {
hsr.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
} else if (strFileName.toLowerCase().endsWith("xls")) {
hsr.setContentType("application/vnd.ms-excel");
} else {
hsr.setContentType("application/octet-stream");
}
hsr.addHeader("Content-disposition", "inline; filename=\"" + strFileName + "\"");
OutputStream os = hsr.getOutputStream();
ByteArrayOutputStream bos = new ByteArrayOutputStream();
wbCurrent.write(bos);
bos.writeTo(os);
os.close();
} catch (Exception e) {
ErrorPageBuilder.getInstance()
.processError(hsr, "Error during SVE-Generation (Workbook Export)", e);
}
}
public void exportarPlanilha(File arquivoDeSaida) throws FileNotFoundException, IOException {
logger.info("Gerando relatório");
workbook = new HSSFWorkbook();
dataCellStyle = buildCellStyle();
headerCellStyle = buildCellStyle();
totalHeaderStyle = buildCellStyle();
totalHeaderStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
totalHeaderStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
totalCellStyle = buildCellStyle();
totalCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
totalCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
totalCellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
totalRegistrosGravar = categorias.size() * medicos.size() * planos.size();
logger.debug("{} registros para gravar", totalRegistrosGravar);
registrosGravados = 0;
for (String categoria : categorias.keySet()) {
Sheet sheet = workbook.createSheet(WorkbookUtil.createSafeSheetName(categoria));
Row rowPlanos = sheet.createRow(0);
buildHeaderCell(rowPlanos, (short) 0);
Map<String, AtomicInteger> totaisPlanos = new HashMap<String, AtomicInteger>();
for (String plano : planos.keySet()) {
buildHeaderCell(rowPlanos, rowPlanos.getLastCellNum()).setCellValue(plano);
totaisPlanos.put(plano, new AtomicInteger());
}
Cell cellTotalHeader = buildTotalHeaderCell(rowPlanos, rowPlanos.getLastCellNum());
cellTotalHeader.setCellValue("TOTAL");
AtomicInteger totalGeral = new AtomicInteger();
for (String medico : medicos.keySet()) {
Row row = sheet.createRow(sheet.getLastRowNum() + 1);
Cell cellMedico = buildHeaderCell(row, (short) 0);
cellMedico.setCellValue(medico);
AtomicInteger totalMedico = new AtomicInteger();
for (String plano : planos.keySet()) {
Chave chave = new Chave(plano, medico, categoria);
AtomicInteger contador = contadores.get(chave);
Cell cell = buildDataCell(row, row.getLastCellNum());
if (contador != null) {
cell.setCellValue(contador.get());
totalMedico.addAndGet(contador.get());
totaisPlanos.get(plano).addAndGet(contador.get());
} else {
cell.setCellValue("*");
}
EventQueue.invokeLater(
new Runnable() {
@Override
public void run() {
int porcentagemConcluido =
50 + (int) ((++registrosGravados / (float) totalRegistrosGravar) * 50);
progressBar.setValue(porcentagemConcluido);
}
});
}
Cell cellTotal = buildTotalCell(row, row.getLastCellNum());
cellTotal.setCellValue(totalMedico.get());
totalGeral.addAndGet(totalMedico.get());
}
Row rowTotais = sheet.createRow(sheet.getLastRowNum() + 1);
Cell cellTotalPlanosHeader = buildTotalHeaderCell(rowTotais, (short) 0);
cellTotalPlanosHeader.setCellValue("TOTAL");
for (String plano : planos.keySet()) {
Cell cellTotal = buildTotalCell(rowTotais, rowTotais.getLastCellNum());
cellTotal.setCellValue(totaisPlanos.get(plano).get());
}
Cell cellTotalGeral = buildTotalCell(rowTotais, rowTotais.getLastCellNum());
cellTotalGeral.setCellValue(totalGeral.get());
for (int i = 0; i < sheet.getRow(0).getLastCellNum(); i++) {
sheet.autoSizeColumn(i);
}
}
FileOutputStream fileOut = new FileOutputStream(arquivoDeSaida);
workbook.write(fileOut);
fileOut.close();
logger.info("Relatório concluido");
}
