之前写了一篇根据模板导入导出Excel文章POI按模板导入导出EXCEL工具类
上代码
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
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;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
*@author xiliang.xiao
*@date 2015年2月5日 下午4:50:11
*
**/
@SuppressWarnings({"unchecked","rawtypes"})
public class GenerateXSSFExcel {
private Map<Integer,Sheet> sheetMap = new HashMap<Integer, Sheet>();
private Map<Integer,SheetBean> sheetBeanMap = new HashMap<Integer, SheetBean>();
private List<int[]> merged = new ArrayList<int[]>();//合并单元格占用的表格
/**
* 表格的总行数,总列数
*/
private ArrayList m_arraylist = new ArrayList();
// 默认单元格样式： 居左-居中-不加粗-无背景色-只读-字体9-自动换行
public static final String DEFAULT_STYLE = "left-center-n-blank-y-9-y";
// 样式ID定义规则:水平对齐-垂直对齐-是否加粗-背景颜色-是否只读-字体大小-是否自动换行
// 默认表头样式： 居中-居中-加粗-背景色（LIGHT_CORNFLOWER_BLUE）-只读-字体9-自动换行
public static final String DEFAULT_HEAD_STYLE = "head";
public static final short DEFAULT_FONT_SIZE = 9;
public static final short DEFAULT_FONT_HEADSIZE = 9;
public static Map colorMap = new HashMap();// 存放枚举颜色 key为颜色枚举字符串,值为颜色对应index
private Map styleMap = new HashMap();// 已使用样式
private Workbook workbook;
/**
* 初始化颜色枚举
*/
static {
Hashtable m = HSSFColor.getMutableIndexHash();
for (Iterator iterator = m.keySet().iterator(); iterator.hasNext();) {
Object key = iterator.next();
HSSFColor col = (HSSFColor) m.get(key);
// short[] s = col.getTriplet();
colorMap.put(col.getClass().getName().split("\\$")[1], col.getIndex() + "");
// System.out.println(""+s[0]+","+s[1]+","+s[2]+"\t"+col.getClass().getName().split("\\$")[1]);
}
}
/**
* 构造函数
*/
public GenerateXSSFExcel(){
init();
}
public void setArraylist(ArrayList arraylist) {
m_arraylist.add(arraylist);
}
/**
*插入的图片二进制流数组(BASE64字符串)
*/
private ArrayList m_arrayListPictureBitArray = new ArrayList();
public void setarrayListPictureBitArray(ArrayList arrayListPictureBitArray) {
m_arrayListPictureBitArray.add(arrayListPictureBitArray);
}
/**
*插入的图片尺寸(宽，高)
*/
private ArrayList m_arrayListPictureSize = new ArrayList();
public void setarrayListPictureSize(ArrayList arrayListPictureSize) {
m_arrayListPictureSize.add(arrayListPictureSize);
}
//初始化工作区等
private void init(){
//建立缓存工作区，当1000保存有一1000行时缓存到磁盘
this.workbook = new SXSSFWorkbook(1000);
initStyle();
}
/**
* 将缓存excel写到输出流
* @param os
* @throws IOException
*/
public void write(OutputStream os) throws IOException{
//sheet善后处理
if(sheetMap!=null&&!sheetMap.isEmpty()&&sheetBeanMap!=null&&!sheetBeanMap.isEmpty()){
for(Integer i:sheetMap.keySet()){
Sheet sheet = sheetMap.get(i);
SheetBean sheetBean = sheetBeanMap.get(i);
if (m_arrayListPictureBitArray != null && m_arrayListPictureBitArray.size() > 0
&& m_arrayListPictureBitArray.get(i) != null) {
String str = m_arraylist.get(i) == null ? "" : m_arraylist.get(i).toString();
String[] s = str.replaceAll("\\[", "").replaceAll("\\]", "").split(",");// 总行数，总列数
if (s.length == 2) {
str = m_arrayListPictureSize.get(i) == null ? "" : m_arrayListPictureSize
.get(i).toString();// 图片尺寸：宽，高
String[] sSize = str.replaceAll("\\[", "").replaceAll("\\]", "").split(",");
sun.misc.BASE64Decoder decoder = new sun.misc.BASE64Decoder();
byte[] bt = decoder.decodeBuffer(m_arrayListPictureBitArray.get(i).toString()
.replaceAll("\\[", "").replaceAll("\\]", ""));
short cols = 15;
if (sSize.length == 2) {
cols = (short) Math.ceil(Double.valueOf(sSize[0]).doubleValue() / 65);
}
processPicture(sheet, bt, XSSFWorkbook.PICTURE_TYPE_JPEG, 0, Integer
.valueOf(s[0]).intValue(), Short.valueOf(s[1]).shortValue(),
(short) (cols + Short.valueOf(s[1]).shortValue()));
}
}
if(sheetBean.isReadonly()){
//sheet.protectSheet(StringSupport.generateID32());
}
if(!sheetBean.isDisplaygridlines()){
sheet.setDisplayGridlines(false);
}
sheet.setForceFormulaRecalculation(true);// 重新计算公式
}
}
this.workbook.write(os);
}
/**
* 写入excel文件内容
* @param sheetBean
* @param rowList
* @throws Exception
*/
public void processWorkSheet(SheetBean sheetBean,List<RowBean> rowList) throws Exception{
processWorkSheet(sheetBean, rowList, null);
}
/**
* 写入excel文件内容
* @param sheetBean
* @param rowList
* @throws Exception
*/
public void processWorkSheet(SheetBean sheetBean,List<RowBean> rowList,CellStyle cellStyle) throws Exception{
if(sheetBean==null){
throw new Exception("sheet not init!");
}
Sheet sheet = null;
if(!sheetMap.containsKey(sheetBean.getIndex())){
//初始化sheet
sheet = workbook.createSheet(sheetBean.getName() == null ? "sheet" : sheetBean.getName());
sheetMap.put(sheetBean.getIndex(), sheet);
merged.clear();//新加sheet时，将前sheet单元格占用表格清空，
}else{
sheet = sheetMap.get(sheetBean.getIndex());
}
if(sheetBean.getRowList()!=null&&sheetBean.getRowList().size()>0){
if(rowList==null){
rowList = new ArrayList<RowBean>();
}
rowList.addAll(sheetBean.getRowList());
sheetBean.getRowList().clear();
}
if(rowList!=null&&rowList.size()>0){
int rowIndex = sheet.getPhysicalNumberOfRows() == 0 ? 0 : sheet.getLastRowNum() + 1;
for(RowBean row:rowList){
pocessRow(row, sheet,rowIndex,cellStyle);
rowIndex++;
}
}
}
/**
* 行数据写入
* @param rowList
* @param sheet
* @throws Exception
*/
private void pocessRow(RowBean r, Sheet sheet,int rowIndex,CellStyle cellStyle) throws Exception {
Row row = sheet.createRow(rowIndex);
if (r.getHeight() != 0) {
row.setHeightInPoints(r.getHeight());
}
if(r.getCellList()!=null&&r.getCellList().size()>0){
pocessCell(r,row,sheet,rowIndex,cellStyle);
}
}
/**
* 写入单元格数据
* @param row
* @throws Exception
*/
private void pocessCell(RowBean r,Row row, Sheet sheet,int rowIndex,CellStyle cellStyle) throws Exception {
int cellIndex=0;
for (int i = 0; i < r.getCellList().size(); i++) {
CellBean cb = r.getCellList().get(i);
cellIndex = checkMerged(rowIndex,cellIndex);
Cell cell = row.createCell(cellIndex);
String type = "String";
Object value = cb.getValue();
if(value!=null){
type = value.getClass().getSimpleName();
}
try {
// 设置单元格公式
if (!"".equals(cb.getFormula())) {
cell.setCellFormula(cb.getFormula());
}
// 设置单元格值
if (value != null) {
if ("Double".equalsIgnoreCase(type) && !"".equals(value)) {
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(Double.parseDouble(value.toString()));
}
if ("Integer".equalsIgnoreCase(type) && !"".equals(value)) {
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(Integer.parseInt(value.toString()));
// }if("Date".equalsIgnoreCase(type) && !"".equals(value)){
// cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
// cell.setCellValue(dateFormat.parse(value));
} else {
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setCellValue(new XSSFRichTextString(value.toString()));
}
} else {
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
}
} catch (Exception e) {
}
if (cb.getMerge() != null) {
String[] mergeds = cb.getMerge().toUpperCase()
.split(",");
if (mergeds.length == 4) {
int fromRow = Integer.parseInt(mergeds[0]) - 1;
short fromCol = (short) (Integer.parseInt(mergeds[1]) - 1);
int toRow = Integer.parseInt(mergeds[2]) - 1;
short toCol = (short) (Integer.parseInt(mergeds[3]) - 1);
sheet.addMergedRegion(new CellRangeAddress(fromRow, toRow, fromCol, toCol));
//记录已占用的表格
merged.add(new int[]{fromRow, fromCol, toRow, toCol});
} else {
throw new Exception("Merge cells is not configured correctly:"
+ cb.getMerge());
}
}
// 设置单元格宽度，不乘以系数
if (cb.getWidthn() != 0) {
sheet.setColumnWidth(cellIndex, cb.getWidthn());
} else if (cb.getWidth() != 0) {
// 设置单元格宽度
sheet.setColumnWidth(cellIndex, (short)(cb.getWidth() * 36.55));
}
// 单元格是否可可见
if (cb.isHidden()) {
sheet.setColumnHidden(cellIndex, true);
}
cell.setCellStyle(cellStyle==null?creatCellStyle(cb, r):cellStyle);
cellIndex++;
}
}
/**
* 检查当前表格是否在已合并单元格所占用的表格中，并返回可用的cellIndex
* @param rowIndex
* @param cellIndex
* @return
*/
private int checkMerged(int rowIndex, int cellIndex) {
if(merged!=null&&!merged.isEmpty()){
//fromRow, fromCol, toRow, toCol
for(int[] m:merged){
//当前行大于或小于最大占用行时，肯定不会在单元格中
if(rowIndex>m[2]||rowIndex<m[0]){
continue;
}
//当前列大于或小于最大占用行时，肯定不会在单元格中
if(cellIndex>m[3]|| cellIndex<m[1]){
continue;
}
//否则当前表格从合并单元格最大列开始
cellIndex = m[3]+1;
//递归检查
cellIndex = checkMerged(rowIndex,cellIndex);
}
}
return cellIndex;
}
/**
* 根据cellBean rowBena创建cellStyle
* @param cb
* @param r
* @return
* @throws Exception
*/
public CellStyle creatCellStyle(CellBean cb,RowBean r) throws Exception {
// 根据配置设置单元格样式
Map<String, String> styleAtts = new HashMap<String, String>();
StringBuffer styleStr = new StringBuffer();
// 水平对齐 left-center-right
if (r.getAlign() != null || cb.getAlign() != null) {
String align = "";
if (cb.getAlign() != null) {
align = cb.getAlign();
} else {
align = r.getAlign();
}
styleAtts.put("align", align);
styleStr.append( align );
} else {
styleAtts.put("align", "left");
styleStr.append( "left" );
}
// 垂直对齐 top-center-bottom
if (r.getValign() != null
|| cb.getValign() != null) {
String valign = "";
if (cb.getValign() != null) {
valign = cb.getValign();
} else {
valign = r.getValign();
}
styleAtts.put("valign", valign);
styleStr.append( "-" + valign );
} else {
styleAtts.put("valign", "center");
styleStr.append( "-center" );
}
// 字体加粗
if (cb.isBorder()) {
styleAtts.put("border", "y");
styleStr.append( "-y" );
} else {
styleAtts.put("border", "n");
styleStr.append( "-n" );
}
// 字体颜色
if (r.getFontColor() != null || cb.getFontColor() != null) {
String font_color = "";
if (cb.getFontColor() != null) {
font_color = cb.getFontColor();
} else {
font_color = r.getFontColor();
}
styleAtts.put("font_color", font_color);
styleStr.append( "-" + font_color );
} else {
styleAtts.put("font_color", XSSFFont.COLOR_NORMAL + "");
styleStr.append( "-" + XSSFFont.COLOR_NORMAL );
}
// 字体
if (r.getFontName() != null
|| cb.getFontName() != null) {
String font_name = "";
if (cb.getFontName() != null) {
font_name = cb.getFontName();
} else {
font_name = r.getFontName();
}
styleAtts.put("font_name", font_name);
styleStr.append( "-" + font_name );
} else {
styleAtts.put("font_name", "宋体");
styleStr.append( "-宋体" );
}
// 背景颜色-不需要转换颜色
if (r.getBgcolorn() != null
|| cb.getBgcolorn() != null) {
String bgcolorn = "";
if (cb.getBgcolorn() != null) {
bgcolorn = cb.getBgcolorn();
} else {
bgcolorn = r.getBgcolorn();
}
styleAtts.put("bgcolorn", bgcolorn);
styleStr.append( "-" + bgcolorn );
} else if (r.getBgcolor() != null
|| cb.getBgcolor() != null) {
String bgcolor = "";
if (cb.getBgcolor() != null) {
bgcolor = cb.getBgcolor();
} else {
bgcolor = r.getBgcolor();
}
styleAtts.put("bgcolor", bgcolor);
styleStr.append( "-" + bgcolor );
} else {
styleAtts.put("bgcolor", "blank");
styleStr.append( "-blank" );
}
// 单元格是否可编辑
if (!cb.isReadonly()) {
styleAtts.put("readonly", "n");
styleStr.append( "-n" );
} else {
styleAtts.put("readonly", "y");
styleStr.append( "-y" );
}
// 列属性-边框上
if (cb.getBordertop()!= null) {
String bordertop = cb.getBordertop();
styleAtts.put("bordertop", bordertop);
styleStr.append( "-" + bordertop );
} else {
styleAtts.put("bordertop", XSSFCellStyle.BORDER_THIN + "");
styleStr.append( "-" + XSSFCellStyle.BORDER_THIN );
}
// 列属性-边框下
if (cb.getBorderbottom() != null) {
String borderbottom = cb.getBorderbottom();
styleAtts.put("borderbottom", borderbottom);
styleStr.append( "-" + borderbottom );
} else {
styleAtts.put("borderbottom", XSSFCellStyle.BORDER_THIN + "");
styleStr.append( "-" + XSSFCellStyle.BORDER_THIN );
}
// 列属性-边框左
if (cb.getBorderleft() != null) {
String borderleft = cb.getBorderleft();
styleAtts.put("borderleft", borderleft);
styleStr.append( "-" + borderleft );
} else {
styleAtts.put("borderleft", XSSFCellStyle.BORDER_THIN + "");
styleStr.append( "-" + XSSFCellStyle.BORDER_THIN );
}
// 列属性-边框右
if (cb.getBorderright() != null) {
String borderright = cb.getBorderright();
styleAtts.put("borderright", borderright);
styleStr.append( "-" + borderright );
} else {
styleAtts.put("borderright", XSSFCellStyle.BORDER_THIN + "");
styleStr.append( "-" + XSSFCellStyle.BORDER_THIN );
}
// 字体大小
if (cb.getSize() != null || r.getSize() != null) {
String size = "";
if (cb.getSize() != null) {
size = cb.getSize();
} else {
size = r.getSize();
}
styleAtts.put("size", size);
styleStr.append( "-" + size );
} else {
styleAtts.put("size", DEFAULT_FONT_SIZE + "");
styleStr.append( "-" + DEFAULT_FONT_SIZE );
}
// 单元格是否自动换行
if (!cb.isWrap()) {
styleAtts.put("wrap", "n");
styleStr.append( "-n" );
} else {
styleAtts.put("wrap", "y");
styleStr.append( "-y" );
}
// 生成单元格样式
if (r.getStyle() != null || cb.getStyle() != null) {
String style = "";
if (cb.getStyle() != null) {
style = cb.getStyle();
} else {
style = r.getStyle();
}
if ("head".equalsIgnoreCase(style)) {
return (XSSFCellStyle) styleMap.get(DEFAULT_HEAD_STYLE);
} else {
return getCellStyle(styleAtts, styleStr.toString());
}
} else {
return getCellStyle(styleAtts, styleStr.toString());
}
}
/**
* 获取CELL样式
* @param stylsAtts
* @param styleStr
* @return
* @throws Exception
*/
private CellStyle getCellStyle(Map stylsAtts, String styleStr) throws Exception {
if (styleStr == null || "".equalsIgnoreCase(styleStr)
|| DEFAULT_STYLE.equalsIgnoreCase(styleStr))
return (XSSFCellStyle) styleMap.get(DEFAULT_STYLE);
if (styleMap.get(styleStr) != null)
return (XSSFCellStyle) styleMap.get(styleStr);
CellStyle cellStyle = workbook.createCellStyle();
Font font = workbook.createFont();
cellStyle.setWrapText(true);
for (Iterator i = stylsAtts.keySet().iterator(); i.hasNext();) {
String key = (String) i.next();
String value = stylsAtts.get(key).toString();
if ("align".equalsIgnoreCase(key)) {
// 水平对齐
if (value.equalsIgnoreCase("left")) {
cellStyle.setAlignment(XSSFCellStyle.ALIGN_LEFT);
} else if (value.equalsIgnoreCase("center")) {
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
} else if (value.equalsIgnoreCase("right")) {
cellStyle.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
} else {
cellStyle.setAlignment(XSSFCellStyle.ALIGN_LEFT);
}
} else if ("valign".equalsIgnoreCase(key)) {
// 垂直对齐
if (value.equalsIgnoreCase("top")) {
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP);
} else if (value.equalsIgnoreCase("center")) {
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
} else if (value.equalsIgnoreCase("bottom")) {
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_BOTTOM);
} else {
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
}
} else if ("border".equalsIgnoreCase(key)) {
// 是否加粗
if ("y".equalsIgnoreCase(value)) {
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
}
} else if ("bgcolor".equalsIgnoreCase(key)) {
// 单元格背景颜色
cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
value = value.toUpperCase();
Object color = colorMap.get(value);
if (color != null) {
cellStyle.setFillForegroundColor(Short.parseShort(color.toString()));
} else {
cellStyle.setFillForegroundColor(HSSFColor.WHITE.index);
}
} else if ("bgcolorn".equalsIgnoreCase(key)) {
// 单元格背景颜色-不需要转换颜色
cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(Short.parseShort(value));
} else if ("readonly".equalsIgnoreCase(key)) {
// 设置单元格是否可编辑
if ("y".equals(value)) {
cellStyle.setLocked(true);
} else {
cellStyle.setLocked(false);
}
} else if ("size".equalsIgnoreCase(key)) {
// 单元格字体大小
font.setFontHeightInPoints(Short.parseShort(value));
} else if ("font_color".equalsIgnoreCase(key)) {
// 单元格字体颜色
font.setColor(Short.parseShort(value));
} else if ("font_name".equalsIgnoreCase(key)) {
// 单元格字体
font.setFontName(value);
} else if ("bordertop".equalsIgnoreCase(key)) {
// 边框上
cellStyle.setBorderTop(Short.parseShort(value));
} else if ("borderbottom".equalsIgnoreCase(key)) {
// 边框下
cellStyle.setBorderBottom(Short.parseShort(value));
} else if ("borderleft".equalsIgnoreCase(key)) {
// 边框左
cellStyle.setBorderLeft(Short.parseShort(value));
} else if ("borderright".equalsIgnoreCase(key)) {
// 边框右
cellStyle.setBorderRight(Short.parseShort(value));
} else if ("wrap".equalsIgnoreCase(key)) {
// 单元格自动换行
if ("n".equalsIgnoreCase(value)) {
cellStyle.setWrapText(false);
} else {
cellStyle.setWrapText(true);
}
}
}
cellStyle.setFont(font);
styleMap.put(styleStr, cellStyle);
return cellStyle;
}
private void processPicture(Sheet workSheet, byte[] btPic, int picType, int startRow,
int endRow, short startCol, short endCol) {
Drawing patriarch = workSheet.createDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 512, 255, (short) startCol, startRow,
(short) endCol, endRow);
patriarch.createPicture(anchor, workbook.addPicture(btPic, picType));
}
/**
* 初始化默认样式
*/
private void initStyle() {
CellStyle defaultStyle = workbook.createCellStyle();
Font defaultFont = workbook.createFont();
defaultStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
defaultStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
defaultStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
defaultStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
defaultStyle.setWrapText(true);
defaultFont.setFontHeightInPoints((short) DEFAULT_FONT_SIZE);
defaultStyle.setFont(defaultFont);
styleMap.put(DEFAULT_STYLE, defaultStyle);
CellStyle defaultHeadStyle = workbook.createCellStyle();
Font defaultHeadFont = workbook.createFont();
defaultHeadStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
defaultHeadStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
defaultHeadStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
defaultHeadStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
defaultHeadStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
defaultHeadStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
defaultHeadStyle.setFillPattern((short) 1);
defaultHeadStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
defaultHeadStyle.setLocked(true);
defaultHeadStyle.setWrapText(true);
defaultHeadFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
defaultHeadFont.setFontHeightInPoints((short) DEFAULT_FONT_HEADSIZE);
defaultHeadStyle.setFont(defaultHeadFont);
styleMap.put(DEFAULT_HEAD_STYLE, defaultHeadStyle);
}
public static void main(String[] args) throws Exception {
GenerateXSSFExcel g = new GenerateXSSFExcel();
SheetBean sb = new SheetBean();
for(int n=0;n<2;n++){
sb.setIndex(n);
sb.setName("huge test"+n);
List<RowBean> rList = new ArrayList<RowBean>();
for(int i=0;i<100001;i++){
RowBean rb = new RowBean();
List<CellBean> cList = new ArrayList<CellBean>();
for(int j=0;j<4;j++){
CellBean cb = new CellBean();
cb.setValue(i+" @ "+j);
cList.add(cb);
}
rb.setCellList(cList);
rList.add(rb);
if(i!=0&&i%1000==0){
g.processWorkSheet(sb, rList);
rList.clear();
System.out.println(i);
}
}
}
FileOutputStream os = new FileOutputStream(new File("d:/huge2.xlsx"));
g.write(os);
os.flush();
}
}自定义实体类
import java.util.List;
/**
*@author xiliang.xiao
*@date 2015年2月6日 上午9:37:26
*
**/
public class SheetBean {
private int index;//sheet 位置
private boolean readonly=false;//是否可编辑
private boolean displaygridlines=true;//是否显示表格
private String name;//sheet名字
private List<RowBean> rowList;
public int getIndex() {
return index;
}
public void setIndex(int index) {
this.index = index;
}
public boolean isReadonly() {
return readonly;
}
public void setReadonly(boolean readonly) {
this.readonly = readonly;
}
public boolean isDisplaygridlines() {
return displaygridlines;
}
public void setDisplaygridlines(boolean displaygridlines) {
this.displaygridlines = displaygridlines;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public List<RowBean> getRowList() {
return rowList;
}
public void setRowList(List<RowBean> rowList) {
this.rowList = rowList;
}
}import java.util.List;
/**
*@author xiliang.xiao
*@date 2015年2月6日 上午9:11:59
*
**/
public class RowBean {
private int height;
private String align;//水平位置 left,right,center
private String valign;//垂直位置 top,bottom,center
private String fontColor;//字体颜色
private boolean border=false;//字体加粗
private String fontName;//字体
private String bgcolor;//背景颜色
private String bgcolorn;//背景颜色,比bgcolor优先
private String size;//字体 大小
private String style;//单元格样式
private List<CellBean> cellList;
public int getHeight() {
return height;
}
public void setHeight(int height) {
this.height = height;
}
public List<CellBean> getCellList() {
return cellList;
}
public void setCellList(List<CellBean> cellList) {
this.cellList = cellList;
}
public String getAlign() {
return align;
}
public void setAlign(String align) {
this.align = align;
}
public String getValign() {
return valign;
}
public void setValign(String valign) {
this.valign = valign;
}
public String getFontColor() {
return fontColor;
}
public void setFontColor(String fontColor) {
this.fontColor = fontColor;
}
public boolean isBorder() {
return border;
}
public void setBorder(boolean border) {
this.border = border;
}
public String getFontName() {
return fontName;
}
public void setFontName(String fontName) {
this.fontName = fontName;
}
public String getBgcolor() {
return bgcolor;
}
public void setBgcolor(String bgcolor) {
this.bgcolor = bgcolor;
}
public String getSize() {
return size;
}
public void setSize(String size) {
this.size = size;
}
public String getStyle() {
return style;
}
public void setStyle(String style) {
this.style = style;
}
public String getBgcolorn() {
return bgcolorn;
}
public void setBgcolorn(String bgcolorn) {
this.bgcolorn = bgcolorn;
}
}/**
*@author xiliang.xiao
*@date 2015年2月6日 上午9:11:50
*
**/
public class CellBean {
private int width;//宽度*36.55
private int widthn;//宽度，比width优先
private Object value;
private String formula;//公式
private String merge;//合并单元格 由4个数字,号分割fromRow, fromCol, toRow, toCol
private String align;//水平位置 left,right,center
private String valign;//垂直位置 top,bottom,center
private boolean border=false;//字体加粗
private String fontColor;//字体颜色
private String fontName;//字体
private String bgcolor;//背景颜色
private String bgcolorn;//背景颜色,比bgcolor优先
private boolean readonly=true;//是否可编辑
private String bordertop;//列属性-边框上CellStyle.BORDER*
private String borderleft;
private String borderbottom;
private String borderright;
private String size;//字体 大小
private boolean hidden=false;//是否隐藏
private boolean wrap=true;//是否换行
private String style;//单元格样式
public CellBean() {
super();
}
public CellBean(Object value) {
super();
this.value = value;
}
public CellBean(int width, Object value) {
super();
this.width = width;
this.value = value;
}
public CellBean(Object value, String merge) {
super();
this.value = value;
this.merge = merge;
}
public int getWidth() {
return width;
}
public void setWidth(int width) {
this.width = width;
}
public int getWidthn() {
return widthn;
}
public void setWidthn(int widthn) {
this.widthn = widthn;
}
public Object getValue() {
return value;
}
public void setValue(Object value) {
this.value = value;
}
public String getFormula() {
return formula;
}
public void setFormula(String formula) {
this.formula = formula;
}
public String getMerge() {
return merge;
}
public void setMerge(String merge) {
this.merge = merge;
}
public String getAlign() {
return align;
}
public void setAlign(String align) {
this.align = align;
}
public String getValign() {
return valign;
}
public void setValign(String valign) {
this.valign = valign;
}
public boolean isBorder() {
return border;
}
public void setBorder(boolean border) {
this.border = border;
}
public String getFontColor() {
return fontColor;
}
public void setFontColor(String fontColor) {
this.fontColor = fontColor;
}
public String getFontName() {
return fontName;
}
public void setFontName(String fontName) {
this.fontName = fontName;
}
public String getBgcolor() {
return bgcolor;
}
public void setBgcolor(String bgcolor) {
this.bgcolor = bgcolor;
}
public boolean isReadonly() {
return readonly;
}
public void setReadonly(boolean readonly) {
this.readonly = readonly;
}
public String getBordertop() {
return bordertop;
}
public void setBordertop(String bordertop) {
this.bordertop = bordertop;
}
public String getBorderleft() {
return borderleft;
}
public void setBorderleft(String borderleft) {
this.borderleft = borderleft;
}
public String getBorderbottom() {
return borderbottom;
}
public void setBorderbottom(String borderbottom) {
this.borderbottom = borderbottom;
}
public String getBorderright() {
return borderright;
}
public void setBorderright(String borderright) {
this.borderright = borderright;
}
public String getSize() {
return size;
}
public void setSize(String size) {
this.size = size;
}
public boolean isHidden() {
return hidden;
}
public void setHidden(boolean hidden) {
this.hidden = hidden;
}
public boolean isWrap() {
return wrap;
}
public void setWrap(boolean wrap) {
this.wrap = wrap;
}
public String getStyle() {
return style;
}
public void setStyle(String style) {
this.style = style;
}
public String getBgcolorn() {
return bgcolorn;
}
public void setBgcolorn(String bgcolorn) {
this.bgcolorn = bgcolorn;
}
public CellBean(int width, int widthn, Object value, String formula,
String merge, String align, String valign, boolean border,
String fontColor, String fontName, String bgcolor, String bgcolorn,
boolean readonly, String bordertop, String borderleft,
String borderbottom, String borderright, String size,
boolean hidden, boolean wrap, String style) {
super();
this.width = width;
this.widthn = widthn;
this.value = value;
this.formula = formula;
this.merge = merge;
this.align = align;
this.valign = valign;
this.border = border;
this.fontColor = fontColor;
this.fontName = fontName;
this.bgcolor = bgcolor;
this.bgcolorn = bgcolorn;
this.readonly = readonly;
this.bordertop = bordertop;
this.borderleft = borderleft;
this.borderbottom = borderbottom;
this.borderright = borderright;
this.size = size;
this.hidden = hidden;
this.wrap = wrap;
this.style = style;
}
/**
* @param width
* @param value
* @param merge
*/
public CellBean(int width, Object value, String merge) {
super();
this.width = width;
this.value = value;
this.merge = merge;
}
/**
* @param value
* @param border
*/
public CellBean(Object value, boolean border) {
super();
this.value = value;
this.border = border;
}
}
1591
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
