一、导入
@Override
public ResultVO<String> mimport(HttpServletRequest request, MultipartFile file) {
ResultVO<String> result=new ResultVO<String>();
try {
if(file==null) {
result.setMsg("上传文件不能为空");
return result;
}
String fileName = file.getOriginalFilename();
if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
result.setMsg("上传文件格式错误,请上传后缀为.xls或.xlsx的文件");
return result;
}
boolean isExcel2003 = true;
if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
isExcel2003 = false;
}
InputStream is = file.getInputStream();
Workbook wb = null;
if (isExcel2003) {
wb = new HSSFWorkbook(is);
} else {
wb = new XSSFWorkbook(is);
}
Sheet sheet = wb.getSheetAt(0);
if(sheet!=null){
//notNull = true;
}
for (int r = 1; r <= sheet.getLastRowNum(); r++) {
Row row = sheet.getRow(r);
if (row == null) {
continue;
}
int totalCells= row.getPhysicalNumberOfCells();
for (int j = 1; j < totalCells; j++) {
if(row.getCell(j)!=null){
row.getCell(j).setCellType(CellType.STRING);
String cellValue = row.getCell(j).getStringCellValue();
System.err.println(cellValue);
}
}
}
} catch (IOException e) {
}
result.setMsg("success");
return result;
}
二、导出
ExcelData data=new ExcelData();
List<String[]> orders=new ArrayList<String[]>();
for (VWorkOrderPojo workOrderPojo:workOrders){
String[] arr=new String[]{workOrderPojo.getWorkorderid(), workOrderPojo.getWorkorderstatename()};
orders.add(arr);
}
data.setData(orders);
data.setFileName("导出");
String[] head=new String[]{"工单id","工单状态"};
data.setHead(head);
ExcelUtil.exportExcel(response,data);
public static void exportExcel(HttpServletResponse response, ExcelData data) {
// log.info("导出解析开始,fileName:{}",data.getFileName());
try {
//实例化HSSFWorkbook
HSSFWorkbook workbook = new HSSFWorkbook();
//创建一个Excel表单,参数为sheet的名字
HSSFSheet sheet = workbook.createSheet("sheet");
//设置表头
setTitle(workbook, sheet, data.getHead());
//设置单元格并赋值
setData(sheet, data.getData());
//设置浏览器下载
setBrowser(response, workbook, data.getFileName());
// log.info("导出解析成功!");
} catch (Exception e) {
// log.info("导出解析失败!");
e.printStackTrace();
}
}
package com.jsyl.acsbs.util;
import com.github.tobato.fastdfs.service.FastFileStorageClient;
import com.jsyl.acsbs.common.ExcelData;
import org.apache.poi.hssf.usermodel.*;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.List;
//import static org.apache.poi.ss.usermodel.CellType.*;
/**
* 路径:com.example.demo.utils
* 类名:
* 功能:导入导出
*/
public class ExcelUtil {
@Resource
private static FastFileStorageClient storageClient;
/**
* 方法名:exportExcel
* 功能:导出Excel
*/
public static void exportExcel(HttpServletResponse response, ExcelData data) {
// log.info("导出解析开始,fileName:{}",data.getFileName());
try {
//实例化HSSFWorkbook
HSSFWorkbook workbook = new HSSFWorkbook();
//创建一个Excel表单,参数为sheet的名字
HSSFSheet sheet = workbook.createSheet("sheet");
//设置表头
setTitle(workbook, sheet, data.getHead());
//设置单元格并赋值
setData(workbook,sheet, data.getData());
//设置浏览器下载
setBrowser(response, workbook, data.getFileName());
// log.info("导出解析成功!");
} catch (Exception e) {
// log.info("导出解析失败!");
e.printStackTrace();
}
}
/**
* 方法名:setTitle
* 功能:设置表头
*/
public static void setTitle(HSSFWorkbook workbook, HSSFSheet sheet, String[] str) {
try {
HSSFRow row = sheet.createRow(0);
//设置列宽,setColumnWidth的第二个参数要乘以256,这个参数的单位是1/256个字符宽度
for (int i = 0; i <= str.length; i++) {
sheet.setColumnWidth(i, 15 * 256);
}
//设置为居中加粗,格式化时间格式
HSSFCellStyle style = workbook.createCellStyle();
HSSFFont font = workbook.createFont();
font.setBold(true);
style.setFont(font);
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
//创建表头名称
HSSFCell cell;
for (int j = 0; j < str.length; j++) {
cell = row.createCell(j);
cell.setCellValue(str[j]);
cell.setCellStyle(style);
}
} catch (Exception e) {
// log.info("导出时设置表头失败!");
e.printStackTrace();
}
}
/**
* 方法名:setData
* 功能:表格赋值
*/
public static void setData(HSSFWorkbook workbook,HSSFSheet sheet, List<String[]> data) {
try{
int rowNum = 1;
HSSFCell cellRow = null;
for (int i = 0; i < data.size(); i++) {
HSSFRow row = sheet.createRow(rowNum);
for (int j = 0; j < data.get(i).length; j++) {
//行单元格
cellRow = row.createCell(j);
cellRow.setCellValue(data.get(i)[j]);
}
rowNum++;
}
// log.info("表格赋值成功!");
}catch (Exception e){
// log.info("表格赋值失败!");
e.printStackTrace();
}
}
/**
* 方法名:setBrowser
* 功能:使用浏览器下载
*/
public static void setBrowser(HttpServletResponse response, HSSFWorkbook workbook, String fileName) {
try {
//清空response
response.reset();
//设置response的Header
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
//重要点
// String filename = new String(fileName.getBytes("ISO8859-1"), "UTF-8")+dateFormat.format(new Date().getTime())+".xls";
// // System.err.println(" filename "+filename);
response.setHeader("Content-Disposition", "attachment;filename=" + filename);
// response.addHeader("Content-Disposition", "attachment;filename=" + filename);
// OutputStream os = new BufferedOutputStream(response.getOutputStream());
response.setContentType("application/msexcel;charset=GBK");
//
//
// response.setHeader("Content-Type", "application/force-download");
// response.setHeader("Content-type", "application/vnd.ms-excel");
// response.setHeader("Content-disposition", "attachment; filename="
// + new String(fileName.getBytes("GBK"), "ISO-8859-1") + ".xls");
// // 定义输出类型
// response.setContentType("application/msexcel;charset=GBK");
String filename = new String(fileName.getBytes(), "iso8859-1") + dateFormat.format(System.currentTimeMillis())+".xls";
response.setHeader("Content-Disposition", "attachment;filename=" + filename);
OutputStream os = new BufferedOutputStream(response.getOutputStream());
response.setContentType("application/vnd.ms-excel;charset=gb2312");
// response.setCharacterEncoding("UTF-8");
//将excel写入到输出流中
workbook.write(os);
os.flush();
os.close();
// log.info("设置浏览器下载成功!");
} catch (Exception e) {
// log.info("设置浏览器下载失败!");
e.printStackTrace();
}
}
// public static List<Object[]> importExcel(String fileName) {
log.info("导入解析开始,fileName:{}",fileName);
// try {
// List<Object[]> list = new ArrayList<>();
// InputStream inputStream = new FileInputStream(fileName);
// Workbook workbook = WorkbookFactory.create(inputStream);
// Sheet sheet = workbook.getSheetAt(0);
// //获取sheet的行数
// int rows = sheet.getPhysicalNumberOfRows();
// for (int i = 0; i < rows; i++) {
// //过滤表头行
// if (i == 0) {
// continue;
// }
// //获取当前行的数据
// Row row = sheet.getRow(i);
// Object[] objects = new Object[row.getPhysicalNumberOfCells()];
// int index = 0;
// for (Cell cell : row) {
// if (cell.getCellType().equals(NUMERIC)) {
// objects[index] = (int) cell.getNumericCellValue();
// }
// if (cell.getCellType().equals(STRING)) {
// objects[index] = cell.getStringCellValue();
// }
// if (cell.getCellType().equals(BOOLEAN)) {
// objects[index] = cell.getBooleanCellValue();
// }
// if (cell.getCellType().equals(ERROR)) {
// objects[index] = cell.getErrorCellValue();
// }
// index++;
// }
// list.add(objects);
// }
log.info("导入文件解析成功!");
// return list;
// }catch (Exception e){
log.info("导入文件解析失败!");
// e.printStackTrace();
// }
// return null;
// }
}
package com.jsyl.acsbs.common;
import lombok.Data;
import java.util.List;
@Data
public class ExcelData {
/**
* 具体内容
*/
private List<String[]> data;
private String fileName;
private String[] head;
}
三、基于模板导出
@Override
public void modelexport(HttpServletResponse response) throws IOException {
ClassPathResource resource = new ClassPathResource("templates/234.xlsx");
boolean isFile = resource.isFile();
if(!isFile){ //如果不存在返回
return;
}
String path = resource.getFile().getPath();
//获取文件路径 /* 数据写入模板文件中 */
// 更改文件名编码
String fileName = "ex.xlsx";
String gFileName = URLEncoder.encode(fileName, "UTF-8");
//如进行下载名为:文件(3).txt,下载时显示名为:文件+(3).txt --空格变为了+号
//解决办法如下
String dFileName = gFileName.replaceAll("\\+", "%20");
InputStream in = null; Workbook exl = null;
ByteArrayOutputStream out = new ByteArrayOutputStream();
try {
in = new FileInputStream(path);
exl = WorkbookFactory.create(in);
Sheet sheet1 = exl.getSheetAt(0);
int rowNums = sheet1.getLastRowNum();
// CellStyle style = exl.createCellStyle(); //创建样式
// style.setBorderBottom(BorderStyle.THIN);//下边框
// HSSFCellStyle style = s.createCellStyle(); //创建样式
// style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); //字体右对齐
// style.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
// style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
// style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
// style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//模板的行数0开始,返回值比行数小1
Row row4 = sheet1.getRow( 4 );
// row4.getCell(1).setCellStyle(style);
row4.getCell(1).setCellValue("立项申请部门");
row4.createCell(5).setCellValue("2019-10-15");
Row row5 = sheet1.getRow( 5);
// row5.getCell(1).setCellStyle(style);
row5.getCell(1).setCellValue("项目名称");
Row row6 = sheet1.getRow( 6);
row6.getCell(1).setCellValue("客户名称");
Row row7 = sheet1.getRow( 7);
row7.getCell(1).setCellValue("陈XX 139");
Row row8 = sheet1.getRow( 8);
row8.getCell(1).setCellValue("工程施工");
row8.getCell(4).setCellValue("站段更改计划");
Row row9 = sheet1.getRow( 9);
row9.getCell(1).setCellValue("项目规模\n" +
"(万元)");
row9.getCell(4).setCellValue("2019/6/23--2019/7/20");
Row row10 = sheet1.getRow( 10);
row10.getCell(1).setCellValue("项目需求:对应“立项申请”页面的“项目需求”");
Row row11 = sheet1.getRow( 11);
Row row12 = sheet1.getRow( 12);
row12.getCell(1).setCellValue("P0920190016");
row12.getCell(4).setCellValue("关联项目编号");
//激活浏览器弹出窗口
response.setContentType("application/x-msdownload");
//浏览器弹出窗口显示的文件名
response.addHeader("Content-Disposition", "attachment;filename=" + dFileName);
exl.write(out);
//in = new ByteArrayInputStream(out.toByteArray());
response.getOutputStream().write(out.toByteArray());
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if (exl != null) {
exl.close();
} if (out != null) {
out.close();
} if (in != null) {
in.close();
}
} catch (IOException ex) {
ex.printStackTrace();
}
}
}
四、基于模板导出(List)
@Override
public void listexport(HttpServletResponse response) throws IOException {
List<CustomerPojo> list =new ArrayList<CustomerPojo>();
CustomerPojo c1=new CustomerPojo();
c1.setId(1);
c1.setCustomer("ceshi");
c1.setCustomerid("0");
c1.setCustomername("测定");
CustomerPojo c2=new CustomerPojo();
c2.setId(2);
c2.setCustomer("22");
c2.setCustomerid("02");
c2.setCustomername("测定2");
CustomerPojo c3=new CustomerPojo();
c3.setId(3);
c3.setCustomer("33");
c3.setCustomerid("03");
c3.setCustomername("测定3");
list.add(c3);
list.add(c1);
list.add(c2);
ClassPathResource resource = new ClassPathResource("templates/list.xlsx");
boolean isFile = resource.isFile();
if(!isFile){ //如果不存在返回
return;
}
String path = resource.getFile().getPath();
//获取文件路径 /* 数据写入模板文件中 */
// 更改文件名编码
String fileName = "modellist.xlsx";
String gFileName = URLEncoder.encode(fileName, "UTF-8");
//如进行下载名为:文件(3).txt,下载时显示名为:文件+(3).txt --空格变为了+号
//解决办法如下
String dFileName = gFileName.replaceAll("\\+", "%20");
InputStream in = null; Workbook exl = null;
ByteArrayOutputStream out = new ByteArrayOutputStream();
try {
in = new FileInputStream(path);
exl = WorkbookFactory.create(in);
Sheet sheet1 = exl.getSheetAt(0);
int rowNums =0;
Row row2 = sheet1.getRow( 2);
row2.getCell(0).setCellValue("立项申请部门");
CellStyle style = exl.createCellStyle(); //创建样式
style.setBorderBottom(BorderStyle.THIN);//下边框
style.setBorderLeft(BorderStyle.THIN);//左边框
style.setBorderTop(BorderStyle.THIN);//上边框
style.setBorderRight(BorderStyle.THIN);//右边框
//模板的行数0开始,返回值比行数小1
sheet1.shiftRows(7, sheet1.getLastRowNum(), list.size(), true, false);
sheet1.createRow(list.size());
for(int i = 0;i<list.size();i++){
Row row = sheet1.createRow(i+7);//从第三行开始填充数据
row.createCell(0).setCellStyle(style);
row.getCell(0).setCellValue(list.get(i).getId());
row.createCell(1).setCellStyle(style);
row.getCell(1).setCellValue(list.get(i).getCustomer());
row.createCell(2).setCellStyle(style);
row.getCell(2).setCellValue(list.get(i).getCustomername());
row.createCell(3).setCellStyle(style);
row.getCell(3).setCellValue(list.get(i).getCustomername());
row.createCell(4).setCellStyle(style);
row.getCell(4).setCellValue(list.get(i).getCustomername());
row.createCell(5).setCellStyle(style);
row.getCell(5).setCellValue(list.get(i).getCustomername());
row.createCell(6).setCellStyle(style);
row.getCell(6).setCellValue(list.get(i).getCustomername());
row.createCell(7).setCellStyle(style);
row.getCell(7).setCellValue(list.get(i).getCustomername());
CellRangeAddress region = new CellRangeAddress(i+7, i+7, 8, 9);
sheet1.addMergedRegion(region);
row.createCell(8).setCellStyle(style);
row.createCell(9).setCellStyle(style);
row.getCell(8).setCellValue("采购方式");
}
rowNums=7+list.size()+2;
sheet1.shiftRows(rowNums, sheet1.getLastRowNum(), list.size(), true, false);
sheet1.createRow(list.size());
for(int i = 0;i<list.size();i++){
Row row = sheet1.createRow(i+rowNums);//从第三行开始填充数据
row.createCell(0).setCellStyle(style);
row.getCell(0).setCellValue(list.get(i).getId());
row.createCell(1).setCellStyle(style);
row.getCell(1).setCellValue("qq");
CellRangeAddress region1 = new CellRangeAddress(i+rowNums, i+rowNums, 2, 4);
sheet1.addMergedRegion(region1);
row.createCell(2).setCellStyle(style);
row.createCell(3).setCellStyle(style);
row.createCell(4).setCellStyle(style);
row.getCell(2).setCellValue("qq2");
CellRangeAddress region2 = new CellRangeAddress(i+rowNums, i+rowNums, 5, 6);
sheet1.addMergedRegion(region2);
row.createCell(5).setCellStyle(style);
row.createCell(6).setCellStyle(style);
row.getCell(5).setCellValue("qq5");
CellRangeAddress region3 = new CellRangeAddress(i+rowNums, i+rowNums, 7, 9);
sheet1.addMergedRegion(region3);
row.createCell(7).setCellStyle(style);
row.createCell(8).setCellStyle(style);
row.createCell(9).setCellStyle(style);
row.getCell(7).setCellValue("qq7");
}
rowNums=rowNums+list.size()+2;
Row rownum0 = sheet1.getRow( rowNums );
rownum0.getCell(2).setCellValue("立项申请部门");
Row rownum1 = sheet1.getRow( rowNums+1);
rownum1.getCell(2).setCellValue("项目名称");
Row rownum2 = sheet1.getRow( rowNums+2);
rownum2.getCell(2).setCellValue("客户名称");
Row rownum3 = sheet1.getRow( rowNums+3);
rownum3.getCell(2).setCellValue("陈XX 1391");
Row rownum4 = sheet1.getRow( rowNums+4);
rownum4.getCell(2).setCellValue("工程施工");
Row rownum5 = sheet1.getRow( rowNums+5);
rownum5.getCell(2).setCellValue("项目规模");
//激活浏览器弹出窗口
response.setContentType("application/x-msdownload");
//浏览器弹出窗口显示的文件名
response.addHeader("Content-Disposition", "attachment;filename=" + dFileName);
exl.write(out);
//in = new ByteArrayInputStream(out.toByteArray());
response.getOutputStream().write(out.toByteArray());
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if (exl != null) {
exl.close();
} if (out != null) {
out.close();
} if (in != null) {
in.close();
}
} catch (IOException ex) {
ex.printStackTrace();
}
}
}
ShiftRows 是HSSFSheet工作薄的方法
ShiftRows(int startRow,int endRow,int n)
startRow:开始行
endRow:末尾行
n:移动n行数startRow到endRow数据域(正数:向下移,负数:向上移)