前言:小刘工作时候总是遇到excel的导入导出功能,今天小刘自己搭项目写功能!
poi操作excel进行数据的导入和导出
1.maven 相关jar包:
<!--POI操作EXCEL-->
<!-- excel -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
<!-- excel -->
<dependency>
<groupId>org.apache.log4j</groupId>
<artifactId>com.springsource.org.apache.log4j</artifactId>
<version>1.2.15</version>
</dependency>
<!-- jxl表格操作-->
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
2.导入和导出的工具类
package com;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.springframework.util.StringUtils;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.NumberFormat;
import java.util.*;
/**
* @author lhl
* @version 1.0
* @date 2020/1/4 14:42
* @description TODO
*/
public class ExcelUtils {
/**
*导出excel工具类
* @param list 数据集合
* @param titles 每个row的标题
* @param os 输出流
* @param str 导出文档的sheet标题
*/
public static void exportExcelDataInfo(List<Map<String, Object>> list,List<String> titles, OutputStream os, String str) {
if(list != null) {
// HSSFWorkbook 97-2003 版本的 ,
// XSSFWorkbook 2007以上版本的 无关紧要导出哪个版本的。
Workbook workbook = new HSSFWorkbook();
// Sheet sheet = workbook.createSheet(str);
for(int k = 0;k<list.size()/60000+1;k++) {
int num = list.size() < ((k + 1) * 60000 - 1) ? list.size()-1 :((k + 1) * 60000 - 1);
List<Map<String, Object>> excelDatas = list.subList(k * 60000, num+1);
Sheet sheet = workbook.createSheet(str+(k+1));
if(titles != null && titles.size() > 0) {
int r = 0;
int columnNum = titles.size();
Row titleRow = sheet.createRow(r++);
CellStyle cellStyle = getTitleStyle(workbook);
for(int i = 0; i < columnNum; i++) {
Cell titleCell = titleRow.createCell(i);
titleCell.setCellValue(titles.get(i));
titleCell.setCellStyle(cellStyle);
}
if(excelDatas != null && excelDatas.size() > 0) {
for(Map<String, Object> map : excelDatas) {
Row row = sheet.createRow(r++);// 数据行
for(int j = 0; j < columnNum; j++) {
Cell cell = row.createCell(j);
cell.setCellValue(Objects.toString(map.get(titles.get(j)), ""));
}
}
}
sheet.setDefaultColumnStyle(k, getErrorColumnStyle(workbook));
}
}
try {
workbook.write(os);
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 解析excel数据
*
* @Title: excel2DataInfo
* @param inputStream
* @return
* @return Map<String,Object>
* List<String> titles excelTitles
* List<List<String>> excelList
* @throws
*/
public static Map<String,Object> importExcelToSql(InputStream inputStream) {
Map<String,Object>mapExcel=new HashMap<>();
List<String> titles = new ArrayList<String>();
List<List<String>> dataInfos = new ArrayList<List<String>>();
try {
Workbook workbook = WorkbookFactory.create(inputStream);// 处理输入流
Sheet sheet = workbook.getSheetAt(0);// 获取第一个sheet
int rowNum = sheet.getLastRowNum();// 总行数
Row firstRow = sheet.getRow(0); // 获取总行数
int columnNum = firstRow.getLastCellNum();// 总列数
for(int i = 0; i < columnNum; i++) {// 获取excel每列的表头
Cell cell = firstRow.getCell((short) i);
String title = cell.getStringCellValue();
if(StringUtils.isEmpty(title)) {
break;
}
titles.add(title);
}
mapExcel.put("excelTitles",titles);
for (int i = 1; i <= rowNum; i++) {
List<String> cellValues = new ArrayList<String>();
Row row = sheet.getRow(i);
for (int j = 0; j < columnNum; j++) {//对一行的每个列进行解析
Cell cell = row.getCell((short) j);
Object value = null;
if(null!=cell){
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BLANK: // 貌似永远不会出现,
value = "";
break;
case Cell.CELL_TYPE_NUMERIC: // 数值或者日期类型
if (DateUtil.isCellDateFormatted(cell)) { // 日期
value = cell.getDateCellValue();
} else {// 数值
NumberFormat nf = NumberFormat.getInstance();
String s = nf.format(cell.getNumericCellValue());
if (s.indexOf(",") >= 0) {
s = s.replace(",", "");
}
value = s;
}
break;
case Cell.CELL_TYPE_BOOLEAN: // 布尔值
value = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_STRING: // 字符串
value = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_FORMULA://excel公式
try {
value = cell.getStringCellValue();
} catch (IllegalStateException e) {
value = String.valueOf(cell.getNumericCellValue());
}
break;
default:
break;
}
}
cellValues.add(Objects.toString(value, ""));
}
dataInfos.add(cellValues);
}
mapExcel.put("excelList",dataInfos);
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return mapExcel;
}
/**
* 标题单元格样式
*
* @Title: getTitleStyle
* @param workbook
* @return
* @return CellStyle
* @throws
*/
public static CellStyle getTitleStyle(Workbook workbook) {
CellStyle titleStyel = workbook.createCellStyle();
/*XSSFFont font = (XSSFFont) workbook.createFont();
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 设置粗体 */
HSSFFont font = (HSSFFont) workbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
titleStyel.setFont(font);
return titleStyel;
}
/**
* 设置错误单元格样式
*
* @Title: getErrorColumnStyle
* @param workbook
* @return
* @return CellStyle
* @throws
*/
public static CellStyle getErrorColumnStyle(Workbook workbook) {
CellStyle errorColumnStyel = workbook.createCellStyle();
/*XSSFFont font = (XSSFFont) workbook.createFont();*/
// font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 设置粗体
HSSFFont font = (HSSFFont) workbook.createFont();
font.setColor(HSSFColor.WHITE.index);
font.setColor(Font.COLOR_RED);
errorColumnStyel.setFont(font);
errorColumnStyel.setFillBackgroundColor(HSSFColor.RED.index);
return errorColumnStyel;
}
}
3.使用方法
/**
* 获取导出的数据,这里可以根据具体需求查询所要导出的数据
*
* @return
*/
List<Map<String,Object>> getExportSqlList();
/**------------------mybatis语句---------------------------**/
<sql id="exportSql">
c.ID "编号",c.Name "名称",c.CountryCode "城市编码",
c.District "区域",
c.Population "人口",
status "状态",sex "性别",birthday "生日",createTime "创建时间"
</sql>
<select id="getExportSqlList" parameterType="map" resultMap="map">
select <include refid="exportSql"/>
from city as c
</select>
4.页面以及控制层方法
/**这里是全部数据,可以根据具体需求导出
* 数据为world数据库中city表
* 个别自己新增的字段
* 导出数据到excel
*/
@RequestMapping(value = "/getExportExcel")
public void getExportExcel(HttpServletResponse response){
try {
List<String> titles = Arrays.asList(
"编号",
"名称",
"城市编码",
"区域",
"人口",
"状态",
"性别",
"生日",
"创建时间"
);
List<Map<String, Object>> listData = cityDao.getExportSqlList();
response.setContentType("text/html; charset=utf-8");
response.setContentType("application/octet-stream");
//xxxx列表为文件的列表
response.addHeader("Content-Disposition", "attachment;filename=" + new String("xxxx列表.xls".getBytes("GB2312"), "ISO-8859-1"));
ExcelUtils.exportExcelDataInfo(listData,titles,response.getOutputStream(),"xxxx");
} catch (Exception e) {
e.printStackTrace();
}
}
5.效果:
至此基本的导出功能实现完成了。
导入:
注意:上传的东西也要有:
<!--文件上传所需的jar-->
<!-- https://mvnrepository.com/artifact/commons-fileupload/commons-fileupload -->
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
<!-- SpringMVC上传文件时,需要配置MultipartResolver处理器 -->
<bean id="multipartResolver"
class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
<property name="defaultEncoding" value="utf-8"></property>
<property name="maxUploadSize" value="10485760000"></property>
<property name="maxInMemorySize" value="40960"></property>
<!-- 指定所上传文件的总大小不能超过200KB。注意maxUploadSize属性的限制不是针对单个文件,而是所有文件的容量之和 -->
</bean>
控制层代码:
/**
* 读取excel存储到数据库中
*/
@RequestMapping(value = "/excelToSql")
@ResponseBody
public void excelToSql(MultipartFile excelFile) throws IOException {
System.out.println("-----------------------------------------------");
try {
Map<String, Object> map = ExcelUtils.importExcelToSql(excelFile.getInputStream());
List<String> titles= (List<String>) map.get("excelTitles");
List<List<String>>mapList= (List<List<String>>) map.get("excelList");
System.out.println("---------------读出excel数据------------------");
mapList.forEach(t-> System.out.println(t));
} catch (IOException e) {
e.printStackTrace();
}
}
显示效果:
即打印了出来,将这些数据一个个或者批量插入即可:
工作具体的业务逻辑:
/**
* 获取excel中的数据
* @param inputStream
* @param sheetIndex
* @return
*/
public static ExcelImportDataInfo getExcelData(InputStream inputStream, int sheetIndex) {
ExcelImportDataInfo excelDataInfo = new ExcelImportDataInfo();
List<String> titles = new ArrayList<String>();
List<List<String>> dataInfos = new ArrayList<List<String>>();
try {
Workbook workbook = WorkbookFactory.create(inputStream);// 处理输入流
Sheet sheet = workbook.getSheetAt(sheetIndex);// 获取第一个sheet
System.out.println("sheetName-------"+sheet.getSheetName());
int rowNum = sheet.getLastRowNum();// 总行数
Row firstRow = sheet.getRow(0); // 获取总行数
int columnNum = firstRow.getLastCellNum();// 总列数
for (int i = 0; i < columnNum; i++) {// 获取excel表头
Cell cell = firstRow.getCell((short) i);
String title = cell.getStringCellValue();
if (org.apache.commons.lang3.StringUtils.isEmpty(title)) {
break;
}
titles.add(title);
}
excelDataInfo.setTitles(titles);
Map<Object,Integer>mapCount=new HashMap<>();//每种药品的唯一标识和数量
Map<Object,Integer>mapIndex=new HashMap<>();//每种药品所在行的索引
for (int i = 1; i <= rowNum; i++) {
int count=0;//记录客户端药品重复数量
List<String> cellValues = new ArrayList<String>();
Row row = sheet.getRow(i);
Object indexValue=null;//唯一标识的数值
for (int j = 0; j < columnNum; j++) {//对一行的每个列进行解析
Cell cell = row.getCell((short) j);
Object value = null;
if (null != cell) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BLANK: // 貌似永远不会出现,
value = "";
break;
case Cell.CELL_TYPE_NUMERIC: // 数值或者日期类型
if (DateUtil.isCellDateFormatted(cell)) { // 日期
value = cell.getDateCellValue();
} else {// 数值
NumberFormat nf = NumberFormat.getInstance();
String s = nf.format(cell.getNumericCellValue());
if (s.indexOf(",") >= 0) {
s = s.replace(",", "");
}
value = s;
}
break;
case Cell.CELL_TYPE_BOOLEAN: // 布尔值
value = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_STRING: // 字符串
value = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_FORMULA://excel公式
try {
value = cell.getStringCellValue();
} catch (IllegalStateException e) {
value = String.valueOf(cell.getNumericCellValue());
}
break;
default:
break;
}
}
cellValues.add(Objects.toString(value, ""));
/**
* 判断客户端excel的药品是否有重复的药品,有重复的数量相加
*/
if (j==6){
if (mapCount.containsKey(value)){
++count;
Integer number=mapCount.get(value);
mapCount.put(value,number+Integer.parseInt(cellValues.get(4).toString()));
}else {
mapCount.put(value,Integer.parseInt(cellValues.get(4).toString()));
}
indexValue=value;
}
}
if (count!=0){
Integer index = mapIndex.get(indexValue);
List<String> list = dataInfos.get(index-1);//特别注意索引
list.set(4,mapCount.get(indexValue).toString());
continue;
}else {
mapIndex.put(indexValue,i);
}
dataInfos.add(cellValues);
}
excelDataInfo.setExcelimportDatas(dataInfos);
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return excelDataInfo;
}
@Override
public Pagination importDrugExcel(List<List<String>> listList, SysUser sysUser) {
List<Map<String, Object>> addlist = new ArrayList<>();
List<Map<String, Object>> update = new ArrayList<>();
Pagination pagination = new Pagination();
pagination.setMsg("操作成功");
pagination.setSuccess(true);
Integer index = 0;
for (List<String> info : listList) {
++index;
Map<String, Object> map = new HashMap<>();
map.put("productName", info.get(0));//药品名称 productName
map.put("outlook", info.get(1));//规格型号 outlook
map.put("unit", info.get(2));//单位 unit
map.put("temporaryRetailPrice", info.get(3));//零售价、、参考价
map.put("count", info.get(4));//数量 count
map.put("companyNameSc", info.get(5));//生产企业 companyNameSc
map.put("procurecatalogId", info.get(7));//药品编码 procurecatalogId
pagination.setConditions(map);
// DrugpurSupplyStore drugpurSupplyStore = drugpurSupplyStoreDao.existByprocureCatalogId(Integer.parseInt(map.get("procurecatalogId").toString()), sysUser.getOrgId());
DrugpurSupplyStore drugpurSupplyStore =drugpurSupplyStoreDao.getOneProcureCatalog(
Integer.parseInt(map.get("procurecatalogId").toString()),
sysUser.getOrgId(),
map.get("productName").toString(),map.get("outlook").toString(),map.get("unit").toString(),map.get("companyNameSc").toString());
if (drugpurSupplyStore != null) {
//更新
map.put("storeId", drugpurSupplyStore.getStoreId());
map.put("lastUpdateUserId", sysUser.getUserId());
map.put("lastUpdateUserName", sysUser.getUserName());
map.put("lastUpdateTime", new Date());
//更新数量
Integer count = drugpurSupplyStore.getCount();
if (count != null) {
int count1 = Integer.parseInt(String.valueOf(map.get("count")));
int c=count1 + count;
map.put("count", c);
}
update.add(map);
} else {
map.put("addUserId", sysUser.getUserId());
map.put("addUserName", sysUser.getUserName());
map.put("addTime", new Date());
map.put("hospCode", sysUser.getOrgId());
map.put("hospName", sysUser.getName());
addlist.add(map);
}
}
if (addlist.size() != 0) {
drugpurSupplyStoreDao.importProcureCatalogFile(addlist);
System.out.println("------------------导入成功---------------------");
}
if (update.size() != 0) {
drugpurSupplyStoreDao.importFileUpdateBatch(update);
System.out.println("-------------------修改成功---------------------------");
}
return pagination;
}
mybatis方法:
<insert id="importProcureCatalogFile" parameterType="list">
INSERT INTO DRUGPUR_SUPPLY_STORE (
STORE_ID,
COUNT,
PROCURECATALOG_ID,
PRODUCT_NAME,
OUTLOOK,
UNIT,
COMPANY_NAME_SC,
TEMPORARY_RETAIL_PRICE,
ADD_USER_ID ,
ADD_USER_NAME ,
ADD_TIME,
HOSP_CODE,
HOSP_NAME
)
<foreach collection="list" item="item" index="index" separator="union all">
(select sys_guid(),
#{item.count},
#{item.procurecatalogId},
#{item.productName},
#{item.outlook},
#{item.unit},
#{item.companyNameSc},
#{item.temporaryRetailPrice},
#{item.addUserId},
#{item.addUserName},
SYSDATE,
#{item.hospCode},
#{item.hospName}
from dual)
</foreach>
</insert>
<update id="importFileUpdateBatch" parameterType="list">
begin
<foreach collection="list" item="item" index="index" separator=";">
update DRUGPUR_SUPPLY_STORE
<set>
COUNT = #{item.count},
LAST_UPDATE_USER_ID = #{item.lastUpdateUserId},
LAST_UPDATE_USER_NAME = #{item.lastUpdateUserName},
LAST_UPDATE_TIME = #{item.lastUpdateTime},
</set>
WHERE STORE_ID = #{item.storeId}
</foreach>
;end;
</update>
如有不解,请加java爱好群大家交流:852665736;群里都是热心好客的小伙伴,大家一同进步。
无偿免费分享源码以及技术和面试文档,更多优秀精致的源码技术栈分享请关注微信公众号:gh_817962068649