第一种:导入后保存的格式为.xls格式,针对EXCEL2003 版本
采用的是SSM框架从后台获取数据
打开文件后的内容的具体格式
具体实现代码如下,注释写的很详细,不在阐述
@RequestMapping("/cargo/outproduct/print.action")
public void print(String inputDate,HttpServletResponse response) throws IOException{
List<OutProductVO> dataList=outProductService.find(inputDate);
Workbook wb=new HSSFWorkbook(); //创建一个工作簿
Sheet sheet=wb.createSheet(); //创建一个工作表
Row nRow=null;
Cell nCell=null;
int rowNo=0; //行号
int colNo=1; //列号
sheet.setColumnWidth(0, 2*200); //列宽
sheet.setColumnWidth(1, 26*300);
sheet.setColumnWidth(2, 12*300);
sheet.setColumnWidth(3, 29*300);
sheet.setColumnWidth(4, 10*300);
sheet.setColumnWidth(5, 12*300);
sheet.setColumnWidth(6, 16*300);
sheet.setColumnWidth(7, 15*300);
sheet.setColumnWidth(8, 15*300);
sheet.setColumnWidth(9, 12*300);
//声明样式对象和字体对象
CellStyle nStyle=wb.createCellStyle();
Font nFont=wb.createFont();
//大标题,合并单元格
sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 9));
//合并单元格的内容是写在合并单元格之前的第一个单元格中
nRow=sheet.createRow(rowNo++);
nRow.setHeightInPoints(36); //行高
//初始化
nStyle=wb.createCellStyle();
nFont=wb.createFont();
nCell=nRow.createCell(1);
nCell.setCellStyle(this.bigTitle(wb, nStyle, nFont));
nCell.setCellValue(inputDate.replaceFirst("-0", "-").replaceFirst("-", "年")+"月份出货表");
String [] title=new String[]{"客户","订单号","货号","数量","工厂","附件","工厂交期","船期","贸易条款"};
nRow=sheet.createRow(rowNo++);
//初始化
nStyle=wb.createCellStyle();
nFont=wb.createFont();
for (int i = 0; i < title.length; i++) {
nCell=nRow.createCell(i+1);
nCell.setCellValue(title[i]);
nCell.setCellStyle(this.title(wb, nStyle, nFont));
}
//初始化
nStyle=wb.createCellStyle();
nFont=wb.createFont();
//处理数据
for (int i = 0; i < dataList.size(); i++) {
colNo=1;
OutProductVO op=dataList.get(i);
nRow = sheet.createRow(rowNo++);
nRow.setHeightInPoints(24);
nCell = nRow.createCell(colNo++);
nCell.setCellValue(op.getCustomName());
nCell.setCellStyle(this.text(wb, nStyle, nFont));
nCell = nRow.createCell(colNo++);
nCell.setCellValue(op.getContractNo());
nCell.setCellStyle(this.text(wb, nStyle, nFont));
nCell = nRow.createCell(colNo++);
nCell.setCellValue(op.getProductNo());
nCell.setCellStyle(this.text(wb, nStyle, nFont));
nCell = nRow.createCell(colNo++);
nCell.setCellValue(op.getCnumber());
nCell.setCellStyle(this.text(wb, nStyle, nFont));
nCell = nRow.createCell(colNo++);
nCell.setCellValue(op.getFactoryName());
nCell.setCellStyle(this.text(wb, nStyle, nFont));
nCell = nRow.createCell(colNo++);
nCell.setCellValue(op.getExts());
nCell.setCellStyle(this.text(wb, nStyle, nFont));
nCell = nRow.createCell(colNo++);
nCell.setCellValue(op.getDeliveryPeriod());
nCell.setCellStyle(this.text(wb, nStyle, nFont));
nCell = nRow.createCell(colNo++);
nCell.setCellValue(op.getShipTime());
nCell.setCellStyle(this.text(wb, nStyle, nFont));
nCell = nRow.createCell(colNo++);
nCell.setCellValue(op.getTradeTerms());
nCell.setCellStyle(this.text(wb, nStyle, nFont));
}
/*
//直接保存文件到桌面的方法
OutputStream os=new FileOutputStream(new File("C:\\Users\\Administrator\\Desktop\\outproduct.xls"));
wb.write(os);
os.flush();
os.close();*/
//下载,自己封装的一个工具类,点击后弹出下载保存框
DownloadUtil du=new DownloadUtil();
ByteArrayOutputStream os=new ByteArrayOutputStream();
wb.write(os);
du.download(os, response, "出货表.xls");
}
//大标题样式
public CellStyle bigTitle(Workbook wb,CellStyle nStyle,Font nFont){
nFont.setFontName("宋体");
nFont.setFontHeightInPoints((short)16);
nFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //加粗
nStyle.setAlignment(CellStyle.ALIGN_CENTER); //横向居中
nStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//纵向居中
nStyle.setFont(nFont);
return nStyle;
}
//标题样式
public CellStyle title(Workbook wb,CellStyle nStyle,Font nFont){
nFont.setFontName("黑体");
nFont.setFontHeightInPoints((short)12);
nStyle.setAlignment(CellStyle.ALIGN_CENTER); //横向居中
nStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//纵向居中
//表格线
nStyle.setBorderBottom(CellStyle.BORDER_THIN);
nStyle.setBorderLeft(CellStyle.BORDER_THIN);
nStyle.setBorderRight(CellStyle.BORDER_THIN);
nStyle.setBorderTop(CellStyle.BORDER_THIN);
nStyle.setFont(nFont);
return nStyle;
}
//文字样式
public CellStyle text(Workbook wb,CellStyle nStyle,Font nFont){
nFont.setFontName("Times New Roman");
nFont.setFontHeightInPoints((short)12);
nStyle.setAlignment(CellStyle.ALIGN_CENTER); //横向居中
nStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//纵向居中
//表格线
nStyle.setBorderBottom(CellStyle.BORDER_THIN);
nStyle.setBorderLeft(CellStyle.BORDER_THIN);
nStyle.setBorderRight(CellStyle.BORDER_THIN);
nStyle.setBorderTop(CellStyle.BORDER_THIN);
nStyle.setFont(nFont);
return nStyle;
}
第二种: 这种方法是对上一种方法的优化,可以直接利用现成的模板直接获取模板里面的样式,以后修改样式只需对模板进行修改,而不用修改代码
模板样式的.XLS文件,在EXCEL弄好所要的样式文件,在代码中直接get行,列的样式等属性,同样是EXCEL2003版本
我的模板文件放置的目录
具体效果不贴图了,下面是代码的实现
//模板开发
@RequestMapping("/cargo/outproduct/printHSSF.action")
public void printHSSF(String inputDate, HttpServletRequest request, HttpServletResponse response) throws IOException{
//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;
int rowNo = 0; //行号
int colNo = 1; //列号
//获取模板上的单元格样式
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 extStyle = nCell.getCellStyle();
//工厂交期
nCell = nRow.getCell(7);
CellStyle fdateStyle = nCell.getCellStyle();
//船期交期
nCell = nRow.getCell(7);
CellStyle sdateStyle = nCell.getCellStyle();
//贸易条款的样式
nCell = nRow.getCell(8);
CellStyle tradeStyle = nCell.getCellStyle();
//处理大标题
nRow = sheet.getRow(rowNo++); //获取一个行对象
nCell = nRow.getCell(colNo); //获取一个单元格对象
nCell.setCellValue(inputDate.replaceFirst("-0", "-").replaceFirst("-", "年") + "月份出货表"); //yyyy-MM
rowNo++; //跳过静态表格头
//处理内容
List<OutProductVO> dataList = outProductService.find(inputDate);
for(int j=0;j<dataList.size();j++){
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.getExts());
nCell.setCellStyle(extStyle);
nCell = nRow.createCell(colNo++);
nCell.setCellValue(op.getDeliveryPeriod());
nCell.setCellStyle(fdateStyle);
nCell = nRow.createCell(colNo++);
nCell.setCellValue(op.getShipTime());
nCell.setCellStyle(sdateStyle);
nCell = nRow.createCell(colNo++);
nCell.setCellValue(op.getTradeTerms());
nCell.setCellStyle(tradeStyle);
}
// OutputStream os = new FileOutputStream("c:\\outproduct.xls");
// wb.write(os);
//
// os.flush();
// os.close();
ByteArrayOutputStream os = new ByteArrayOutputStream();
wb.write(os);
DownloadUtil downloadUtil = new DownloadUtil(); //直接弹出下载框,用户可以打开,可以保存
downloadUtil.download(os, response, "出货表.xls");
os.flush();
os.close();
}
第三种: 对于EXCEL 2007版本的保存方式,.xlsx文件,同样采用的是模板保存的方式进行保存,跟第二种方法差不多,主要将HSSF改成XSSF,模板文件同样要改成xlsx格式的,具体实现如下
//模板开发XSSF
@RequestMapping("/cargo/outproduct/printXSSF.action")
public void printXSSF(String inputDate, HttpServletRequest request, HttpServletResponse response) throws IOException{
//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;
int rowNo = 0; //行号
int colNo = 1; //列号
//获取模板上的单元格样式
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 extStyle = nCell.getCellStyle();
//工厂交期
nCell = nRow.getCell(7);
CellStyle fdateStyle = nCell.getCellStyle();
//船期交期
nCell = nRow.getCell(7);
CellStyle sdateStyle = nCell.getCellStyle();
//贸易条款的样式
nCell = nRow.getCell(8);
CellStyle tradeStyle = nCell.getCellStyle();
//处理大标题
nRow = sheet.getRow(rowNo++); //获取一个行对象
nCell = nRow.getCell(colNo); //获取一个单元格对象
nCell.setCellValue(inputDate.replaceFirst("-0", "-").replaceFirst("-", "年") + "月份出货表"); //yyyy-MM
rowNo++; //跳过静态表格头
//处理内容
List<OutProductVO> dataList = outProductService.find(inputDate);
for(int j=0;j<dataList.size();j++){
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.getExts());
nCell.setCellStyle(extStyle);
nCell = nRow.createCell(colNo++);
nCell.setCellValue(op.getDeliveryPeriod());
nCell.setCellStyle(fdateStyle);
nCell = nRow.createCell(colNo++);
nCell.setCellValue(op.getShipTime());
nCell.setCellStyle(sdateStyle);
nCell = nRow.createCell(colNo++);
nCell.setCellValue(op.getTradeTerms());
nCell.setCellStyle(tradeStyle);
}
// OutputStream os = new FileOutputStream("c:\\outproduct.xls");
// wb.write(os);
//
// os.flush();
// os.close();
ByteArrayOutputStream os = new ByteArrayOutputStream();
wb.write(os);
DownloadUtil downloadUtil = new DownloadUtil(); //直接弹出下载框,用户可以打开,可以保存
downloadUtil.download(os, response, "出货表.xlsx");
os.flush();
os.close();
}
转载请注明出处,谢谢!