POI 和 excel

谁践踏了优雅 2022-08-26 05:57 101阅读 0赞

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;
    	
    	@Override
    	public 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是1
    		try {
    			//开始的行数
    			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);
    			//数据写入reponse
    			this.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;
    	}
    }