@Test
@RequestMapping("/upload")
public void upload(@RequestParam(value = "fileinfo", required = false) MultipartFile file,HttpServletRequest req,HttpServletResponse resq){
String json="";
InputStream input = null;
List<?> list = null;
Map<String, String> fields = null;
try {
input=file.getInputStream();
fields = sjyjgl;
list = ExeclUtil.ExecltoList(input, yjsjXLSBean.class, fields);
// 去除无效数据
if (list != null && !list.isEmpty()) {
for (int i = list.size() - 1; i >= 0; i--) {
yjsjXLSBean _o = (yjsjXLSBean) list.get(i);
if ((_o.getXm() == null|| "".equals(_o.getXm().trim())) && (_o.getYwm() == null|| "".equals(_o.getYwm().trim()))) {
list.remove(i);
}
}
}
yjsjService.saveyjlr_batch(list);
json=JsonUtil.bean2json(null, RETCODE.R200.toString(),"操作成功");
} catch (Exception e) {
e.printStackTrace();
json=JsonUtil.bean2json(null, RETCODE.R500.toString(),"系统错误,请联系管理管");
}
write(resq, json);
}
操作excel类
package com.zhsh.base.utils;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLDecoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Pattern;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
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.CellType;
public class ExeclUtil {
static int sheetsize = 50000;
static Pattern pattern = Pattern.compile("(-?\\d+\\.?\\d*)[Ee]{1}[\\+-]?[0-9]*");
static DecimalFormat ds = new DecimalFormat("0");
static boolean isENum(String input) {//判断输入字符串是否为科学计数法
return pattern.matcher(input).matches();
}
/**
* @author: zhsh
* @param: data 导入到excel中的数据
* @param: out 数据写入的文件
* @param: fields 需要注意的是这个方法中的map中：每一列对应的实体类的英文名为键，excel表格中每一列名为值
* @throws: Exception
*/
public static <T> void ListtoExecl(List<T> data, OutputStream out, Map<String, String> fields)
throws Exception {
HSSFWorkbook workbook = new HSSFWorkbook();
// 如果导入数据为空，则抛出异常。
if (data == null || data.size() == 0) {
HSSFSheet sheet = workbook.createSheet();
HSSFRow row = sheet.createRow(0);
String[] cntitles = new String[fields.size()];
Iterator<String> it = fields.keySet().iterator();
int count = 0;
while (it.hasNext()) {
String egtitle = (String) it.next();
String cntitle = fields.get(egtitle);
cntitles[count] = cntitle;
count++;
}
for (int f = 0; f < cntitles.length; f++) {
HSSFCell cell = row.createCell(f);
cell.setCellValue(cntitles[f]);
}
workbook.write(out);
workbook.close();
throw new Exception("导出的数据为空");
}
// 根据data计算有多少页sheet
int pages = data.size() / sheetsize;
if (data.size() % sheetsize > 0) {
pages += 1;
}
// 提取表格的字段名（英文字段名是为了对照中文字段名的）
String[] egtitles = new String[fields.size()];
String[] cntitles = new String[fields.size()];
Iterator<String> it = fields.keySet().iterator();
int count = 0;
while (it.hasNext()) {
String egtitle = (String) it.next();
String cntitle = fields.get(egtitle);
egtitles[count] = egtitle;
cntitles[count] = cntitle;
count++;
}
// 添加数据
for (int i = 0; i < pages; i++) {
int rownum = 0;
// 计算每页的起始数据和结束数据
int startIndex = i * sheetsize;
int endIndex = (i + 1) * sheetsize - 1 > data.size() ? data.size() : (i + 1) * sheetsize - 1;
// 创建每页，并创建第一行
HSSFSheet sheet = workbook.createSheet();
HSSFRow row = sheet.createRow(rownum);
// 在每页sheet的第一行中，添加字段名
for (int f = 0; f < cntitles.length; f++) {
HSSFCell cell = row.createCell(f);
cell.setCellValue(cntitles[f]);
}
rownum++;
// 将数据添加进表格
for (int j = startIndex; j < endIndex; j++) {
row = sheet.createRow(rownum);
T item = data.get(j);
for (int h = 0; h < cntitles.length; h++) {
Field fd = item.getClass().getDeclaredField(egtitles[h]);
fd.setAccessible(true);
Object o = fd.get(item);
String value = o == null ? "" : o.toString();
HSSFCell cell = row.createCell(h);
cell.setCellValue(value);
}
rownum++;
}
}
// 将创建好的数据写入输出流
workbook.write(out);
// 关闭workbook
workbook.close();
}
/**
* @author: zhsh
* @param: data 导入到excel中的数据
* @param: out 数据写入的文件
* @param: fields 需要注意的是这个方法中的map中：每一列对应的实体类的英文名为键，excel表格中每一列名为值
* @throws: Exception
*/
public static <T> void ListMaptoExecl(List<T> data, OutputStream out, Map<String, String> fields)
throws Exception {
HSSFWorkbook workbook = new HSSFWorkbook();
// 如果导入数据为空，则抛出异常。
if (data == null || data.size() == 0) {
HSSFSheet sheet = workbook.createSheet();
HSSFRow row = sheet.createRow(0);
String[] cntitles = new String[fields.size()];
Iterator<String> it = fields.keySet().iterator();
int count = 0;
while (it.hasNext()) {
String egtitle = (String) it.next();
String cntitle = fields.get(egtitle);
cntitles[count] = cntitle;
count++;
}
for (int f = 0; f < cntitles.length; f++) {
HSSFCell cell = row.createCell(f);
cell.setCellValue(cntitles[f]);
}
workbook.write(out);
workbook.close();
throw new Exception("导出的数据为空");
}
// 根据data计算有多少页sheet
int pages = data.size() / sheetsize;
if (data.size() % sheetsize > 0) {
pages += 1;
}
// 提取表格的字段名（英文字段名是为了对照中文字段名的）
String[] egtitles = new String[fields.size()];
String[] cntitles = new String[fields.size()];
Iterator<String> it = fields.keySet().iterator();
int count = 0;
while (it.hasNext()) {
String egtitle = (String) it.next();
String cntitle = fields.get(egtitle);
egtitles[count] = egtitle;
cntitles[count] = cntitle;
count++;
}
// 添加数据
for (int i = 0; i < pages; i++) {
int rownum = 0;
// 计算每页的起始数据和结束数据
int startIndex = i * sheetsize;
int endIndex = (i + 1) * sheetsize - 1 > data.size() ? data.size() : (i + 1) * sheetsize - 1;
// 创建每页，并创建第一行
HSSFSheet sheet = workbook.createSheet();
HSSFRow row = sheet.createRow(rownum);
// 在每页sheet的第一行中，添加字段名
for (int f = 0; f < cntitles.length; f++) {
HSSFCell cell = row.createCell(f);
cell.setCellValue(cntitles[f]);
}
rownum++;
// 将数据添加进表格
for (int j = startIndex; j < endIndex; j++) {
row = sheet.createRow(rownum);
Map<String,Object> item = (Map<String, Object>) data.get(j);
for (int h = 0; h < cntitles.length; h++) {
Object fd = item.get(egtitles[h]);
String value = fd == null ? "" : fd.toString();
HSSFCell cell = row.createCell(h);
cell.setCellValue(value);
}
rownum++;
}
}
// 将创建好的数据写入输出流
workbook.write(out);
// 关闭workbook
workbook.close();
}
/**
*
* @author: zhsh
* @param: entityClass excel中每一行数据的实体类
* @param: in excel文件
* @param: fields 字段名字 需要注意的是这个方法中的map中：excel表格中每一列名为键，每一列对应的实体类的英文名为值
* @throws: Exception
*/
public static <T> List<T> ExecltoList(InputStream in, Class<T> entityClass, Map<String, String> fields)
throws Exception {
List<T> resultList = new ArrayList<T>();
HSSFWorkbook workbook = new HSSFWorkbook(in);
// excel中字段的中英文名字数组
String[] egtitles = new String[fields.size()];
String[] cntitles = new String[fields.size()];
Iterator<String> it = fields.keySet().iterator();
int count = 0;
while (it.hasNext()) {
String cntitle = (String) it.next();
String egtitle = fields.get(cntitle);
egtitles[count] = egtitle;
cntitles[count] = cntitle;
count++;
}
// 得到excel中sheet总数
int sheetcount = workbook.getNumberOfSheets();
if (sheetcount == 0) {
workbook.close();
throw new Exception("Excel文件中没有任何数据");
}
// 数据的导出
for (int i = 0; i < sheetcount; i++) {
HSSFSheet sheet = workbook.getSheetAt(i);
if (sheet == null) {
continue;
}
// 每页中的第一行为标题行，对标题行的特殊处理
HSSFRow firstRow = sheet.getRow(0);
if(firstRow == null)
continue;
int celllength = firstRow.getLastCellNum();
String[] excelFieldNames = new String[celllength];
LinkedHashMap<String, Integer> colMap = new LinkedHashMap<String, Integer>();
// 获取Excel中的列名
for (int f = 0; f < celllength; f++) {
HSSFCell cell = firstRow.getCell(f);
excelFieldNames[f] = cell.getStringCellValue().trim();
// 将列名和列号放入Map中,这样通过列名就可以拿到列号
for (int g = 0; g < excelFieldNames.length; g++) {
colMap.put(excelFieldNames[g], g);
}
}
// 由于数组是根据长度创建的，所以值是空值，这里对列名map做了去空键的处理
colMap.remove(null);
// 判断需要的字段在Excel中是否都存在
// 需要注意的是这个方法中的map中：中文名为键，英文名为值
boolean isExist = true;
List<String> excelFieldList = Arrays.asList(excelFieldNames);
for (String cnName : fields.keySet()) {
if (!excelFieldList.contains(cnName)) {
isExist = false;
break;
}
}
// 如果有列名不存在，则抛出异常，提示错误
if (!isExist) {
workbook.close();
throw new Exception("Excel中缺少必要的字段，或字段名称有误");
}
// 将sheet转换为list
System.out.println(sheet.getLastRowNum());
// for (int j = 1; j <= sheet.getLastRowNum()-1; j++) {
for (int j = 1; j <= sheet.getLastRowNum(); j++) {
HSSFRow row = sheet.getRow(j);
// 根据泛型创建实体类
T entity = entityClass.newInstance();
// 给对象中的字段赋值
for (Entry<String, String> entry : fields.entrySet()) {
// 获取中文字段名
String cnNormalName = entry.getKey();
// 获取英文字段名
String enNormalName = entry.getValue();
// 根据中文字段名获取列号
int col = colMap.get(cnNormalName);
// 获取当前单元格中的内容
String content = "";
if (row.getCell(col) == null){
content = "";
}else{
content=row.getCell(col).toString().trim();
}
// 给对象赋值
setFieldValueByName(enNormalName, row.getCell(col), entity);
}
resultList.add(entity);
}
}
workbook.close();
return resultList;
}
/**
*
* @author: zhsh
* @param: entityClass excel中每一行数据的实体类
* @param: in excel文件
* @param: fields 字段名字 需要注意的是这个方法中的map中：excel表格中每一列名为键，每一列对应的实体类的英文名为值
* @throws: Exception
*/
public static String ExecltoType(InputStream in)
throws Exception {
String type = "";
HSSFWorkbook workbook = new HSSFWorkbook(in);
// 得到excel中sheet总数
int sheetcount = workbook.getNumberOfSheets();
if (sheetcount == 0) {
workbook.close();
throw new Exception("Excel文件中没有任何数据");
}
HSSFSheet sheet = workbook.getSheetAt(0);
if(sheet == null)
throw new Exception("数据页获取失败");
HSSFRow r = sheet.getRow(0);
if(sheet == null)
throw new Exception("表头获取失败");
String v1 = r.getCell(0).getStringCellValue();
if("托运日期".equals(v1.trim())){
type = "JFH";
}else if("日期".equals(v1.trim())){
type = "HL";
}
return type;
}
/**
* @MethodName: setFieldValueByName
* @Description: 根据字段名给对象的字段赋值
* @param: fieldName 字段名
* @param: fieldValue 字段值
* @param: o 对象
*/
private static void setFieldValueByName(String fieldName, HSSFCell value, Object o) throws Exception {
String fieldValue = null;
if(value == null){
fieldValue = "";
}else if (value.toString().contains("-") && checkDate(value.toString())){
value.setCellType(CellType.STRING);
fieldValue=formatExcelDate(value.toString());
}else{
fieldValue = value.toString();
if (isENum(fieldValue)) {
fieldValue= ds.format(Double.parseDouble(fieldValue)).trim();
}
}
Field field = getFieldByName(fieldName, o.getClass());
if (field != null) {
field.setAccessible(true);
// 获取字段类型
Class<?> fieldType = field.getType();
if(fieldValue != null && !"".equals(fieldValue)){
// 根据字段类型给字段赋值
try{
if (String.class == fieldType) {
field.set(o, String.valueOf(fieldValue));
} else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) {
field.set(o, Integer.parseInt(fieldValue.toString()));
} else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) {
field.set(o, Long.valueOf(fieldValue.toString()));
} else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) {
field.set(o, Float.valueOf(fieldValue.toString()));
} else if ((Short.TYPE == fieldType) || (Short.class == fieldType)) {
field.set(o, Short.valueOf(fieldValue.toString()));
} else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) {
field.set(o, Double.valueOf(fieldValue.toString()));
} else if (Character.TYPE == fieldType) {
if ((fieldValue != null) && (fieldValue.toString().length() > 0)) {
field.set(o, Character.valueOf(fieldValue.toString().charAt(0)));
}
} else if (Date.class == fieldType) {
field.set(o, value.toString());
} else {
field.set(o, fieldValue);
}
}catch (Exception e) {
field.set(o, fieldValue);
}
}else{
field.set(o, null);
}
} else {
throw new Exception(o.getClass().getSimpleName() + "类不存在字段名 " + fieldName);
}
}
/**
* @MethodName: getFieldByName
* @Description: 根据字段名获取字段
* @param: fieldName 字段名
* @param: clazz 包含该字段的类
* @return: 字段
*/
private static Field getFieldByName(String fieldName, Class<?> clazz) {
// 拿到本类的所有字段
Field[] selfFields = clazz.getDeclaredFields();
// 如果本类中存在该字段，则返回
for (Field field : selfFields) {
if (field.getName().equals(fieldName)) {
return field;
}
}
// 否则，查看父类中是否存在此字段，如果有则返回
Class<?> superClazz = clazz.getSuperclass();
if (superClazz != null && superClazz != Object.class) {
return getFieldByName(fieldName, superClazz);
}
// 如果本类和父类都没有，则返回空
return null;
}
public void exportExcel(String title, List<String> headers, List<Map<String,Object>> list,
OutputStream out, String pattern) throws Exception {
// 声明一个工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(title);
// 设置表格默认列宽度为15个字符
sheet.setDefaultColumnWidth((short) 20);
// 设置标题样式
HSSFCellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
// 设置标题字体
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.VIOLET.index);
// 把字体应用到当前的样式
style.setFont(font);
// 设置正文样式,用于设置内容样式
HSSFCellStyle style2 = workbook.createCellStyle();
style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
// 设置正文字体
HSSFFont font2 = workbook.createFont();
// 把字体应用到当前的样式
style2.setFont(font2);
// 设置头部样式
HSSFCellStyle styleBold = workbook.createCellStyle();
// 产生头部标题行并合并相应单元格
styleBold.setFillForegroundColor((short) 13);// 头部设置背景色
// 设置头部字体:
HSSFFont font3 = workbook.createFont();
/*
* font3.setFontName("黑体"); font3.setFontHeightInPoints((short)
* 16);//设置字体大小
*/
font3.setFontName("仿宋_GB2312");
font3.setFontHeightInPoints((short) 20);
styleBold.setFont(font3);
HSSFRow row_1 = sheet.createRow(0);
HSSFCell _cell = row_1.createCell((short) 0);
_cell.setCellStyle(styleBold);
String text_1 = URLDecoder.decode("", "UTF-16");
_cell.setCellValue(text_1);
row_1.createCell((short) 0);
row_1.createCell((short) 1);
row_1.createCell((short) 2);
int i = 3;
if(headers !=null && headers.size() >0){
for(String da :headers){
HSSFCell createCell = row_1.createCell((short) i++);
createCell.setCellValue(da);
row_1.createCell((short) i++);
row_1.createCell((short) i++);
row_1.createCell((short) i++);
}
}
HSSFRow row_2 = sheet.createRow(1);
HSSFCell createCell = row_2.createCell((short) 0);
createCell.setCellValue("姓名");
HSSFCell createCell2 = row_2.createCell((short) 1);
createCell2.setCellValue("证件号");
HSSFCell createCell3 = row_2.createCell((short) 2);
createCell3.setCellValue("身份证号");
i = 3;
if(headers !=null && headers.size() >0){
for(String da :headers){
HSSFCell t1 = row_2.createCell((short) i++);
t1.setCellValue("航班号");
HSSFCell t2 = row_2.createCell((short) i++);
t2.setCellValue("出发地");
HSSFCell t3 = row_2.createCell((short) i++);
t3.setCellValue("到达地");
HSSFCell t4 = row_2.createCell((short) i++);
t4.setCellValue("来自国家地区");
}
}
try {
List<String> dateListName = null;
if(list!=null && list.size()> 0 && list.get(0)!=null){
dateListName = (List<String>)list.get(0).get("dateListName");
}
int k = 2;
if(list!=null){
for(Map<String, Object> vo :list){
HSSFRow t = sheet.createRow(k);
k++;
HSSFCell v0 = t.createCell(0);
v0.setCellValue(vo.get("xm")==null?"":(vo.get("xm")+"").trim());
HSSFCell v1 = t.createCell(1);
v1.setCellValue(vo.get("zjhm")==null?"":(vo.get("zjhm")+"").trim());
HSSFCell v2 = t.createCell(2);
v2.setCellValue(vo.get("sfzh")==null?"":(vo.get("sfzh")+"").trim());
if(vo.get("dateList")!=null){
List<List> tv0 = (List<List>)vo.get("dateList");
int m = 3;
for(List<String> _hb : tv0){
if(_hb!=null){
for(String value:_hb){
HSSFCell _v2 = t.createCell(m++);
if(value!=null&&!"null".equals(value)){
value=value.trim();
}else{
value="";
}
_v2.setCellValue(value);
}
}
}
}
}
}
workbook.write(out);
} catch (Exception e) {
e.printStackTrace();
}
}
public static String getCellValue(HSSFCell value){
//解决日期03-五月-2020格式读入后的问题，POI读取后变成“03-五月-2020”格式
//定义一个新的字符串
String anString="";
//设置日期格式
if(value.toString().length()>15){
anString =new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(value.getDateCellValue());
}else{
anString =new SimpleDateFormat("yyyy-MM-dd").format(value.getDateCellValue());
}
anString=anString+"";
return anString;
}
/**
* 判断是否是“02-五月-2020”格式的日期类型
*/
private static boolean checkDate(String str){
String[] dataArr =str.split("-");
try {
if(dataArr.length == 3){
int x = Integer.parseInt(dataArr[0]);
String y = dataArr[1];
int z = Integer.parseInt(dataArr[2]);
if(x>0 && x<32 && z>0 && z< 10000 && y.endsWith("月")){
return true;
}
}
} catch (Exception e) {
return false;
}
return false;
}
public static String formatExcelDate(String excelDate){
String dayStr = excelDate.substring(0,excelDate.lastIndexOf("."));
String numStr = "0"+excelDate.substring(excelDate.lastIndexOf("."),excelDate.length());
int days = Integer.parseInt(dayStr);
double ditNumber = Double.parseDouble(numStr);
Date date = getDate(days);
Date dateTime = getTime(date, ditNumber);
String dateToString =StringUtil.dateToString(dateTime, "dateTime");
return dateToString;
}
public static Date getDate(int days) {
Calendar c = Calendar.getInstance();
c.set(1900, 0, 1);
c.add(Calendar.DATE, days - 2);
return c.getTime();
}
public static Date getTime(Date date, double ditNumber) {
Calendar c = Calendar.getInstance();
int mills = (int) (Math.round(ditNumber * 24 * 3600));
int hour = mills / 3600;
int minute = (mills - hour * 3600) / 60;
int second = mills - hour * 3600 - minute * 60;
c.setTime(date);
c.set(Calendar.HOUR_OF_DAY, hour);
c.set(Calendar.MINUTE, minute);
c.set(Calendar.SECOND, second);
return c.getTime();
}
}
953
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
