/**
* 初始化函数
*
* @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.");
}
private void headerProcess(Sheet tempSheet, Sheet newSheet, Date cycleFrom, Date cycleTo)
throws Exception {
Cell tCell = tempSheet.getRow(0).getCell(0, Row.CREATE_NULL_AS_BLANK);
Cell nCell = newSheet.createRow(0).createCell(0, tCell.getCellType());
nCell.setCellValue(tCell.getStringCellValue().replaceAll(PARAM_COMPANY, FWD_COMP_NAME));
nCell.setCellStyle(tCell.getCellStyle());
tCell = tempSheet.getRow(1).getCell(0, Row.CREATE_NULL_AS_BLANK);
nCell = newSheet.createRow(1).createCell(0, tCell.getCellType());
nCell.setCellValue(
tCell
.getStringCellValue()
.replaceAll(PARAM_ABBR_NAME, "ADAMS-TVD")
.replaceAll(
PARAM_CYCLE_FROM, DateUtil.convDateToString(DISPLAY_DATE_PATTERN, cycleFrom))
.replaceAll(PARAM_CYCLE_TO, DateUtil.convDateToString(DISPLAY_DATE_PATTERN, cycleTo)));
nCell.setCellStyle(tCell.getCellStyle());
Row tempRow = tempSheet.getRow(4);
Row newRow = newSheet.createRow(4);
for (int c = 0; c < tempRow.getLastCellNum(); c++) {
tCell = tempRow.getCell(c, Row.CREATE_NULL_AS_BLANK);
nCell = newRow.createCell(c, tCell.getCellType());
nCell.setCellValue(tCell.getStringCellValue());
nCell.setCellStyle(tCell.getCellStyle());
}
for (int i = 0; i < tempSheet.getNumMergedRegions(); i++) {
CellRangeAddress mergedRegion = tempSheet.getMergedRegion(i);
newSheet.addMergedRegion(mergedRegion);
}
}
/**
* Forms report in excel file. Sum weight. Sum volume etc.
*
* @param sheet Sheet where write the items
* @param c Container from where take the info
* @param lastRow last row after all items were written method uses lastRow + 2 to write report in
* next 2 rows after all items of the container were written
*/
private void setReport(Container c, int lastRow) {
String[] reportHeadings = {
"Суммарный вес", // 6
"Суммарный объем", // 7
"Остаток вес", // 8
"Остаток объем" // 9
};
double[] values = {
c.getWeight(),
c.getVolume(),
c.getWeightLimit() - c.getWeight(),
c.getVolumeLimit() - c.getVolume()
};
int dataCell = 5;
int valuesIndex = 0; // index of the array of doubles(weigh, volume, etc)
lastRow++;
Row headings = outputSheet.createRow(lastRow);
lastRow += 2;
Row data = outputSheet.createRow(lastRow);
for (String report : reportHeadings) {
Cell heading = headings.createCell(dataCell);
heading.setCellValue(report);
Cell dataCellValue = data.createCell(dataCell);
dataCellValue.setCellValue(values[valuesIndex]);
dataCell++;
valuesIndex++;
}
}
@Test
public void overrideRowsInTemplate() throws IOException {
XSSFWorkbook template = new XSSFWorkbook();
template.createSheet().createRow(1);
Workbook wb = new SXSSFWorkbook(template);
try {
Sheet sheet = wb.getSheetAt(0);
try {
sheet.createRow(1);
fail("expected exception");
} catch (Throwable e) {
assertEquals(
"Attempting to write a row[1] in the range [0,1] that is already written to disk.",
e.getMessage());
}
try {
sheet.createRow(0);
fail("expected exception");
} catch (Throwable e) {
assertEquals(
"Attempting to write a row[0] in the range [0,1] that is already written to disk.",
e.getMessage());
}
sheet.createRow(2);
} finally {
wb.close();
template.close();
}
}
@RequestMapping(value = "/excel", method = RequestMethod.GET)
public void excel(HttpServletResponse response) throws Exception {
Workbook wb = new HSSFWorkbook();
// CreationHelper createHelper = wb.getCreationHelper();
Sheet sheet = wb.createSheet("new sheet");
org.apache.poi.ss.usermodel.Row row = sheet.createRow((short) 0);
row = sheet.createRow((short) 1);
row.createCell(0).setCellValue("Mã");
row.createCell(1).setCellValue("Tên");
row.createCell(2).setCellValue("Mã Path");
row.createCell(3).setCellValue("Tên path");
row.createCell(4).setCellValue("Cấp danh mục");
int i = 1;
List<Category> listAll = categoryService.listAll();
for (Category print : listAll) {
if (print.getName().length() > 19) {
i++;
row = sheet.createRow((short) i);
row.createCell(0).setCellValue(String.valueOf(print.getId()));
row.createCell(1).setCellValue(String.valueOf(print.getName()));
row.createCell(2).setCellValue(String.valueOf(print.getPath()));
String path = "";
for (String categoryId : print.getPath()) {
Category get = categoryService.get(categoryId);
path += get.getName() + " >>";
}
row.createCell(3).setCellValue(String.valueOf(path));
row.createCell(4).setCellValue(String.valueOf(print.getLevel()));
}
}
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment; filename=Danh-sach-danh-muc.xls");
wb.write(response.getOutputStream());
}
private InputStream generateExcel(List<Map<String, Object>> detailList) throws IOException {
Workbook wb = new HSSFWorkbook();
Sheet sheet1 = wb.createSheet("sheet1");
CellStyle headerStyle = getHeaderStyle(wb);
CellStyle firstCellStyle = getFirsetCellStyle(wb);
CellStyle commonCellStyle = getCommonCellStyle(wb);
CellStyle amtCellStyle = getAmtCellStyle(wb);
for (int i = 0; i < LENGTH_9; i++) {
sheet1.setColumnWidth(i, STR_15 * STR_256);
}
// 表头
Row row = sheet1.createRow(0);
row.setHeightInPoints(STR_20);
Cell cell = headInfo(headerStyle, row);
if (detailList.size() == 0) {
row = sheet1.createRow(1);
cell = row.createCell(0);
cell.setCellValue(NO_RECORD);
} else {
fillData(detailList, sheet1, firstCellStyle, commonCellStyle, amtCellStyle);
}
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
wb.write(outputStream);
InputStream inputStream = new ByteArrayInputStream(outputStream.toByteArray());
outputStream.close();
return inputStream;
}
// ����Excel
public String ExcelExport() throws Exception {
HttpServletRequest request = ServletActionContext.getRequest();
String ids = request.getParameter("ids");
List<Dise> list = new ArrayList<Dise>();
String[] array = ids.split(",");
int[] id = new int[array.length];
for (int i = 0; i < id.length; i++) {
Dise dise = DiseService.findById(Integer.valueOf(array[i]));
list.add(dise);
}
Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet("ѧ����Ϣ");
Row row = sheet.createRow(0);
row.createCell(0).setCellValue("ѧ��");
row.createCell(1).setCellValue("����");
row.createCell(2).setCellValue("����");
row.createCell(3).setCellValue("�Ա�");
row.createCell(4).setCellValue("��ַ");
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
for (int i = 1; i <= list.size(); i++) {
Dise stu = list.get(i - 1);
row = sheet.createRow(i);
row.createCell(0).setCellValue(stu.getIds());
row.createCell(1).setCellValue(stu.getName());
row.createCell(2).setCellValue(stu.getA());
row.createCell(3).setCellValue(stu.getB());
row.createCell(4).setCellValue(stu.getC());
}
ByteArrayOutputStream baos = new ByteArrayOutputStream();
workbook.write(baos);
excelFile = new ByteArrayInputStream(baos.toByteArray());
baos.close();
return "excel";
}
// =================================================================================================================================
private int exportFrontEquipListReport_header(
List<FrontEquipListReport_subtype> subtypes, int rownum, XSSFWorkbook wb, Sheet sheet) {
CellStyle style = getHeaderStyle(wb, (short) 11);
CellStyle style2 = getHeaderStyle(wb, (short) 10);
// 第一行列标题
Row row_subtype = sheet.createRow(rownum++);
Cell cell_subtype_0 = row_subtype.createCell(0);
cell_subtype_0.setCellValue("");
cell_subtype_0.setCellStyle(style);
sheet.setColumnWidth(0, 700);
Cell cell_subtype_1 = row_subtype.createCell(1);
cell_subtype_1.setCellValue("");
cell_subtype_1.setCellStyle(style);
sheet.setColumnWidth(1, 1800);
Cell cell_subtype_2 = row_subtype.createCell(2);
cell_subtype_2.setCellValue("");
cell_subtype_2.setCellStyle(style);
sheet.setColumnWidth(2, 4800);
// 第二行列标题
Row row_prod = sheet.createRow(rownum++);
Cell cell_prod_0 = row_prod.createCell(0);
cell_prod_0.setCellValue("序号");
cell_prod_0.setCellStyle(style2);
Cell cell_prod_1 = row_prod.createCell(1);
cell_prod_1.setCellValue("点位编号");
cell_prod_1.setCellStyle(style2);
Cell cell_prod_2 = row_prod.createCell(2);
cell_prod_2.setCellValue("点位名称");
cell_prod_2.setCellStyle(style2);
int cell_num = 3;
for (FrontEquipListReport_subtype subtype : subtypes) {
int start_cell_num = cell_num;
Cell cell_subtype = row_subtype.createCell(cell_num);
cell_subtype.setCellValue(subtype.getSubtype_name());
cell_subtype.setCellStyle(style);
for (FrontEquipListReport_prod prod : subtype.getProds()) {
if (start_cell_num != cell_num) {
cell_subtype = row_subtype.createCell(cell_num);
cell_subtype.setCellStyle(style);
}
Cell cell_prod = row_prod.createCell(cell_num);
cell_prod.setCellValue(prod.getProd_name() + "(" + prod.getProd_style() + ")");
cell_prod.setCellStyle(style2);
cell_num++;
}
// 对子类型进行横向的单元格合并
sheet.addMergedRegion(
new CellRangeAddress(1, (short) 1, start_cell_num, (short) cell_num - 1));
}
return rownum;
}
/**
* make the excel file based on headers and contents which should have been set
*
* @param sheetName specified sheet name
* @return ExcelMaker itself
* @throws IllegalArgumentException only support String, Integer, Long, Double, Date, null as cell
* data.
*/
public ExcelMaker make(String sheetName) throws IllegalArgumentException {
// check if headers and contents set
if (!contextReady()) {
throw new IllegalArgumentException("invalid headers or contents");
}
Sheet sheet = sheetName == null ? workbook.createSheet() : workbook.createSheet(sheetName);
// write the display headers as the 1st row in the sheet
int rowNum = 0;
Row headerRow = sheet.createRow(rowNum++);
if (displayHeaders != null) {
for (int cellNum = 0; cellNum < displayHeaders.size(); cellNum++) {
String header = displayHeaders.get(cellNum);
Cell cell = headerRow.createCell(cellNum);
cell.setCellValue(header);
}
} else { // if display headers not set, use headers as default
for (int cellNum = 0; cellNum < headers.size(); cellNum++) {
String header = headers.get(cellNum);
Cell cell = headerRow.createCell(cellNum);
cell.setCellValue(header);
}
}
// parse each Object in content list and write as a row
for (Object content : contents) {
Map<String, Object> headerMap =
objectMapper.convertValue(content, new TypeReference<HashMap<String, Object>>() {});
Row row = sheet.createRow(rowNum++);
// only find properties that declared by headers
for (int cellNum = 0; cellNum < headers.size(); cellNum++) {
String header = headers.get(cellNum);
Cell cell = row.createCell(cellNum);
Object obj = headerMap.get(header);
if (obj == null) {
obj = "";
}
if (obj instanceof String) {
cell.setCellValue((String) obj);
} else if (obj instanceof Integer) {
cell.setCellValue((Integer) obj);
} else if (obj instanceof Long) {
cell.setCellValue((Long) obj);
} else if (obj instanceof Double) {
cell.setCellValue((Double) obj);
} else if (obj instanceof Date) {
cell.setCellValue((Date) obj);
} else {
throw new IllegalArgumentException("unsupported cell type");
}
}
}
return this;
}
public static File writerFile(String[] title, List<String[]> content, String filePath)
throws IOException {
checkDir(filePath);
File f = new File(filePath);
if (!f.exists()) {
f.createNewFile();
}
FileOutputStream out = new FileOutputStream(f);
wb = new SXSSFWorkbook();
setStyle(wb);
Sheet sheet = wb.createSheet("sheet1");
Row titleRow = sheet.createRow(0);
titleRow.setHeightInPoints(20);
int tCount = title.length;
for (int i = 0; i < tCount; i++) {
Cell cell = titleRow.createCell(i);
cell.setCellStyle(titleStyle);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(title[i]);
sheet.setColumnWidth(i, 5000);
}
int rnum = 1;
for (String[] c : content) {
Row r = sheet.createRow(rnum);
for (int i = 0; i < c.length; i++) {
Cell cell = r.createCell(i);
cell.setCellStyle(contentStyle);
cell.setCellType(Cell.CELL_TYPE_STRING);
sheet.setColumnWidth(i, 5000);
String v = c[i];
if (v == null) {
v = "";
}
cell.setCellValue(v);
}
rnum++;
}
wb.write(out);
out.flush();
wb.close();
out.close();
return f;
}
private void writeModule(TrainingModule module) {
Row row = trainingSheet.createRow(++actualRow);
row.createCell(COL_MODULE_NUMBER).setCellValue(module.getNumber());
row.createCell(COL_MODULE).setCellValue(module.getName());
// for all topics write topics
Iterator<TrainingTopic> it = module.getTopics().iterator();
while (it.hasNext()) {
row = trainingSheet.createRow(++actualRow);
writeTopic(it.next(), row);
}
}
private org.apache.poi.ss.usermodel.Row getRowForCoordinate(
RowIndex rowIndex, SheetIndex sheetIndex) {
Sheet sheet = workbook.getSheetAt(sheetIndex.value());
org.apache.poi.ss.usermodel.Row row = sheet.getRow(rowIndex.value());
if (row == null) row = sheet.createRow(rowIndex.value());
return row;
}
public static void copySheet(Sheet sheet, Sheet newSheet) {
int maxCol = 0;
for (int row = 0; row <= sheet.getLastRowNum(); row++) {
Row oldRow = sheet.getRow(row);
if (oldRow == null) continue;
Row newRow = newSheet.getRow(row);
if (newRow == null) newRow = newSheet.createRow(row);
if (oldRow.getHeight() >= 0) newRow.setHeight(oldRow.getHeight());
maxCol = (maxCol >= oldRow.getLastCellNum() - 1 ? maxCol : oldRow.getLastCellNum() - 1);
for (int col = 0; col < oldRow.getLastCellNum(); col++) {
Cell oldCell = oldRow.getCell(col);
if (oldCell == null) continue;
Cell newCell = newRow.getCell(col);
if (newCell == null) newCell = newRow.createCell(col);
copyCell(oldCell, newCell, true);
}
}
for (int col = 0; col <= maxCol; col++) {
if (sheet.getColumnWidth(col) >= 0) newSheet.setColumnWidth(col, sheet.getColumnWidth(col));
}
for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
CellRangeAddress cra = sheet.getMergedRegion(i);
newSheet.addMergedRegion(cra);
}
}
/**
* write excel sheet, specified value to specified cell.
*
* @param sheetName excel sheet name
* @param row row index which to be changed
* @param col column index which to be changed
* @param value value to be put into cell
* @throws RuntimeException
*/
public void setExcelValue(String sheetName, int row, int col, String value) {
Workbook workBook = null;
try {
if (new File(fileName).exists()) {
workBook = getWorkBook(new FileInputStream(fileName), false);
} else {
workBook = getWorkBook(null, false);
}
xlSheet = workBook.getSheet(sheetName);
if (xlSheet == null) {
xlSheet = workBook.createSheet(sheetName);
}
xlRow = xlSheet.getRow(row - 1);
if (xlRow == null) {
xlRow = xlSheet.createRow((short) row - 1);
}
xlCell = xlRow.getCell(col - 1);
if (xlCell == null) {
xlCell = xlRow.createCell(col - 1);
}
xlCell.setCellType(1); // set cell type as string
xlCell.setCellValue(value);
FileOutputStream fileOut = new FileOutputStream(fileName);
workBook.write(fileOut);
fileOut.flush();
fileOut.close();
} catch (Exception e) {
LOG.error(e);
throw new RuntimeException("set excel value failed:" + e.getMessage());
}
}
/*
* Don't hardcode US Locale for datetimes in Excel. Experiment a bit.
*/
public static void issue26() throws InvalidFormatException, IOException {
System.out.println("Testing issue26 =================================");
Workbook wb = new XSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper();
Sheet sheet = wb.createSheet("Sheet1");
Row row = sheet.createRow(0);
// first cell
Cell cell0 = row.createCell(0);
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm:ss"));
cell0.setCellValue(new Date(1385903303326L)); // 12/1/2013 08:08 AM
cell0.setCellStyle(cellStyle);
// second cell using another format with French locale
CellStyle cs2 = wb.createCellStyle();
String excelFormatPrefix = DateFormatConverter.getPrefixForLocale(Locale.FRENCH);
System.out.println("The LOCALE prefix is: " + excelFormatPrefix);
String excelFormatPattern = excelFormatPrefix + "dd MMM, yyyy;@";
System.out.println(
"Converted pattern in FRENCH locale is: "
+ DateFormatConverter.convert(Locale.FRENCH, "m/d/yy h:mm:ss"));
DataFormat df = wb.createDataFormat();
cs2.setDataFormat(df.getFormat(excelFormatPattern));
Cell cell1 = row.createCell(1);
cell1.setCellValue(new Date(1385903303326L));
cell1.setCellStyle(cs2);
FileOutputStream out = new FileOutputStream("/tmp/issue26_out.xlsx");
wb.write(out);
out.close();
System.out.println("Wrote /tmp/issue26_out.xlsx");
}
private void setDefaults(Sheet worksheet) {
try {
for (Integer rowNo = 1; rowNo < 3000; rowNo++) {
Row row = worksheet.getRow(rowNo);
if (row == null) row = worksheet.createRow(rowNo);
writeFormula(
PRODUCT_COL,
row,
"IF(ISERROR(VLOOKUP($C"
+ (rowNo + 1)
+ ",$Q$2:$S$"
+ (savings.size() + 1)
+ ",2,FALSE)),\"\",VLOOKUP($C"
+ (rowNo + 1)
+ ",$Q$2:$S$"
+ (savings.size() + 1)
+ ",2,FALSE))");
writeFormula(
OPENING_BALANCE_COL,
row,
"IF(ISERROR(VLOOKUP($C"
+ (rowNo + 1)
+ ",$Q$2:$S$"
+ (savings.size() + 1)
+ ",3,FALSE)),\"\",VLOOKUP($C"
+ (rowNo + 1)
+ ",$Q$2:$S$"
+ (savings.size() + 1)
+ ",3,FALSE))");
}
} catch (Exception e) {
logger.error(e.getMessage());
}
}
private static void createRow(
Sheet sheet,
Map<String, Object> rowData,
List<String> mapping,
List<CellStyle> styles,
int startRowNum) {
Row row = sheet.createRow(startRowNum);
for (int i = 0; i < mapping.size(); i++) {
String name = mapping.get(i);
Object obj = rowData.get(name);
Cell newCell = row.createCell(i);
CellStyle style = styles.get(i);
newCell.setCellStyle(style);
if (obj != null) {
if (obj instanceof Date) {
newCell.setCellValue((Date) obj);
} else if (obj instanceof BigDecimal) {
double dd = ((BigDecimal) obj).doubleValue();
newCell.setCellValue(dd);
} else {
newCell.setCellValue(obj.toString());
}
}
}
}
private Result populateLoansTable(Sheet savingsTransactionSheet) {
Result result = new Result();
Workbook workbook = savingsTransactionSheet.getWorkbook();
CellStyle dateCellStyle = workbook.createCellStyle();
short df = workbook.createDataFormat().getFormat("dd/mm/yy");
dateCellStyle.setDataFormat(df);
int rowIndex = 1;
Row row;
Collections.sort(savings, CompactSavingsAccount.ClientNameComparator);
try {
for (CompactSavingsAccount savingsAccount : savings) {
row = savingsTransactionSheet.createRow(rowIndex++);
writeString(LOOKUP_CLIENT_NAME_COL, row, savingsAccount.getClientName());
writeLong(LOOKUP_ACCOUNT_NO_COL, row, Long.parseLong(savingsAccount.getAccountNo()));
writeString(LOOKUP_PRODUCT_COL, row, savingsAccount.getSavingsProductName());
writeDouble(LOOKUP_OPENING_BALANCE_COL, row, savingsAccount.getMinRequiredOpeningBalance());
writeDate(
LOOKUP_SAVINGS_ACTIVATION_DATE_COL,
row,
savingsAccount.getTimeline().getActivatedOnDate().get(2)
+ "/"
+ savingsAccount.getTimeline().getActivatedOnDate().get(1)
+ "/"
+ savingsAccount.getTimeline().getActivatedOnDate().get(0),
dateCellStyle);
}
} catch (Exception e) {
result.addError(e.getMessage());
logger.error(e.getMessage());
}
return result;
}
public void insertDataToExcel(int numRow, Object[] object) {
try {
if (null != wb.getSheetAt(0)) {
Sheet aSheet = wb.getSheetAt(0);
Row row = aSheet.getRow((short) numRow);
if (row == null) row = aSheet.createRow((short) numRow);
for (int i = 0; i < object.length; i++) {
Cell csCell = row.createCell((short) i);
CellStyle style = wb.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.BLACK.index);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setTopBorderColor(HSSFColor.BLACK.index);
csCell.setCellStyle(style);
if (object[i] != null) csCell.setCellValue(object[i].toString());
else csCell.setCellValue("0");
}
}
} catch (Exception e) {
System.out.println("insertDataToExcel" + e);
}
}
public void writeData(Data data, boolean onlyNumbers) {
try {
File file = new File(path);
if (!file.exists()) {
file.createNewFile();
}
FileOutputStream fOutputStream = new FileOutputStream(file);
org.apache.poi.ss.usermodel.Workbook workbook = new HSSFWorkbook();
org.apache.poi.ss.usermodel.Sheet sheet = workbook.createSheet("Sheet1");
@SuppressWarnings("rawtypes")
ArrayList<ArrayList> dataList = data.getData();
for (int i = 0; i < dataList.size(); i++) {
Row row = sheet.createRow(i);
for (int j = 0; j < dataList.get(i).size(); j++) {
row.createCell(j);
if (onlyNumbers) {
row.getCell(j).setCellValue(Double.parseDouble((String) dataList.get(i).get(j)));
} else {
row.getCell(j).setCellValue((String) dataList.get(i).get(j));
}
}
}
workbook.write(fOutputStream);
workbook.close();
fOutputStream.close();
workbook = null;
file = null;
sheet = null;
} catch (EncryptedDocumentException | IOException e) {
e.printStackTrace();
}
}
public void format() {
wb = new XSSFWorkbook();
Map styles = createStyles(wb);
Sheet sheet = wb.createSheet("Loan Calculator");
sheet.setPrintGridlines(false);
sheet.setDisplayGridlines(false);
PrintSetup printSetup = sheet.getPrintSetup();
printSetup.setLandscape(true);
sheet.setFitToPage(true);
sheet.setHorizontallyCenter(true);
sheet.setColumnWidth(0, 768);
sheet.setColumnWidth(1, 768);
sheet.setColumnWidth(2, 2816);
sheet.setColumnWidth(3, 3584);
sheet.setColumnWidth(4, 3584);
sheet.setColumnWidth(5, 3584);
sheet.setColumnWidth(6, 3584);
Row titleRow = sheet.createRow(0);
titleRow.setHeightInPoints(35F);
for (int i = 1; i <= 7; i++)
titleRow.createCell(i).setCellStyle((CellStyle) styles.get("title"));
Cell titleCell = titleRow.getCell(2);
titleCell.setCellValue("Simple");
}
public void generateReport() throws IOException {
System.out.println("Test Report Gen");
Sheet sheet = workbook.getSheet("sheet1");
try {
String strSql = "Select * from participant;";
Sql db = new Sql();
ResultSet rs = db.executeQuery(strSql);
ResultSetMetaData rsMeta = rs.getMetaData();
Row row = sheet.createRow(0);
Cell cell;
// get table columns which will act as title columns
for (int i = 1; i <= rsMeta.getColumnCount(); i++) {
String colName = rsMeta.getColumnName(i);
int dataType = rsMeta.getColumnType(i);
cell = row.createCell(i - 1);
cell.setCellValue(colName);
}
int i = 1;
while (rs.next()) {
// create row to write data to
row = sheet.createRow(i);
// create cells for writing data to
for (int c = 1; c <= rsMeta.getColumnCount(); c++) {
cell = row.createCell(c - 1);
String colName = rsMeta.getColumnName(c);
cell.setCellValue(rs.getString(colName));
}
i++;
}
JOptionPane.showMessageDialog(null, "Excel Report of All Participants Generated");
} catch (SQLException ex) {
ex.printStackTrace();
// log Error
FacesFingerPrintProject.logger.log(Level.SEVERE, "ERROR", ex);
}
/// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("reportTest.xls");
workbook.write(fileOut);
fileOut.close();
}
/** @throws SQLException */
public void fillSheet() throws SQLException {
Row row = reportSheet.createRow(0);
Cell cell = null;
/** Setting head of report */
for (int i = 0; i < reportResultSet.getMetaData().getColumnCount(); i++) {
cell = row.createCell(i);
cell.setCellValue(reportResultSet.getMetaData().getColumnName(i + 1));
}
reportResultSet.first();
/** Filling data */
for (int i = 1; reportResultSet.next() && i <= DEFAULT_MAX_ROW_COUNT; i++) {
row = reportSheet.createRow(i);
for (int j = 0; j < reportResultSet.getMetaData().getColumnCount(); j++) {
cell = row.createCell(j);
cell.setCellValue(reportResultSet.getString(j + 1));
}
}
}
/**
* Main method for writing output of calculation
*
* @param outputFile path to output file
* @param finalList list with sorted containers and items
* @param sheet sheet to create
* @param lastRow last row for items of each container
*/
public void writeOutput(String outputFile) {
XSSFWorkbook outputBook = new XSSFWorkbook();
outputSheet = outputBook.createSheet();
outputSheet.setColumnWidth(0, 1300);
Row headingsRow = outputSheet.createRow(0);
setHeadings(headingsRow);
setValues();
writeToFile(outputFile, outputBook);
}
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);
}
}
protected Row getCurrentRow() {
synchronized (syncObj) {
Sheet s = getCurrentSheet();
Row r = s.getRow(rowPointer);
if (r == null) {
r = s.createRow(rowPointer);
}
return r;
}
}
public void appendToFile(Map<String, Double> content, String name) {
Sheet sheet = null;
sheet = workbook.createSheet();
Row head = sheet.createRow(0);
head.createCell(0).setCellValue(name);
head.createCell(1).setCellValue("Сумма");
int index = 1;
double sum = 0;
for (Map.Entry<String, Double> entry : content.entrySet()) {
Row row = sheet.createRow(index);
row.createCell(0).setCellValue(entry.getKey());
row.createCell(1).setCellValue(entry.getValue());
sum += entry.getValue();
index++;
}
Row sumRow = sheet.createRow(index);
sumRow.createCell(0).setCellValue("Итого");
sumRow.createCell(1).setCellValue(sum);
}
/**
* @see
* net.sourceforge.squirrel_sql.fw.gui.action.exportData.AbstractDataExportFileWriter#addHeaderCell(int,
* java.lang.String)
*/
@Override
protected void addHeaderCell(int colIdx, String columnName) throws Exception {
this.withHeader = true;
Row headerRow = sheet.getRow(0);
if (headerRow == null) {
headerRow = sheet.createRow(0);
}
Cell cell = headerRow.createCell(colIdx);
cell.setCellValue(columnName);
}
@Test
public void overrideFlushedRows() throws IOException {
Workbook wb = new SXSSFWorkbook(3);
try {
Sheet sheet = wb.createSheet();
sheet.createRow(1);
sheet.createRow(2);
sheet.createRow(3);
sheet.createRow(4);
thrown.expect(Throwable.class);
thrown.expectMessage(
"Attempting to write a row[1] in the range [0,1] that is already written to disk.");
sheet.createRow(1);
} finally {
wb.close();
}
}
/**
* Sheet initialization. We create as many rows as it is required to contain the crosstab.
*
* @param sheet The XLS sheet
* @param json The crosstab data (it must have been enriched with the calculateDescendants method)
* @throws JSONException
*/
public int initSheet(Sheet sheet, CrossTab cs) throws JSONException {
int columnsDepth = cs.getColumnsRoot().getSubTreeDepth();
int rowsNumber = cs.getRowsRoot().getSubTreeDepth();
// + 1 because there may be also the bottom row with the totals
int totalRowsNumber = columnsDepth + rowsNumber + 1;
for (int i = 0; i < totalRowsNumber + 4; i++) {
sheet.createRow(i);
}
return totalRowsNumber + 4;
}