/**
* @param newSheet the sheet to create from the copy.
* @param sheet the sheet to copy.
* @param copyStyle true copy the style.
*/
public static void copySheets(XSSFSheet newSheet, XSSFSheet sheet, boolean copyStyle) {
int maxColumnNum = 0;
Map<Integer, XSSFCellStyle> styleMap =
(copyStyle) ? new HashMap<Integer, XSSFCellStyle>() : null;
for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
XSSFRow srcRow = sheet.getRow(i);
XSSFRow destRow = newSheet.createRow(i);
if (srcRow != null) {
XSSFCopySheet.copyRow(sheet, newSheet, srcRow, destRow, styleMap);
if (srcRow.getLastCellNum() > maxColumnNum) {
maxColumnNum = srcRow.getLastCellNum();
}
}
}
for (int i = 0; i <= maxColumnNum; i++) {
newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
}
}
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;
}
@Override
public Workbook writeWorkbook(
Workbook workbook, String sheetTitle, List<List<String>> tableDataList, int headerNum) {
if (workbook == null) workbook = new XSSFWorkbook();
int exportRecordNum = tableDataList.size(); // 导出的总记录数
int tableStartRowNum = headerNum > 0 ? headerNum : 0; // 表体数据开始行数
int sheetNum =
exportRecordNum / (CommonConst.EXCEL_MAX_EXPORT_NUM - headerNum)
+ 1; // 工作表的页数
List<XSSFSheet> sheetList = new ArrayList<XSSFSheet>();
if (StringUtils.isEmpty(sheetTitle)) sheetTitle = "sheet";
for (int i = 0; i < sheetNum; i++) {
// 在Excel工作簿中建一工作表
String sTitle = sheetTitle + (i + 1);
XSSFSheet sheet = (XSSFSheet) workbook.createSheet(sTitle);
sheet.setSelected(true); // 设置工作薄为选中
sheet.setAutobreaks(true);
sheet.setPrintGridlines(true);
sheetList.add(sheet);
}
/** ***********************输出表头********************************* */
if (headerNum > 0) {
for (XSSFSheet sheet : sheetList) {
XSSFRow headRow = sheet.createRow(0);
headRow.setHeightInPoints(20);
for (int i = 0; i < headerNum; i++) {
List<String> headerRowDataList = tableDataList.get(i);
for (int j = 0; j < headerRowDataList.size(); j++) {
XSSFCellStyle cellStyle = (XSSFCellStyle) createDefHeaderCellStyle(workbook); // 默认表头样式
createCell(headRow, j, headerRowDataList.get(j), cellStyle);
}
}
// 固定表头
sheet.createFreezePane(0, 1);
}
}
/** *********************输出表体内容************************* */
// 设置列样式
XSSFCellStyle columnStyle = (XSSFCellStyle) workbook.createCellStyle();
columnStyle.setFillBackgroundColor(HSSFColor.GREEN.index);
columnStyle.setWrapText(true);
if (tableDataList.size() > (tableStartRowNum + 1)) {
for (int i = tableStartRowNum; i < exportRecordNum; i++) {
List<String> rowDataList = tableDataList.get(i);
XSSFRow row = null;
int currentSheet = i / CommonConst.EXCEL_MAX_EXPORT_NUM; // 当前工作表的页数
XSSFSheet sheet = sheetList.get(currentSheet);
int rowIndex = i - CommonConst.EXCEL_MAX_EXPORT_NUM * currentSheet;
row = sheet.createRow(rowIndex);
for (int colIndex = 0, colLength = rowDataList.size(); colIndex < colLength; colIndex++) {
createCell(row, colIndex, rowDataList.get(colIndex));
}
}
}
// 调整列的宽度(取第一列为基准)
for (XSSFSheet sheet : sheetList) {
for (int i = 0; i < tableDataList.get(0).size(); i++) {
sheet.autoSizeColumn((short) i);
sheet.setColumnWidth((short) i, (short) (sheet.getColumnWidth((short) i) + 1000));
}
}
return workbook;
}
/**
* Set the width (in units of 1/256th of a character width)
*
* <p>The maximum column width for an individual cell is 255 characters. This value represents the
* number of characters that can be displayed in a cell that is formatted with the standard font.
*
* @param columnIndex - the column to set (0-based)
* @param width - the width in units of 1/256th of a character width
*/
public void setColumnWidth(int columnIndex, int width) {
_sh.setColumnWidth(columnIndex, width);
}
public static boolean writeMultArray2XLSXFile(
String fileName,
List<List<String[]>> arrys,
List<String> sheetLabels,
boolean hasHead,
int indexKey)
throws Exception {
if (arrys.isEmpty()) {
System.err.println("No input data!");
return false;
}
XSSFWorkbook wb = new XSSFWorkbook();
XSSFCellStyle headStyle = wb.createCellStyle();
// apply custom font to the text in the comment
XSSFFont font = wb.createFont();
font.setFontName("Courier New");
font.setFontHeightInPoints((short) 10);
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
font.setColor(HSSFColor.RED.index);
headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
headStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
headStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
headStyle.setLocked(true);
headStyle.setFont(font);
XSSFCellStyle bodyStyle = wb.createCellStyle();
bodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
bodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
bodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
XSSFCellStyle markedBodyStyle = wb.createCellStyle();
markedBodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
markedBodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
markedBodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
markedBodyStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
markedBodyStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
String lastKey = null;
int switcher = -1;
XSSFCell cell = null;
String[] titleNames = null;
int d = 0;
for (List<String[]> arry : arrys) {
XSSFSheet sheet1 = wb.createSheet(sheetLabels.get(d));
if (hasHead) {
titleNames = (String[]) arry.get(0);
}
int columnNum = ((String[]) arry.get(0)).length;
for (int i = 0; i < columnNum; i++) {
sheet1.setColumnWidth(i, (short) ((30 * 6) / ((double) 1 / 20)));
}
int rowIndex = 0;
// create titile row
XSSFRow row = sheet1.createRow(rowIndex);
if (titleNames != null) {
for (int i = 0; i < columnNum; i++) {
cell = row.createCell(i);
cell.setCellValue(titleNames[i]);
cell.setCellStyle(headStyle);
}
rowIndex++;
}
int rowNum = arry.size();
for (int i = rowIndex; i < rowNum; i++) {
row = sheet1.createRow((i));
String[] line = (String[]) arry.get(i);
columnNum = line.length;
if (indexKey >= 0) {
if (lastKey == null && line[indexKey] != null) {
lastKey = line[indexKey];
switcher *= -1;
} else if (lastKey != null && line[indexKey] == null) {
lastKey = line[indexKey];
switcher *= -1;
} else if (lastKey == null && line[indexKey] == null) {
} else {
if (!lastKey.equals(line[indexKey])) {
switcher *= -1;
lastKey = line[indexKey];
}
}
} else {
switcher = 1;
}
for (int j = 0; j < columnNum; j++) {
cell = row.createCell(j);
if (switcher > 0) {
cell.setCellStyle(bodyStyle);
} else {
cell.setCellStyle(markedBodyStyle);
}
if (line[j] != null) {
if (Util.isNumeric(line[j])) {
// org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC
cell.setCellType(0);
cell.setCellValue(Double.parseDouble(line[j]));
} else {
cell.setCellValue(line[j]);
}
} else {
cell.setCellValue(".");
}
}
}
d++;
}
// Write the output to a inFile
FileOutputStream fileOut = new FileOutputStream(fileName);
wb.write(fileOut);
fileOut.close();
return true;
}
public static boolean convertTextFile2XLSXFile(
String inFileName, String outFileName, boolean hasHead, int indexKey) throws Exception {
BufferedReader br = LocalFileFunc.getBufferedReader(inFileName);
String line = br.readLine();
if (line == null) {
return false;
}
String[] cells1 = Util.tokenize(line, '\t');
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet1 = wb.createSheet("Data");
int columnNum = cells1.length;
for (int i = 0; i < columnNum; i++) {
sheet1.setColumnWidth(i, (short) ((30 * 6) / ((double) 1 / 20)));
}
XSSFCellStyle headStyle = wb.createCellStyle();
// apply custom font to the text in the comment
XSSFFont font = wb.createFont();
font.setFontName("Courier New");
font.setFontHeightInPoints((short) 10);
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
font.setColor(HSSFColor.RED.index);
headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
headStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
headStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
headStyle.setLocked(true);
headStyle.setFont(font);
XSSFCellStyle bodyStyle = wb.createCellStyle();
bodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
bodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
bodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
XSSFCellStyle markedBodyStyle = wb.createCellStyle();
markedBodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
markedBodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
markedBodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
markedBodyStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
markedBodyStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
int rowIndex = 0;
// create titile row
XSSFRow row = sheet1.createRow(rowIndex);
String lastKey = null;
int switcher = -1;
XSSFCell cell = null;
if (hasHead) {
for (int i = 0; i < columnNum; i++) {
cell = row.createCell(i);
cell.setCellValue(cells1[i]);
cell.setCellStyle(headStyle);
}
} else {
for (int i = 0; i < columnNum; i++) {
cell = row.createCell(i);
cell.setCellValue(cells1[i]);
cell.setCellStyle(bodyStyle);
}
}
rowIndex++;
while ((line = br.readLine()) != null) {
cells1 = Util.tokenize(line, '\t');
row = sheet1.createRow((rowIndex));
columnNum = cells1.length;
if (indexKey >= 0) {
if (lastKey == null && cells1[indexKey] != null) {
lastKey = cells1[indexKey];
switcher *= -1;
} else if (lastKey != null && cells1[indexKey] == null) {
lastKey = cells1[indexKey];
switcher *= -1;
} else if (lastKey == null && cells1[indexKey] == null) {
} else {
if (!lastKey.equals(cells1[indexKey])) {
switcher *= -1;
lastKey = cells1[indexKey];
}
}
} else {
switcher = 1;
}
// System.out.println(cells1[0]);
for (int j = 0; j < columnNum; j++) {
cell = row.createCell(j);
if (switcher > 0) {
cell.setCellStyle(bodyStyle);
} else {
cell.setCellStyle(markedBodyStyle);
}
if (cells1[j] != null) {
if (Util.isNumeric(cells1[j])) {
// org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC
cell.setCellType(0);
cell.setCellValue(Double.parseDouble(cells1[j]));
} else {
cell.setCellValue(cells1[j]);
}
} else {
cell.setCellValue(".");
}
}
rowIndex++;
}
br.close();
// Write the output to a inFile
FileOutputStream fileOut = new FileOutputStream(outFileName);
wb.write(fileOut);
fileOut.close();
return true;
}
public static boolean writeArray2XLSXSheet(
XSSFSheet sheet1, XSSFWorkbook wb, List<String[]> arry, boolean hasHead) throws Exception {
int rowNum = arry.size();
if (rowNum == 0) {
System.err.println("No input data!");
return false;
}
String[] titleNames = null;
if (hasHead) {
titleNames = (String[]) arry.get(0);
}
int columnNum = ((String[]) arry.get(0)).length;
for (int i = 0; i < columnNum; i++) {
sheet1.setColumnWidth((short) i, (short) ((30 * 8) / ((double) 1 / 20)));
}
XSSFFont font = wb.createFont();
font.setFontName("Courier New");
font.setFontHeightInPoints((short) 10);
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
font.setColor(HSSFColor.RED.index);
XSSFCellStyle headStyle = wb.createCellStyle();
headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
headStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
headStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
headStyle.setLocked(true);
headStyle.setFont(font);
XSSFCellStyle bodyStyle = wb.createCellStyle();
bodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
bodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
bodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
XSSFCellStyle markedBodyStyle = wb.createCellStyle();
markedBodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
markedBodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
markedBodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
markedBodyStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
markedBodyStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
int rowIndex = 0;
// create titile row
XSSFRow row = sheet1.createRow(rowIndex);
XSSFCell cell = null;
if (titleNames != null) {
for (int i = 0; i < columnNum; i++) {
cell = row.createCell((short) i);
cell.setCellValue(titleNames[i]);
cell.setCellStyle(headStyle);
}
rowIndex++;
}
for (int i = rowIndex; i < rowNum; i++) {
row = sheet1.createRow((i));
String[] line = (String[]) arry.get(i);
columnNum = line.length;
for (int j = 0; j < columnNum; j++) {
cell = row.createCell(j);
if (line[0] != null) {
cell.setCellStyle(markedBodyStyle);
} else {
cell.setCellStyle(bodyStyle);
}
if (line[j] != null) {
if (Util.isNumeric(line[j])) {
// org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC
cell.setCellType(0);
cell.setCellValue(Double.parseDouble(line[j]));
} else {
cell.setCellValue(line[j]);
}
} else {
cell.setCellValue("");
}
}
}
return true;
}
@Override
public byte[] getImportTemplate(Boolean editProductType) throws IOException {
XSSFWorkbook workBook = new XSSFWorkbook();
XSSFSheet productSheet = workBook.createSheet(PRODUCT_SHEETNAME);
XSSFSheet typeSheet = createTypeSheet(workBook);
if (!(true == editProductType)) {
workBook.setSheetHidden(
workBook.getSheetIndex(typeSheet), XSSFWorkbook.SHEET_STATE_VERY_HIDDEN);
}
XSSFRow titleRow = productSheet.createRow(0);
List<TransformSetting> transSettings = new TransformSettingFactory().getProductSetting();
XSSFDataValidationHelper helper = new XSSFDataValidationHelper(productSheet);
for (int i = 0; i < transSettings.size(); i++) {
TransformSetting setting = transSettings.get(i);
XSSFCell titleCell = null;
titleCell = titleRow.createCell(i, Cell.CELL_TYPE_STRING);
String cellValue = setting.getDispName();
titleCell.setCellValue(cellValue);
productSheet.autoSizeColumn(i, true);
int columnWidth = productSheet.getColumnWidth(i);
int needWidth = cellValue.getBytes().length * 256;
if (columnWidth < needWidth) {
productSheet.setColumnWidth(i, needWidth);
}
if ("productTypeName".equals(setting.getPropertyName())) {
Name type = workBook.createName();
String name = "type";
type.setNameName(name); // 定义名称
type.setRefersToFormula(
"OFFSET("
+ typeSheet.getSheetName()
+ "!$A$3,0,0,COUNTA("
+ typeSheet.getSheetName()
+ "!$A:$A)-2)");
XSSFDataValidationConstraint constraint =
(XSSFDataValidationConstraint) helper.createFormulaListConstraint(name); // 使用定义的名称
CellRangeAddressList regions = new CellRangeAddressList(1, 10, i, i);
DataValidation dataValidation = helper.createValidation(constraint, regions);
productSheet.addValidationData(dataValidation);
} else if ("productTypeItem".equals(setting.getPropertyName())) { // item是通过查询前面的名称来自动取得
for (int j = 0; j < 10; j++) {
XSSFRow row = productSheet.createRow(j + 1);
XSSFCell itemCell = row.createCell(i, Cell.CELL_TYPE_STRING);
int cellnum = j + 2;
itemCell.setCellFormula(
"IF(A"
+ cellnum
+ "=\"\",\"\",VLOOKUP(A"
+ cellnum
+ ",OFFSET(productType!$A$3,,,COUNTA(productType!$A:$A),2),2,0))");
}
} else if ("costMethod".equals(setting.getPropertyName())) {
int len = CostMethod.values().length;
String[] costNames = new String[len];
for (int ci = 0; ci < len; ci++) {
costNames[ci] = CostMethod.values()[ci].getMethodName();
}
// XSSFDataValidationHelper helper = new XSSFDataValidationHelper(productSheet);
XSSFDataValidationConstraint constraint =
(XSSFDataValidationConstraint) helper.createExplicitListConstraint(costNames);
CellRangeAddressList addressList = new CellRangeAddressList(1, 10, i, i);
XSSFDataValidation validation =
(XSSFDataValidation) helper.createValidation(constraint, addressList);
validation.setShowErrorBox(true);
productSheet.addValidationData(validation);
}
}
workBook.setActiveSheet(workBook.getSheetIndex(productSheet));
ByteArrayOutputStream baos = new ByteArrayOutputStream(1024);
workBook.write(baos);
return baos.toByteArray();
}