首先是导出
public void getExcelTemplate(HttpServletResponse response) {
List<Object[]> list = new ArrayList<>();
String[] colCaption = new String[]{"题型","分类","题目","选项1","选项2","选项3","选项4","正确答案","答案解析"};
Object[] obj = new Object[9];
obj[0] = "单选题";
obj[1] = "原料管理";
obj[2] = "下列哪种物品是食品添加剂?";
obj[3] = "豆酱";
obj[4] = "鱼露";
obj[5] = "鸡粉";
obj[6] = "小苏打";
obj[7] = "D";
obj[8] = "";
list.add(obj);
Object[] obj1 = new Object[9];
obj1[0] = "多选题";
obj1[1] = "默认分类";
obj1[2] = "食品安全事故发生后应采取的措施有?";
obj1[3] = "送医";
obj1[4] = "食品留样";
obj1[5] = "信息上报";
obj1[6] = "拍照留证";
obj1[7] = "ABCDE";
obj1[8] = "";
list.add(obj1);
Object[] obj2 = new Object[9];
obj2[0] = "判断题";
obj2[1] = "加工制作";
obj2[2] = "可以使用甲醛泡发海产品。";
obj2[3] = "正确";
obj2[4] = "错误";
obj2[5] = "";
obj2[6] = "";
obj2[7] = "B";
obj2[8] = "甲醛会与海产品中蛋白质发生反应,破坏蛋白质的结构,使其变质,危害人体健康。";
list.add(obj2);
ExcelExport excelExport = new ExcelExport("导入模板");
excelExport.addSheetByArray("导入模板", list, colCaption);
excelExport.export(response);
}
接下来是导入,导入需要解析数据,诸多验证需要自己去加
public Boolean importExcel(MultipartFile file) {
List<String[]> excelDataAll = ExcelImport.getExcelDataAll(file).get(0);
Iterator<String[]> iterator = excelDataAll.iterator();
String[] next1 = iterator.next();
s: while (iterator.hasNext()) {
String[] next = iterator.next();
for (int i = 0; i < next.length; i++) {
switch (i) {
case 0:
//解析
String str = next[i];
//执行操作
continue;
case 1:
//解析
String str = next[i];
//执行操作
continue;
case 2:
//解析
String str = next[i];
//执行操作
continue;
...
default:
continue s;
}
}
}
return flag ;
}
工具类 ExcelExport
public class ExcelExport {
public static final String EXCEL03_EXTENSION = ".xls";
public static final String EXCEL07_EXTENSION = ".xls";
private Workbook workBook;
private String exportFileName;
private Log logger;
public ExcelExport() {
this("workbook.xlsx");
}
public ExcelExport(String exportFileName) {
this.logger = LogFactory.getLog(ExcelExport.class);
if (StringUtils.isBlank(exportFileName)) {
exportFileName = "workbook.xlsx";
}
String fileName = exportFileName.toLowerCase();
if (fileName.endsWith(".xls")) {
this.workBook = new HSSFWorkbook();
} else if (exportFileName.endsWith(".xls")) {
this.workBook = new XSSFWorkbook();
} else {
this.workBook = new XSSFWorkbook();
fileName = fileName + ".xlsx";
}
this.exportFileName = fileName;
}
public void export(HttpServletResponse response) {
response.reset();
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");
String fileName = null;
try {
HttpServletRequest request = ((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getRequest();
String userAgent = request.getHeader("User-Agent");
if (!userAgent.contains("MSIE") && !userAgent.contains("Trident")) {
fileName = new String(this.exportFileName.getBytes("UTF-8"), "ISO-8859-1");
} else {
fileName = URLEncoder.encode(this.exportFileName, "UTF-8");
}
} catch (UnsupportedEncodingException var6) {
fileName = "export.xls";
}
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
try {
ServletOutputStream output = response.getOutputStream();
this.workBook.write(output);
output.flush();
output.close();
} catch (IOException var5) {
this.logger.error("导出文件出错了", var5);
throw new CustomException("文件导出错误");
}
}
public static void main(String[] args) {
ExcelExport ee1 = new ExcelExport();
List<Object[]> list1 = new ArrayList();
List<Object> obj = new ArrayList();
obj.add("370681198002042214");
obj.add(new Date());
obj.add(new Timestamp(System.currentTimeMillis()));
obj.add(1);
obj.add(11111111111111L);
obj.add(123456.125F);
obj.add(1.2345678912345678E8D);
obj.add(new BigDecimal("123456789123456789.1234"));
obj.add(true);
list1.add(obj.toArray());
String[] header = new String[]{"身份证号", "日期", "时间", "整型", "长整", "浮点", "双精度", "大数", "布尔"};
ee1.addSheetByArray("测试1", list1, header);
try {
OutputStream fis = new FileOutputStream("D:\\test1.xlsx");
ee1.getWorkbook().write(fis);
} catch (FileNotFoundException var7) {
var7.printStackTrace();
} catch (IOException var8) {
var8.printStackTrace();
}
}
public void addSheetByArray(String sheetName, List<Object[]> list, String[] colCaption) {
Sheet sheet;
if (StringUtils.isBlank(sheetName)) {
sheet = this.workBook.createSheet();
} else {
sheet = this.workBook.createSheet(sheetName);
}
this.createCaptionRow(colCaption, sheet);
int colNum = 0;
int startRow = 1;
int cols;
for(Iterator var7 = list.iterator(); var7.hasNext(); colNum = colNum > cols ? colNum : cols) {
Object[] obj = (Object[])var7.next();
Row row = sheet.createRow(startRow++);
cols = this.createRowData(row, Arrays.asList(obj));
row.setHeight((short)400);
}
this.adjustCellWidth(colNum, startRow, sheet);
}
public void addSheetByMap(String sheetName, List<Map<String, Object>> list, String[] colCaption) {
Sheet sheet;
if (StringUtils.isBlank(sheetName)) {
sheet = this.workBook.createSheet();
} else {
sheet = this.workBook.createSheet(sheetName);
}
this.createCaptionRow(colCaption, sheet);
int colNum = 0;
int startRow = 1;
int cols;
for(Iterator var7 = list.iterator(); var7.hasNext(); colNum = colNum > cols ? colNum : cols) {
Map<String, Object> map = (Map)var7.next();
Row row = sheet.createRow(startRow++);
cols = this.createRowData(row, map.values());
row.setHeight((short)400);
}
this.adjustCellWidth(colNum, startRow, sheet);
}
private boolean isInIgnors(String name, String[] ignores) {
String[] var3 = ignores;
int var4 = ignores.length;
for(int var5 = 0; var5 < var4; ++var5) {
String ignore = var3[var5];
if (name.equals(ignore)) {
return true;
}
}
return false;
}
private int createRowData(Row row, Collection<Object> coll) {
int cellNum = 0;
for(Iterator var4 = coll.iterator(); var4.hasNext(); ++cellNum) {
Object obj = var4.next();
if (obj == null) {
obj = "";
}
String strValue = obj.toString();
Cell cell = row.createCell(cellNum);
if (!strValue.startsWith("FORMULA:")) {
this.setCellValue(cell, obj);
}
}
return cellNum;
}
private void setCellValue(Cell cell, Object obj) {
CreationHelper createHelper = this.workBook.getCreationHelper();
String strValue = obj.toString();
if (obj instanceof Timestamp) {
Date dt = (Timestamp)obj;
CellStyle cellStyle = this.workBook.createCellStyle();
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
cell.setCellValue(dt);
cell.setCellStyle(cellStyle);
} else if (obj instanceof Date) {
CellStyle cellStyle = this.workBook.createCellStyle();
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd"));
cell.setCellValue((Date)obj);
cell.setCellStyle(cellStyle);
} else {
DecimalFormat df;
String value;
if (obj instanceof Integer) {
cell.setCellType(CellType.NUMERIC);
df = new DecimalFormat("0");
value = df.format(obj);
cell.setCellValue(value);
} else if (obj instanceof Long) {
cell.setCellType(CellType.NUMERIC);
df = new DecimalFormat("0");
value = df.format(obj);
cell.setCellValue(value);
} else if (obj instanceof Float) {
cell.setCellType(CellType.NUMERIC);
df = new DecimalFormat("0.0000");
value = df.format(obj);
cell.setCellValue(value);
} else if (obj instanceof Double) {
cell.setCellType(CellType.NUMERIC);
df = new DecimalFormat("0.0000");
value = df.format(obj);
cell.setCellValue(value);
} else if (obj instanceof BigDecimal) {
cell.setCellType(CellType.STRING);
cell.setCellValue(strValue);
} else if (obj instanceof String) {
cell.setCellType(CellType.STRING);
cell.setCellValue(strValue);
} else {
cell.setCellType(CellType.STRING);
cell.setCellValue(strValue);
}
}
}
private void createCaptionRow(String[] colCaption, Sheet sheet) {
Row row = sheet.createRow(0);
row.setHeight((short)400);
if (colCaption != null) {
for(int i = 0; i < colCaption.length; ++i) {
Cell cell = row.createCell(i);
cell.setCellStyle(this.getStyle("title"));
cell.setCellType(CellType.STRING);
cell.setCellValue(colCaption[i]);
}
}
}
private void adjustCellWidth(int cols, int rows, Sheet sheet) {
int[] cellWidth = new int[cols];
int j;
for(j = 0; j < cols; ++j) {
for(int row = 0; row < rows; ++row) {
Cell cell = sheet.getRow(row).getCell(j);
String value = this.getCellValue(cell);
int length = value.getBytes().length;
if (length > cellWidth[j]) {
cellWidth[j] = length;
}
}
}
for(j = 0; j < cellWidth.length; ++j) {
if (cellWidth[j] > 254) {
cellWidth[j] = 254;
}
sheet.setColumnWidth(j, cellWidth[j] * 12 > 255 ? 6528 : cellWidth[j] * 12 * 256 / 10);
}
}
private String getCellValue(Cell cell) {
String value = "";
try {
switch(cell.getCellTypeEnum()) {
case NUMERIC:
value = cell.getNumericCellValue() + "1111";
break;
case STRING:
value = cell.getStringCellValue();
}
} catch (Exception var4) {
}
return value;
}
private CellStyle getStyle(String type) {
CellStyle cs = this.workBook.createCellStyle();
if ("title".equals(type)) {
Font font = this.workBook.createFont();
font.setBold(true);
cs.setAlignment(HorizontalAlignment.CENTER);
cs.setFillBackgroundColor((short)40);
cs.setFont(font);
}
return cs;
}
public Workbook getWorkbook() {
return this.workBook;
}
public void setWorkbook(Workbook workBook) {
this.workBook = workBook;
}
public String getExportFileName() {
return this.exportFileName;
}
public void setExportFileName(String exportFileName) {
this.exportFileName = exportFileName;
}
}