[quote]导出数据[/quote]
[quote]导入xls/xlsx文件[/quote]
[quote]通用获取单元格格式化值[/quote]
@Override
public void exportExcelData(File file, PageIdxPagetion pagetion,
Map<String, Object> searchParam) {
List<ProdVo> list = null;
Workbook workbook;
try {
workbook = WorkbookFactory.create(FileUtils.openInputStream(file));
do {
list = prodVoService.list(searchParam, pagetion);
Sheet sheet = workbook.createSheet((pagetion.getItemIdx() + 1)
+ "-" + pagetion.getNextPageItemIdx());
Row row = sheet.createRow((int) 0);
Cell cell = row.createCell(0);
CellStyle style = workbook.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER); // 创建一个居中格式
style.setBorderBottom((short) 1);
style.setBorderRight((short) 1);
String[] header = new String[] { "ID", "商户名称", "店铺名称", "MCC代码",
"渠道编号", "产品编号", "商户属性", "线上线下标识0:线上轧差1.线下返佣" };
sheet.setColumnWidth(0, 30 * 256);
sheet.setColumnWidth(1, 50 * 256);
sheet.setColumnWidth(2, 50 * 256);
sheet.setColumnWidth(7, 30 * 256);
for (int j = 0; j < header.length; j++) {
cell = row.createCell((short) j);
cell.setCellValue(header[j]);
cell.setCellStyle(style);
}
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow((int) i + 1);
ProdVo prodVo = (ProdVo) list.get(i);
// 创建单元格,并设置值
cell = row.createCell((short) 0);
cell.setCellValue(prodVo.getId().toString());
cell = row.createCell((short) 1);
cell.setCellValue(prodVo.getCompName());
cell.setCellStyle(style);
cell = row.createCell((short) 2);
cell.setCellValue(prodVo.getShopName());
cell.setCellStyle(style);
cell = row.createCell((short) 3);
cell.setCellValue(prodVo.getMcc());
cell.setCellStyle(style);
cell = row.createCell((short) 4);
cell.setCellValue(prodVo.getChannelNo());
cell.setCellStyle(style);
cell = row.createCell((short) 5);
cell.setCellValue(prodVo.getProdNo());
cell.setCellStyle(style);
cell = row.createCell((short) 6);
String mchProp = "";
if (null != prodVo.getMchProp()) {
if (prodVo.getMchProp().ordinal() == 1) {
mchProp = "收单";
} else if (prodVo.getMchProp().ordinal() == 2) {
mchProp = "分期";
} else if (prodVo.getMchProp().ordinal() == 3) {
mchProp = "积分";
} else if (prodVo.getMchProp().ordinal() == 4) {
mchProp = "定向支付";
}
}
cell.setCellValue(mchProp);
cell = row.createCell((short) 7);
cell.setCellValue("");
CellStyle style1 = workbook.createCellStyle();
style1.setLocked(false);
style1.setFillBackgroundColor(IndexedColors.YELLOW
.getIndex());
cell.setCellStyle(style1);
}
sheet.protectSheet("123");
pagetion.setPageIdx(pagetion.getPageIdx() + 1);
} while (pagetion.getPageIdx() <= pagetion.getTotalPage());
FileOutputStream fout = FileUtils.openOutputStream(file);
workbook.write(fout);
fout.close();
logger.info("文件 写入完成" + file.getName());
} catch (EncryptedDocumentException e) {
logger.error("异常" + e.getMessage());
} catch (InvalidFormatException e) {
logger.error("异常" + e.getMessage());
} catch (IOException e) {
logger.error("文件流异常" + e.getMessage());
}
}
[quote]导入xls/xlsx文件[/quote]
@Override
public Boolean importExcel(String fileName) {
Workbook workbook = null;
File file = new File(localTmpPath + fileName);
logger.info("文件地址" + file.getPath());
// 标志量,任何校验错误都会被设置为true
boolean flag = false;
try {
workbook = WorkbookFactory.create(FileUtils.openInputStream(file));
int st = workbook.getNumberOfSheets();
for (int i = 0; i < st; i++) {
Sheet sheet = workbook.getSheetAt(i);
Drawing drw = sheet.createDrawingPatriarch();
Iterator<Row> rows = sheet.rowIterator(); // 获得第一个表单的迭代器
rows.next();// 排除表头
while (rows.hasNext()) {
Row row = rows.next(); // 获得行数据
ProdVo prodVo = new ProdVo();
Cell cell = row.getCell(0);
prodVo.setId(UUID.fromString(cell.getStringCellValue()));
Cell cell7 = row.getCell(7);
int flagValue = 0;
if (cell7.getCellType() == Cell.CELL_TYPE_STRING) {
if (StringUtils.isBlank(cell7.getStringCellValue())) {
createComment(drw, cell7, "线下标识为空");
flag = true;
} else {
try {
flagValue = Integer.parseInt(cell7
.getStringCellValue());
} catch (Exception e) {
createComment(drw, cell7, "数据不合法");
flag = true;
}
}
} else if (cell7.getCellType() == Cell.CELL_TYPE_NUMERIC) {
double value = cell7.getNumericCellValue();
flagValue = (int) value;
}
if (!flag) {
prodVo.setOnlineFlag(flagValue);
prodVoService.modify(prodVo);
}
}
}
FileOutputStream fout = FileUtils.openOutputStream(file);
workbook.write(fout);
fout.close();
} catch (Exception e) {
logger.error("读取文件错误" + fileName);
}
return flag;
}
private void createComment(Drawing drw, Cell cell, String string) {
Comment comment = drw.createCellComment(drw.createAnchor(0, 0, 0, 0,
cell.getColumnIndex() + 1, cell.getRowIndex(),
cell.getColumnIndex() + 2, cell.getRowIndex() + 1));
CreationHelper ch = cell.getSheet().getWorkbook().getCreationHelper();
comment.setString(ch.createRichTextString(string));
comment.setAuthor("admin");
cell.setCellComment(comment);
}
[quote]通用获取单元格格式化值[/quote]
private static String getCellContent(Cell cell) {
return FORMATTER.formatCellValue(cell);
}