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;
}
}
POI 和 excel
最新推荐文章于 2024-09-19 12:02:28 发布