java poi excel输出

package com.gzpykj.adrhis.project.excel;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;




import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.xmlbeans.impl.xb.xsdschema.Public;
import org.eaglesoft.json.JSON;
import org.eaglesoft.json.JSONArray;
import org.eaglesoft.json.JSONObject;
import org.eaglesoft.json.JSONSerializer;
import org.eaglesoft.json.JSONString;
import org.eaglesoft.json.util.JSONUtils;
import org.eaglesoft.mvc.BaseAction;
import org.eaglesoft.util.StringUtil;


import com.sun.org.apache.bcel.internal.generic.Type;
 
public class ExcelOutPutAction extends BaseAction{
public static final int[] UNIT_OFFSET_MAP = new int[] { 0, 36, 73, 109, 146, 182, 219 };
public static void writeExcel03() throws IOException{
//创建工作簿
HSSFWorkbook workBook = new HSSFWorkbook();
//创建工作表  工作表的名字叫helloWorld
HSSFSheet sheet = workBook.createSheet("helloWorld");
//创建行,第3行
HSSFRow row = sheet.createRow(2);
//创建单元格，操作第三行第三列
HSSFCell cell = row.createCell(2, Cell.CELL_TYPE_STRING);
cell.setCellValue("helloWorld");
OutputStream out =new FileOutputStream("C:\\Users\\Administrator\\Desktop\\测试.xls");
//workBook.write((new File(\"d:\\poi\\测试.xls\")))
workBook.write(out);
workBook.cloneSheet(0);//最后记得关闭工作簿
}
public static void writeExcel07() throws IOException{
//创建工作簿
XSSFWorkbook workBook = new XSSFWorkbook();
//创建工作表
XSSFSheet sheet = workBook.createSheet("helloWorld");
//创建行
XSSFRow row = sheet.createRow(2);
//创建单元格，操作第三行第三列
XSSFCell cell = row.createCell(2, Cell.CELL_TYPE_STRING);
cell.setCellValue("helloWorld");

FileOutputStream outputStream = new FileOutputStream(new File("C:\\Users\\Administrator\\Desktop\\测试.xlsx"));
workBook.write(outputStream);

workBook.cloneSheet(0);//最后记得关闭工作簿
}
public static void main(String[] args) throws IOException {
// exp();
}
public   void execute() throws IOException{
String jsonDate = context.getParameter("json");
String filename = context.getParameter("filename");
String sheetname = context.getParameter("sheetname");
/*String sheetname ="抽取报告规范性情况";
String filename ="抽取报告规范性情况.xls";
String jsonDate ="[{\"tr\":[{\"td\":[{\"type\":\"th\",\"val\":\"项目\",\"rowSpan\":\"2\",\"colSpan\":\"2\"}]},{\"td\":[{\"type\":\"th\",\"val\":\"正确率\",\"rowSpan\":0,\"colSpan\":\"4\"}]},{\"td\":[{\"type\":\"th\",\"val\":\"合计\",\"rowSpan\":\"2\",\"colSpan\":0}]}]},{\"tr\":[{\"td\":[{\"type\":\"th\",\"val\":\"严重\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"th\",\"val\":\"死亡\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"th\",\"val\":\"新的一般\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"th\",\"val\":\"一般\",\"rowSpan\":0,\"colSpan\":0}]}]},{\"tr\":[{\"td\":[{\"type\":\"td\",\"val\":\"报告类型\",\"rowSpan\":0,\"colSpan\":\"2\"}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]}]},{\"tr\":[{\"td\":[{\"type\":\"td\",\"val\":\"报告时限\",\"rowSpan\":0,\"colSpan\":\"2\"}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]}]},{\"tr\":[{\"td\":[{\"type\":\"td\",\"val\":\"ADR名称\",\"rowSpan\":0,\"colSpan\":\"2\"}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]}]},{\"tr\":[{\"td\":[{\"type\":\"td\",\"val\":\"药品信息\",\"rowSpan\":\"6\",\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"通用名称\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]}]},{\"tr\":[{\"td\":[{\"type\":\"td\",\"val\":\"剂型\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]}]},{\"tr\":[{\"td\":[{\"type\":\"td\",\"val\":\"生产厂家\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]}]},{\"tr\":[{\"td\":[{\"type\":\"td\",\"val\":\"批号\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]}]},{\"tr\":[{\"td\":[{\"type\":\"td\",\"val\":\"用法用量\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]}]},{\"tr\":[{\"td\":[{\"type\":\"td\",\"val\":\"用药原因\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]}]},{\"tr\":[{\"td\":[{\"type\":\"td\",\"val\":\"原患疾病\",\"rowSpan\":0,\"colSpan\":\"2\"}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]}]},{\"tr\":[{\"td\":[{\"type\":\"td\",\"val\":\"关联性评价\",\"rowSpan\":0,\"colSpan\":\"2\"}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]}]},{\"tr\":[{\"td\":[{\"type\":\"td\",\"val\":\"ADR分析\",\"rowSpan\":0,\"colSpan\":\"2\"}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]},{\"td\":[{\"type\":\"td\",\"val\":\"0.00%\",\"rowSpan\":0,\"colSpan\":0}]}]}]";
*///1.创建工作簿
HSSFWorkbook workBook = new HSSFWorkbook();
//2.创建工作表
HSSFSheet sheet = workBook.createSheet(sheetname);
//解析json转为table
JSONArray table =JSONArray.fromObject(jsonDate);
JSONArray inCosOrRow= new JSONArray();
JSONArray inCosOrRowAddress= new JSONArray();
HSSFCellStyle style = workBook.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBorderTop(CellStyle.BORDER_THIN);
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setBorderRight(CellStyle.BORDER_THIN);
int  cellLenght = Integer.parseInt(context.getParameter("cellLenght"));
int cellWidth =Integer.parseInt(context.getParameter("cellWidth"));
for(int o=0;o<cellLenght;o++){
sheet.setColumnWidth(o,pixelWidth(cellWidth));
}

for(int i=0;i<table.size();i++){
JSONObject tri = table.getJSONObject(i);
JSONArray tr =tri.getJSONArray("tr");
int targetrow=0,targetcos=0,lastrow=0,lastcol=0;
HSSFRow row ;
if(sheet.getRow(i)==null){
row = sheet.createRow(i);
}else{
row = sheet.getRow(i);
}
for(int j=0;j<tr.size();j++){
int rowSpan=Integer.parseInt(""+tr.getJSONObject(j).getJSONArray("td").getJSONObject(0).get("rowSpan"));
int colSpan=Integer.parseInt(""+tr.getJSONObject(j).getJSONArray("td").getJSONObject(0).get("colSpan"));
if(rowSpan!=0||colSpan!=0){
//创建合并单元格对象
if(rowSpan>0){
targetrow=i+rowSpan-1;
}else if(targetrow==0&&rowSpan==0){
targetrow=i;
}else{
targetrow=0;
}
if( colSpan>1){
targetcos=lastcol+colSpan-1;
} else{
targetcos=lastcol+colSpan;
}

inCosOrRowAddress.add(inCosOrRowAddress.size(),i+","+ targetrow+"," +lastcol+","+ targetcos);
inCosOrRow=setinCosOrRow(inCosOrRow,lastrow, targetrow, lastcol, targetcos,sheet,style);
int cellNum=getNum(row,lastcol);
HSSFCell cell;
if(row.getCell(cellNum)==null){
cell=row.createCell(cellNum);
}else{
cell=row.getCell(cellNum);
}
cell.setCellStyle(style);
inCosOrRow=addCellValueSet(inCosOrRow,i,cellNum);
cell.setCellType(1);
String cellValue=""+tr.getJSONObject(j).getJSONArray("td").getJSONObject(0).get("val");
cell.setCellValue(cellValue);
inCosOrRow=addCellValueSet(inCosOrRow,i,lastcol);

}else{
int cellNum=getNum(inCosOrRow,i,lastcol);
HSSFCell cell;
if(row.getCell(cellNum)==null){
cell=row.createCell(cellNum);
}else{
cell=row.getCell(cellNum);
}
cell.setCellStyle(style);
inCosOrRow=addCellValueSet(inCosOrRow,i,cellNum);
cell.setCellType(1);
String cellValue=""+tr.getJSONObject(j).getJSONArray("td").getJSONObject(0).get("val");
cell.setCellValue(cellValue);
inCosOrRow=addCellValueSet(inCosOrRow,i,j);
}
lastcol=targetcos+1;
}

}
for(int i=0;i<inCosOrRowAddress.size();i++){
String []cs=inCosOrRowAddress.getString(i).split(",");
int _row=Integer.parseInt(cs[0]);
int _tagger_row=Integer.parseInt(cs[1]);
int _cos=Integer.parseInt(cs[2]);
int _tagger_cos=Integer.parseInt(cs[3]);
CellRangeAddress rangeAddress = new CellRangeAddress(_row,_tagger_row,_cos,_tagger_cos);
sheet.addMergedRegion(rangeAddress);
}

//输出
//FileOutputStream outputStream = new FileOutputStream(new File("C:\\Users\\Administrator\\Desktop\\"+filename));
OutputStream outputStream=context.getFileOutputStream(filename);
workBook.write(outputStream);

outputStream.close();


} 
private static int getNum(JSONArray inCosOrRow, int i, int j) {
if(inCosOrRow.indexOf(i+","+j)<0){
return j;
}else{
return getNum(inCosOrRow,i,j+1);
}

}
private static JSONArray setinCosOrRow(JSONArray inCosOrRow, int lastrow, int targetrow,int lastcol, int targetcos, HSSFSheet sheet, HSSFCellStyle style) {
for(int i=lastrow;i<=targetrow;i++){
for(int j=lastcol;j<=targetcos;j++){
if(inCosOrRow.indexOf(i+","+j)<0){
HSSFRow row ;
if(sheet.getRow(i)==null){
row = sheet.createRow(i);
}else{
row = sheet.getRow(i);
}
HSSFCell cell= row.createCell(j);
cell.setCellStyle(style);
cell.setCellValue("");
inCosOrRow.add(inCosOrRow.size(), i+","+j);
};
}
}
if(targetrow-lastrow==0){
for(int j=lastcol;j<=targetcos;j++){
if(inCosOrRow.indexOf(targetrow+","+j)<0){
inCosOrRow.add(inCosOrRow.size(), targetrow+","+j);
};
}
}
return inCosOrRow;
}
private static JSONArray addCellValueSet(JSONArray inCosOrRow, int row, int cell) {
if(inCosOrRow.indexOf(row+","+cell)<0){
inCosOrRow.add(inCosOrRow.size(), row+","+cell);
};

return inCosOrRow;
}


public static int getNum(HSSFRow row ,int a){
if(org.eaglesoft.util.StringUtil.isEmpty(row.getCell(a).getCellComment())){
return a;
}else{
return getNum(row ,a+1);
}
}
public static short pixelWidth(int pxs) {
        short width = (short) (256 * (pxs / 7));
        width += UNIT_OFFSET_MAP[(pxs % 7)];
        return width;
    }


}