前一阵子客户要求复杂表格,所以参考了网上的一些资料,自己写了一个多功能封装Excel类的方法,可以对于表格的列与行进行合并 ,如图示:
测试数据测试数据 | ||
测试数据测试数据 | 测试数据测试数据 | |
测试数据测试数据(*) | 测试数据测试数据 | |
测试数据测试数据(*) | 测试数据测试数据,测试数据测试数据,测试数据测试数据 | |
测试数据测试数据 | 是 | |
测试数据测试数据/测试数据测试数据 | 无 | |
测试数据测试数据 | 测试数据测试数据 | 测试数据测试数据 |
测试数据测试数据 | 测试数据测试数据 | |
测试数据测试数据 | 测试数据测试数据 | 测试数据测试数据 |
测试数据测试数据 | 测试数据测试数据 | |
测试数据测试数据 | 测试数据测试数据 | |
测试数据测试数据 | 测试数据测试数据 | |
测试数据测试数据 | 测试数据测试数据 | |
测试数据测试数据 | 测试数据测试数据 | |
测试数据测试数据 | 测试数据测试数据 | |
测试数据测试数据 | 测试数据测试数据 | |
测试数据测试数据 | 测试数据测试数据 | |
测试数据测试数据 | 测试数据测试数据 | |
测试数据测试数据 | 这是备注 | |
业务要素 | 描述分项 | 业务描述 |
测试数据测试数据 | 测试数据测试数据 | 测试数据测试数据 |
测试数据测试数据 | 测试数据测试数据 | |
测试数据测试数据 | 测试数据测试数据 | |
套餐名称及编号 | 测试数据测试数据,测试数据测试数据:测试数据测试数据 | |
测试数据测试数据 | 测试数据测试数据,测试数据测试数据:测试数据测试数据 | |
测试数据测试数据 | 测试数据测试数据 | |
测试数据测试数据 | 测试数据测试数据 | |
测试数据测试数据 | 3000.01.01 | |
测试数据测试数据 | 测试数据测试数据测试数据测试数据 | 测试数据测试数据 |
测试数据测试数据 | 测试数据测试数据 | |
测试数据测试数据 | 测试数据测试数据 | |
测试数据测试数据 | 测试数据测试数据 | |
测试数据测试数据 | 测试数据测试数据 | |
测试数据测试数据 | 测试数据测试数据 | |
测试数据测试数据 | 测试数据测试数据 | |
测试数据测试数据 | 测试数据测试数据 |
代码如下:
package cn.indoing.poi.chap3;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
/**
* <p>Title:关于Java中用Poi包写复杂Excel表格代码 </p>
*
* <p>Description: </p>
*
* <p>Copyright: Copyright (c) 2009</p>
*
* <p>Company: </p>
*
* @author virture
* @version 1.0
*/
public class PerFeeToExcel
{
private static int currRows = 0;// 当前所在行
/**
* 创建一行数据
*
* @param st
* @param normalStyle
* @param ruleStr
* @param lettersStr
*/
public void WriteColsCell(HSSFSheet st, HSSFCellStyle normalStyle, HSSFCellStyle colorStyle,int[] colorCol,String ruleStr, String[] letters)
{
String[] temp = ruleStr.split(";");
int[] rules = new int[temp.length];
for (int i = 0; i < temp.length; i++)
{
rules[i] = Integer.parseInt(temp[i]);
}
HSSFRow row = st.createRow(currRows);
int preCols = 0;
int colorInt=0;//当前行颜色
for (int i = 0; i < rules.length; i++)
{
st.addMergedRegion(new Region(currRows, (short) preCols, currRows, (short) (preCols + rules[i] - 1)));
HSSFCell cell = row.createCell((short) preCols);
if (colorCol!=null && colorCol.length>colorInt){
if (colorCol[colorInt]==i){
cell.setCellValue(new HSSFRichTextString(letters[i]));
cell.setCellStyle(colorStyle);
colorInt++;
}else{
cell.setCellValue(new HSSFRichTextString(letters[i]));
cell.setCellStyle(normalStyle);
}
}else{
cell.setCellValue(new HSSFRichTextString(letters[i]));
cell.setCellStyle(normalStyle);
}
for (int j = preCols + 1; j < rules[i]; j++)
{
row.createCell((short) j).setCellStyle(normalStyle);
}
preCols = preCols + rules[i];
}
currRows++;
}
/**
* 增加几列数据,只能对第一列数据有效
*
* @param st
* @param normalStyle
* @param ruleStr
* @param lettersStr
*/
public void WriteRowsCell(HSSFSheet st, HSSFCellStyle normalStyle,HSSFCellStyle colorStyle,int[] colorCol,String ruleStr, String[] letters)
{
String[] temp = ruleStr.split(";");
int[] rules = new int[temp.length];
for (int i = 0; i < temp.length; i++)
{
rules[i] = Integer.parseInt(temp[i]);
}
int curCols = 0;//当前所在列
int curLetters = 0;//当前字符值
int tempRows=0;
int colorInt=0;//当前行颜色
for (int i = 0; i < rules.length; i++)
{
if (rules[i] != -1)
{
if (rules[i]>1) tempRows=rules[i];
st.addMergedRegion(new Region(currRows, (short) curCols, currRows + rules[i] - 1, (short) curCols));
HSSFRow row = st.createRow(currRows);
HSSFCell cell = row.createCell((short) curCols);
if (colorCol!=null && colorCol.length>colorInt){
if (colorCol[colorInt]==curLetters){
cell.setCellValue(new HSSFRichTextString(letters[curLetters++]));
cell.setCellStyle(colorStyle);
colorInt++;
}else{
cell.setCellValue(new HSSFRichTextString(letters[curLetters++]));
cell.setCellStyle(normalStyle);
}
}else{
cell.setCellValue(new HSSFRichTextString(letters[curLetters++]));
cell.setCellStyle(normalStyle);
}
curCols++;
} else
{
currRows++;
if ((tempRows--)>1) curCols = 1;
}
}
}
/**
* @param args
*/
public static void main(String[] args) throws IOException
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet st = wb.createSheet("测试数据测试数据");
// 这里首先创建一个单元格样式对象,设置了四周的边框以及字体可以换行
// 其中的字体换行是用来竖向显示其中的一个单元格的
// 更好的一点儿做法是再做一个单独的单元格样式对象
// 要不然在处理自动列宽的时候可能会有点儿小问题
HSSFCellStyle normalStyle = wb.createCellStyle();
normalStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
normalStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
normalStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
normalStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
normalStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
normalStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
normalStyle.setWrapText(true);
HSSFCellStyle colorStyle = wb.createCellStyle();//背景有颜色
colorStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
colorStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
colorStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
colorStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
colorStyle.setFillPattern(HSSFCellStyle.BRICKS);
colorStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
colorStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
colorStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
colorStyle.setFillBackgroundColor(HSSFColor.LIGHT_YELLOW.index);
PerFeeToExcel pe = new PerFeeToExcel();
pe.WriteColsCell(st, normalStyle, colorStyle,null,"3", new String[]{"测试数据测试数据"});
pe.WriteColsCell(st, colorStyle, colorStyle,null,"2;1", new String[]{"测试数据测试数据","测试数据测试数据"});
pe.WriteColsCell(st, normalStyle, colorStyle,null,"2;1", new String[]{"测试数据测试数据(*)"," 测试数据测试数据"});
pe.WriteColsCell(st, normalStyle, colorStyle,null,"2;1", new String[]{"测试数据测试数据(*)","测试数据测试数据,测试数据测试数据,测试数据测试数据"});
pe.WriteColsCell(st, normalStyle, colorStyle,null,"2;1", new String[]{"测试数据测试数据","是"});
pe.WriteColsCell(st, normalStyle, colorStyle,null,"2;1", new String[]{"测试数据测试数据/测试数据测试数据","无"});
pe.WriteRowsCell(st, normalStyle, colorStyle,null, "2;1;1;-1;1;1;-1", new String[]{"测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据"});
pe.WriteRowsCell(st, normalStyle, colorStyle,null, "2;1;1;-1;1;1;-1", new String[]{"测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据"});
pe.WriteColsCell(st, normalStyle, colorStyle,null,"2;1", new String[]{"测试数据测试数据","测试数据测试数据"});
pe.WriteColsCell(st, normalStyle, colorStyle,null,"2;1", new String[]{"测试数据测试数据","测试数据测试数据"});
pe.WriteColsCell(st, normalStyle, colorStyle,null,"2;1", new String[]{"测试数据测试数据","测试数据测试数据"});
pe.WriteColsCell(st, normalStyle, colorStyle,null,"2;1", new String[]{"测试数据测试数据","测试数据测试数据"});
pe.WriteColsCell(st, normalStyle, colorStyle,null,"2;1", new String[]{"测试数据测试数据","测试数据测试数据"});
pe.WriteColsCell(st, normalStyle, colorStyle,null,"2;1", new String[]{"测试数据测试数据","测试数据测试数据"});
pe.WriteColsCell(st, normalStyle, colorStyle,null,"2;1", new String[]{"测试数据测试数据","测试数据测试数据"});
pe.WriteColsCell(st, normalStyle, colorStyle,null,"2;1", new String[]{"测试数据测试数据","测试数据测试数据"});
pe.WriteColsCell(st, normalStyle, colorStyle,null,"2;1", new String[]{"测试数据测试数据","这是备注"});
pe.WriteColsCell(st, colorStyle, colorStyle,null,"1;1;1", new String[]{"业务要素","描述分项","业务描述"});
pe.WriteRowsCell(st, normalStyle,colorStyle,new int[]{0}, "8;1;1;-1;1;1;-1;1;1;-1;1;1;-1;1;1;-1;1;1;-1;1;1;-1;1;1;-1", new String[]{"测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","套餐名称及编号","测试数据测试数据,测试数据测试数据:测试数据测试数据","测试数据测试数据","测试数据测试数据,测试数据测试数据:测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","3000.01.01"});
pe.WriteRowsCell(st, normalStyle,colorStyle,new int[]{0}, "8;1;1;-1;1;1;-1;1;1;-1;1;1;-1;1;1;-1;1;1;-1;1;1;-1;1;1;-1",new String[]{"测试数据测试数据","测试数据测试数据测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据"});
pe.WriteRowsCell(st, normalStyle,colorStyle,new int[]{0}, "8;1;1;-1;1;1;-1;1;1;-1;1;1;-1;1;1;-1;1;1;-1;1;1;-1",new String[]{"测试数据测试数据","测试数据测试数据。测试数据测试数据","测试数据测试数据","测试数据测试数据(测试数据测试数据)","","测试数据测试数据","月测试数据测试数据","测试数据测试数据","否","测试数据测试数据","否","测试数据测试数据","无","其他","无"});
pe.WriteRowsCell(st, normalStyle,colorStyle,new int[]{0}, "8;1;1;-1;1;1;-1;1;1;-1;1;1;-1;1;1;-1;1;1;-1;1;1;-1",new String[]{"测试数据测试数据","测试数据测试数据","测试数据测试数据","营业前后台能否受理该业务","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据 (测试数据测试数据)","测试数据测试数据,测试数据测试数据","业务服务区编号","","测试数据测试数据","","其他","测试数据测试数据"});
pe.WriteRowsCell(st, normalStyle,colorStyle,new int[]{0}, "2;1;1;-1;1;1;-1",new String[]{"测试数据测试数据","测试数据测试数据,测试数据测试数据","","测试数据测试数据",""});
pe.WriteColsCell(st, normalStyle, colorStyle,new int[]{0},"1;2", new String[]{"测试数据测试数据","测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据"});
pe.WriteColsCell(st, normalStyle, colorStyle,new int[]{0},"1;2", new String[]{"测试数据测试数据",""});
pe.WriteColsCell(st, normalStyle, colorStyle,new int[]{0},"1;2", new String[]{"测试数据测试数据",""});
pe.WriteColsCell(st, normalStyle, colorStyle,new int[]{0},"1;2", new String[]{"测试数据测试数据","测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据"});
pe.WriteColsCell(st, normalStyle, colorStyle,new int[]{0},"1;2", new String[]{"测试数据测试数据",""});
pe.WriteColsCell(st, normalStyle, colorStyle,new int[]{0},"1;2", new String[]{"测试数据测试数据",""});
pe.WriteColsCell(st, normalStyle, colorStyle,new int[]{0},"1;2", new String[]{"测试数据测试数据",""});
pe.WriteColsCell(st, colorStyle, colorStyle,null,"3", new String[]{"测试数据测试数据"});
pe.WriteColsCell(st, colorStyle, colorStyle,null,"1;1;1", new String[]{"测试数据测试数据","测试数据测试数据","测试数据测试数据"});
pe.WriteColsCell(st, normalStyle, colorStyle,null,"1;1;1", new String[]{"测试数据测试数据1","测试数据测试数据1","测试数据测试数据1"});
pe.WriteColsCell(st, normalStyle, colorStyle,null,"1;1;1", new String[]{"测试数据测试数据2","测试数据测试数据2","测试数据测试数据2"});
pe.WriteColsCell(st, normalStyle, colorStyle,null,"1;1;1", new String[]{"测试数据测试数据3","测试数据测试数据3","测试数据测试数据3"});
pe.WriteColsCell(st, normalStyle, colorStyle,new int[]{0},"1;2", new String[]{"备注(注意,不能删除该行!)","测试数据测试数据测试数据测试数据测试数据测试数据"});
OutputStream outputStream = new FileOutputStream("c:/a.xls");
wb.write(outputStream);
outputStream.close();
pe.readExcel("c:/a.xls");
}
public void readExcel(String sFileName)
{
try
{
Workbook book = Workbook.getWorkbook(new File(sFileName));
// 获得第一个工作表对象
Sheet sheet = book.getSheet(0);
// 得到第一列第一行的单元格
int columnum = sheet.getColumns(); // 得到列数
int rownum = sheet.getRows(); // 得到行数
for (int i = 0; i < rownum; i++) // 循环进行读写
{
for (int j = 0; j < columnum; j++)
{
Cell cell1 = sheet.getCell(j, i);
String result = cell1.getContents();
System.out.print("content[" + i + "][" + j + "]=" + result);
System.out.print(" ");
}
System.out.println();
}
book.close();
} catch (BiffException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IndexOutOfBoundsException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}