POI 和 excel

package action.app.icp;import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.util.Date;import java.util.Iterator;import java.util.Map;import java.util.Set;import net.sf.json.JSONArray;import net.sf.json.JSONObject;import org.apache.commons.lang.StringUtils;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFDataFormat;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.ss.usermodel.RichTextString;import action.app.icp.dao.itemExpDao;import app.icp.desk.DeskDao;import app.icp.statistics.StatisticsDao;import com.inspur.base.BaseAction;import com.inspur.bean.DataSet;import com.inspur.bean.ParameterSet;import com.inspur.util.PathUtil;import com.inspur.util.Tools;import com.inspur.util.URLEncode;public class declareStatisticExpCity extends BaseAction {//excel文件样式表private HSSFCellStyle cellLeftStyle;private HSSFCellStyle cellRightStyle;private HSSFCellStyle cellCenterStyle;private HSSFCellStyle cellPerStyle;@Overridepublic boolean handler(Map<String, Object> data) {Map param = this.getPostData();//等同页面传递参数ParameterSet pSet = new ParameterSet();String type = (String) param.get("type");//xls文件的目录String xmlPath = PathUtil.getTempPath() + Tools.getUUID32() + ".xls";//模板的位置String itemTemplatePath = PathUtil.getWebPath() + "public" + File.separator + "template" + File.separator;int len;//列数int column;//行数itemTemplatePath += "city.xls";len=19;column = 0;//为什么column是1try {//开始的行数int start = 4;HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(itemTemplatePath));HSSFSheet sheet0 = wb.getSheetAt(0); // 第一个工作表//设计表格的样式cellLeftStyle = wb.createCellStyle();cellLeftStyle.setBorderTop((short) 1);cellLeftStyle.setBorderLeft((short) 1);cellLeftStyle.setBorderRight((short) 1);cellLeftStyle.setBorderBottom((short) 1);cellLeftStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);cellRightStyle = wb.createCellStyle();cellRightStyle.setBorderTop((short) 1);cellRightStyle.setBorderLeft((short) 1);cellRightStyle.setBorderRight((short) 1);cellRightStyle.setBorderBottom((short) 1);cellRightStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);cellCenterStyle = wb.createCellStyle();cellCenterStyle.setBorderTop((short) 1);cellCenterStyle.setBorderLeft((short) 1);cellCenterStyle.setBorderRight((short) 1);cellCenterStyle.setBorderBottom((short) 1);cellCenterStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);cellPerStyle = wb.createCellStyle();cellPerStyle.setBorderTop((short) 1);cellPerStyle.setBorderLeft((short) 1);cellPerStyle.setBorderRight((short) 1);cellPerStyle.setBorderBottom((short) 1);cellPerStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);cellPerStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));// 写数据到excel表DataSet items1 = StatisticsDao.getInstance().getDeptStatisticGzs(pSet);JSONArray jaItems = items1.getJAData();int ilen = jaItems.size();JSONObject io = null;for (int i = 0; i < ilen; i++) {io = jaItems.getJSONObject(i);// 申请材料int rowIndex = start + i;Float sub1;sub1=Float.parseFloat(io.getString("NARU_SX"));int num10,num12,num14;num10=Integer.parseInt(io.getString("DECLARE_SERVICE_LEVE_1"));num12=Integer.parseInt(io.getString("DECLARE_SERVICE_LEVE_2"));num14=Integer.parseInt(io.getString("DECLARE_SERVICE_LEVE_3"));String per10=formatSt(sub1, num10);String per12=formatSt(sub1, num12);String per14=formatSt(sub1, num14);HSSFRow tempRow = sheet0.createRow(rowIndex);setCellValue(tempRow, 0 + column, io.getString("NAME"),false,"string");setCellValue(tempRow, 1 + column, io.getString("SX_NUM"), false, "number");setCellValue(tempRow, 2 + column, io.getString("GS_SX_NUM"), false, "number");setCellValue(tempRow, 3 + column, io.getString("SX"), false, "number");setCellValue(tempRow, 4 + column, io.getString("XZXK"), false, "number");setCellValue(tempRow, 5 + column, io.getString("FXZXK"), false, "number");setCellValue(tempRow, 6 + column, io.getString("GS_XZXK"), false, "number");setCellValue(tempRow, 7 + column, io.getString("SX_SECRET"), false, "number");setCellValue(tempRow, 8 + column, io.getString("NARU_SX"), false, "number");setCellValue(tempRow, 9 + column, io.getString("DECLARE_SERVICE_LEVE_1"), false, "number");setCellValue(tempRow, 10 + column, per10, true, "number");setCellValue(tempRow, 11 + column, io.getString("DECLARE_SERVICE_LEVE_2"), false, "number");setCellValue(tempRow, 12 + column, per12, true, "number");setCellValue(tempRow, 13 + column, io.getString("DECLARE_SERVICE_LEVE_3"), false, "number");setCellValue(tempRow, 14 + column, per14, true, "number");setCellValue(tempRow, 15 + column, io.getString("SERVICE_SX"),false,"number");setCellValue(tempRow, 16 + column, io.getString("GS_SERVICE_SX"),false,"number");setCellValue(tempRow, 17 + column, io.getString("SUIT_ONLINE_SERVICE"),false,"number");setCellValue(tempRow, 18 + column, io.getString("SUIT_ONLINE_PER"),true,"number");}FileOutputStream fileOut = new FileOutputStream(xmlPath);wb.write(fileOut);fileOut.close();File f = new File(xmlPath);//数据写入reponsethis.write(Tools.getFile(f));this.setContentType("application/vnd.ms-excel");this.setHeader("Content-Disposition", "attachment; filename="+URLEncode.encodeURL("statistics.xls"));f.delete();} catch (Exception ex) {ex.printStackTrace();}return false;}/* * row 行数 * int 列 * value 值 * isPer 是否是百分率 * type 数据类型 */public void setCellValue(HSSFRow row,int column,String value,boolean isPer,String type){HSSFCell cell = null;if (StringUtils.isEmpty(value) || "0".equals(value)) {cell = row.createCell(column,HSSFCell.CELL_TYPE_STRING);cell.setCellValue("-");cell.setCellStyle(cellCenterStyle);}else{if (isPer) {cell = row.createCell(column,HSSFCell.CELL_TYPE_STRING);cell.setCellStyle(cellCenterStyle);cell.setCellValue(" "+value+"%");}else{if ("string".equals(type)) {cell = row.createCell(column,HSSFCell.CELL_TYPE_STRING);cell.setCellStyle(cellLeftStyle);cell.setCellValue(value);}else if("number".equals(type)){cell = row.createCell(column,HSSFCell.CELL_TYPE_NUMERIC);cell.setCellStyle(cellCenterStyle);cell.setCellValue(Integer.parseInt(value));}}}}public String formatSt(Float sub,int num){String per;if(sub !=0 && num !=0){per = String.valueOf(num/sub*100);int len = per.indexOf('.');if("0".endsWith(per.substring(len+1, len+2))){per = per.substring(0,len);}else{per = per.substring(0, len+2);}}else{per="0";}return per;}}