poi 操作excle小例子
来源:互联网 发布:中文字符串相似度算法 编辑:程序博客网 时间:2024/05/16 09:35
package com.dimi.qiss.iptecl;import java.io.FileOutputStream;import java.io.IOException;import java.io.OutputStream;import java.util.ArrayList;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.HSSFColor;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.ss.util.CellRangeAddress;public class Cexcle {public void importExcle(List<Procity> lProcities) {Workbook wb = new HSSFWorkbook();Sheet sheet = wb.createSheet("超时预警清单");sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));sheet.setColumnWidth(1, 4500);sheet.setColumnWidth(2, 4500);sheet.setColumnWidth(3, 4500);sheet.setColumnWidth(4, 5000);sheet.setColumnWidth(5, 4500);// 标题样式HSSFFont font = (HSSFFont) wb.createFont();font.setFontHeight((short) 350);font.setFontName("黑体");font.setColor(HSSFColor.RED.index);font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示HSSFCellStyle stitle = (HSSFCellStyle) wb.createCellStyle();stitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);stitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);stitle.setFont(font);// 条目标题样式HSSFCellStyle style = (HSSFCellStyle) wb.createCellStyle();style.setAlignment(HSSFCellStyle.ALIGN_CENTER);style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 设置背景或前景颜色需要设置填充style.setFillForegroundColor(HSSFColor.GREEN.index);style.setBorderBottom(HSSFCellStyle.BORDER_THIN);style.setBorderLeft(HSSFCellStyle.BORDER_THIN);style.setBorderTop(HSSFCellStyle.BORDER_THIN);style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 条目样式HSSFCellStyle srow = (HSSFCellStyle) wb.createCellStyle();srow.setAlignment(HSSFCellStyle.ALIGN_CENTER);srow.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);srow.setBorderBottom(HSSFCellStyle.BORDER_THIN);srow.setBorderLeft(HSSFCellStyle.BORDER_THIN);srow.setBorderTop(HSSFCellStyle.BORDER_THIN);srow.setBorderRight(HSSFCellStyle.BORDER_THIN);Row row = sheet.createRow(0);row.setHeight((short) 800);row.createCell(0, 5);Cell cell = row.createCell(0);cell.setCellValue("超时递送清单详情");cell.setCellStyle(stitle);row = sheet.createRow(1);row.setHeight((short) 500);// row.setRowStyle(style);cell = row.createCell(0);cell.setCellStyle(style);cell.setCellValue("序号");cell = row.createCell(1);cell.setCellStyle(style);cell.setCellValue("物流单号");cell = row.createCell(2);cell.setCellStyle(style);cell.setCellValue("物流公司");cell = row.createCell(3);cell.setCellStyle(style);cell.setCellValue("收件机构");cell = row.createCell(4);cell.setCellStyle(style);cell.setCellValue("递送时间");cell = row.createCell(5);cell.setCellStyle(style);cell.setCellValue("单证分数");int count = 0;for (Procity pro : lProcities) {count++;row = sheet.createRow(++count);cell = row.createCell(0);cell.setCellStyle(srow);cell.setCellValue(--count);cell = row.createCell(1);cell.setCellStyle(srow);cell.setCellValue(pro.getLogistics());cell = row.createCell(2);cell.setCellStyle(srow);cell.setCellValue(pro.getLogistComp());cell = row.createCell(3);cell.setCellStyle(srow);cell.setCellValue(pro.getReceiveDept());cell = row.createCell(4);cell.setCellStyle(srow);cell.setCellValue(pro.getSendTime());cell = row.createCell(5);cell.setCellStyle(srow);cell.setCellValue(pro.getBitchCount());}// OutputStream oStream = new ByteArrayOutputStream();try {OutputStream oStream = new FileOutputStream("d:aaa.xls");wb.write(oStream);} catch (IOException e) {e.printStackTrace();}// return oStream;}public static void main(String[] args) {Cexcle cexcle = new Cexcle();List<Procity> lProcities = new ArrayList<Procity>();Procity procity = new Procity("86520001264", "申通快递", "上海嘉定收件中心","2014-04-01 11:38:23", 35);lProcities.add(procity);procity = new Procity("86920306214", "圆通快递", "浙江杭州收件中心","2014-04-01 11:38:23", 35);lProcities.add(procity);procity = new Procity("89820201254", "天天快递", "苏州昆山大王庄收件中心","2014-04-01 11:38:23", 35);lProcities.add(procity);cexcle.importExcle(lProcities);}}
POI中可能会用到一些需要设置EXCEL单元格格式的操作小结:
先获取工作薄对象:
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFCellStyle setBorder = wb.createCellStyle();
一、设置背景色:
setBorder.setFillForegroundColor((short) 13);// 设置背景色
setBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
二、设置边框:
setBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
setBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
setBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
setBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
三、设置居中:
setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
四、设置字体:
HSSFFont font = wb.createFont();
font.setFontName("黑体");
font.setFontHeightInPoints((short) 16);//设置字体大小
HSSFFont font2 = wb.createFont();
font2.setFontName("仿宋_GB2312");
font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
font2.setFontHeightInPoints((short) 12);
setBorder.setFont(font);//选择需要用到的字体格式
五、设置列宽:
sheet.setColumnWidth(0, 3766); //第一个参数代表列id(从0开始),第2个参数代表宽度值
六、设置自动换行:
setBorder.setWrapText(true);//设置自动换行
七、合并单元格:
Region region1 = new Region(0, (short) 0, 0, (short) 6);
//参数1:行号 参数2:起始列号 参数3:行号 参数4:终止列号
sheet.addMergedRegion(region1);
八、加边框
HSSFCellStyle cellStyle= wookBook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setBorderBottom(HSSFCellStyle.BorderBORDER_MEDIUM);
cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
另附:完整小例子一个
开发环境:IntelliJ IDEA 10.0.2
@ResponseBody
@RequestMapping(value = "/reportForms/joinStocktaking/exportStorage.api")
public AjaxResponse exportStorage(@RequestBody StorageModel model) throws Exception {
if (logger.isDebugEnabled())
logger.debug("tmpdir is, {}", System.getProperty(JAVA_IO_TMPDIR));
int row = 1;
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet hssfSheet = workbook.createSheet();
HSSFCellStyle style = workbook.createCellStyle();
style.setFillBackgroundColor(HSSFCellStyle.LEAST_DOTS);
style.setFillPattern(HSSFCellStyle.LEAST_DOTS);
//设置Excel中的边框(表头的边框)
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
style.setRightBorderColor(HSSFColor.BLACK.index);
style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
style.setTopBorderColor(HSSFColor.BLACK.index);
//设置字体
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short) 14); // 字体高度
font.setFontName(" 黑体 "); // 字体
style.setFont(font);
HSSFRow firstRow = hssfSheet.createRow((short) 0);
HSSFCell firstCell = firstRow.createCell(0);
firstRow.setHeight((short) 400);
//设置Excel中的背景
style.setFillForegroundColor(HSSFColor.GREEN.index);
style.setFillBackgroundColor(HSSFColor.GREEN.index);
firstCell.setCellValue(new HSSFRichTextString("库房"));
firstCell.setCellStyle(style);
HSSFCell secondCell = firstRow.createCell(1);
firstRow.setHeight((short) 400);
style.setFillForegroundColor(HSSFColor.GREEN.index);
style.setFillBackgroundColor(HSSFColor.GREEN.index);
secondCell.setCellValue(new HSSFRichTextString("库区"));
secondCell.setCellStyle(style);
HSSFCell threeCell = firstRow.createCell(2);
firstRow.setHeight((short) 400);
style.setFillForegroundColor(HSSFColor.GREEN.index);
style.setFillBackgroundColor(HSSFColor.GREEN.index);
threeCell.setCellValue(new HSSFRichTextString("物料编号"));
threeCell.setCellStyle(style);
HSSFCell fourCell = firstRow.createCell(3);
firstRow.setHeight((short) 400);
style.setFillForegroundColor(HSSFColor.GREEN.index);
style.setFillBackgroundColor(HSSFColor.GREEN.index);
fourCell.setCellValue(new HSSFRichTextString("物料名称"));
fourCell.setCellStyle(style);
HSSFCell fiveCell = firstRow.createCell(4);
firstRow.setHeight((short) 400);
style.setFillForegroundColor(HSSFColor.GREEN.index);
style.setFillBackgroundColor(HSSFColor.GREEN.index);
fiveCell.setCellValue(new HSSFRichTextString("在库数量"));
fiveCell.setCellStyle(style);
HSSFCell sixCell = firstRow.createCell(5);
firstRow.setHeight((short) 400);
style.setFillForegroundColor(HSSFColor.GREEN.index);
style.setFillBackgroundColor(HSSFColor.GREEN.index);
sixCell.setCellValue(new HSSFRichTextString("锁定数量"));
sixCell.setCellStyle(style);
//设置列宽
hssfSheet.setColumnWidth(0, 7000);
hssfSheet.setColumnWidth(1, 8000);
hssfSheet.setColumnWidth(2, 4000);
hssfSheet.setColumnWidth(3, 6000);
hssfSheet.setColumnWidth(4, 4000);
hssfSheet.setColumnWidth(5, 4000);
List<?> list = joinStocktackingService.findjoinStorageByTerm(model.getWareHouse(), model.getStockArea(), model.getMaterialCode(), model.getMaterialName());
for (Object object : list) {
Object[] objects = (Object[]) object;
Storage storage = (Storage) objects[0];
Warehouse warehouse = (Warehouse) objects[1];
StockArea stockArea = (StockArea) objects[2];
Material material = (Material) objects[3];
//设置Excel中的边框
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
HSSFRow hssfRow = hssfSheet.createRow((short) row);
HSSFCell firstHssfCell = hssfRow.createCell(0);//库房
firstHssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);
firstHssfCell.setCellValue(new HSSFRichTextString(warehouse.getName()));
firstHssfCell.setCellStyle(cellStyle);//设置单元格的样式
HSSFCell secondHssfCell = hssfRow.createCell(1);
secondHssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);
secondHssfCell.setCellValue(new HSSFRichTextString(stockArea.getName()));
secondHssfCell.setCellStyle(cellStyle);//设置单元格的样式
HSSFCell threeHssfCell = hssfRow.createCell(2);
threeHssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);
threeHssfCell.setCellValue(new HSSFRichTextString(material.getCode()));
threeHssfCell.setCellStyle(cellStyle);//设置单元格的样式
HSSFCell fourHssfCell = hssfRow.createCell(3);
fourHssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);
fourHssfCell.setCellValue(new HSSFRichTextString(material.getName()));
fourHssfCell.setCellStyle(cellStyle);//设置单元格的样式
HSSFCell fiveHssfCell = hssfRow.createCell(4);
fiveHssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);
fiveHssfCell.setCellValue(new HSSFRichTextString(String.valueOf(storage.getQty())));
fiveHssfCell.setCellStyle(cellStyle);//设置单元格的样式
HSSFCell sixHssfCell = hssfRow.createCell(5);
sixHssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);
sixHssfCell.setCellValue(new HSSFRichTextString(String.valueOf(storage.getQtyLocked())));
sixHssfCell.setCellStyle(cellStyle);//设置单元格的样式
row++;
}
String newFileName = String.format("%s.%s", "joinStocktaking-" + (new Date()).getTime(), "xls");
String uploadPath = FileUtils.contractPath(System.getProperty(JAVA_IO_TMPDIR), newFileName);
FileOutputStream fOut = new FileOutputStream(uploadPath);
workbook.write(fOut);
fOut.flush();
fOut.close();
return AjaxResponse.createSuccess(newFileName);
}
- poi 操作excle小例子
- Java Poi 操作Excle
- Java Poi 操作Excle
- poi操作Excel的一个小例子
- Java操作POI导出excle(单个excle)
- poi(excle)
- java POI 操作EXCEL小例子+各种属性
- apache POI 操作excle表格文件,搬运百度的
- Java操作POI批量导出多个excle
- poi 实现excel小例子
- poi读Excle
- POI导出Excle HSSF
- poi读取excle文件
- poi导出excle
- POI遍历Excle表
- springMVC导入excle poi
- POI导出excle数据
- POI操作EXCEL例子代码
- forward和sendRedirect的区别
- nginx-maven-配置文件关键点
- 解决懒加载问题的过滤器
- 手机软件开发者的系统选择
- HDU 1312 Red and Black
- poi 操作excle小例子
- 回车符和换行符区别
- java.lang.String中的trim()方法的详细说明
- Android有效解决加载大图片时内存溢出问题VMRuntime
- 如何才能跟 App Store 审核团队有效沟通?
- Shellcode
- 内核钩子实例
- 我的第一篇博客
- Window事件大全