引用
导出数据
@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());
}
}
引用
导入xls/xlsx文件
@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);
}
引用
通用获取单元格格式化值
private static String getCellContent(Cell cell) {
return FORMATTER.formatCellValue(cell);
}
分享到:
相关推荐
matlab导入excel数据教程
PB中导入EXCEL数据
批量导入Excel数据到数据库,.NET开发,带数据校验、事务回滚、Excel预览、日志功能的完整开发实例
C#导入excel数据
在.net与EXCEL数据的导入导出,在WINFORM中导入EXCEL数据。
Developer导入Excel数据
实现从Excel中读取数据并展示在MATLAB表格控件中
图解运用PLSQL工具导入Excel数据
EXCEL数据导入EXCEL数据导入EXCEL数据导入
java 多线程导入excel数据,预防高并发,线程同步锁,
2.angular-cli.json更新引用路径 "apps": [ { "root": "src", "outDir": "dist", "scripts": [ "assets/xlsx/xlsx.full.min.js" ], } ], 3. *.component.ts引用 declare let XSX; @Component({ ...
C#将数据导入excel和Excel数据导入数据库使用的是SQL Server数据库
matlab导入excel数据 在MATLAB中导入Excel数据,你可以使用几种不同的方法。下面是两种常见的方法: 方法一:使用readtable函数 readtable函数允许你导入Excel文件中的数据,并将其存储为表格。 方法二:使用xlsread...
摘要:在程序编制过程和数据汇总交换过程中,经常会碰到需要将其他人员在office办公环境下编制的文件数据内容导入oracle中的情况。目前程序开发者经常使用的方法有如下几种:1,使用oracle提供的导入工具sql*loader...
C# 将Excel文件的数据表导入至DataSet,并可以指定各列对应的列名.
excel宏导入excel数据经典
使用aspose.cell 导入excel数据到数据库
自己整理的利用c#写的excel数据导入到oracle11G数据库,100%可用,里面包含程序demo和excel表格、建表的sql,需要学习和借鉴的朋友可以下载。
ArcGis导入EXCEL数据,join之后为什么是NULL.doc
NULL 博文链接:https://forlab.iteye.com/blog/1821162