Office 2003和Office 2007及以上版本Excel的区别:
| Excel 2003 | Excel 2007以上版本 |
技术背景 | OLE2(二进制文件) | OOXML(XML文件) |
行数(单sheet) | 65536 | 1048576 |
列数 | 255 | 65536 |
最后的效果图:
Excel 2003操作使用HSSF
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
@RequestMapping("/cargo/outproduct/printNotemplate.action")
public void printNotemplate(String inputDate, HttpServletRequest request, HttpServletResponse response) throws Exception
{
/*
* POI实现excel打印
* 1、大标题,合并单元格
* 2、标题,修饰
* 3、内容,修饰
*
*/
Workbook _wb = new HSSFWorkbook(); //创建工作簿
Sheet _sheet = _wb.createSheet(); //创建工作表
_sheet.setColumnWidth(0, 1*278); //设置列宽 256,BUG,精度不够,总是差一点
_sheet.setColumnWidth(1, 26*278);
Cell _cell = null;
int _rowNo = 0;
//处理大标题
_sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 8)); //合并单元格
Row _row = _sheet.createRow(_rowNo++);
_row.setHeightInPoints(36);
_cell = _row.createCell(1);
_cell.setCellValue(inputDate + "月份出货表");
//设置大标题的样式
CellStyle _bigTitleCellStyle = setBigTitleCellStyle(_wb);
_cell.setCellStyle(_bigTitleCellStyle);
//处理标题
String[] title = new String[]{"客户","订单号","货号","数量","工厂","工厂交期","船期","贸易条款"}; //标题数组
_row = _sheet.createRow(_rowNo++);
_row.setHeightInPoints(26);
for(int i = 0; i < title.length; ++i)
{
<span style="white-space:pre"> </span>_cell = _row.createCell(i + 1);
_cell.setCellValue(title[i]);
CellStyle _titleCellStyle = setTitleCellStyle(_wb);
_cell.setCellStyle(_titleCellStyle);
}
//处理内容
List<OutProductVO> _dataList = outProductService.find(inputDate);
inputDate = inputDate.replaceFirst("-0", "-").replaceFirst("-", "年");
CellStyle _textCellStyle = setTextCellStyle(_wb);
for(int i = 0; i < _dataList.size(); ++i)
{
OutProductVO _outProductVO = _dataList.get(i);
_row = _sheet.createRow(_rowNo++);
_row.setHeightInPoints(24);
int _columnNo = 1;
_cell = _row.createCell(_columnNo++);
_cell.setCellValue(_outProductVO.getCustomName());
_cell.setCellStyle(_textCellStyle);
_cell = _row.createCell(_columnNo++);
_cell.setCellValue(_outProductVO.getContractNo());
_cell.setCellStyle(_textCellStyle);
_cell = _row.createCell(_columnNo++);
_cell.setCellValue(_outProductVO.getProductNo());
_cell.setCellStyle(_textCellStyle);
_cell = _row.createCell(_columnNo++);
_cell.setCellValue(_outProductVO.getCnumber());
_cell.setCellStyle(_textCellStyle);
_cell = _row.createCell(_columnNo++);
_cell.setCellValue(_outProductVO.getFactoryName());
_cell.setCellStyle(_textCellStyle);
_cell = _row.createCell(_columnNo++);
_cell.setCellValue(_outProductVO.getDeliveryPeriod());
_cell.setCellStyle(_textCellStyle);
_cell = _row.createCell(_columnNo++);
_cell.setCellValue(_outProductVO.getShipTime());
_cell.setCellStyle(_textCellStyle);
_cell = _row.createCell(_columnNo++);
_cell.setCellValue(_outProductVO.getTradeTerms());
_cell.setCellStyle(_textCellStyle);
}
ByteArrayOutputStream _os = new ByteArrayOutputStream();
_wb.write(_os);
DownloadUtil downloadUtil = new DownloadUtil(); //直接弹出下载框,用户可以打开,可以保存
downloadUtil.download(_os, response, inputDate + "月份出货表.xls");
_os.flush();
_os.close();
}
//大标题样式
private CellStyle setBigTitleCellStyle(Workbook workbook)
{
CellStyle _cellStyle = workbook.createCellStyle();
_cellStyle.setAlignment(CellStyle.ALIGN_CENTER); //横向居中
_cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); //纵向居中
Font _font = workbook.createFont();
_font.setFontName("宋体");
_font.setFontHeightInPoints((short)16); //字体大小
_font.setBoldweight(Font.BOLDWEIGHT_BOLD); //字体加粗
_cellStyle.setFont(_font); //绑定字体
return _cellStyle;
}
//小标题样式
private CellStyle setTitleCellStyle(Workbook workbook)
{
CellStyle _cellStyle = workbook.createCellStyle();
_cellStyle.setAlignment(CellStyle.ALIGN_CENTER); //横向居中
_cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); //纵向居中
_cellStyle.setBorderTop(CellStyle.BORDER_THIN); //设置四周边线,细线
_cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
_cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
_cellStyle.setBorderRight(CellStyle.BORDER_THIN);
Font _font = workbook.createFont();
_font.setFontName("黑体");
_font.setFontHeightInPoints((short)12);
_cellStyle.setFont(_font);
return _cellStyle;
}
//文字样式
private CellStyle setTextCellStyle(Workbook workbook)
{
CellStyle _cellStyle = workbook.createCellStyle();
_cellStyle.setAlignment(CellStyle.ALIGN_LEFT); //横向居左
_cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); //纵向居中
//设置四周边线,细线
_cellStyle.setBorderTop(CellStyle.BORDER_THIN);
_cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
_cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
_cellStyle.setBorderRight(CellStyle.BORDER_THIN);
Font _font = workbook.createFont();
_font.setFontName("Times New Roman");
_font.setFontHeightInPoints((short)10);
_cellStyle.setFont(_font);
return _cellStyle;
}
//模板开发
@RequestMapping("/cargo/outproduct/printHSSF.action")
public void printHSSF(String inputDate, HttpServletRequest request, HttpServletResponse response) throws Exception
{
//linux下jdk1.8 方法获取时,不会拼接自己写的目录
String path = request.getSession().getServletContext().getRealPath("/") + "/make/xlsprint/";
InputStream is = new FileInputStream(new File(path + "tOUTPRODUCT.xls"));
Workbook wb = new HSSFWorkbook(is); //打开一个模板文件,工作簿
Sheet sheet = wb.getSheetAt(0); //获取到第一个工作表
Row nRow = null;
Cell nCell = null;
//获取模板上的单元格样式
nRow = sheet.getRow(2);
//客户的样式
nCell = nRow.getCell(1);
CellStyle customStyle = nCell.getCellStyle();
//订单号的样式
nCell = nRow.getCell(2);
CellStyle contractNoStyle = nCell.getCellStyle();
//货号的样式
nCell = nRow.getCell(3);
CellStyle productNoStyle = nCell.getCellStyle();
//数量的样式
nCell = nRow.getCell(4);
CellStyle numStyle = nCell.getCellStyle();
//生产厂家的样式
nCell = nRow.getCell(5);
CellStyle factoryStyle = nCell.getCellStyle();
//日期的样式
nCell = nRow.getCell(6);
CellStyle dateStyle = nCell.getCellStyle();
//贸易条款的样式
nCell = nRow.getCell(8);
CellStyle tradeStyle = nCell.getCellStyle();
//处理大标题
nRow = sheet.getRow(0); //获取一个行对象
nCell = nRow.getCell(1); //获取一个单元格对象
String _inputDade = inputDate.replaceFirst("-0", "-").replaceFirst("-", "年");
nCell.setCellValue(_inputDade + "月份出货表"); //yyyy-MM
int rowNo = 2;
//处理内容
List<OutProductVO> dataList = outProductService.find(inputDate);
for(int j=0;j<dataList.size();j++)
{
int colNo = 1; //初始化列号
OutProductVO op = dataList.get(j);
nRow = sheet.createRow(rowNo++);
nRow.setHeightInPoints(24);
nCell = nRow.createCell(colNo++);
nCell.setCellValue(op.getCustomName());
nCell.setCellStyle(customStyle);
nCell = nRow.createCell(colNo++);
nCell.setCellValue(op.getContractNo());
nCell.setCellStyle(contractNoStyle);
nCell = nRow.createCell(colNo++);
nCell.setCellValue(op.getProductNo());
nCell.setCellStyle(productNoStyle);
nCell = nRow.createCell(colNo++);
nCell.setCellValue(op.getCnumber());
nCell.setCellStyle(numStyle);
nCell = nRow.createCell(colNo++);
nCell.setCellValue(op.getFactoryName());
nCell.setCellStyle(factoryStyle);
nCell = nRow.createCell(colNo++);
nCell.setCellValue(op.getDeliveryPeriod());
nCell.setCellStyle(dateStyle);
nCell = nRow.createCell(colNo++);
nCell.setCellValue(op.getShipTime());
nCell.setCellStyle(dateStyle);
nCell = nRow.createCell(colNo++);
nCell.setCellValue(op.getTradeTerms());
nCell.setCellStyle(tradeStyle);
}
<span style="white-space:pre"> </span>ByteArrayOutputStream os = new ByteArrayOutputStream();
wb.write(os);
DownloadUtil downloadUtil = new DownloadUtil(); <span style="white-space:pre"> </span>//直接弹出下载框,用户可以打开,可以保存
downloadUtil.download(os, response, _inputDade + "月份出货表.xls");
os.flush();
os.close();
}
Excel 2007及以上版本使用XSSF
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
//模板开发XSSF
@RequestMapping("/cargo/outproduct/print.action")
public void print(String inputDate, HttpServletRequest request, HttpServletResponse response) throws Exception
{
//linux下jdk1.8 方法获取时,不会拼接自己写的目录
String path = request.getSession().getServletContext().getRealPath("/") + "/make/xlsprint/";
InputStream is = new FileInputStream(new File(path + "tOUTPRODUCT.xlsx"));
Workbook wb = new XSSFWorkbook(is); //打开一个模板文件,工作簿 2007以上版本
Sheet sheet = wb.getSheetAt(0); //获取到第一个工作表
Row nRow = null;
Cell nCell = null;
//获取模板上的单元格样式
nRow = sheet.getRow(2);
//客户的样式
nCell = nRow.getCell(1);
CellStyle customStyle = nCell.getCellStyle();
//订单号的样式
nCell = nRow.getCell(2);
CellStyle contractNoStyle = nCell.getCellStyle();
//货号的样式
nCell = nRow.getCell(3);
CellStyle productNoStyle = nCell.getCellStyle();
//数量的样式
nCell = nRow.getCell(4);
CellStyle numStyle = nCell.getCellStyle();
//生产厂家的样式
nCell = nRow.getCell(5);
CellStyle factoryStyle = nCell.getCellStyle();
//日期的样式
nCell = nRow.getCell(6);
CellStyle dateStyle = nCell.getCellStyle();
//贸易条款的样式
nCell = nRow.getCell(8);
CellStyle tradeStyle = nCell.getCellStyle();
//处理大标题
nRow = sheet.getRow(0); //获取一个行对象
nCell = nRow.getCell(1); //获取一个单元格对象
String _inputDate = inputDate.replaceFirst("-0", "-").replaceFirst("-", "年");
nCell.setCellValue(_inputDate + "月份出货表"); //yyyy-MM
int rowNo = 2;
//处理内容
List<OutProductVO> dataList = outProductService.find(inputDate);
for(int j=0;j<dataList.size();j++)
{
int colNo = 1; //初始化列号
OutProductVO op = dataList.get(j);
nRow = sheet.createRow(rowNo++);
nRow.setHeightInPoints(24);
nCell = nRow.createCell(colNo++);
nCell.setCellValue(op.getCustomName());
nCell.setCellStyle(customStyle);
nCell = nRow.createCell(colNo++);
nCell.setCellValue(op.getContractNo());
nCell.setCellStyle(contractNoStyle);
nCell = nRow.createCell(colNo++);
nCell.setCellValue(op.getProductNo());
nCell.setCellStyle(productNoStyle);
nCell = nRow.createCell(colNo++);
nCell.setCellValue(op.getCnumber());
nCell.setCellStyle(numStyle);
nCell = nRow.createCell(colNo++);
nCell.setCellValue(op.getFactoryName());
nCell.setCellStyle(factoryStyle);
nCell = nRow.createCell(colNo++);
nCell.setCellValue(op.getDeliveryPeriod());
nCell.setCellStyle(dateStyle);
nCell = nRow.createCell(colNo++);
nCell.setCellValue(op.getShipTime());
nCell.setCellStyle(dateStyle);
nCell = nRow.createCell(colNo++);
nCell.setCellValue(op.getTradeTerms());
nCell.setCellStyle(tradeStyle);
}
ByteArrayOutputStream os = new ByteArrayOutputStream();
wb.write(os);
DownloadUtil downloadUtil = new DownloadUtil(); //直接弹出下载框,用户可以打开,可以保存
downloadUtil.download(os, response, _inputDate + "月份出货表.xlsx");
os.flush();
os.close();
}
百万数据导出,只能导出到Excel 2007及以上版本,使用SXSSF
基本原理:在打印过程中,已经加工完的对象,临时存到一个临时文件中,它采用xml临时文件。最终处理完时,以文件写文件的方式将临时文件的内容写入到最终的xlsx文件中。临时文件及其位置:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
public void jdbcex(boolean isClose) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException, IOException, InterruptedException
{
String xlsFile = "c:/poiSXXFSDBBigData.xlsx"; //输出文件
Workbook wb = new SXSSFWorkbook(100); //创建excel文件,内存只有100条记录【关键语句】,每加工完100条记录写入临时文件
Sheet sheet = wb.createSheet("我的第一个工作簿"); //建立新的sheet对象
<span style="white-space:pre"> </span>Row nRow = null;
Cell nCell = null;
//使用jdbc链接数据库
Class.forName("com.mysql.jdbc.Driver").newInstance();
String url = "jdbc:mysql://localhost:3306/jkmore100?characterEncoding=UTF-8";
String user = "root";
String password = "root";
Connection conn = DriverManager.getConnection(url, user,password);
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
String sql = "select name,age,des from customer limit 1000000"; //100万测试数据
ResultSet rs = stmt.executeQuery(sql); //bug 要分次读取,否则记录过多
long startTime = System.currentTimeMillis(); //开始时间
System.out.println("strat execute time: " + startTime);
int rowNo = 0;
int colNo = 0;
while(rs.next())
<span style="white-space:pre"> </span>{
colNo = 0;
nRow = sheet.createRow(rowNo++);
nCell = nRow.createCell(colNo++);
nCell.setCellValue(rs.getString(colNo));
nCell = nRow.createCell(colNo++);
nCell.setCellValue(rs.getString(colNo));
if(rowNo%100==0)
<span style="white-space:pre"> </span>{
<span style="white-space:pre"> </span>System.out.println("row no: " + rowNo);
}
Thread.sleep(1); //休息一下,防止对CPU占用
}
long finishedTime = System.currentTimeMillis(); //处理完成时间
System.out.println("finished execute time: " + (finishedTime - startTime)/1000 + "m");
FileOutputStream fOut = new FileOutputStream(xlsFile);
wb.write(fOut);
fOut.flush();
fOut.close();
long stopTime = System.currentTimeMillis(); //写文件时间
System.out.println("write xlsx file time: " + (stopTime - startTime)/1000 + "m");
if(isClose)
<span style="white-space:pre"> </span>{
this.close(rs, stmt, conn);
}
}
//close resource
private void close(ResultSet rs, Statement stmt, Connection conn ) throws SQLException
{
rs.close();
stmt.close();
conn.close();
}