POI,java导出execl
来源:互联网 发布:起爆点指标公式源码 编辑:程序博客网 时间:2024/05/28 23:11
一。下载POI JAR包。下载地址如下http://download.csdn.net/detail/cowbin2012/7135157
二。实现代码如下
// 声明一个工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); // 生成一个表格 HSSFSheet sheet = workbook.createSheet("记录"); // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth((short) 15); // 生成一个样式 HSSFCellStyle style = workbook.createCellStyle(); // 设置这些样式 style.setFillForegroundColor(HSSFColor.WHITE.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 生成一个字体 HSSFFont font = workbook.createFont(); // font.setColor(HSSFColor.VIOLET.index); // font.setFontHeightInPoints((short) 12); // font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 把字体应用到当前的样式 style.setFont(font); // 生成并设置另一个样式 HSSFCellStyle style2 = workbook.createCellStyle(); style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 生成另一个字体 HSSFFont font2 = workbook.createFont(); font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); // 把字体应用到当前的样式 style2.setFont(font2); // 声明一个画图的顶级管理器 HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); // 定义注释的大小和位置,详见文档 HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5)); // 设置注释内容 comment.setString(new HSSFRichTextString("可以在POI中添加注释!")); // 设置注释作者,当鼠标移动到单元格上是可以在状态栏中看到该内容. comment.setAuthor("leno"); // 产生表格标题行 HSSFRow row = sheet.createRow(0); String [] headers=new String[]{"开始时间","结束时间","有效时间","应用类型","广告类型","素材","运营商","频道名称","区域名称"}; String webPath = request.getSession().getServletContext().getRealPath("/uploadfiles") ; for (short i = 0; i < headers.length; i++) { HSSFCell cell = row.createCell(i); row.setHeightInPoints(20); cell.setCellStyle(style); HSSFRichTextString text = new HSSFRichTextString(headers[i]); cell.setCellValue(text); } Iterator<EventHistoryDivide> it = historyList.iterator();//historyList换为你自己的list数据就可以 int index = 0; while(it.hasNext()){ index++; EventHistoryDivide his=it.next(); row = sheet.createRow(index); for (short i = 0; i < headers.length; i++) { HSSFCell cell = row.createCell(i); cell.setCellStyle(style); if(i==0){ HSSFRichTextString text = new HSSFRichTextString(DateUtil.format(his.getStartDate(), "yyyy-MM-dd HH:mm:ss")); sheet.setColumnWidth(i, (short) (35.7 * 200)); cell.setCellValue(text); } else if(i==1){ HSSFRichTextString text = new HSSFRichTextString(DateUtil.format(his.getEndDate(), "yyyy-MM-dd HH:mm:ss")); sheet.setColumnWidth(i, (short) (35.7 * 200)); cell.setCellValue(text); } else if(i==2){ HSSFRichTextString text = new HSSFRichTextString(his.getEffectTimeStr()); cell.setCellValue(text); } else if(i==3){ HSSFRichTextString text = new HSSFRichTextString(his.getAppTypeName()); cell.setCellValue(text); } else if(i==4){ HSSFRichTextString text = new HSSFRichTextString((his.getAdvTypeName()==null?"":his.getAdvTypeName())); cell.setCellValue(text); } //插图 else if(i==5){ if(his.getEventType().longValue()!=1){ row.setHeightInPoints(20); HSSFRichTextString text = new HSSFRichTextString(his.getContent().getContentName()); cell.setCellValue(text); } else if(his.getEventType().longValue()==1){ if(his.getPicture()!=null){ File f=new File(webPath.substring(0, webPath.length()-12)+his.getPicture().getPicturePath()); BufferedImage bufferImg =null; if(f.exists()){ ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream(); bufferImg =ImageIO.read(f); ImageIO.write(bufferImg,"jpg",byteArrayOut); row.setHeightInPoints(40); sheet.setColumnWidth(i, (short) (35.7 * 110)); HSSFClientAnchor anchor = new HSSFClientAnchor(5, 5, 1023, 255, (short) 5, index, (short) 5, index); anchor.setAnchorType(1); patriarch.createPicture(anchor, workbook.addPicture( byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG)); HSSFRichTextString text = new HSSFRichTextString(his.getPicture().getPicturePath()); } } } } else if(i==6){ HSSFRichTextString text = new HSSFRichTextString(his.getCompany().getCompanyName()); cell.setCellValue(text); } else if(i==7){ HSSFRichTextString text = new HSSFRichTextString(his.getServiceName()); cell.setCellValue(text); } else if(i==8){ HSSFRichTextString text = new HSSFRichTextString(his.getRegionName()); cell.setCellValue(text); } } } FileOutputStream fileOut = null; try { File file=new File(webPath+"/记录.xls"); if(file.exists()){ file.delete(); } fileOut = new FileOutputStream(file); workbook.write(fileOut); } catch (IOException e) { e.printStackTrace(); }finally{ if(fileOut!=null){ try { fileOut.close(); } catch (IOException e) { e.printStackTrace(); } } }
二。jsp页面
<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="GBK"%><%@page import="java.io.*"%><%@ page import="java.io.OutputStream" %><!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html><head><meta http-equiv="Content-Type" content="text/html; charset=GBK"><title>Insert title here</title></head><body><%String filename = new String("播出记录".getBytes("GBK"), "iso8859-1");String webPath = request.getSession().getServletContext().getRealPath("/uploadfiles") ;String fileName = webPath+"\\播出记录.xls";//String file = fileName.substring(fileName.lastIndexOf("/")+1);ServletOutputStream sos = null;//response.getOutputStream();try{out.clearBuffer();// 加上这一句//response.setContentType("application/vnd.ms-excel;charset=GBK");//直接IE打开//response.addHeader("Content-disposition", "attachment;filename="//+ new String(file.getBytes("GBK"), "iso-8859-1") );response.setContentType("application/x-msdownload;charset=GBK"); response.setHeader("Content-Disposition","attachment;filename="+filename+".xls"); sos = response.getOutputStream();InputStream is = new FileInputStream(new File(fileName));byte[] b = new byte[2048];int len;while ((len = is.read(b)) > 0) {sos.write(b, 0, len);}is.close();sos.flush();out.clear();out = pageContext.pushBody();} catch (Exception e) { e.printStackTrace();}finally{ try{ if(sos!=null) sos.close(); }catch (Exception ex) { ex.printStackTrace(); }}%></body></html>
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFCellStyle setBorder = wb.createCellStyle();
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); // 居中
四、设置字体:
HSSFF 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);//设置自动换行
七、合并单元格:
//参数1:行号 参数2:起始列号 参数3:行号 参数4:终止列号
Region region1 = new Region(0, (short) 0, 0, (short) 6);
sheet.addMergedRegion(region1);
附一个例子:
package cn.com.util;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.CellStyle;
mport java.io.FileOutputStream;
import javax.servlet.http.HttpServlet;
public class CreateXL extends HttpServlet {
/** Excel 文件要存放的位置,假定在D盘下 */
public static String outputFile = "c:\\test.xls";
private void cteateCell(HSSFWorkbook wb, HSSFRow row, short col, String val) {
HSSFCell cell = row.createCell(col);
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(val);
HSSFCellStyle cellstyle = wb.createCellStyle();
cellstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);
cell.setCellStyle(cellstyle);
}
public static void main(String argv[]) {
try {
// 创建新的Excel 工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
// 设置字体
HSSFFont font = workbook.createFont();
// font.setColor(HSSFFont.COLOR_RED);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontHeightInPoints((short) 14);
// HSSFFont font2 = workbook.createFont();
// font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// font.setFontHeightInPoints((short)14);
// 设置样式
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFont(font);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// HSSFCellStyle cellStyle2= workbook.createCellStyle();
// cellStyle.setFont(font2);
// cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 在Excel工作簿中建一工作表,其名为缺省值
// 如要新建一名为"月报表"的工作表,其语句为:
HSSFSheet sheet = workbook.createSheet("月报表");
CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0,
11);
sheet.addMergedRegion(cellRangeAddress);
//第一行
// 在索引0的位置创建行(最顶端的行)
HSSFRow row = sheet.createRow(0);
// 在索引0的位置创建单元格(左上端)
HSSFCell cell = row.createCell(0);
// 定义单元格为字符串类型
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyle);
// 在单元格中输入一些内容
cell.setCellValue(new HSSFRichTextString("北京亿卡联科技发展有限公司小区门禁维修月报表"));
//第二行
cellRangeAddress = new CellRangeAddress(1, 1, 3, 6);
sheet.addMergedRegion(cellRangeAddress);
row = sheet.createRow(1);
HSSFCell datecell = row.createCell(3);
datecell.setCellType(HSSFCell.CELL_TYPE_STRING);
datecell.setCellStyle(cellStyle);
datecell.setCellValue("时间间隔xxxxx");
cellRangeAddress = new CellRangeAddress(1, 1, 9,
10);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(9).setCellValue("单位:元");
//第三行
row=sheet.createRow(2);
row.createCell(0).setCellValue("一、");
row.createCell(1).setCellValue("基本资料");
//第4行
row=sheet.createRow(3);
row.createCell(1).setCellValue("小区名称:");
cellRangeAddress=new CellRangeAddress(3,3,2,11);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(2).setCellValue("xxxxx");
//第5行
row=sheet.createRow(4);
row.createCell(1).setCellValue("座落地点:");
cellRangeAddress=new CellRangeAddress(4,4,2,11);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(2).setCellValue("xxxxx");
//第6行
row=sheet.createRow(5);
row.createCell(1).setCellValue("建成年月:");
cellRangeAddress=new CellRangeAddress(5,5,2,4);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(2).setCellValue("年月日:xxxxx");
row.createCell(5).setCellValue("联系人");
cellRangeAddress=new CellRangeAddress(5,5,6,8);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(6).setCellValue("XXX");
row.createCell(9).setCellValue("电话");
cellRangeAddress=new CellRangeAddress(5,5,10,11);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(10).setCellValue("XXX");
//第7行
row=sheet.createRow(6);
row.createCell(1).setCellValue("住户:");
row.createCell(2).setCellValue("(XX)");
row.createCell(3).setCellValue("(户)");
cellRangeAddress=new CellRangeAddress(6,6,4,5);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(4).setCellValue("共计( )");
row.createCell(6).setCellValue("幢");
cellRangeAddress=new CellRangeAddress(6,6,7,8);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(7).setCellValue("发卡张数");
cellRangeAddress=new CellRangeAddress(6,6,9,10);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(9).setCellValue("xxxx");
//第9行
row=sheet.createRow(8);
row.createCell(0).setCellValue("二、");
cellRangeAddress=new CellRangeAddress(8,8,1,2);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(1).setCellValue("维修用材料台账");
row.createCell(6).setCellValue("三、");
cellRangeAddress=new CellRangeAddress(8,8,7,9);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(7).setCellValue("维修工时记录");
//第10行
row=sheet.createRow(9);
row.createCell(0).setCellValue("日期");
row.createCell(1).setCellValue("维修事项");
row.createCell(2).setCellValue("材料清单");
row.createCell(3).setCellValue("数量");
row.createCell(4).setCellValue("单价");
row.createCell(5).setCellValue("材料金额");
row.createCell(7).setCellValue("日期");
row.createCell(8).setCellValue("技工");
row.createCell(9).setCellValue("工时数");
row.createCell(10).setCellValue("单价");
row.createCell(11).setCellValue("工时金额");
//填充数据
for (int i = 0; i < 10; i++) {
row=sheet.createRow(9+i+1);
row.createCell(0).setCellValue("日期");
row.createCell(1).setCellValue("维修事项");
row.createCell(2).setCellValue("材料清单");
row.createCell(3).setCellValue("数量");
row.createCell(4).setCellValue("单价");
row.createCell(5).setCellValue("材料金额");
row.createCell(7).setCellValue("日期");
row.createCell(8).setCellValue("技工");
row.createCell(9).setCellValue("工时数");
row.createCell(10).setCellValue("单价");
row.createCell(11).setCellValue("工时金额");
}
//第n+10行
row=sheet.createRow(9+10+1);
//cellRangeAddress=new CellRangeAddress(19,19,0,4);
//sheet.addMergedRegion(cellRangeAddress);
row.createCell(0).setCellValue("累计:");
row.createCell(1).setCellValue("xxx");
row.createCell(7).setCellValue("累计:");
row.createCell(8).setCellValue("xxx");
// 新建一输出文件流
FileOutputStream fOut = new FileOutputStream(outputFile);
// 把相应的Excel 工作簿存盘
workbook.write(fOut);
fOut.flush();
// 操作结束,关闭文件
fOut.close();
System.out.println("文件生成...");
} catch (Exception e) {
System.out.println("已运行 xlCreate() : " + e);
}
}
}
- POI,java导出execl
- java poi导出execl类
- poi导出execl
- poi导出execl
- java对execl的导入、导出操作-- POI / JXL
- java对execl的导入、导出操作-- POI / JXL .
- java 使用poi 结合Struts2导出execl表格
- Java POI操作execl
- java execl报表导出
- Java 导出 execl
- java导出execl表
- java Execl导出功能
- Java POI 读取 Execl 图片
- springmvc结合poI实现Execl导出
- java使用poi导出execl模板给其控件赋值的小技巧
- 【小白做项目Java】——POI导入和导出 execl文件
- java使用apache.poi SXSSFWorkbook导出大数据的execl表格并下载
- java jsp 导出execl报表
- 第四周作业——图的表示
- oracle数据库导入导出(本地数据库、远程数据库)
- win7更改计算机名称-登录显示当前用户为admin
- html+div+css布局入门
- 如何实时刷新文本框中的文字
- POI,java导出execl
- LeetCode problem 4:Sort List
- 使用KeyboardView导致EditText光标不显示的问题
- 00认证(Authentication)和授权(Authorization)
- 电脑高手常用的5个按钮
- 查询依赖对象(嵌套)
- Java基础---Java---网络编程---TCP、UDP、UDP-键盘录入方式数据、Socket、TCP复制文件、UDP-聊天
- Excel2003默认调色板颜色索引对照表
- 阿里实习生招聘笔试题目