@Override
public void writeRow(List<?> row) {
Row record = sheet.createRow(sheet.getLastRowNum() + 1);
for (int i = 0; i < row.size(); i++) {
Cell cell = record.createCell(i);
Object value = row.get(i);
if (value == null) {
cell.setCellValue("");
} else if (value instanceof String) {
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue((String) value);
} else if (value instanceof Number) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(converters.convert(value, Double.class));
} else if (value instanceof Date || value instanceof DateTime || value instanceof Calendar) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellStyle(dateCellStyle);
cell.setCellValue(converters.convert(value, Date.class));
} else if (value instanceof Boolean) {
cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
cell.setCellValue((Boolean) value);
} else {
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(converters.convert(value, String.class));
}
}
}
@Override
public void writeHeader() {
if (header != null) {
Row record = sheet.createRow(0);
for (int i = 0; i < header.size(); i++) {
Cell cell = record.createCell(i);
cell.setCellValue(header.get(i));
}
}
}
private void saveExcelPoject(File file) throws IOException {
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("timeplan");
// Заголовок в 0 строке
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("Филиал");
cell = row.createCell(1);
cell.setCellValue("Город");
Calendar cal = Calendar.getInstance();
cal.set(2017, 0, 5); // Начальная дата проекта
SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yy");
for (int i = 0; i < 3 * 52; i++) { // Счетчик по неделям
cell = row.createCell(i + 2);
cell.setCellValue(sdf.format(cal.getTime()));
cal.add(Calendar.WEEK_OF_YEAR, 1); // Следующая неделя
}
// sheet.setColumnWidth(0, 256);
// Цвета ячеек
CellStyle[] styles = new CellStyle[6];
styles[0] = wb.createCellStyle();
styles[0].setFillForegroundColor(HSSFColor.RED.index);
styles[0].setFillPattern(FillPatternType.SOLID_FOREGROUND);
styles[1] = wb.createCellStyle();
styles[1].setFillForegroundColor(HSSFColor.GREEN.index);
styles[1].setFillPattern(FillPatternType.SOLID_FOREGROUND);
styles[2] = wb.createCellStyle();
styles[2].setFillForegroundColor(HSSFColor.BLUE.index);
styles[2].setFillPattern(FillPatternType.SOLID_FOREGROUND);
styles[3] = wb.createCellStyle();
styles[3].setFillForegroundColor(HSSFColor.ROSE.index);
styles[3].setFillPattern(FillPatternType.SOLID_FOREGROUND);
styles[4] = wb.createCellStyle();
styles[4].setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
styles[4].setFillPattern(FillPatternType.SOLID_FOREGROUND);
styles[5] = wb.createCellStyle();
styles[5].setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
styles[5].setFillPattern(FillPatternType.SOLID_FOREGROUND);
short rowIdx = 0;
for (Region region : this.regions) {
row = sheet.createRow(++rowIdx);
cell = row.createCell(0);
cell.setCellValue(region.filial);
cell = row.createCell(1);
cell.setCellValue(region.name);
cal = Calendar.getInstance();
cal.set(2017, 0, 5); // Начальная дата проекта
for (int i = 0; i < 3 * 52; i++) { // Счетчик по неделям
short color = region.getDateColorIndex(cal.getTime());
if (color >= 0) {
cell = row.createCell(i + 2);
cell.setCellStyle(styles[color]);
}
cal.add(Calendar.WEEK_OF_YEAR, 1); // Следующая неделя
}
}
try (FileOutputStream fileOut = new FileOutputStream(file)) {
wb.write(fileOut);
}
}
private Workbook handleExcel(List objs, Class clz, boolean isXssf, String message) {
XSSFWorkbook wb = null;
try {
if (isXssf) {
XSSFWorkbook w = new XSSFWorkbook();
} else {
HSSFWorkbook w = new HSSFWorkbook();
}
wb = new XSSFWorkbook();
XSSFDataFormat format = wb.createDataFormat();
XSSFSheet sheet = wb.createSheet(message + "备份记录"); // 取excel工作表对象
XSSFCellStyle cellStyle = wb.createCellStyle(); // 设置excel单元格样式
XSSFCellStyle passwordCellStyle = wb.createCellStyle(); // 设置密码单元格样式
XSSFDataFormat passwordFormat = wb.createDataFormat();
passwordCellStyle.setDataFormat(passwordFormat.getFormat(";;;"));
List<ExcelHeader> headers = getHeaderList(clz);
Collections.sort(headers);
sheet.addMergedRegion(new CellRangeAddress(0, (short) 0, 0, (short) (headers.size() - 1)));
Row r0 = sheet.createRow(0);
Cell cell = r0.createCell(0);
r0.setHeightInPoints(28);
cell.setCellValue(message + "备份记录");
Row r = sheet.createRow(1);
r.setHeightInPoints(25);
cell.setCellStyle(cellStyle);
// 输出标题
for (int i = 0; i < headers.size(); i++) {
Cell cell1 = r.createCell(i);
if (headers.get(i).getTitle().equals("密码")) cell1.setCellStyle(passwordCellStyle);
else cell1.setCellStyle(cellStyle);
cell1.setCellValue(headers.get(i).getTitle());
}
Object obj = null;
// 输出用户资料信息
if (message.indexOf("用户资料 ") > 0) {
sheet.setColumnWidth(3, 32 * 150);
sheet.setColumnWidth(4, 32 * 110);
sheet.setColumnWidth(7, 32 * 120);
for (int i = 0; i < objs.size(); i++) {
r = sheet.createRow(i + 2);
obj = objs.get(i);
for (int j = 0; j < headers.size(); j++) {
Cell cell2 = r.createCell(j);
copyDefaultCellStyle(null, cell2, cellStyle, 0);
if (getMethodName(headers.get(j)).equals("nabled"))
cell2.setCellValue(BeanUtils.getProperty(obj, "enabled"));
else if (getMethodName(headers.get(j)).equals("password")) {
cell2.setCellStyle(passwordCellStyle);
cell2.setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));
} else cell2.setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));
}
}
}
// 输出房间使用信息数据
else {
sheet.setColumnWidth(0, 32 * 80);
sheet.setColumnWidth(2, 32 * 100);
sheet.setColumnWidth(3, 32 * 190);
sheet.setColumnWidth(4, 32 * 190);
sheet.setColumnWidth(5, 32 * 190);
sheet.setColumnWidth(10, 32 * 130);
for (int i = 0; i < objs.size(); i++) {
r = sheet.createRow(i + 2);
obj = objs.get(i);
for (int j = 0; j < headers.size(); j++) {
Cell cell2 = r.createCell(j);
if (j == 3 || j == 4 || j == 5) {
XSSFCellStyle cs3 = wb.createCellStyle();
cell2.setCellValue(new Date());
copyDefaultCellStyle(format, cell2, cs3, 1);
}
if (j == 10) {
XSSFCellStyle cs2 = wb.createCellStyle();
copyDefaultCellStyle(format, cell2, cs2, 2);
}
copyDefaultCellStyle(null, cell2, cellStyle, 0);
cell2.setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));
}
}
}
// 设置行列的默认宽度和高度
} catch (IllegalAccessException e) {
e.printStackTrace();
logger.error(e);
} catch (InvocationTargetException e) {
e.printStackTrace();
logger.error(e);
} catch (NoSuchMethodException e) {
e.printStackTrace();
logger.error(e);
}
return wb;
}
public File generateXLSResponse(QueryResult queryResult, Locale locale, String baseURL) {
File excelFile = new File("export_parts.xls");
// Blank workbook
XSSFWorkbook workbook = new XSSFWorkbook();
// Create a blank sheet
XSSFSheet sheet = workbook.createSheet("Parts Data");
String header = StringUtils.join(queryResult.getQuery().getSelects(), ";");
String[] columns = header.split(";");
Map<Integer, String[]> data = new HashMap<>();
String[] headerFormatted = createXLSHeaderRow(header, columns, locale);
data.put(1, headerFormatted);
Map<Integer, String[]> commentsData = new HashMap<>();
String[] headerComments = createXLSHeaderRowComments(header, columns);
commentsData.put(1, headerComments);
List<String> selects = queryResult.getQuery().getSelects();
int i = 1;
for (QueryResultRow row : queryResult.getRows()) {
i++;
data.put(i, createXLSRow(selects, row, baseURL));
commentsData.put(i, createXLSRowComments(selects, row));
}
// Iterate over data and write to sheet
Set<Integer> keyset = data.keySet();
int rownum = 0;
for (Integer key : keyset) {
Row row = sheet.createRow(rownum++);
String[] objArr = data.get(key);
int cellnum = 0;
for (String obj : objArr) {
Cell cell = row.createCell(cellnum++);
cell.setCellValue(obj);
}
CreationHelper factory = workbook.getCreationHelper();
Drawing drawing = sheet.createDrawingPatriarch();
String[] commentsObjArr = commentsData.get(key);
cellnum = 0;
for (String commentsObj : commentsObjArr) {
if (commentsObj.length() > 0) {
Cell cell = row.getCell(cellnum) != null ? row.getCell(cellnum) : row.createCell(cellnum);
// When the comment box is visible, have it show in a 1x3 space
ClientAnchor anchor = factory.createClientAnchor();
anchor.setCol1(cell.getColumnIndex());
anchor.setCol2(cell.getColumnIndex() + 1);
anchor.setRow1(row.getRowNum());
anchor.setRow2(row.getRowNum() + 1);
Comment comment = drawing.createCellComment(anchor);
RichTextString str = factory.createRichTextString(commentsObj);
comment.setString(str);
// Assign the comment to the cell
cell.setCellComment(comment);
}
cellnum++;
}
}
// Define header style
Font headerFont = workbook.createFont();
headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
headerFont.setFontHeightInPoints((short) 10);
headerFont.setFontName("Courier New");
headerFont.setItalic(true);
headerFont.setColor(IndexedColors.WHITE.getIndex());
CellStyle headerStyle = workbook.createCellStyle();
headerStyle.setFont(headerFont);
headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
// Set header style
for (int j = 0; j < columns.length; j++) {
Cell cell = sheet.getRow(0).getCell(j);
cell.setCellStyle(headerStyle);
if (cell.getCellComment() != null) {
String comment = cell.getCellComment().getString().toString();
if (comment.equals(QueryField.CTX_PRODUCT_ID)
|| comment.equals(QueryField.CTX_SERIAL_NUMBER)
|| comment.equals(QueryField.PART_MASTER_NUMBER)) {
for (int k = 0; k < queryResult.getRows().size(); k++) {
Cell grayCell =
sheet.getRow(k + 1).getCell(j) != null
? sheet.getRow(k + 1).getCell(j)
: sheet.getRow(k + 1).createCell(j);
grayCell.setCellStyle(headerStyle);
}
}
}
}
try {
// Write the workbook in file system
FileOutputStream out = new FileOutputStream(excelFile);
workbook.write(out);
out.close();
} catch (Exception e) {
LOGGER.log(Level.FINEST, null, e);
}
return excelFile;
}
