java poi导入Excel、导出excel

java poi导入Excel、导出excel

导出meven架包

        <dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.1</version></dependency>

导入Excel

public void uploadFile(HttpServletRequest request, HttpServletResponse response, @RequestParam(value="file",required = false) MultipartFile file) {return import(request, response, file);}public void import(HttpServletRequest request, HttpServletResponse response, MultipartFile file) {long startTime = System.currentTimeMillis();//解析ExcelList<DTO> excelInfo = ReadPatientExcelUtil.getExcelInfo(file);if(excelInfo!=null && excelInfo.size()>0){for (int i = 0; i < excelInfo.size(); i++) {DTO dto = excelInfo.get(i);}}else{System.out.println("导入失败，请注意参数格式！");}long endTime = System.currentTimeMillis();String time = String.valueOf((endTime - startTime) / 1000);log.info("导入数据用时:"+time+"秒");return ResponseMsg.success("导入成功！");}

ReadPatientExcelUtil

import com.ly.directserver.dto.*;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;/**** 解析导入Excel数据* @author manba*/
@Slf4j
public class ReadPatientExcelUtil {//总行数private static int totalRows = 0;//总条数private static int totalCells = 0;//错误信息接收器private static String errorMsg;/**** 读取Excel* @param mFile* @return*/public static List<DTO> getExcelInfo(MultipartFile mFile) {String fileName = mFile.getOriginalFilename();//获取文件名try {if (!validateExcel(fileName)) {// 验证文件名是否合格return null;}boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本if (isExcel2007(fileName)) {isExcel2003 = false;}List<DTO> agentList = getExcel(mFile.getInputStream(), isExcel2003);return agentList;} catch (Exception e) {e.printStackTrace();}return null;}public static List<DTO> getAgentExcel(InputStream is, boolean isExcel2003) {try {Workbook wb = null;if (isExcel2003) {// 当excel是2003时,创建excel2003wb = new HSSFWorkbook(is);} else {// 当excel是2007时,创建excel2007wb = new XSSFWorkbook(is);}List<DTO> DTOS = readExcelValue(wb);// 读取Excel里面客户的信息return DTOS;} catch (IOException e) {e.printStackTrace();}return null;}private static List<DTO> readExcelValue(Workbook wb) {//默认会跳过第一行标题// 得到第一个shellSheet sheet = wb.getSheetAt(0);// 得到Excel的行数totalRows = sheet.getPhysicalNumberOfRows();// 得到Excel的列数(前提是有行数)if (totalRows > 1 && sheet.getRow(0) != null) {totalCells = sheet.getRow(0).getPhysicalNumberOfCells();}List<DTO> DTOS = new ArrayList<>();// 循环Excel行数for (int r = 1; r < totalRows; r++) {Row row = sheet.getRow(r);if (row == null) {continue;}DTO DTO = new DTO();// 循环Excel的列for (int c = 0; c < totalCells ; c++) {Cell cell = row.getCell(c);if (null != cell) {if (c == 0) {           //第一列//如果是纯数字if (cell.getCellTypeEnum() == CellType.NUMERIC) {cell.setCellType(CellType.NUMERIC);int a =(int) cell.getNumericCellValue();}}  else if (c == 1) {//如果是doubleif (cell.getCellTypeEnum() == CellType.NUMERIC) {cell.setCellType(CellType.NUMERIC);}double stringCellValue = cell.getNumericCellValue();} else if (c == 2) {if (cell.getCellTypeEnum() == CellType.STRING) {cell.setCellType(CellType.STRING);//如果是字符串String str = cell.getStringCellValue();}else if (cell.getCellTypeEnum() == CellType.NUMERIC) {cell.setCellType(CellType.NUMERIC);//如果是数字，需要转换为字符串String desc = NumberToTextConverter.toText(cell.getNumericCellValue());}}}}//将excel解析出来的数据赋值给对象添加到list中// 添加到listDTOS.add(DTO);}return DTOS;}}

导出Excel

	    public void exportCollectRecord(HttpServletResponse res){File file = createExcelFile();FileUtils.downloadFile(res, file, file.getName());}public static File createExcelFile(List<DTO> list) {Workbook workbook = new XSSFWorkbook();//创建一个sheet，括号里可以输入sheet名称，默认为sheet0Sheet sheet = workbook.createSheet();Row row0 = sheet.createRow(0);int columnIndex = 0;row0.createCell(columnIndex).setCellValue("xxx");row0.createCell(++columnIndex).setCellValue("xxx");row0.createCell(++columnIndex).setCellValue("xxx");for (int i = 0; i < list.size(); i++) {DTO dto = list.get(i);Row row = sheet.createRow(i + 1);for (int j = 0; j < columnIndex + 2; j++) {row.createCell(j);}columnIndex = 0;row.getCell(columnIndex).setCellValue("xxx");row.getCell(++columnIndex).setCellValue("xxx");row.getCell(++columnIndex).setCellValue("xxx");}//调用PoiUtils工具包return PoiUtils.createExcelFile(workbook, DateUtils.fmtDateToStr(new Date(), "yyyy-MM-dd HH_mm_ss") );}

PoiUtils

import org.apache.poi.ss.usermodel.Workbook;
import java.io.*;public class PoiUtils {/*** 生成Excel文件* @param workbook* @param fileName* @return*/public static File createExcelFile(Workbook workbook, String fileName) {OutputStream stream = null;File file = null;try {//用了createTempFile，这是创建临时文件，系统会自动给你的临时文件编号，所以后面有号码，你用createNewFile的话就完全按照你指定的名称来了file = File.createTempFile(fileName, ".xlsx");stream = new FileOutputStream(file.getAbsoluteFile());workbook.write(stream);} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();} finally {//这里调用了IO工具包控制开关IOUtils.closeQuietly(workbook);IOUtils.closeQuietly(stream);}return file;}}

FileUtils

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import javax.servlet.http.HttpServletResponse;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import java.io.*;
import java.lang.reflect.Method;
import java.security.MessageDigest;public class FileUtils {/*** 下载文件* @param response* @param file* @param newFileName*/public static void downloadFile(HttpServletResponse response, File file, String newFileName) {try {response.setHeader("Content-Disposition", "attachment; filename=" + new String(newFileName.getBytes("ISO-8859-1"), "UTF-8"));BufferedOutputStream bos = new BufferedOutputStream(response.getOutputStream());InputStream is = new FileInputStream(file.getAbsolutePath());BufferedInputStream bis = new BufferedInputStream(is);int length = 0;byte[] temp = new byte[1 * 1024 * 10];while ((length = bis.read(temp)) != -1) {bos.write(temp, 0, length);}bos.flush();bis.close();bos.close();is.close();} catch (Exception e) {e.printStackTrace();}}// 将输入流使用指定编码转化为字符串public static String inputStream2String(InputStream inputStream, String charset) throws Exception {// 建立输入流读取类InputStreamReader reader = new InputStreamReader(inputStream, charset);// 设定每次读取字符个数char[] data = new char[512];int dataSize = 0;// 循环读取StringBuilder stringBuilder = new StringBuilder();while ((dataSize = reader.read(data)) != -1) {stringBuilder.append(data, 0, dataSize);}return stringBuilder.toString();}private static DocumentBuilderFactory documentBuilderFactory = null;public static <T> T parseXml2Obj(String xml, Class<T> tclass) throws Exception {if (isEmpty(xml)) throw new NullPointerException("要解析的xml字符串不能为空。");if (documentBuilderFactory == null) { // 文档解析器工厂初始documentBuilderFactory = DocumentBuilderFactory.newInstance();}// 拿到一个文档解析器。DocumentBuilder documentBuilder = documentBuilderFactory.newDocumentBuilder();// 准备数据并解析。byte[] bytes = xml.getBytes("UTF-8");Document parsed = documentBuilder.parse(new ByteArrayInputStream(bytes));// 获取数据T obj = tclass.newInstance();Element documentElement = parsed.getDocumentElement();NodeList childNodes = documentElement.getChildNodes();for (int i = 0; i < childNodes.getLength(); i++) {Node item = childNodes.item(i);// 节点类型是 ELEMENT 才读取值// 进行此判断是因为如果xml不是一行，而是多行且有很好的格式的，就会产生一些文本的node，这些node内容只有换行符或空格// 所以排除这些换行符和空格。if (item.getNodeType() == Node.ELEMENT_NODE) {String key = item.getNodeName();String value = item.getTextContent();// 拿到设置值的set方法。Method declaredMethod = tclass.getDeclaredMethod("set" + key, String.class);if (declaredMethod != null) {declaredMethod.setAccessible(true);declaredMethod.invoke(obj, value); // 设置值}}}return obj;}// 将二进制数据转换为16进制字符串。public static String byte2HexString(byte[] src) {StringBuilder stringBuilder = new StringBuilder();if (src == null || src.length <= 0) {return null;}for (byte b : src) {String hv = Integer.toHexString(b & 0xFF);if (hv.length() < 2) {stringBuilder.append(0);}stringBuilder.append(hv);}return stringBuilder.toString();}// 对字符串进行sha1加签public static String sha1(String context) throws Exception {// 获取sha1算法封装类MessageDigest sha1Digest = MessageDigest.getInstance("SHA-1");// 进行加密byte[] digestResult = sha1Digest.digest(context.getBytes("UTF-8"));// 转换为16进制字符串return byte2HexString(digestResult);}public static boolean isEmpty(String value) {return value == null || value.length() == 0;}}