本人记性比较差,为了方便快速开发EXCL导入导出功能,记录一下EXCL基本代码
package com.depoo.npcCustomer.controller.committeeManagement;
import org.apache.http.entity.ContentType;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
public class Test {
public static void main(String[] args) throws IOException {
//由于上传文件格式都是MultipartFile 所以把File 转换成 MultipartFile
File file = new File("d:\\ss.xls");
FileInputStream fileInputStream = new FileInputStream(file);
MultipartFile multipartFile = new MockMultipartFile(file.getName(), file.getName(),
ContentType.APPLICATION_OCTET_STREAM.toString(), fileInputStream);
Test.importExlce(multipartFile);
//导出
// Test.importExlce(HttpServletResponse respon);
}
/**
* EXCL 导入
*
* @param file
* @return void
* @throws
* @Date 2019/11/27 10:32
*/
public static void importExlce(MultipartFile file) throws IOException {
Workbook workbook = null;
try { // excl 2007导入
workbook = new HSSFWorkbook(file.getInputStream()); // 创建工作薄
} catch (Exception e) {
workbook = new XSSFWorkbook(file.getInputStream());
}
Sheet sheet = workbook.getSheetAt(0);
//sheet.getLastRowNum();//返回最后一行的索引,即比行总数小1
// 如果sheet中一行数据都没有则返回-1,只有第一行有数据则返回0,最后有数据的行是第n行则返回 n-1;
int totalRow = sheet.getLastRowNum();
Row row;// 对应excel的行
Cell cell;// 对应excel的列
for (int i = 0; i <= totalRow; i++) {
//行
String rowNum = (i + 1) + "";
row = sheet.getRow(i);
//返回的是最后一列的列数,即等于总列数
// row中一列数据都没有则返回-1,只有第一列有数据则返回1,最后有数据的列是第n列则返回 n;
int totaleCell = row.getLastCellNum();
try {
//列
for (int j = 0; j < totaleCell; j++) {
cell = row.getCell(j);
System.out.print("第" + rowNum + "行第" + (j + 1) + "列的值:" + cell.getStringCellValue() + "\t");
if (j == (totaleCell - 1)) {
System.out.println();
}
}
} catch (Exception e) {
System.out.println("请设置EXCEL文件单元格类型为文本类型" +
"【步骤:选中整列单元格)->数据->分列->下一步->下一步->列数据格式---选择文本,完成即可】");
}
}
}
/**
* excl 导出
*
* @param response
* @return void
* @throws
* @Author luyatao
* @Date 2019/11/27 10:37
*/
public static void importExlce(HttpServletResponse response) throws IOException {
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet("测试excl");
// 设置表格默认列宽度为15个字节
int defaultColumnWidth = 20;
sheet.setDefaultColumnWidth(defaultColumnWidth);
// 创建 标题所在行
HSSFRow row0 = sheet.createRow(0);
row0.setHeight((short) 500);
HSSFCell tcell = row0.createCell(0);
tcell.setCellValue("测试");
Test.exportExcelofCustomer(response, workbook, "测试EXCL导出");
}
/**
* 导出excel并弹出下载(客户列表)
*
* @param response
* @param workbook
* @param fileName
*/
public static void exportExcelofCustomer(HttpServletResponse response, Workbook workbook, String fileName) {
// 弹出下载
ByteArrayOutputStream os = null;
InputStream is = null;
OutputStream out = null;
try {
// 将 excel转 为流
os = new ByteArrayOutputStream();
workbook.write(os);
is = new ByteArrayInputStream(os.toByteArray());
// 设置response参数,可以打开下载页面
response.reset();
response.setCharacterEncoding("utf-8");
response.setContentType("application/vnd.ms-excel");
DateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
response.setHeader("Content-Disposition", "attachment;fileName="
+ new String((fileName
+ dateFormat.format(new Date()) + ".xls")
.getBytes(), "iso-8859-1"));
out = response.getOutputStream();
byte[] b = new byte[2048];
int length;
while ((length = is.read(b)) > 0) {
out.write(b, 0, length);
}
} catch (IOException ioE) {
ioE.printStackTrace();
} finally {
try {
if (os != null) {
os.close();
}
if (is != null) {
is.close();
}
if (out != null) {
out.close();
}
} catch (IOException ioE) {
ioE.printStackTrace();
}
}
}
}
这个类用到的jar 包有poi-3.10-FINAL-20140208.jar,httpcore-4.4.1.jar,spring-test-3.2.0.RELEASE.jar