java poi導(dǎo)入Excel、導(dǎo)出excel
導(dǎo)出meven架包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.1</version>
</dependency>
導(dǎo)入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();
//解析Excel
List<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("導(dǎo)入失敗，請注意參數(shù)格式！");
}
long endTime = System.currentTimeMillis();
String time = String.valueOf((endTime - startTime) / 1000);
log.info("導(dǎo)入數(shù)據(jù)用時:"+time+"秒");
return ResponseMsg.success("導(dǎo)入成功！");
}
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;
/***
* 解析導(dǎo)入Excel數(shù)據(jù)
* @author manba
*/
@Slf4j
public class ReadPatientExcelUtil {
//總行數(shù)
private static int totalRows = 0;
//總條數(shù)
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;// 根據(jù)文件名判斷文件是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) {// 當(dāng)excel是2003時,創(chuàng)建excel2003
wb = new HSSFWorkbook(is);
} else {// 當(dāng)excel是2007時,創(chuàng)建excel2007
wb = 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) {
//默認會跳過第一行標(biāo)題
// 得到第一個shell
Sheet sheet = wb.getSheetAt(0);
// 得到Excel的行數(shù)
totalRows = sheet.getPhysicalNumberOfRows();
// 得到Excel的列數(shù)(前提是有行數(shù))
if (totalRows > 1 && sheet.getRow(0) != null) {
totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
}
List<DTO> DTOS = new ArrayList<>();
// 循環(huán)Excel行數(shù)
for (int r = 1; r < totalRows; r++) {
Row row = sheet.getRow(r);
if (row == null) {
continue;
}
DTO DTO = new DTO();
// 循環(huán)Excel的列
for (int c = 0; c < totalCells ; c++) {
Cell cell = row.getCell(c);
if (null != cell) {
if (c == 0) { //第一列
//如果是純數(shù)字
if (cell.getCellTypeEnum() == CellType.NUMERIC) {
cell.setCellType(CellType.NUMERIC);
int a =(int) cell.getNumericCellValue();
}
} else if (c == 1) {
//如果是double
if (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);
//如果是數(shù)字，需要轉(zhuǎn)換為字符串
String desc = NumberToTextConverter.toText(cell.getNumericCellValue());
}
}
}
}
//將excel解析出來的數(shù)據(jù)賦值給對象添加到list中
// 添加到list
DTOS.add(DTO);
}
return DTOS;
}
}
導(dǎo)出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();
//創(chuàng)建一個sheet，括號里可以輸入sheet名稱，默認為sheet0
Sheet 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");
}
//調(diào)用PoiUtils工具包
return PoiUtils.createExcelFile(workbook, DateUtils.fmtDateToStr(new Date(), "yyyy-MM-dd HH_mm_ss") );
}
PoiUtils文章來源：http://www.zghlxwxcb.cn/news/detail-610281.html
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，這是創(chuàng)建臨時文件，系統(tǒng)會自動給你的臨時文件編號，所以后面有號碼，你用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 {
//這里調(diào)用了IO工具包控制開關(guān)
IOUtils.closeQuietly(workbook);
IOUtils.closeQuietly(stream);
}
return file;
}
}
FileUtils文章來源地址http://www.zghlxwxcb.cn/news/detail-610281.html
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();
}
}
// 將輸入流使用指定編碼轉(zhuǎn)化為字符串
public static String inputStream2String(InputStream inputStream, String charset) throws Exception {
// 建立輸入流讀取類
InputStreamReader reader = new InputStreamReader(inputStream, charset);
// 設(shè)定每次讀取字符個數(shù)
char[] data = new char[512];
int dataSize = 0;
// 循環(huán)讀取
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();
// 準(zhǔn)備數(shù)據(jù)并解析。
byte[] bytes = xml.getBytes("UTF-8");
Document parsed = documentBuilder.parse(new ByteArrayInputStream(bytes));
// 獲取數(shù)據(jù)
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);
// 節(jié)點類型是 ELEMENT 才讀取值
// 進行此判斷是因為如果xml不是一行，而是多行且有很好的格式的，就會產(chǎn)生一些文本的node，這些node內(nèi)容只有換行符或空格
// 所以排除這些換行符和空格。
if (item.getNodeType() == Node.ELEMENT_NODE) {
String key = item.getNodeName();
String value = item.getTextContent();
// 拿到設(shè)置值的set方法。
Method declaredMethod = tclass.getDeclaredMethod("set" + key, String.class);
if (declaredMethod != null) {
declaredMethod.setAccessible(true);
declaredMethod.invoke(obj, value); // 設(shè)置值
}
}
}
return obj;
}
// 將二進制數(shù)據(jù)轉(zhuǎn)換為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"));
// 轉(zhuǎn)換為16進制字符串
return byte2HexString(digestResult);
}
public static boolean isEmpty(String value) {
return value == null || value.length() == 0;
}
}
到了這里，關(guān)于java poi導(dǎo)入Excel、導(dǎo)出excel的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
