回顾
- 七牛云存储
- 附件管理
- 细粒度权限
- poi的入门
1货物的批量导入(重点)
1.1 进入上传的页面
1.2 在service中添加批量保存的方法
1.3 实现货物的批量导入
2出货表打印(重点)
2.1 数据来源和sql语句
SELECT c.custom_name customName, c.contract_no contractNo,
cp.product_no, cp.cnumber, cp.factory_name factoryName,
c.ship_time shipTime, c.trade_terms tradeTerms
FROM co_contract c , co_contract_product cp
WHERE c.id=cp.contract_id AND DATE_FORMAT(c.ship_time ,'%Y-%m')="2016-11"
2.2 配置一个VO对象
public class ContractProductVo implements Serializable {
private String customName; //客户名称
private String contractNo; //合同号,订单号
private String productNo; //货号
private Integer cnumber; //数量
private String factoryName; //厂家名称,冗余字段
private Date deliveryPeriod; //交货期限
private Date shipTime; //船期
private String tradeTerms; //贸易条款
//省略getter,setter
}
2.3实现出货表打印
@Controller
@RequestMapping("/cargo/contract")
public class OutProductController extends BaseController{
@Reference
private ContractProductService contractProductService;
//跳转到下载的页面
@RequestMapping("/print")
public String print() {
return "cargo/print/contract-print";
}
/**
* 根据船期生成出货表
* 参数:船期
* 返回值: void(下载)
*/
@RequestMapping("/printExcel")
public void printExcel(String inputDate) throws IOException {
//i.根据船期查询出货表的所有数据
List<ContractProductVo> list = contractProductService.findContractProductByShipTime(inputDate);
//ii.生成Excel报表 workbook
//1.创建工作簿
Workbook wb = new XSSFWorkbook();
//2.创建sheet
Sheet sheet = wb.createSheet();
//合并单元格
sheet.addMergedRegion(new CellRangeAddress(0,0,1,8)); //开始合并的行索引,结束合并的行索引,开始合并的列索引,结束合并的列索引
//设置单元格的宽度
sheet.setColumnWidth(1,26 * 256); //1.单元格的索引,2.单元格的列宽
sheet.setColumnWidth(2,12 * 256); //1.单元格的索引,2.单元格的列宽
sheet.setColumnWidth(3,30 * 256); //1.单元格的索引,2.单元格的列宽
sheet.setColumnWidth(4,12 * 256); //1.单元格的索引,2.单元格的列宽
sheet.setColumnWidth(5,15 * 256); //1.单元格的索引,2.单元格的列宽
sheet.setColumnWidth(6,10 * 256); //1.单元格的索引,2.单元格的列宽
sheet.setColumnWidth(7,10 * 256); //1.单元格的索引,2.单元格的列宽
sheet.setColumnWidth(8,8 * 256) ; //1.单元格的索引,2.单元格的列宽
//3.设置大标题
Row row = sheet.createRow(0);
row.setHeightInPoints(36);
Cell cell = row.createCell(1);
cell.setCellStyle(bigTitle(wb));
cell.setCellValue(inputDate.replaceAll("-0","-").replaceAll("-","年") +"月份出货表"); //2015-01 --> 2015-1 -->2015年1
//4.设置小标题 客户 订单号 货号 数量 工厂 工厂交期 船期 贸易条款
row = sheet.createRow(1);
row.setHeightInPoints(26);
String titles [] = new String []{"","客户","订单号","货号","数量","工厂","工厂交期","船期","贸易条款"};
for( int i=1;i<titles.length;i++) {
cell = row.createCell(i);
cell.setCellStyle(title(wb));
cell.setCellValue(titles[i]);
}
//5.循环设置数据对应的excel行
int index = 2;
for (ContractProductVo vo : list) {
row = sheet.createRow(index);
row.setHeightInPoints(24);
//客户
cell = row.createCell(1);
cell.setCellStyle(text(wb));
cell.setCellValue(vo.getCustomName());
//订单号
cell = row.createCell(2);
cell.setCellStyle(text(wb));
cell.setCellValue(vo.getContractNo());
// 货号
cell = row.createCell(3);
cell.setCellStyle(text(wb));
cell.setCellValue(vo.getProductNo());
// 数量
cell = row.createCell(4);
cell.setCellStyle(text(wb));
cell.setCellValue(vo.getCnumber());
// 工厂
cell = row.createCell(5);
cell.setCellStyle(text(wb));
cell.setCellValue(vo.getFactoryName());
// 工厂交期
cell = row.createCell(6);
cell.setCellStyle(text(wb));
cell.setCellValue(vo.getDeliveryPeriod());
// 船期
cell = row.createCell(7);
cell.setCellStyle(text(wb));
cell.setCellValue(vo.getShipTime());
// 贸易条款
cell = row.createCell(8);
cell.setCellStyle(text(wb));
cell.setCellValue(vo.getTradeTerms());
index ++;
}
//iii.下载
ByteArrayOutputStream bos = new ByteArrayOutputStream();
wb.write(bos);
new DownloadUtil().download(bos,response,"出货表.xlsx"); //1.byteArrayOutputStream ,2.response,3.文件名
}
//大标题的样式
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;
}
}
#3模板打印(重点)
模板来源 :
/**
* 模板打印
*/
@RequestMapping("/printExcelTemplate")
public void printExcelTemplate(String inputDate) throws IOException {
//i.根据船期查询出货表的所有数据
List<ContractProductVo> list = contractProductService.findContractProductByShipTime(inputDate);
//ii.生成Excel报表
//1.根据模板路径加载工作簿
String path = session.getServletContext().getRealPath("/")+"/make/xlsprint/tOUTPRODUCT.xlsx";
Workbook wb = new XSSFWorkbook(path);
//2.获取第一页
Sheet sheet = wb.getSheetAt(0);
//3.设置大标题
Row row = sheet.getRow(0);
Cell cell = row.getCell(1);
cell.setCellValue(inputDate.replaceAll("-0","-").replaceAll("-","年") +"月份出货表");
//4.设置小标题
//5.获取数据的所有样式
CellStyle css [] = new CellStyle[9];
row = sheet.getRow(2);
for(int i=1;i<css.length;i++) {
cell = row.getCell(i);
css [i] = cell.getCellStyle();
}
//6.循环设置单元格数据
int index = 2;
for (ContractProductVo vo : list) {
row = sheet.createRow(index);
//客户
cell = row.createCell(1);
cell.setCellStyle(css[1]);
cell.setCellValue(vo.getCustomName());
//订单号
cell = row.createCell(2);
cell.setCellStyle(css[2]);
cell.setCellValue(vo.getContractNo());
// 货号
cell = row.createCell(3);
cell.setCellStyle(css[3]);
cell.setCellValue(vo.getProductNo());
// 数量
cell = row.createCell(4);
cell.setCellStyle(css[4]);
cell.setCellValue(vo.getCnumber());
// 工厂
cell = row.createCell(5);
cell.setCellStyle(css[5]);
cell.setCellValue(vo.getFactoryName());
// 工厂交期
cell = row.createCell(6);
cell.setCellStyle(css[6]);
cell.setCellValue(vo.getDeliveryPeriod());
// 船期
cell = row.createCell(7);
cell.setCellStyle(css[7]);
cell.setCellValue(vo.getShipTime());
// 贸易条款
cell = row.createCell(8);
cell.setCellStyle(css[8]);
cell.setCellValue(vo.getTradeTerms());
index++;
}
//iii.下载
ByteArrayOutputStream bos = new ByteArrayOutputStream();
wb.write(bos);
new DownloadUtil().download(bos,response,"出货表.xlsx"); //1.byteArrayOutputStream ,2.response,3.文件名
}
4百万数据POI报表(了解)
4.1 监控工具
java提供一个监控工具,可以监控程序运行所占用的cpu和内存:jvisualvm.exe
在jdk安装目录下bin/目录下
4.2 百万数据的打印
在poi中具有一个专门用于处理报完数据报表打印的对象: SxssfWworkBook
使用条件
- 不支持模板打印
- 使用SxssfWworkBook创建的样式最大支持64000个
- SxssfWworkBook使用方式和之前的Xssfworkbook一模一样
4.3 百万数据报表的解析
4.3.1 事件模型
工具类:
package cn.itcast.parase;
import cn.itcast.handler.SheetHandler;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.xml.sax.InputSource;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.XMLReaderFactory;
import java.io.InputStream;
public class ExcelParse {
public void parse (String path) throws Exception {
//解析器
SheetHandler hl = new SheetHandler();
//1.根据 Excel 获取 OPCPackage 对象
OPCPackage pkg = OPCPackage.open(path, PackageAccess.READ);
try {
//2.创建 XSSFReader 对象
XSSFReader reader = new XSSFReader(pkg);
//3.获取 SharedStringsTable 对象
SharedStringsTable sst = reader.getSharedStringsTable();
//4.获取 StylesTable 对象
StylesTable styles = reader.getStylesTable();
XMLReader parser = XMLReaderFactory.createXMLReader();
// 处理公共属性
parser.setContentHandler(new XSSFSheetXMLHandler(styles,sst, hl,
false));
XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator)
reader.getSheetsData();
//逐行读取逐行解析
while (sheets.hasNext()) {
InputStream sheetstream = sheets.next();
InputSource sheetSource = new InputSource(sheetstream);
try {
parser.parse(sheetSource);
} finally {
sheetstream.close();
}
}
} finally {
pkg.close();
}
}
}
/**
* 行解析器
* 实现接口:XSSFSheetXMLHandler.SheetContentsHandler
*/
public class SheetHandler implements XSSFSheetXMLHandler.SheetContentsHandler {
private ContractProductVo vo;
//开始解析此行调用的方法
//参数 : 行索引
public void startRow(int i) {
if(i>1) {
vo = new ContractProductVo();
}
}
//结束解析此行调用的方法
//参数 : 行索引
public void endRow(int i) {
if(i>1) {
System.out.println(vo);
}
}
/**
* 解析行中每一个单元格调用的方法
* @param cellName : 单元格名称
* B C
* @param cellValue :单元格内容
* @param xssfComment : 注释
*/
public void cell(String cellName, String cellValue, XSSFComment xssfComment) {
cellName = cellName.substring(0,1);
if(vo != null) {
switch (cellName) {
case "B": {
vo.setCustomName(cellValue);
break;
}
case "C": {
vo.setContractNo(cellValue);
break;
}
case "D": {
vo.setProductNo(cellValue);
break;
}
default: {
break;
}
}
}
}
}
作业:
1.货物导入
2.模板打印
3.预习