private Log logger = LogFactory.getLog(this.getClass());
public ActionForward percentageExcel(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response)
throws Exception
{
//查询数据存到list中
List rtnList = new ArrayList();
Object[] objpercentage;
for(Iterator iterator1 = list.iterator(); iterator1.hasNext(); ) {
//完成率数组
objpercentage = new Object[6];
SysOrgElement element=new SysOrgElement();
objs=(Object[]) iterator1.next();
element=(SysOrgElement) objs[0];
objpercentage[0] = element.getFdName();
objpercentage[1] = objs[1];
objpercentage[2] = objs[2];
String fdid=element.getFdId();
objpercentage[3]=Integer.valueOf(objs[3].toString())-Integer.valueOf(objs[2].toString());
objpercentage[4]="100%";
DecimalFormat df = new DecimalFormat("0.00");
String f=df.format(Float.parseFloat(objs[2].toString())/Integer.valueOf(objs[3].toString())*100);
objpercentage[5]=f+"%";
rtnList.add(objpercentage);
}
// excel工作簿
HSSFWorkbook wb = new HSSFWorkbook();
// sheet页
HSSFSheet sheetHSSF = wb.createSheet("培训计划完成率");
//合并表头
sheetHSSF.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
//创建第一行（也可以称为表头）
HSSFRow row4 = null;
row4 = sheetHSSF.createRow(0);
//给表头第一行一次创建单元格
HSSFCell cellzero4= row4.createCell((int) 0);
cellzero4.setCellValue("表.数据查询情况");
//存标题
cellzero4 = row4.createCell( (int) 1);
cellzero4.setCellValue("");
cellzero4 = row4.createCell( (int) 2);
cellzero4.setCellValue("");
cellzero4 = row4.createCell( (int) 3);
cellzero4.setCellValue("");
cellzero4 = row4.createCell( (int) 4);
cellzero4.setCellValue("");
cellzero4 = row4.createCell( (int) 5);
cellzero4.setCellValue("");
row4 = sheetHSSF.createRow(1);
HSSFCell cell4= row4.createCell((int) 0);
cell4.setCellValue("一级机关");
cell4 = row4.createCell((int)1);
cell4.setCellValue("二级机关");
cell4 = row4.createCell((int)2);
cell4.setCellValue("部门领导");
cell4 = row4.createCell((int)3);
cell4.setCellValue("查询数量");
cell4 = row4.createCell((int)4);
cell4.setCellValue("查询数量4");
cell4 = row4.createCell((int)5);
cell4.setCellValue("查询数量5");
//向单元格里填充数据
for (int i = 0; i < rtnList.size(); i++) {
objpercentage = new Object[6];
objpercentage=(Object[]) rtnList.get(i);
row4 = sheetHSSF.createRow(i + 2);
cell4=row4.createCell(0);
cell4.setCellValue(objpercentage[0].toString());
cell4=row4.createCell(1);
cell4.setCellValue(objpercentage[1].toString());
cell4=row4.createCell(2);
cell4.setCellValue(objpercentage[2].toString());
cell4=row4.createCell(3);
cell4.setCellValue(objpercentage[3].toString());
cell4=row4.createCell(4);
cell4.setCellValue(objpercentage[4].toString());
cell4=row4.createCell(5);
cell4.setCellValue(objpercentage[5].toString());
}
if(sheetHSSF.getLastRowNum()>2){//获取最后一行行标，比行数小 满足合并的条件，3是数据行的开始，0 1 2行为表头
HSSFRow row_1 = sheetHSSF.getRow(2);
HSSFCell cell_0 = row_1.getCell(0);
HSSFCell cell_1 = row_1.getCell(1);
HSSFCell cell_2 = row_1.getCell(2);
HSSFCell cell_3 = row_1.getCell(3);
HSSFCell cell_4 = row_1.getCell(4);
HSSFCell cell_5 = row_1.getCell(5);
String leader_name = cell_0.getStringCellValue();
String vice_leader_name = cell_1.getStringCellValue();
String group1_name = cell_2.getStringCellValue();
String group2_name = cell_3.getStringCellValue();
String group3_name = cell_4.getStringCellValue();
String group4_name = cell_5.getStringCellValue();
//判断是否和第一列数据相同 相同单元格则设置为空
for(int i=3;i<=sheetHSSF.getLastRowNum();i++){//除第一个外，循环将内容相同的单元格设为""，这里体现出为什么原数据要有序
HSSFRow rows = sheetHSSF.getRow(i);
//此处表示对4个单元格进行内容相同合并处理
HSSFCell cells_0 = rows.getCell(0);
HSSFCell cells_1 = rows.getCell(1);
HSSFCell cells_2 = rows.getCell(2);
HSSFCell cells_3 = rows.getCell(3);
HSSFCell cells_4 = rows.getCell(4);
HSSFCell cells_5 = rows.getCell(5);
if(leader_name.equals(cells_0.getStringCellValue())){
cells_0.setCellValue("");
}else{
leader_name = cells_0.getStringCellValue();
}
if(vice_leader_name.equals(cells_1.getStringCellValue())){
cells_1.setCellValue("");
}else{
vice_leader_name = cells_1.getStringCellValue();
}
if(group1_name.equals(cells_2.getStringCellValue())){
cells_2.setCellValue("");
}else{
group1_name = cells_2.getStringCellValue();
}
if(group2_name.equals(cells_3.getStringCellValue())){
cells_3.setCellValue("");
}else{
group2_name = cells_3.getStringCellValue();
}
if(group3_name.equals(cells_4.getStringCellValue())){
cells_4.setCellValue("");
}else{
group3_name = cells_4.getStringCellValue();
}
if(group4_name.equals(cells_5.getStringCellValue())){
cells_5.setCellValue("");
}else{
group4_name = cells_5.getStringCellValue();
}
}
}
//将各列数据中为空的单元格和不为空的合并 此处只判断了一列数据
int sk;
if(sheetHSSF.getLastRowNum()>2){//将为空的单元格与之前不为空的合并
/*sk=1;
for(int i=3;i<=sheetHSSF.getLastRowNum();i++){
HSSFRow rows = sheetHSSF.getRow(i);
HSSFCell cell_0 = rows.getCell(0);
if(cell_0.getStringCellValue() == ""){
if(i==sheetHSSF.getLastRowNum()){
sheetHSSF.addMergedRegion(new CellRangeAddress(sk, i, 0, 0));
}
}else{
if(sk != i-1){
sheetHSSF.addMergedRegion(new CellRangeAddress(sk, i-1, 0, 0));//起始行号，终止行号， 起始列号，终止列号
}
sk =i;
}
}*/
/*sk=1;
for(int i=3;i<=sheetHSSF.getLastRowNum();i++){
HSSFRow rows = sheetHSSF.getRow(i);
HSSFCell cell_1 = rows.getCell(1);
if(cell_1.getStringCellValue() == ""){
if(i==sheetHSSF.getLastRowNum()){
sheetHSSF.addMergedRegion(new CellRangeAddress(sk, i, 1, 1));
}
}else{
if(sk != i-1){
sheetHSSF.addMergedRegion(new CellRangeAddress(sk, i-1, 1, 1));//起始行号，终止行号， 起始列号，终止列号
}
sk =i;
}
}*/
/*sk=1;
for(int i=3;i<=sheetHSSF.getLastRowNum();i++){
HSSFRow rows = sheetHSSF.getRow(i);
HSSFCell cell_2 = rows.getCell(2);
if(cell_2.getStringCellValue() == ""){
if(i==sheetHSSF.getLastRowNum()){
sheetHSSF.addMergedRegion(new CellRangeAddress(sk, i, 2, 2));
}
}else{
if(sk != i-1){
sheetHSSF.addMergedRegion(new CellRangeAddress(sk, i-1, 2, 2));//起始行号，终止行号， 起始列号，终止列号
}
sk =i;
}
}*/
/*sk=1;
for(int i=3;i<=sheetHSSF.getLastRowNum();i++){
HSSFRow rows = sheetHSSF.getRow(i);
HSSFCell cell_3 = rows.getCell(3);
if(cell_3.getStringCellValue() == ""){
if(i==sheetHSSF.getLastRowNum()){
sheetHSSF.addMergedRegion(new CellRangeAddress(sk, i, 3, 3));
}
}else{
if(sk != i-1){
sheetHSSF.addMergedRegion(new CellRangeAddress(sk, i-1, 3, 3));//起始行号，终止行号， 起始列号，终止列号
}
sk =i;
}
}*/
sk=2;
for(int i=3;i<=sheetHSSF.getLastRowNum();i++){
HSSFRow rows = sheetHSSF.getRow(i);
HSSFCell cell_4 = rows.getCell(4);
if(cell_4.getStringCellValue() == ""){
if(i==sheetHSSF.getLastRowNum()){
sheetHSSF.addMergedRegion(new CellRangeAddress(sk, i, 4, 4));
}
}else{
if(sk != i-1){
sheetHSSF.addMergedRegion(new CellRangeAddress(sk, i-1, 4, 4));//起始行号，终止行号， 起始列号，终止列号
}
sk =i;
}
}
/*sk=1;
for(int i=3;i<=sheetHSSF.getLastRowNum();i++){
HSSFRow rows = sheetHSSF.getRow(i);
HSSFCell cell_5 = rows.getCell(5);
if(cell_5.getStringCellValue() == ""){
if(i==sheetHSSF.getLastRowNum()){
sheetHSSF.addMergedRegion(new CellRangeAddress(sk, i, 5, 5));
}
}else{
if(sk != i-1){
sheetHSSF.addMergedRegion(new CellRangeAddress(sk, i-1, 5, 5));//起始行号，终止行号， 起始列号，终止列号
}
sk =i;
}
}*/
}
//导出操作
String userAgent = request.getHeader("User-Agent");
if(userAgent.contains("MSIE") || userAgent.contains("rv") && userAgent.contains("Trident")){
response.reset();
output.output(workbook, response);
}
else{
response.reset();
response.setContentType("application/vnd.ms-excel; charset=UTF-8");
if (wb == null)
return null;
String filename = wb.getSheetName(0);
if (filename == null) {
logger.warn("workbook中未设置filename属性，当前使用缺省值“Noname”。");
filename = "Noname";
}
// safari的情况下，coding的解析类型必须是UTF-8才不会导致导出的名字乱码
filename = new String(filename.getBytes("UTF-8"), "ISO-8859-1") + ".xls";
logger.debug("filename=" + filename);
// 火狐导出excel的时候，如果文件名含有空格，会截断后面的字符串，导致下载下来的文件无法解析
response.addHeader("Content-Disposition", "attachment;filename=\""
+ filename + "\"");
OutputStream outputStream = null;
wb.write(response.getOutputStream());
}
return null;
}
//效果图 简单示列 细节还需要更改
3957
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
