首先引入pom
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.0.1</version>
</dependency>
写execel 和 读 execel 简单的例子
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
public class DemoPoi {
//cell转化任意类型
private static Object getCellValue(Cell cell) {
Object o = null;
//获得单元格的类型
CellType cellType = cell.getCellType();
switch (cellType){
case STRING:
o=cell.getStringCellValue();//字符串数据
break;
case NUMERIC: //数字类型 , 在excel中日期类型是数字类型
//判断是日期类型 还是数字类型
if(DateUtil.isCellDateFormatted(cell)){ //是不是日期类型
//是日期类型
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
o = simpleDateFormat.format(cell.getDateCellValue());
}else{
//数字类型
o=cell.getNumericCellValue();
}
break;
case BOOLEAN:
o=cell.getBooleanCellValue();//获得布尔类型数据
break;
default:
break;
}
return o;
}
@Test
public void ReadExecel() throws IOException {
Workbook workbook= new XSSFWorkbook("D:/demo.xlsx");
//3.获得表
Sheet sheet = workbook.getSheetAt(0);
int rows = sheet.getLastRowNum();
/*遍历行数*/
for (int i = 0; i < rows; i++) {
short lastCellNum = sheet.getRow(i).getLastCellNum();
for (int j = 0; j < lastCellNum ; j++) {
Cell cell = sheet.getRow(i).getCell(j);
if (cell != null) {
if (j == lastCellNum - 1) {
Object cellValue = getCellValue(cell);
System.out.println(cellValue);
} else {
Object cellValue = getCellValue(cell);
System.out.print(cellValue+" ");
}
}
}
}
}
/**
* 写execel
* 1.有excel的对象 workBook
* 2.有sheet表
* 3.有行
* 4.有单元格(列)
* 5.赋值数据
* 6.设置样式
* 7.下载(保存到本地)
* @param args
*/
public static void main(String[] args) throws IOException {
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet();
Row row = sheet.createRow(1);
//row.setHeight((short) (50*20));
//设置行高
row.setHeightInPoints(50);
Cell cell = row.createCell(1);
//设置列宽
sheet.setColumnWidth(1,20*256);
cell.setCellValue("加油!大智");
//设置样式
CellStyle cellStyle = workbook.createCellStyle();
//单元格周边加线条
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cell.setCellStyle(cellStyle);
//设置上下,左右居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cell.setCellStyle(cellStyle);
//设置字体
Font font = workbook.createFont();
font.setBold(true);
font.setFontHeightInPoints((short) 20);
//设置颜色
font.setColor((short) 3);
font.setFontName("楷体");
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
/*下载保存*/
FileOutputStream fileOutputStream = new FileOutputStream(new File("D://demo2112.xlsx"));
workbook.write(fileOutputStream);
fileOutputStream.close();
}
}
上传和下载execel
1.上传execel
@RequestMapping(value = "import",name = "上传货物")
public String importExecel(String contractId,MultipartFile file) throws IOException {
//创建货物集合
List<ContractProduct> list =new ArrayList<ContractProduct>();
//获取数据
if (file != null) {
//加载文件
XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
XSSFSheet sheet = workbook.getSheetAt(0);
int lastRowNum = sheet.getLastRowNum();
Row row = null;
Cell cell = null;
for (int i = 1; i <= lastRowNum; i++) {
row = sheet.getRow(i);
short lastCellNum = row.getLastCellNum();
Object[] objects = new Object[lastCellNum];
for (int j = 0; j < lastCellNum; j++) {
cell = row.getCell(j);
if (cell != null) {
//调用上传工具类
Object cellValue = uploadFile.getCellValue(cell);
objects[j] = cellValue;
}
}
System.out.println(Arrays.toString(objects));
//调用自定义构造
ContractProduct contractProduct = new ContractProduct(objects, user.getCompanyId(), user.getCompanyName(), contractId);
list.add(contractProduct);
//循环结束
}
contractProductService.saveList(list);
} else {
System.out.println("没有上传文件");
}
///WEB-INF/pages/cargo/contractProduct/import.jsp
return "redirect:/cargo/contractProduct/toImport.do?contractId="+contractId;
}
上传重点:
//看构造
ContractProduct contractProduct = new ContractProduct(objects, user.getCompanyId(), user.getCompanyName(), contractId);
//就这
public ContractProduct(Object []objs, String companyId, String companyName,String contractId) {
this.factoryName = objs[1].toString();
this.productNo = objs[2].toString();
this.cnumber = ((Double) objs[3]).intValue();
this.packingUnit = objs[4].toString();
this.loadingRate = objs[5].toString();
this.boxNum = ((Double) objs[6]).intValue();
this.price = (Double) objs[7]; //单价
this.productRequest=objs[8].toString();
this.productDesc=objs[9].toString();
this.companyId = companyId;
this.companyName = companyName;
this.contractId = contractId;
}
1.1 上传工具类 转换cell各种类型为object
//调用上传工具类
Object cellValue = uploadFile.getCellValue(cell);
//详情
public static Object getCellValue(Cell cell) {
Object o = null;
//获得单元格的类型
CellType cellType = cell.getCellType();
switch (cellType){
case STRING:
o=cell.getStringCellValue();//字符串数据
break;
case NUMERIC: //数字类型 , 在excel中日期类型是数字类型
//判断是日期类型 还是数字类型
if(DateUtil.isCellDateFormatted(cell)){ //是不是日期类型
//是日期类型
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
o = simpleDateFormat.format(cell.getDateCellValue());
}else{
//数字类型
o=cell.getNumericCellValue();
}
break;
case BOOLEAN:
o=cell.getBooleanCellValue();//获得布尔类型数据
break;
default:
break;
}
return o;
}
2.下载execel:
@RequestMapping(value = "printExcel",name = "打印出货表")
public String printExcel(String inputDate) throws IOException {
//获取合同数据
List<ContractProductVo> list=contractService.findByShipTime(inputDate,user.getCompanyId());
System.out.println(list);
//初始化值
Row row = null;
Cell cell = null;
//创建工作簿
Workbook workbook = new SXSSFWorkbook();
//创建表
Sheet sheet = workbook.createSheet();
//创建大标题
row = sheet.createRow(0);
//创建列
cell= row.createCell(1);
//合并单元格 从0行开始0行结束 从第一列 到 第8列结束
sheet.addMergedRegion(new CellRangeAddress(0,0,1,8));
//2019年2月 设置标题
cell.setCellValue(inputDate.replace("-","年")+"月出货表");
//设置样式
CellStyle cellStyle = bigTitle(workbook);
cell.setCellStyle(cellStyle);
//设置宽高
sheet.setDefaultRowHeightInPoints(36);
sheet.setDefaultRowHeight((short)(36*20));
sheet.setColumnWidth(1, 26 * 256);
sheet.setColumnWidth(2, 11 * 256);
sheet.setColumnWidth(3, 30 * 256);
sheet.setColumnWidth(4, 12 * 256);
sheet.setColumnWidth(5, 15 * 256);
sheet.setColumnWidth(6, 15 * 256);
sheet.setColumnWidth(7, 11 * 256);
sheet.setColumnWidth(8, 11 * 256);
sheet.setColumnWidth(9, 11 * 256);
//第二行 小标题 给每列赋值
Object[] objects = {"","客人", "订单号", "货号", "数量", "工厂", "工厂交期", "船期", "贸易条款"};
row = sheet.createRow(1);
CellStyle title = title(workbook);
for (int i = 0; i <= 8 ; i++) {
cell = row.createCell(i);
cell.setCellValue((String)objects[i]);
cell.setCellStyle(title);
}
//循环数据 遍历输出
for (int i = 1; i < list.size(); i++) {
//创建第三行 数据开始的第一行
row = sheet.createRow(i+1);
//获取第一个数据对象
ContractProductVo contractProductVo = list.get(i-1);
//一行 8个单元格赋值
for (int j = 1; j <= 8 ; j++) {
cell = row.createCell(j);
switch (j) {
case 1:
cell.setCellValue(contractProductVo.getCustomName());
break;
case 2:
cell.setCellValue(contractProductVo.getContractNo());
break;
case 3:
cell.setCellValue(contractProductVo.getProductNo());
break;
case 4:
cell.setCellValue(contractProductVo.getCnumber());
break;
case 5:
cell.setCellValue(contractProductVo.getFactoryName());
break;
case 6:
cell.setCellValue(contractProductVo.getDeliveryPeriod());
break;
case 7:
cell.setCellValue(contractProductVo.getShipTime());
break;
case 8:
cell.setCellValue(contractProductVo.getTradeTerms());
break;
default:
break;
}
}
}
//文件写回 浏览器
DownloadUtil downloadUtil = new DownloadUtil();
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
workbook.write(byteArrayOutputStream);
downloadUtil.download(byteArrayOutputStream,response,"出货表.xlsx");
return "cargo/print/contract-print";
}
下载的工具类:
package com.czh.common.utils;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
public class DownloadUtil {
/**
* @param filePath 要下载的文件路径
* @param returnName 返回的文件名
* @param response HttpServletResponse
* @param delFlag 是否删除文件
*/
protected void download(String filePath,String returnName,HttpServletResponse response,boolean delFlag){
this.prototypeDownload(new File(filePath), returnName, response, delFlag);
}
/**
* @param file 要下载的文件
* @param returnName 返回的文件名
* @param response HttpServletResponse
* @param delFlag 是否删除文件
*/
protected void download(File file,String returnName,HttpServletResponse response,boolean delFlag){
this.prototypeDownload(file, returnName, response, delFlag);
}
/**
* @param file 要下载的文件
* @param returnName 返回的文件名
* @param response HttpServletResponse
* @param delFlag 是否删除文件
*/
public void prototypeDownload(File file,String returnName,HttpServletResponse response,boolean delFlag){
// 下载文件
FileInputStream inputStream = null;
ServletOutputStream outputStream = null;
try {
if(!file.exists()) return;
response.reset();
//设置响应类型 PDF文件为"application/pdf",WORD文件为:"application/msword", EXCEL文件为:"application/vnd.ms-excel"。
response.setContentType("application/octet-stream;charset=utf-8");
//设置响应的文件名称,并转换成中文编码
//returnName = URLEncoder.encode(returnName,"UTF-8");
returnName = response.encodeURL(new String(returnName.getBytes(),"iso8859-1")); //保存的文件名,必须和页面编码一致,否则乱码
//attachment作为附件下载;inline客户端机器有安装匹配程序,则直接打开;注意改变配置,清除缓存,否则可能不能看到效果
response.addHeader("Content-Disposition", "attachment;filename="+returnName);
//将文件读入响应流
inputStream = new FileInputStream(file);
outputStream = response.getOutputStream();
int length = 1024;
int readLength=0;
byte buf[] = new byte[1024];
readLength = inputStream.read(buf, 0, length);
while (readLength != -1) {
outputStream.write(buf, 0, readLength);
readLength = inputStream.read(buf, 0, length);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
outputStream.flush();
} catch (IOException e) {
e.printStackTrace();
}
try {
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
//删除原文件
if(delFlag) {
file.delete();
}
}
}
/**
* @param byteArrayOutputStream 将文件内容写入ByteArrayOutputStream
* @param response HttpServletResponse 写入response
* @param returnName 返回的文件名
*/
public void download(ByteArrayOutputStream byteArrayOutputStream, HttpServletResponse response, String returnName) throws IOException{
response.setContentType("application/octet-stream;charset=utf-8");
//保存的文件名,必须和页面编码一致,否则乱码
returnName = response.encodeURL(new String(returnName.getBytes(),"iso8859-1"));
System.out.println("returnName : "+returnName);
response.addHeader("Content-Disposition", "attachment;filename=" + returnName);
response.setContentLength(byteArrayOutputStream.size());
ServletOutputStream outputstream = response.getOutputStream(); //取得输出流
byteArrayOutputStream.writeTo(outputstream); //写到输出流
byteArrayOutputStream.close(); //关闭
outputstream.flush(); //刷数据
}
}
模板打印execel
@RequestMapping(value = "printExcel",name = "打印出货表")
public String printExcel(String inputDate) throws IOException {
//获取合同数据
List<ContractProductVo> list=contractService.findByShipTime(inputDate,user.getCompanyId());
System.out.println(list);
//初始化值
Row row = null;
Cell cell = null;
//读取模板数据
InputStream is = session.getServletContext().getResourceAsStream("/make/xlsprint/tOUTPRODUCT.xlsx");
//创建workbook
XSSFWorkbook workbook = new XSSFWorkbook(is);
XSSFSheet sheet = workbook.getSheetAt(0);
row = sheet.getRow(0);
cell= row.getCell(1);
//大标题
cell.setCellValue( inputDate.replace("-","年")+"月出货表");
//小标题已写好
//开始写数据
//获取样式
row = sheet.getRow(2);
short lastCellNum = row.getLastCellNum();
//存储每个样式
CellStyle[] cellStyles = new CellStyle[lastCellNum];
for (int i = 1; i < lastCellNum; i++) {
cell = row.getCell(i);
CellStyle cellStyle = cell.getCellStyle();
cellStyles[i]=cellStyle;
}
//循环数据 遍历输出
for (int i = 1; i < list.size(); i++) {
//创建第三行 数据开始的第一行
row = sheet.createRow(i+1);
//获取第一个数据对象
ContractProductVo contractProductVo = list.get(i-1);
//一行 8个单元格赋值
for (int j = 1; j <= 8 ; j++) {
cell = row.createCell(j);
switch (j) {
case 1:
cell.setCellValue(contractProductVo.getCustomName());
cell.setCellStyle(cellStyles[1]);
break;
case 2:
cell.setCellValue(contractProductVo.getContractNo());
cell.setCellStyle(cellStyles[2]);
break;
case 3:
cell.setCellValue(contractProductVo.getProductNo());
cell.setCellStyle(cellStyles[3]);
break;
case 4:
cell.setCellValue(contractProductVo.getCnumber());
cell.setCellStyle(cellStyles[4]);
break;
case 5:
cell.setCellValue(contractProductVo.getFactoryName());
cell.setCellStyle(cellStyles[5]);
break;
case 6:
SimpleDateFormat simpleDateFormat1 = new SimpleDateFormat("yyyy-MM-dd");
String format1 = simpleDateFormat1.format(contractProductVo.getDeliveryPeriod());
cell.setCellValue(format1);
cell.setCellStyle(cellStyles[6]);
break;
case 7:
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
String format = simpleDateFormat.format(contractProductVo.getShipTime());
System.out.println(format);
cell.setCellValue(format);
cell.setCellStyle(cellStyles[7]);
break;
case 8:
cell.setCellValue(contractProductVo.getTradeTerms());
cell.setCellStyle(cellStyles[8]);
break;
default:
break;
}
}
}
//文件写回 浏览器
DownloadUtil downloadUtil = new DownloadUtil();
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
workbook.write(byteArrayOutputStream);
downloadUtil.download(byteArrayOutputStream,response,"出货表.xlsx");
return "cargo/print/contract-print";
}
一些简单的样式
//大标题的样式
public CellStyle bigTitle(Workbook wb){
CellStyle style = wb.createCellStyle();
Font font = wb.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short)16);
font.setBold(true);//字体加粗
style.setFont(font);
style.setAlignment(HorizontalAlignment.CENTER); //横向居中
style.setVerticalAlignment(VerticalAlignment.CENTER); //纵向居中
return style;
}
//小标题的样式
public CellStyle title(Workbook wb){
CellStyle style = wb.createCellStyle();
Font font = wb.createFont();
font.setFontName("黑体");
font.setFontHeightInPoints((short)12);
style.setFont(font);
style.setAlignment(HorizontalAlignment.CENTER); //横向居中
style.setVerticalAlignment(VerticalAlignment.CENTER); //纵向居中
style.setBorderTop(BorderStyle.THIN); //上细线
style.setBorderBottom(BorderStyle.THIN); //下细线
style.setBorderLeft(BorderStyle.THIN); //左细线
style.setBorderRight(BorderStyle.THIN); //右细线
return style;
}
//文字样式
public CellStyle text(Workbook wb){
CellStyle style = wb.createCellStyle();
Font font = wb.createFont();
font.setFontName("Times New Roman");
font.setFontHeightInPoints((short)10);
style.setFont(font);
style.setAlignment(HorizontalAlignment.LEFT); //横向居左
style.setVerticalAlignment(VerticalAlignment.CENTER); //纵向居中
style.setBorderTop(BorderStyle.THIN); //上细线
style.setBorderBottom(BorderStyle.THIN); //下细线
style.setBorderLeft(BorderStyle.THIN); //左细线
style.setBorderRight(BorderStyle.THIN); //右细线
return style;
}
测试例子:
CellStyle cellStyle = bigTitle(workbook);
cell.setCellStyle(cellStyle);