导入成功后:
导入时格式错误提示:
当导入模板错时提示
当标题有错误时提示
直接上代码:
package org.jeecgframework.web.cgform.service.excel;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import javax.annotation.PostConstruct;
import javax.swing.JOptionPane;
import org.hibernate.validator.xml.GetterType;
import org.jeecgframework.web.cgform.entity.config.CgFormFieldEntity;
import org.jeecgframework.web.cgreport.controller.core.CgReportController;
import org.jeecgframework.web.cgreport.service.core.CgReportServiceI;
import org.jeecgframework.web.system.pojo.base.DictEntity;
import org.jeecgframework.web.system.service.SystemService;
import org.jeecgframework.web.system.service.impl.SystemServiceImpl;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jeecgframework.core.common.exception.BusinessException;
import org.jeecgframework.core.constant.SysConstant;
import org.jeecgframework.core.util.StringUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
/**
* 模版导出工具类
*
* @author huiyong
*
*/
@Component
public class ExcelTempletService {
// 默认列宽
// TODO 慧勇 从数据库或者配置文件设置默认列宽
private static int maxColumnWidth = 30;
private static int minColumnWidth = 12;
// TODO 文件生成的空行太多的话，填写文件又没有填写满的话，空行数据在导入的时候会导入空数据进数据库，
private static int defaultBlankRow = 1;
@Autowired
private SystemService service;
private static ExcelTempletService excelTempletService;
@PostConstruct
public void init() {
excelTempletService = this;
excelTempletService.service = this.service;
}
/**
* 导出excel模版
*
* @param title
* @param titleSet
* @param datalist
* @return
*/
public static HSSFWorkbook exportExcel(String title, Collection<?> dataSet, List<Map<String, Object>> datalist) {
// 使用userModel模式实现的，当excel文档出现10万级别的大数据文件可能导致OOM内存溢出
return exportExcelInUserModel2File2(title, dataSet, datalist);
}
@SuppressWarnings("unchecked")
private static HSSFWorkbook exportExcelInUserModel2File(String title, Collection<?> dataSet, List<Map<String, Object>> datalist) {
// 声明一个工作薄
HSSFWorkbook workbook = null;
try {
// 首先检查数据看是否是正确的
if (dataSet == null || dataSet.size() == 0) {
throw new Exception("导出数据为空！");
}
if (title == null) {
throw new Exception("传入参数不能为空！");
}
// 声明一个工作薄
workbook = new HSSFWorkbook();
// 生成一个表格
Sheet sheet = workbook.createSheet(title);
int index = 0;
// 产生表格标题行
Row row = sheet.createRow(index);
row.setHeight((short) 450);
CellStyle titleStyle = getTitleStyle(workbook);
Iterator it = dataSet.iterator();
while (it.hasNext()) {
CgFormFieldEntity type = (CgFormFieldEntity) it.next();
// 输入需要显示的字段信息
if (type.getIsExport().equals("Y")) {
Cell cell = row.createCell(index);
RichTextString text = new HSSFRichTextString(type.getContent());
cell.setCellValue(text);
// 设置列宽
int columnWidth = type.getLength() == 0 ? minColumnWidth : type.getLength() > maxColumnWidth ? maxColumnWidth : type.getLength();
sheet.setColumnWidth(index, 256 * columnWidth);
cell.setCellStyle(titleStyle);
index++;
// 标题列下方默认给三行的边框空位置
}
setBlankRows(defaultBlankRow, index, workbook);
}
for (int i = 0; i < datalist.size(); i++) {
it = dataSet.iterator();
row = sheet.createRow(i + 1);
index = 0;
while (it.hasNext()) {
CgFormFieldEntity type = (CgFormFieldEntity) it.next();
// 输入需要显示的字段信息
if (type.getIsExport().equals("Y")) {
Cell cell = row.createCell(index);
if (datalist.get(i).get(type.getFieldName()) != null) {
RichTextString text = new HSSFRichTextString(datalist.get(i).get(type.getFieldName()).toString());
cell.setCellValue(text);
}
// 标题列下方默认给三行的边框空位置
index++;
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
return workbook;
}
@SuppressWarnings("unchecked")
private static HSSFWorkbook exportExcelInUserModel2File2(String title, Collection<?> dataSet, List<Map<String, Object>> datalist) {
// 声明一个工作薄
HSSFWorkbook workbook = null;
try {
// 首先检查数据看是否是正确的
if (dataSet == null || dataSet.size() == 0) {
throw new Exception("导出数据为空！");
}
if (title == null) {
throw new Exception("传入参数不能为空！");
}
// 声明一个工作薄
workbook = new HSSFWorkbook();
// 生成一个表格
Sheet sheet = workbook.createSheet(title);
int index = 0;
// 产生表格标题行
Row row = sheet.createRow(index);
row.setHeight((short) 450);
CellStyle titleStyle = getTitleStyle(workbook);
Iterator it = dataSet.iterator();
while (it.hasNext()) {
CgFormFieldEntity type = (CgFormFieldEntity) it.next();
// 输入需要显示的字段信息
//if (type.getIsShow().equals("Y")) {
Cell cell = row.createCell(index);
RichTextString text = new HSSFRichTextString(type.getContent());
cell.setCellValue(text);
// 设置列宽
int columnWidth = type.getLength() == 0 ? minColumnWidth : type.getLength() > maxColumnWidth ? maxColumnWidth : type.getLength();
sheet.setColumnWidth(index, 256 * columnWidth);
cell.setCellStyle(titleStyle);
index++;
// 标题列下方默认给三行的边框空位置
//}
}
for (int i = 0; i < datalist.size(); i++) {
it = dataSet.iterator();
row = sheet.createRow(i + 1);
index = 0;
while (it.hasNext()) {
CgFormFieldEntity type = (CgFormFieldEntity) it.next();
// 输入需要显示的字段信息
//if (type.getIsShow().equals("Y")) {
Cell cell = row.createCell(index);
if (datalist.get(i).get(type.getFieldName()) != null) {
RichTextString text = new HSSFRichTextString(datalist.get(i).get(type.getFieldName()).toString());
cell.setCellValue(text);
}
// 标题列下方默认给三行的边框空位置
index++;
//}
}
}
} catch (Exception e) {
e.printStackTrace();
}
return workbook;
}
/**
* exce表头单元格样式处理
*
* @param workbook
* @return
*/
public static HSSFCellStyle getTitleStyle(HSSFWorkbook workbook) {
// 产生Excel表头
HSSFCellStyle titleStyle = workbook.createCellStyle();
titleStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE); // 设置边框样式
titleStyle.setBorderLeft((short) 2); // 左边框
titleStyle.setBorderRight((short) 2); // 右边框
titleStyle.setBorderTop((short) 2); // 左边框
titleStyle.setBorderBottom((short) 2); // 右边框
titleStyle.setBorderTop(HSSFCellStyle.BORDER_DOUBLE); // 顶边框
titleStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index); // 填充的背景颜色
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 填充图案
return titleStyle;
}
// 生成多少行的带有比边框的空行
public static void setBlankRows(int rows, int columns, HSSFWorkbook workbook) {
// 得到第一页
Sheet sheet = workbook.getSheetAt(0);
// 样式
CellStyle cellStyle = getOneStyle(workbook);
for (int i = 1; i <= rows; i++) {
Row row = sheet.createRow(i);
for (int j = 0; j < columns; j++) {
row.createCell(j).setCellStyle(cellStyle);
}
}
}
public static HSSFCellStyle getTwoStyle(HSSFWorkbook workbook) {
// 产生Excel表头
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderLeft((short) 1); // 左边框
style.setBorderRight((short) 1); // 右边框
style.setBorderBottom((short) 1);
style.setBorderTop((short) 1);
style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index); // 填充的背景颜色
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 填充图案
return style;
}
public static HSSFCellStyle getOneStyle(HSSFWorkbook workbook) {
// 产生Excel表头
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderLeft((short) 1); // 左边框
style.setBorderRight((short) 1); // 右边框
style.setBorderBottom((short) 1);
style.setBorderTop((short) 1);
return style;
}
/**
* 导入 excel
*
* @param inputstream
* : 文件输入流
* @param pojoClass
* : 对应的导入对象 (每行记录)
* @return
*/
public static Collection importExcelByIs(InputStream inputstream, List<CgFormFieldEntity> lists) {
Map<String, CgFormFieldEntity> fieldMap = ConvertDate(lists);
// 返回的数据类型
List<Map<String, Object>> tObject = new ArrayList<Map<String, Object>>();
try {
// 将传入的File构造为FileInputStream;
// // 得到工作表
HSSFWorkbook book = new HSSFWorkbook(inputstream);
// // 得到第一页
HSSFSheet sheet = book.getSheetAt(0);
// // 得到第一面的所有行
Iterator<Row> row = sheet.rowIterator();
// 得到第一行，也就是标题行
Row title = row.next();
// 得到第一行的所有列
Iterator<Cell> cellTitle = title.cellIterator();
// 将标题的文字内容放入到一个map中。
Map titlemap = new HashMap();
// 从标题第一列开始
int i = 0;
// 循环标题所有的列
while (cellTitle.hasNext()) {
Cell cell = cellTitle.next();
String value = cell.getStringCellValue();
if (fieldMap.get(value) == null) {
throw new BusinessException("导入数据excel列名有不能识别的列");
}
titlemap.put(i, value);
i = i + 1;
}
// 用来格式化日期的DateFormat
Map<String, Object> retMap = null;
while (row.hasNext()) {
retMap = new HashMap<String, Object>();
// 标题下的第一行
Row rown = row.next();
// 行的所有列
Iterator<Cell> cellbody = rown.cellIterator();
int k = 0;
// 遍历一行的列
while (cellbody.hasNext()) {
Cell cell = cellbody.next();
// 这里得到此列的对应的标题
String titleString = (String) titlemap.get(k);
if (fieldMap.containsKey(titleString)) {
retMap.put(fieldMap.get(titleString).getFieldName(), getCellValueString(cell));
}
// 下一列
k = k + 1;
}
tObject.add(retMap);
}
} catch (Exception e) {
e.printStackTrace();
return null;
}
return tObject;
}
/**
* 导入 excel
*
* @param inputstream
* : 文件输入流
* @param pojoClass
* : 对应的导入对象 (每行记录)
* @return
*/
public static Collection importExcelByIs2(InputStream inputstream, List<CgFormFieldEntity> lists) {
Map<String, CgFormFieldEntity> fieldMap = ConvertDate(lists);
// 返回的数据类型
List<Map<String, Object>> tObject = new ArrayList<Map<String, Object>>();
//行数
int g = 0;
//列数
int k1 = 0;
try {
// 将传入的File构造为FileInputStream;
// // 得到工作表
XSSFWorkbook book = new XSSFWorkbook(inputstream);
// // 得到第一页
XSSFSheet sheet = book.getSheetAt(0);
// // 得到第一面的所有行
Iterator<Row> row = sheet.rowIterator();
// 得到第一行，也就是标题行
Row title = row.next();
// 得到第一行的所有列
Iterator<Cell> cellTitle = title.cellIterator();
// 将标题的文字内容放入到一个map中。
Map titlemap = new HashMap();
// 从标题第一列开始
int i = 0;
// 循环标题所有的列
while (cellTitle.hasNext()) {
Cell cell = cellTitle.next();
String value = cell.getStringCellValue();
//如果表格标题有数据库没有的字段,执行提示
if (fieldMap.get(value) == null) {
JOptionPane.showMessageDialog(null,i+1+"列有不能识别的列名:"+value);
throw new BusinessException("导入数据excel列名有不能识别的列:"+value);
}
titlemap.put(i, value);
i = i + 1;
}
// 用来格式化日期的DateFormat
Map<String, Object> retMap = null;
while (row.hasNext()) {
retMap = new HashMap<String, Object>();
// 标题下的第一行
Row rown = row.next();
// 行的所有列
Iterator<Cell> cellbody = rown.cellIterator();
int k = 0;
// 遍历一行的列
while (cellbody.hasNext()) {
Cell cell = cellbody.next();
// 这里得到此列的对应的标题
String titleString = (String) titlemap.get(k);
if (fieldMap.containsKey(titleString)) {
//xiaoyanan 数据字典项
String valueString = "";
List<DictEntity> data = loadDic((CgFormFieldEntity)fieldMap.get(titleString));
if(data.size()>0){
for(int p=0;p<data.size();p++){
if(getCellValueString(cell).equals(data.get(p).getTypename())){
valueString = data.get(p).getTypecode();
}
}
}
else{
if("unit".equals(fieldMap.get(titleString).getFieldName())){
List<Map<String,Object>> map = excelTempletService.service.findForJdbc("select id from za_unit where dwqc=? limit 1", getCellValueString(cell));
if(map.size()>0){
retMap.put("unit_id",map.get(0).get("id"));
}
valueString = getCellValueString(cell);
}else if("sfzh".equals(fieldMap.get(titleString).getFieldName())){
List<Map<String,Object>> map = excelTempletService.service.findForJdbc("select id from za_staypeople where identity=? limit 1", getCellValueString(cell));
if(map.size()>0){
retMap.put("owner",map.get(0).get("id"));
}
valueString = getCellValueString(cell);
}
//判断时间属性字段是否符合格式,不符合时提示
else if("kysj".equals(fieldMap.get(titleString).getFieldName())){
String arr1[] =getCellValueString(cell).split("-");
if(arr1.length!=3){
JOptionPane.showMessageDialog(null,"第"+(g+1)+"行"+(k+1)+"列有不能识别的格式,请更改格式后重试");
//停止运行后面的方法,但不停止整个服务器的运行
boolean flag = true;
while(flag) {
}
}
}
else if("xjzd".equals(fieldMap.get(titleString).getFieldName())){
String arr[] =getCellValueString(cell).split("-");
List<DictEntity> fwwzList = queryDic("","unittype","");
List<DictEntity> fwlhList = queryDic("","lh","");
List<DictEntity> fwdyhList = queryDic("","dyh","");
List<DictEntity> fwfjhList = queryDic("","fjh","");
String fwwzStr = "";
String fwlhStr = "";
String fwdyhStr ="";
String fwfjhStr ="";
for(DictEntity fwwz:fwwzList){
if(arr[0].equals(fwwz.getTypename())){
fwwzStr = fwwz.getTypecode();
}
}
for(DictEntity fwlh:fwlhList){
if(arr[1].equals(fwlh.getTypename())){
fwlhStr =fwlh.getTypecode();
}
}
if("生活区".equals(arr[0])){
fwdyhStr = "1";
for(DictEntity fjh:fwfjhList){
if(arr[2].equals(fjh.getTypename())){
fwfjhStr =fjh.getTypecode();
}
}
}else{
for(DictEntity dyh:fwdyhList){
if(arr[2].equals(dyh.getTypename())){
fwdyhStr = dyh.getTypecode();
}
}
for(DictEntity fjh:fwfjhList){
if(arr[3].equals(fjh.getTypename())){
fwfjhStr = fjh.getTypecode();
}
}
}
List<Map<String,Object>> map = excelTempletService.service.findForJdbc("select id from za_renthouse where fwwz=? and fwlh=? and fwdyh =? and fwfjh = ? limit 1",fwwzStr,fwlhStr,fwdyhStr,fwfjhStr);
if(map.size()>0){
retMap.put("xjzdxz",map.get(0).get("id"));
}
if("生活区".equals(arr[0])){
valueString = arr[0]+arr[1]+"号楼"+" "+arr[2]+"室";
}else{
valueString = arr[0]+arr[1]+"号楼"+arr[2]+arr[3]+"室";
}
}
else
{
valueString = getCellValueString(cell);
}
}
retMap.put(fieldMap.get(titleString).getFieldName(),valueString);
}
// 下一列
k = k + 1;
k1 = k1 + 1;
}
g = g + 1;
tObject.add(retMap);
}
} catch (Exception e) {
//如果出现模板导入错误,表的标题不正确时都会提示
e.printStackTrace();
JOptionPane.showMessageDialog(null,"识别模版数据错误");
return null;
}
//导入成功后提示,并进行传值
JOptionPane.showMessageDialog(null,"导入成功:共"+g+"条数据");
return tObject;
}
// TODO huiyong excel日期格式处理默认如此，需要从数据库或者配置文件读取
public final static DateFormat DEFAULT_DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd");
/**
* 得到某个格子的值 已经对过时方法进行更新
*
* @param cell
* 格子对象
* @return 格子的值
*/
public static String getCellValueString(Cell cell) {
if (cell == null) {
return null;
}
// 时间对象 特殊处理
int dataFormat = cell.getCellStyle().getDataFormat();
if (dataFormat == 14 || dataFormat == 178 || dataFormat == 180 || dataFormat == 181 || dataFormat == 182) {
return getDateValue(cell);
}
String value = null;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
value = new DecimalFormat("0.##########").format(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
// value = cell.getStringCellValue();
value = cell.getRichStringCellValue().toString();
break;
case Cell.CELL_TYPE_FORMULA:
value = String.valueOf(cell.getCellFormula());
break;
case Cell.CELL_TYPE_BLANK:
// value = String.valueOf(cell.getStringCellValue());
value = String.valueOf(cell.getRichStringCellValue().toString());
break;
case Cell.CELL_TYPE_BOOLEAN:
value = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR:
value = String.valueOf(cell.getErrorCellValue());
break;
}
return value;
}
/**
* 返回时间内的特殊时间格式 OFFICE2003
*
* @param cell
* @return
*/
private static String getDateValue(Cell cell) {
return DEFAULT_DATE_FORMAT.format(cell.getDateCellValue());
}
/**
* 数据处理
*/
private static Map<String, CgFormFieldEntity> ConvertDate(List<CgFormFieldEntity> lists) {
Map<String, CgFormFieldEntity> maps = new HashMap<String, CgFormFieldEntity>();
for (CgFormFieldEntity cgFormFieldEntity : lists) {
maps.put(cgFormFieldEntity.getContent(), cgFormFieldEntity);
}
return maps;
}
/**
* 装载数据字典
*
* @param m
* 要放入freemarker的数据
* @param bean
* 获取字典数据
*/
private static List<DictEntity> loadDic(CgFormFieldEntity bean) {
List<DictEntity> dicDatas = new ArrayList<DictEntity>(0);
String dicT = bean.getDictTable();// 字典Table
String dicF = bean.getDictField();// 字典Code
String dicText = bean.getDictText();// 字典Text
if (StringUtil.isNotEmpty(dicT) || StringUtil.isNotEmpty(dicF)) {
if (!bean.getShowType().equals("popup")) {
dicDatas = queryDic(dicT, dicF, dicText);
}
}
return dicDatas;
}
private static List<DictEntity> queryDic(String dicTable, String dicCode, String dicText) {
return excelTempletService.service.queryDict(dicTable, dicCode, dicText);
}
}
1971
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
