java通用excel导入导出(适合n张表)
通用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.ap...
·
通用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;
/**
* @author csz
* @date 2020/3/6 20:05
*/
public class ExportExcelUtils {
/**
* 下载文件
* @param response
* @param fileName
* @param data
* @throws Exception
*/
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());
}
/**
* 创建 表格
* @param data
* @param out
* @throws Exception
*/
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 {
}
}
/**
* 将数据写入表格
* @param wb
* @param sheet
* @param data
*/
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);
}
/**
* 写入表头
* @param wb
* @param sheet
* @param titles
* @return
*/
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;
}
/**
* 将数据写入
* @param wb
* @param sheet
* @param rows
* @param rowIndex
* @return
*/
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;
}
/**
* 自动调整大小
* @param sheet
* @param columnNumber
*/
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);
}
}
}
/**
* 设置表格样式
* @param style
* @param border
* @param color
*/
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);
}
/**
* 将rgb颜色码 转换为 XSSFColor
* @param color
* @return
*/
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;
}
/**
* 将颜色码 转换为 r g b
* @param hex
* @return
*/
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 };
}
}
- ExcelData.java 实体
package com.location.bjgwserver.domain;
import java.io.Serializable;
import java.util.List;
import java.util.Map;
/**
* @author Thinkpad
* @date 2020/3/6 20:06
*/
public class ExcelData implements Serializable {
private static final long serialVersionUID = 4454016249210520899L;
/**
* 表头
*/
private List<String> titles;
/**
* 数据
*/
private List<List<Object>> rows;
//private List<Map<String,String>> 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;
}
}
- ExcelController 控制层
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;
/**
* Excel控制层
* @author csz
* @date 2020/3/10
*/
@RestController
@RequestMapping("/services/excel")
public class ExcelController {
@Resource
private ExcelService excelService;
/**
* 导入Excelexcel
* @param excelFile
* @author csz
* @data 2020/3/5
* @return
*/
@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("添加失败！");
}
}
/**
* 导出Excelexcel
* @param tableName 传入要导出的表名
* @param fileName 传入要导出的文件名
* @param guides 传入要导出的guides
* @author csz
* @data 2020/3/11
* @return
*/
@RequestMapping(value = "/exportExcel", method = RequestMethod.GET)
public void exportExcel(HttpServletResponse response,String tableName, String fileName, @RequestParam String guides) throws Exception {
//添加excel sheet名字
ExcelData data = new ExcelData();
data.setName("sheet1");
//根据表名获取标题名称
List<String> titles = excelService.findTitlesByTableName(tableName);
data.setTitles(titles);
//根据表名获取字段值和数据库对应
List<Map<String,Object>> fields = excelService.findFieldByTableName(tableName);
//代表整个excel
List<List<Object>> rows = new ArrayList();
//根据guid获取所有要导出的信息
List<Map<String,Object>> ExcelList = excelService.findExcelByIdAndTableName(tableName,guides.split(","));
for(Map<String,Object> map : ExcelList ){
//代表excel的每一行
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);
}
//将每一行添加到excel中
rows.add(row1);
}
//将整个excel添加到ExcelData实体中
data.setRows(rows);
//通过excel工具类将创建好的excel导出
ExportExcelUtils.exportExcel(response,fileName+".xlsx",data);
}
}
- ExcelService.java 业务层
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.*;
/**
* Excel业务层
* @author csz
* @date 2020/3/10
*/
@Service
public class ExcelService {
@Resource
private ExcelMapper excelMapper;
@Resource
private FieldOrderMapper fieldOrderMapper;
/**
* 根据表名查询excel第一行的title
* @return
*/
public List<String> findTitlesByTableName(String tableName) {
List<String> list = new ArrayList();
//查询t_fieldorder表中field_alias_name的值
List<FieldOrderEntity> tFieldOrderList = fieldOrderMapper.findTitlesByTableName(tableName);
for(int i=0;i<tFieldOrderList.size();i++){
//遍历集合查询大集合中field_name的值，并将其存入新的集合中
list.add(tFieldOrderList.get(i).getField_alias_name());
}
return list;
}
/**
* 根据表名查询字段值和数据库字段对应
* @return
*/
public List<Map<String,Object>> findFieldByTableName(String tableName) {
//查询t_fieldorder表中field_name的值
String sql = "SELECT * FROM t_fieldorder where table_name "+" = " +"'"+tableName+"'" + " ORDER BY id" ;
List<Map<String,Object>> list = excelMapper.findFieldByTableName(sql);
return list;
}
/**
* 根据表名,guides查询需要导出的数据
* @return
*/
public List<Map<String,Object>> findExcelByIdAndTableName(String tableName,String[] guides) {
String guid = "";
//加工in()后边的字符串，使其匹配in('0001','0002')这种格式
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;
}
/**
* 导入Excelexcel
* @author csz
* @data 2020/3/10
*/
public void importExcel(MultipartFile testFile,String tableName) {
try {
//获取文件的输入流
InputStream inputStream = testFile.getInputStream();
//根据不同类型excel创建book页。
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) {
//map.put("table_name",tableName);
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++) {
//第一行跳过，字段名，从第2行开始
XSSFRow row = sheet.getRow(i);
Map<String,Object> excel = new HashMap<>();
String guid = UUID.randomUUID().toString().replace("-", "");
excel.put("guid",guid);
//查询t_fieldorder表中field_name的值
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;
}
}
- ExcelMapper.java dao层
package com.location.bjgwserver.mapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.Map;
/**
* Exceldao层
* @author csz
* @date 2020/3/17
*/
@Repository
public interface ExcelMapper {
//根据表名查询字段名称
List<Map<String,Object>> findFieldByTableName(@Param(value="value") String sql);
//查询台账信息根据名称
List<Map<String,Object>> findExcelByIdAndTableName(@Param(value="value") String sql);
//导入Excel
void importExcel(@Param(value="map") Map<String,Object> map,@Param(value="table_name") String tableName);
}
- ExcelMapper.XML 操作数据库
<?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结构
华为开发者空间，是为全球开发者打造的专属开发空间，汇聚了华为优质开发资源及工具，致力于让每一位开发者拥有一台云主机，基于华为根生态开发、创新。
更多推荐
0
0- 0
已为社区贡献3条内容
所有评论(0)