jxl，poi导出Excel

来源：互联网发布：steam邮箱数据渠道编辑：程序博客网时间：2024/05/21 02:33

最近在学excel导出，参考了两位前辈的资料，学习了记下来！

http://blog.csdn.net/houxuehan/article/details/50960259

http://blog.csdn.net/yunsyz/article/details/42561107

在使用poi3.9.jar导出excel时还要在pom.xml中引入poi-ooxml架包否则没有相关类

package com.controller;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.bean.MyExcel;
import com.bean.Student;
import com.mapper.MyExcelMapper;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

@Controller
public class MyController {
@Autowired
private MyExcelMapper mem;
public static int DEFAULT_COLOUMN_WIDTH = 17;

@RequestMapping("getall")
//@ResponseBody
public ModelAndView getall(){
ModelAndView mav=new ModelAndView();
List<MyExcel> list=mem.getall();
mav.addObject("excels",list);
mav.addObject("name","jack");
mav.setViewName("index");
for(MyExcel s:list){
System.out.println(s);
}
return mav;
}
@RequestMapping("exceloutbyjxl")
public void exceloutbyjxl(HttpServletResponse os){
//使用jxl架包进行导出excel
WritableWorkbook bWorkbook = null;
try {
os.reset();
//设置文件标题及格式
os.setHeader("Content-disposition","attachment;filename="+new String("我的Excel".getBytes("GB2312"),"iso8859_1")+".xls");
//os.setContentType("MSEXCEL");
// 创建Excel对象
bWorkbook = Workbook.createWorkbook(os.getOutputStream());
// 通过Excel对象创建一个选项卡对象
WritableSheet sheet = bWorkbook.createSheet("我的文档", 0);
//获取要输出的数据
List<MyExcel> arrayList=mem.getall();
//使用循环将数据读出
String[] str=new String[]{"序号","帐号","密码","时间"};
for(int i=0;i<str.length;i++){
Label label=new Label(i,0,str[i]);
sheet.addCell(label);
}
for (int i =0; i <arrayList.size(); i++) {
MyExcel book=arrayList.get(i);
//label是单元格数字表示的是列号 i表示行号
Label label=new Label(0,i+1,String.valueOf(book.getEid()));
Label label1=new Label(1,i+1,String.valueOf(book.getEname()));
Label label2=new Label(2,i+1,String.valueOf(book.getPswd()));
Label label3=new Label(3,i+1,String.valueOf(book.getDate()));
sheet.addCell(label);
sheet.addCell(label1);
sheet.addCell(label2);
sheet.addCell(label3);
}

// 创建一个单元格对象，第一个为列，第二个为行，第三个为值
//Label label = new Label(0, 2, "test");
// 将创建好的单元格放入选项卡中
//sheet.addCell(label);
// 写如目标路径
bWorkbook.write();
bWorkbook.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//title是sheet里面第一行的标题，headMap是第二行的字段类型，jsonArray是数据，colWidth列宽
public static void exceloutbypoi(String title,Map<String, String> headMap,JSONArray jsonArray,int colWidth, OutputStream out){
// 声明一个工作薄
SXSSFWorkbook workbook = new SXSSFWorkbook(1000);//缓存
workbook.setCompressTempFiles(true);

// 生成一个(带标题)表格
SXSSFSheet sheet = workbook.createSheet();
//设置列宽
int minBytes = colWidth<DEFAULT_COLOUMN_WIDTH?DEFAULT_COLOUMN_WIDTH:colWidth;//至少字节数
int[] arrColWidth = new int[headMap.size()];
// 产生表格标题行,以及设置列宽
String[] properties = new String[headMap.size()];
String[] headers = new String[headMap.size()];
int ii = 0;
for (Iterator<String> iter = headMap.keySet().iterator(); iter.hasNext();) {
String fieldName = iter.next();
properties[ii] = fieldName;
headers[ii] = headMap.get(fieldName);
int bytes = fieldName.getBytes().length;
//如果字符长度超过设定列宽则修改列宽
arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
sheet.setColumnWidth(ii,arrColWidth[ii]*256);
ii++;
}
// 遍历集合数据，产生数据行
int rowIndex = 0;
for (Object obj : jsonArray) {
//excel最多显示6553行，也就是说如果行数是第一行或者最后一行生成标题和字段类型
if(rowIndex == 65535 || rowIndex == 0){
//如果数据超过了，则在第二页显示
if ( rowIndex != 0 ) sheet = workbook.createSheet();
//设置标题
SXSSFRow titleRow = sheet.createRow(0);
titleRow.createCell(0).setCellValue(title);
//sheet.addMergedRegion（new CellRangeAddress(起始行号，终止行号，起始列号，终止列号)）用来合并单元格
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));
//设置字段类型
SXSSFRow headerRow = sheet.createRow(1);
for(int i=0;i<headers.length;i++)
{
headerRow.createCell(i).setCellValue(headers[i]);
}
//数据内容从 rowIndex=2开始
rowIndex = 2;
}
//写完标题及字段类型之后把输出的对象转换为JSONObject对象然后输出到Excel
JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
SXSSFRow dataRow = sheet.createRow(rowIndex);
for (int i = 0; i < properties.length; i++)
{
SXSSFCell newCell = dataRow.createCell(i);
Object o = jo.get(properties[i]);
//1,2步骤完成即可把这个对象中的每个属相放入到excel中
String cellValue = o.toString();
newCell.setCellValue(cellValue);
}
rowIndex++;
}
try {
workbook.write(out);
workbook.close();
workbook.dispose();
} catch (IOException e) {
e.printStackTrace();
}
}
public static void main(String[] args) throws IOException {
JSONArray ja = new JSONArray();
for(int i=0;i<100000;i++){
Student s = new Student();
s.setName("POI"+i);
s.setAge(i);
s.setBirthday(new Date());
s.setHeight(i);
s.setWeight(i);
ja.add(s);
}
Map<String,String> headMap = new LinkedHashMap<String,String>();
headMap.put("name","姓名");
headMap.put("age","年龄");
headMap.put("birthday","生日");
headMap.put("height","身高");
headMap.put("weight","体重");
String title = "测试";
OutputStream outXlsx = new FileOutputStream("d://myexcel.xlsx");
System.out.println("正在导出xlsx....");
Date d2 = new Date();
MyController.exceloutbypoi(title,headMap,ja,0,outXlsx);
System.out.println((new Date().getTime()-d2.getTime())+"ms");
outXlsx.close();
}
}

阅读全文

0 0