excel-java(3)

package com.zbt.six.controller;


import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;


import com.zbt.six.dm.Common;
import com.zbt.six.dm.ComplexExportExcelClient;
import com.zbt.six.dm.ExportExcel;
import com.zbt.six.pojo.DepTypeInfo;
import com.zbt.six.pojo.OptionsInfo;
import com.zbt.six.pojo.UserInfo;
import com.zbt.six.service.DepTypeInfoService;
import com.zbt.six.service.IUserService;
import com.zbt.six.service.OptionsInfoService;
import com.zbt.six.service.OptionsRegistrInfoService;


@Controller
@RequestMapping("/page")
public class ExportDataController {
@Resource
private OptionsRegistrInfoService optionsRegistrInfoService;
@Resource
private OptionsInfoService optionsInfoService;
@Resource
private IUserService userService;
@Resource
private DepTypeInfoService depTypeInfoService;


@ResponseBody
@RequestMapping("/exportExcel.do")
public void exportExcel(@RequestParam("userId") long userId,
@RequestParam("month") String month, HttpServletRequest request,
HttpServletResponse response) {


exportDate(userId, month, response);


}


@RequestMapping("/queryOptionsScoreByOption.do")
public void queryOptionsScoreByOption(@RequestParam("userId") long userId,
@RequestParam("date") String date, HttpServletRequest request,
HttpServletResponse response) {


queryOptionsScore(userId, date);


}


@ResponseBody
@RequestMapping("/showExcel.do")
public StringBuilder showExcel(@RequestParam("userId") long userId,
@RequestParam("month") String month, HttpServletRequest request,
HttpServletResponse response) {
// response.setContentType("application/vnd.ms-excel");
// File file=new File("D:\\浏览器下载\\TEST.xls");
// try {
// FileInputStream in=new FileInputStream(file);
// byte[]buffer=new byte[in.available()];
// in.read(buffer);
// response.getOutputStream().write(buffer);
// response.getOutputStream().flush();
// } catch (IOException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// }


HSSFSheet sheet = null;
StringBuilder lsb = new StringBuilder();
try {
// HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(new
// File(filePath))); // 获得整个Excel
HSSFWorkbook workbook = showDate(userId, month, response);
for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
sheet = workbook.getSheetAt(sheetIndex);
if (sheet != null) {
int firstRowNum = sheet.getFirstRowNum() + 2; // 第一行
int lastRowNum = sheet.getLastRowNum(); // 最后一行
lsb.append("<table class='table table-striped table-bordered table-hover' style='width: 80%;'>");
// lsb.append("<tr align='center'><td colspan='9' style='font-size: 30px'>"
// + sheet.getSheetName() + "</td></tr>");
for (int rowNum = firstRowNum; rowNum <= lastRowNum - 1; rowNum++) {
if (sheet.getRow(rowNum) != null) { // 如果行不为空
HSSFRow row = sheet.getRow(rowNum);
int firstCellNum = row.getFirstCellNum();// 该行的第一个单元格
int lastCellNum = row.getLastCellNum();// 该行的最后一个单元格
int height = (int) (row.getHeight() / 15.625); // 行的高度
lsb.append("<tr height='" + height + "'>");
for (int cellNum = firstCellNum; cellNum <= lastCellNum; cellNum++) { // 循环每一个单元格
HSSFCell cell = row.getCell(cellNum);
if (cell != null) {
lsb.append("<td >" + getCellValue(cell)
+ "</td>");
}
}
}
lsb.append("</tr>");
}


if (sheet.getRow(lastRowNum) != null) { // 如果行不为空
HSSFRow row = sheet.getRow(lastRowNum);
int firstCellNum = row.getFirstCellNum();// 该行的第一个单元格
int lastCellNum = row.getLastCellNum();// 该行的最后一个单元格
int height = (int) (row.getHeight() / 15.625); // 行的高度
lsb.append("<tr height='" + height + "'>");
lsb.append("<td > </td><td > </td>");
for (int cellNum = firstCellNum; cellNum <= lastCellNum; cellNum++) { // 循环每一个单元格
HSSFCell cell = row.getCell(cellNum);
if (cell != null) {
lsb.append("<td >" + getCellValue(cell)
+ "</td>");
}
}
}
lsb.append("</tr>");
lsb.append("</table>");
lsb.append("<br>");
}
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return lsb;
}


private static Object getCellValue(HSSFCell cell) throws IOException {
Object value = "";
if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
value = cell.getRichStringCellValue().toString();
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
value = sdf.format(date);
} else {
double value_temp = (double) cell.getNumericCellValue();
BigDecimal bd = new BigDecimal(value_temp);
BigDecimal bd1 = bd.setScale(3, bd.ROUND_HALF_UP);
value = bd1.doubleValue();
}
}
if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
value = "";
}
return value;
}


/**
* 判断单元格在不在合并单元格范围内，如果是，获取其合并的列数。
* 
* @param sheet
*            工作表
* @param cellRow
*            被判断的单元格的行号
* @param cellCol
*            被判断的单元格的列号
* @return
* @throws IOException
*/
private static int getMergerCellRegionCol(HSSFSheet sheet, int cellRow,
int cellCol) throws IOException {
int retVal = 0;
int sheetMergerCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergerCount; i++) {
CellRangeAddress cra = (CellRangeAddress) sheet.getMergedRegion(i);
int firstRow = cra.getFirstRow(); // 合并单元格CELL起始行
int firstCol = cra.getFirstColumn(); // 合并单元格CELL起始列
int lastRow = cra.getLastRow(); // 合并单元格CELL结束行
int lastCol = cra.getLastColumn(); // 合并单元格CELL结束列
if (cellRow >= firstRow && cellRow <= lastRow) { // 判断该单元格是否是在合并单元格中
if (cellCol >= firstCol && cellCol <= lastCol) {
retVal = lastCol - firstCol + 1; // 得到合并的列数
break;
}
}
}
return retVal;
}


/**
* 判断单元格是否是合并的单格，如果是，获取其合并的行数。
* 
* @param sheet
*            表单
* @param cellRow
*            被判断的单元格的行号
* @param cellCol
*            被判断的单元格的列号
* @return
* @throws IOException
*/
private static int getMergerCellRegionRow(HSSFSheet sheet, int cellRow,
int cellCol) throws IOException {
int retVal = 0;
int sheetMergerCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergerCount; i++) {
CellRangeAddress cra = (CellRangeAddress) sheet.getMergedRegion(i);
int firstRow = cra.getFirstRow(); // 合并单元格CELL起始行
int firstCol = cra.getFirstColumn(); // 合并单元格CELL起始列
int lastRow = cra.getLastRow(); // 合并单元格CELL结束行
int lastCol = cra.getLastColumn(); // 合并单元格CELL结束列
if (cellRow >= firstRow && cellRow <= lastRow) { // 判断该单元格是否是在合并单元格中
if (cellCol >= firstCol && cellCol <= lastCol) {
retVal = lastRow - firstRow + 1; // 得到合并的行数
break;
}
}
}
return retVal;
}


/**
* 单元格背景色转换
* 
* @param hc
* @return
*/
private String convertToStardColor(HSSFColor hc) {
StringBuffer sb = new StringBuffer("");
if (hc != null) {
int a = HSSFColor.AUTOMATIC.index;
int b = hc.getIndex();
if (a == b) {
return null;
}
sb.append("#");
for (int i = 0; i < hc.getTriplet().length; i++) {
String str;
String str_tmp = Integer.toHexString(hc.getTriplet()[i]);
if (str_tmp != null && str_tmp.length() < 2) {
str = "0" + str_tmp;
} else {
str = str_tmp;
}
sb.append(str);
}
}
return sb.toString();
}


/**
* 单元格小平对齐
* 
* @param alignment
* @return
*/
private String convertAlignToHtml(short alignment) {
String align = "left";
switch (alignment) {
case HSSFCellStyle.ALIGN_LEFT:
align = "left";
break;
case HSSFCellStyle.ALIGN_CENTER:
align = "center";
break;
case HSSFCellStyle.ALIGN_RIGHT:
align = "right";
break;
default:
break;
}
return align;
}


/**
* 单元格垂直对齐
* 
* @param verticalAlignment
* @return
*/
private String convertVerticalAlignToHtml(short verticalAlignment) {
String valign = "middle";
switch (verticalAlignment) {
case HSSFCellStyle.VERTICAL_BOTTOM:
valign = "bottom";
break;
case HSSFCellStyle.VERTICAL_CENTER:
valign = "center";
break;
case HSSFCellStyle.VERTICAL_TOP:
valign = "top";
break;
default:
break;
}
return valign;
}


public void queryOptionsScore(long userId, String date) {
UserInfo ui = this.userService.selectByPrimaryKey(userId);
long deptypeid = ui.getDeptypeid();
long foruserid = userId;
String begintime = date;
String endtime = Common.getDayAfter1(date, 1);
List<Map<String, Object>> listMaps = new ArrayList<Map<String, Object>>();
listMaps = this.optionsRegistrInfoService.selecOptionsScoreByOption(
begintime, endtime, deptypeid, foruserid);
List<OptionsInfo> ois = new ArrayList<OptionsInfo>();
ois = getOptionInfos(deptypeid);
for (int i = 0; i < ois.size(); i++) {
System.out.println(ois.get(i).getName()
+ "---"
+ ois.get(i).getScore()
+ ois.get(i).getUnit()
+ "---"
+ (Integer) (listMaps.get(i).get("sumNums") == null ? 0
: listMaps.get(i).get("sumNums")) + "");
}
}


public List<OptionsInfo> getOptionInfos(long deptypeid) {
List<OptionsInfo> ois = this.optionsInfoService
.selectByDepTypeId(deptypeid);
return ois;
}


public Map<Long, Map<Object, Object>> getNum(String begintime,
String endtime, long foruserid, long deptypeid) { // 选项ID，对应每天的选项


Map<Long, Map<Object, Object>> mapNums = new HashMap<Long, Map<Object, Object>>();


List<Long> optionIds = new ArrayList<Long>();
List<OptionsInfo> ois = this.optionsInfoService
.selectByDepTypeId(deptypeid);
for (OptionsInfo oi : ois) {
Map<Object, Object> mapNum = new HashMap<Object, Object>();
// 数量list
List<String> nums = new ArrayList<String>();
optionIds.add(oi.getId());
// 根据选项ID、起止日期、分类Id，查询对应填的数字
// 返回map<时间，数量>的list
List<Map<String, Object>> listMaps = this.optionsRegistrInfoService
.selecOptionsByMonth(begintime, endtime, oi.getId(),
foruserid);
for (Map<String, Object> listMap : listMaps) {
nums.add((Integer) (listMap.get("num") == null ? 0 : listMap
.get("num")) * oi.getScore() + "");


}
mapNum.put("nums", nums);
mapNum.put("OptionName", oi.getName());
mapNum.put("ScoreUnit", oi.getScore() + oi.getUnit());
mapNum.put("Frequency", oi.getFrequency());
mapNums.put(oi.getId(), mapNum);
}
return mapNums;
}


public List<String> getScoresRow(String begintime, String endtime,
Long deptypeid, Long foruserid) {
List<Map<String, Object>> listMaps = new ArrayList<Map<String, Object>>();
listMaps = this.optionsRegistrInfoService.selecOptionsScoresByDay(
begintime, endtime, deptypeid, foruserid);
List<String> listScores = new ArrayList<String>();
for (Map<String, Object> map : listMaps) {
listScores.add((Integer) (map.get("sumNums") == null ? 0 : map
.get("sumNums")) + "");
}
return listScores;
}


public List<String> getScoresCell(String begintime, String endtime,
Long deptypeid, Long foruserid) {
List<Map<String, Object>> listMaps = new ArrayList<Map<String, Object>>();
listMaps = this.optionsRegistrInfoService.selecOptionsScoresByOption(
begintime, endtime, deptypeid, foruserid);
List<String> listScores = new ArrayList<String>();
for (Map<String, Object> map : listMaps) {
listScores.add((Integer) (map.get("sumNums") == null ? 0 : map
.get("sumNums")) + "");
}
return listScores;
}


public String getTotleScores(String begintime, String endtime,
Long deptypeid, Long foruserid) {
List<Map<String, Object>> listMaps = new ArrayList<Map<String, Object>>();
listMaps = this.optionsRegistrInfoService.selecOptionsTotleScores(
begintime, endtime, deptypeid, foruserid);
if (null != listMaps.get(0)) {
return (Integer) (listMaps.get(0).get("sumNums") == null ? 0
: listMaps.get(0).get("sumNums")) + "";
} else {
return "0";
}


}


/**
* 
* @param userId
*            人员ID
* @param month
*            所选月份
*/
public void exportDate(long userId, String month,
HttpServletResponse response) {
// 通过月份获取填报信息
UserInfo ui = this.userService.selectByPrimaryKey(userId);
String area = ui.getArea();
String name = ui.getName();
long deptypeid = ui.getDeptypeid();
DepTypeInfo dti = new DepTypeInfo();
dti = this.depTypeInfoService.selectByPrimaryKey(deptypeid);
String depName = dti.getName();
long foruserid = userId;
String begintime = Common.JudgeYearMonth(month) + "-1";
String endtime = Common.getDayAfter(
(Common.JudgeYearMonth(month) + "-" + Common
.JudgeDateAmount(month)), 1);


Map<Long, Map<Object, Object>> mapNums = new HashMap<Long, Map<Object, Object>>();
mapNums = getNum(begintime, endtime, foruserid, deptypeid);


List<OptionsInfo> ois = new ArrayList<OptionsInfo>();
ois = getOptionInfos(deptypeid);
for (int i = 0; i < ois.size(); i++) {
Map<Object, Object> mapNum = new HashMap<Object, Object>();
mapNum = mapNums.get(ois.get(i).getId());
System.out.println(mapNum.get("OptionName") + "-----"
+ mapNum.get("ScoreUnit") + mapNum.get("nums").toString());
}


List<String> rowScores = getScoresRow(begintime, endtime, deptypeid,
foruserid);
List<String> cellScores = getScoresCell(begintime, endtime, deptypeid,
foruserid);
String totleScore = getTotleScores(begintime, endtime, deptypeid,
foruserid);


System.out.println(cellScores.toString());
System.out.println("开始导出");
new ComplexExportExcelClient().exportExcel(depName, month, area, name,
ois, mapNums, rowScores, cellScores, totleScore, response);
System.out.println("导出成功");
}


/**
* 
* @param userId
*            人员ID
* @param month
*            所选月份
* @return
*/
public HSSFWorkbook showDate(long userId, String month,
HttpServletResponse response) {
// 通过月份获取填报信息
UserInfo ui = this.userService.selectByPrimaryKey(userId);
String area = ui.getArea();
String name = ui.getName();
long deptypeid = ui.getDeptypeid();
DepTypeInfo dti = new DepTypeInfo();
dti = this.depTypeInfoService.selectByPrimaryKey(deptypeid);
String depName = dti.getName();
long foruserid = userId;
String begintime = Common.JudgeYearMonth(month) + "-1";
String endtime = Common.getDayAfter(
(Common.JudgeYearMonth(month) + "-" + Common
.JudgeDateAmount(month)), 1);


Map<Long, Map<Object, Object>> mapNums = new HashMap<Long, Map<Object, Object>>();
mapNums = getNum(begintime, endtime, foruserid, deptypeid);


List<OptionsInfo> ois = new ArrayList<OptionsInfo>();
ois = getOptionInfos(deptypeid);
for (int i = 0; i < ois.size(); i++) {
Map<Object, Object> mapNum = new HashMap<Object, Object>();
mapNum = mapNums.get(ois.get(i).getId());
System.out.println(mapNum.get("OptionName") + "-----"
+ mapNum.get("ScoreUnit") + mapNum.get("nums").toString());
}


List<String> rowScores = getScoresRow(begintime, endtime, deptypeid,
foruserid);
List<String> cellScores = getScoresCell(begintime, endtime, deptypeid,
foruserid);
String totleScore = getTotleScores(begintime, endtime, deptypeid,
foruserid);


System.out.println(cellScores.toString());
return new ComplexExportExcelClient()
.showExcel(depName, month, area, name, ois, mapNums, rowScores,
cellScores, totleScore, response);
}
}