// 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;
}
// 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;
}
@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();
}
}