通用Excel导入导出模板
- 我们平时导入导出Excel都是一个Excel对应于一张表,操作多张表的时候不是很方便,所以本文就针对这一情况对导入导出功能进行优化:导入导出的excel可以对应多张表,excel的表头,数据可以是不固定的。通过数据库一张t_fieldorder表对excel导入导出字段进行控制。
- Excel工具类
package com.location.util;
import com.location.bjgwserver.domain.ExcelData;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder;
import org.thymeleaf.util.StringUtils;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
public class ExportExcelUtils {
public static void exportExcel(HttpServletResponse response, String fileName, ExcelData data) throws Exception {
response.reset();
response.addHeader("content-Type", "application/ms-excel;charset=utf-8");
response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
exportExcel(data, response.getOutputStream());
}
public static void exportExcel(ExcelData data, OutputStream out) throws Exception {
XSSFWorkbook wb = new XSSFWorkbook();
try {
String sheetName = data.getName();
if (null == sheetName) {
sheetName = "Sheet1";
}
XSSFSheet sheet = wb.createSheet(sheetName);
writeExcel(wb, sheet, data);
wb.write(out);
} finally {
}
}
private static void writeExcel(XSSFWorkbook wb, XSSFSheet sheet, ExcelData data) {
int rowIndex = 0;
rowIndex = writeTitlesToExcel(wb, sheet, data.getTitles());
writeRowsToExcel(wb, sheet, data.getRows(), rowIndex);
autoSizeColumns(sheet, data.getTitles().size() + 1);
}
private static int writeTitlesToExcel(XSSFWorkbook wb, XSSFSheet sheet, List<String> titles) {
int rowIndex = 0;
int colIndex = 0;
Font titleFont = wb.createFont();
titleFont.setFontName("simsun");
titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
titleFont.setColor(IndexedColors.BLACK.getIndex());
XSSFCellStyle titleStyle = wb.createCellStyle();
titleStyle.setAlignment(HorizontalAlignment.CENTER);
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
titleStyle.setFillForegroundColor(createXssfColor("#FFFFFF"));
titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
titleStyle.setFont(titleFont);
setBorder(titleStyle, BorderStyle.THIN, createXssfColor("#000000"));
Row titleRow = sheet.createRow(rowIndex);
colIndex = 0;
for (String field : titles) {
Cell cell = titleRow.createCell(colIndex);
cell.setCellValue(field);
cell.setCellStyle(titleStyle);
colIndex++;
}
rowIndex++;
return rowIndex;
}
private static int writeRowsToExcel(XSSFWorkbook wb, Sheet sheet, List<List<Object>> rows, int rowIndex) {
int colIndex = 0;
Font dataFont = wb.createFont();
dataFont.setFontName("simsun");
dataFont.setColor(IndexedColors.BLACK.getIndex());
XSSFCellStyle dataStyle = wb.createCellStyle();
dataStyle.setAlignment(HorizontalAlignment.CENTER);
dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
dataStyle.setFont(dataFont);
setBorder(dataStyle, BorderStyle.THIN, createXssfColor("#000000"));
for (List<Object> rowData : rows) {
Row dataRow = sheet.createRow(rowIndex);
colIndex = 0;
for (Object cellData : rowData) {
Cell cell = dataRow.createCell(colIndex);
if (cellData != null) {
cell.setCellValue(cellData.toString());
} else {
cell.setCellValue("");
}
cell.setCellStyle(dataStyle);
colIndex++;
}
rowIndex++;
}
return rowIndex;
}
private static void autoSizeColumns(Sheet sheet, int columnNumber) {
for (int i = 0; i < columnNumber; i++) {
int orgWidth = sheet.getColumnWidth(i);
sheet.autoSizeColumn(i, true);
int newWidth = (int) (sheet.getColumnWidth(i) + 100);
if (newWidth > orgWidth) {
sheet.setColumnWidth(i, newWidth);
} else {
sheet.setColumnWidth(i, orgWidth);
}
}
}
private static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {
style.setBorderTop(border);
style.setBorderLeft(border);
style.setBorderRight(border);
style.setBorderBottom(border);
style.setBorderColor(XSSFCellBorder.BorderSide.TOP, color);
style.setBorderColor(XSSFCellBorder.BorderSide.LEFT, color);
style.setBorderColor(XSSFCellBorder.BorderSide.RIGHT, color);
style.setBorderColor(XSSFCellBorder.BorderSide.BOTTOM, color);
}
private static XSSFColor createXssfColor(String color) {
int[] rgbColor = hexToRgb(color);
XSSFColor xssfColor = new XSSFColor(new java.awt.Color(rgbColor[0], rgbColor[1], rgbColor[2]));
return xssfColor;
}
public static int[] hexToRgb(String hex) {
String colorStr = hex;
if (hex.startsWith("#")) {
colorStr = hex.substring(1);
}
if (StringUtils.length(colorStr) == 8) {
colorStr = hex.substring(2);
}
int r= Integer.valueOf( colorStr.substring( 0, 2 ), 16 );
int g= Integer.valueOf( colorStr.substring( 2, 4 ), 16 );
int b= Integer.valueOf( colorStr.substring( 4, 6 ), 16 );
return new int[] { r, g, b };
}
}
package com.location.bjgwserver.domain;
import java.io.Serializable;
import java.util.List;
import java.util.Map;
public class ExcelData implements Serializable {
private static final long serialVersionUID = 4454016249210520899L;
private List<String> titles;
private List<List<Object>> rows;
private String name;
public static long getSerialVersionUID() {
return serialVersionUID;
}
public List<String> getTitles() {
return titles;
}
public void setTitles(List<String> titles) {
this.titles = titles;
}
public List<List<Object>> getRows() {
return rows;
}
public void setRows(List<List<Object>> rows) {
this.rows = rows;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
package com.location.bjgwserver.controller;
import com.location.bjgwserver.domain.ExcelData;
import com.location.bjgwserver.service.ExcelService;
import com.location.general.domain.ResultCode;
import com.location.util.ExportExcelUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
@RestController
@RequestMapping("/services/excel")
public class ExcelController {
@Resource
private ExcelService excelService;
@RequestMapping("/importExcel")
@ResponseBody
public ResultCode importExcel(MultipartFile excelFile,String tableName) {
try {
excelService.importExcel(excelFile,tableName);
return ResultCode.success("添加成功");
} catch (Exception e) {
e.printStackTrace();
return ResultCode.errorParam("添加失败!");
}
}
@RequestMapping(value = "/exportExcel", method = RequestMethod.GET)
public void exportExcel(HttpServletResponse response,String tableName, String fileName, @RequestParam String guides) throws Exception {
ExcelData data = new ExcelData();
data.setName("sheet1");
List<String> titles = excelService.findTitlesByTableName(tableName);
data.setTitles(titles);
List<Map<String,Object>> fields = excelService.findFieldByTableName(tableName);
List<List<Object>> rows = new ArrayList();
List<Map<String,Object>> ExcelList = excelService.findExcelByIdAndTableName(tableName,guides.split(","));
for(Map<String,Object> map : ExcelList ){
List<Object> row1 = new ArrayList();
for (Map<String, Object> map1 : fields) {
String field_name = map1.get("field_name").toString();
String dataValue = map.get(field_name).toString();
row1.add(dataValue);
}
rows.add(row1);
}
data.setRows(rows);
ExportExcelUtils.exportExcel(response,fileName+".xlsx",data);
}
}
package com.location.bjgwserver.service;
import com.location.bjgwserver.domain.FieldOrderEntity;
import com.location.bjgwserver.mapper.ExcelMapper;
import com.location.bjgwserver.mapper.FieldOrderMapper;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import javax.annotation.Resource;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;
@Service
public class ExcelService {
@Resource
private ExcelMapper excelMapper;
@Resource
private FieldOrderMapper fieldOrderMapper;
public List<String> findTitlesByTableName(String tableName) {
List<String> list = new ArrayList();
List<FieldOrderEntity> tFieldOrderList = fieldOrderMapper.findTitlesByTableName(tableName);
for(int i=0;i<tFieldOrderList.size();i++){
list.add(tFieldOrderList.get(i).getField_alias_name());
}
return list;
}
public List<Map<String,Object>> findFieldByTableName(String tableName) {
String sql = "SELECT * FROM t_fieldorder where table_name "+" = " +"'"+tableName+"'" + " ORDER BY id" ;
List<Map<String,Object>> list = excelMapper.findFieldByTableName(sql);
return list;
}
public List<Map<String,Object>> findExcelByIdAndTableName(String tableName,String[] guides) {
String guid = "";
for(int i=0;i<guides.length;i++){
guid = guid+"'"+guides[i]+"'"+",";
}
guid = guid.substring(0,guid.length()-1);
String sql = "SELECT * FROM "+""+tableName+"" +" where guid "+" in(" +guid+")" ;
List<Map<String,Object>> list = excelMapper.findExcelByIdAndTableName(sql);
return list;
}
public void importExcel(MultipartFile testFile,String tableName) {
try {
InputStream inputStream = testFile.getInputStream();
String fileName = testFile.getOriginalFilename();
XSSFWorkbook book = new XSSFWorkbook(inputStream);
if(book != null){
XSSFSheet sheet = book.getSheetAt(0);
List<Map<String,Object>> Excels = convert(sheet,tableName);
for (Map<String,Object> map : Excels) {
excelMapper.importExcel(map,tableName);
}
}
} catch (IOException e) {
e.printStackTrace();
}
}
private List<Map<String,Object>> convert(XSSFSheet sheet,String tableName){
List<Map<String,Object>> ExcelEntityList = new ArrayList<>();
for (int i = 1; i <= sheet.getLastRowNum() ; i++) {
XSSFRow row = sheet.getRow(i);
Map<String,Object> excel = new HashMap<>();
String guid = UUID.randomUUID().toString().replace("-", "");
excel.put("guid",guid);
String sql = "SELECT * FROM t_fieldorder where table_name "+" = " +"'"+tableName+"'" + " AND is_export > 0 ORDER BY is_export" ;
List<Map<String,Object>> list = excelMapper.findFieldByTableName(sql);
Iterator<Cell> iterator = row.cellIterator();
while (iterator.hasNext()){
Cell cell = iterator.next();
for(int j =0 ;j<list.size(); j++){
if(cell.getColumnIndex() == j){
switch(list.get(j).get("type").toString())
{
case "string" :
cell.setCellType(Cell.CELL_TYPE_STRING);
break;
case "float" :
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
break;
case "boolean" :
cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
break;
default :
System.out.println("未知格式");
}
excel.put(list.get(j).get("field_name").toString(),cell.getStringCellValue());
}
}
}
ExcelEntityList.add(excel);
}
return ExcelEntityList;
}
}
package com.location.bjgwserver.mapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.Map;
@Repository
public interface ExcelMapper {
List<Map<String,Object>> findFieldByTableName(@Param(value="value") String sql);
List<Map<String,Object>> findExcelByIdAndTableName(@Param(value="value") String sql);
void importExcel(@Param(value="map") Map<String,Object> map,@Param(value="table_name") String tableName);
}
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.location.bjgwserver.mapper.ExcelMapper">
<select id="findFieldByTableName" resultType="java.util.Map">
${value}
</select>
<select id="findExcelByIdAndTableName" resultType="java.util.Map">
${value}
</select>
<insert id="importExcel" >
insert into ${table_name}
<!-- map表示dao层的参数, key表示map的键,value表示map的值-->
<!-- 利用foreach对map进行循环,取出里面的值-->
<foreach item="value" collection="map" index="key" open="(" separator="," close=")">
${key}
</foreach>
values
<foreach item="value" collection="map" index="key" open="(" separator="," close=")">
#{value}
</foreach>
</insert>
</mapper>
- 数据库t_fieldorder结构
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/c6a1e1c930aa7615a4811a6cd6215c34.png)
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/6b08b02b9d07cbd3360960e89d1f2b6a.png)