/**
* 数据包Controller
* @author cookie
* @version 2017-08-23
*/
public class BudgetDateController extends BaseController {
/**
* 生成数据包
* @param budgetDatabase
* @param model
* @param redirectAttributes
* @return
*/
@RequestMapping(value = "save")
public String save(BudgetData budgetDatabase, Model model, RedirectAttributes redirectAttributes) throws FileNotFoundException {
//定义目录
String fileName="数据包"+DateUtils.getDate("yyyyMMddHHmmss");
File src=new File("d:\\users\\"+fileName);
List<BudgetIncomeBill> billList=budgetIncomeBillService.findList(budgetIncomeBill);
if(billList.size()>0){
//循环创建7个execl
for(int b=0;b<billList.size();b++){
HSSFWorkbook wbb = new HSSFWorkbook();
//根据tablenum判断是哪种类型的支出,生成对应的execl
if(billList.get(b).getTableNum().equals("2")){
BudgetIncome budgetIncome=new BudgetIncome();
budgetIncome.setHalfYear(budgetDatabase.getHalfYear());
budgetIncome.setSqlMap(sqlMap);
budgetIncome.setBudgetIncomeBill(billList.get(b));
HSSFCellStyle style = wbb.createCellStyle();
Font Font = wbb.createFont();
Font.setFontName("仿宋_GB2312");
Font.setFontHeightInPoints((short) 12);//字号
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//上下居中
style.setFont(Font);
style.setWrapText(true);
//设置左对齐样式
HSSFCellStyle leftstyle = wbb.createCellStyle();
leftstyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
leftstyle.setFont(Font);
//设置右对齐样式
HSSFCellStyle rightstyle = wbb.createCellStyle();
rightstyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
rightstyle.setFont(Font);
HSSFDataFormat df = wbb.createDataFormat();
style.setDataFormat(df.getBuiltinFormat("#,##0.00"));//保留两位小数点
// 第四步之树形式,创建单元格,设置单元格样式
HSSFCellStyle treeStyle = wbb.createCellStyle();
//treeStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
treeStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//上下居中
treeStyle.setFont(Font);
treeStyle.setWrapText(true);
// 第五步,设置边框样式
HSSFCellStyle borderStyle = wbb.createCellStyle();
//borderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
borderStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//上下居中
borderStyle.setFont(Font);
borderStyle.setWrapText(true);
borderStyle.setBorderBottom(CellStyle.BORDER_THIN); // 下边框
borderStyle.setBorderLeft(CellStyle.BORDER_THIN);// 左边框
borderStyle.setBorderTop(CellStyle.BORDER_THIN);// 上边框
borderStyle.setBorderRight(CellStyle.BORDER_THIN);// 右边框
// 第六步,设置表头样式
HSSFCellStyle heardStyle = wbb.createCellStyle();
heardStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
heardStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//上下居中
heardStyle.setWrapText(true);
Font FirstFont = wbb.createFont();
FirstFont.setFontName("黑体");
FirstFont.setFontHeightInPoints((short) 24);//字号
FirstFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //加粗
// 第六步,设置边框样式
HSSFCellStyle centerStyle = wbb.createCellStyle();
centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
centerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//上下居中
centerStyle.setFont(Font);
centerStyle.setWrapText(true);
centerStyle.setBorderBottom(CellStyle.BORDER_THIN); // 下边框
centerStyle.setBorderLeft(CellStyle.BORDER_THIN);// 左边框
centerStyle.setBorderTop(CellStyle.BORDER_THIN);// 上边框
centerStyle.setBorderRight(CellStyle.BORDER_THIN);// 右边框
row1.setHeightInPoints(50);//设置列高
//String sumByOffice=budgetIncomeService.getSumByAccount();得到总数
//cell65.setCellValue(sumByOffice);
List<BudgetIncome> list=budgetIncomeService.findBudget(budgetIncome);
List<BudgetIncome> BudgetList=new ArrayList<BudgetIncome>();
List<BudgetIncome> cellList=new ArrayList<BudgetIncome>();
for(BudgetIncome income:list){
income.setSqlMap(budgetIncome.getSqlMap());
income.setBudgetIncomeBill(budgetIncome.getBudgetIncomeBill());
BudgetList=budgetIncomeService.findBudgetsByParent(income);
for(BudgetIncome income2:BudgetList){
cellList.add(income2);
}
}
cellList=budgetIncomeService.jisuan(cellList);
for(int i=7;i<cellList.size()+7;i++){
HSSFRow rowi= sheet.createRow(i-1);
if(i==7){
if(cellList.get(i-7).getStandardBudget()!=null){
cell64.setCellValue(Double.parseDouble(cellList.get(i-7).getStandardBudget()));
cell64.setCellStyle(borderStyle);
}else{
cell64.setCellValue("");
cell64.setCellStyle(borderStyle);
}
if(cellList.get(i-7).getApplication()!=null){
cell65.setCellValue(Double.parseDouble(cellList.get(i-7).getApplication()));
cell65.setCellStyle(borderStyle);
}else{
cell65.setCellValue("");
cell65.setCellStyle(borderStyle);
}
if(cellList.get(i-7).getAudit()!=null){
cell66.setCellValue(Double.parseDouble(cellList.get(i-7).getAudit()));
cell66.setCellStyle(borderStyle);
}else{
cell66.setCellValue("");
cell66.setCellStyle(borderStyle);
}
if(cellList.get(i-7).getCompareResults()!=null){
cell67.setCellValue(Double.parseDouble(cellList.get(i-7).getCompareResults()));
cell67.setCellStyle(borderStyle);
}else{
cell67.setCellValue("");
cell67.setCellStyle(borderStyle);
}
}
for(int j=1;j<9;j++){
HSSFCell cellij = rowi.createCell((short) j-1);
if(j==1){
cellij.setCellValue(cellList.get(i-7).getAccount().getName());
cellij.setCellStyle(borderStyle);
}
else if(j==2){
cellij.setCellValue(cellList.get(i-7).getMeasuringUnit());
cellij.setCellStyle(borderStyle);
}
else if(j==3){
cellij.setCellValue(cellList.get(i-7).getBudgetSupply());
cellij.setCellStyle(borderStyle);
}
else if(j==4){
if(cellList.get(i-7).getStandardBudget()!=null){
cellij.setCellValue(Double.parseDouble(cellList.get(i-7).getStandardBudget()));
cellij.setCellStyle(borderStyle);
}else{
cellij.setCellValue("");
cellij.setCellStyle(borderStyle);
}
}
else if(j==5){
if(cellList.get(i-7).getApplication()!=null){
cellij.setCellValue(Double.parseDouble(cellList.get(i-7).getApplication()));
cellij.setCellStyle(borderStyle);
}
}
else if(j==6){
if(cellList.get(i-7).getAudit()!=null){
cellij.setCellValue(Double.parseDouble(cellList.get(i-7).getAudit()));
cellij.setCellStyle(borderStyle);
}
}
else if(j==7){
if(cellList.get(i-7).getCompareResults()!=null){
cellij.setCellValue(Double.parseDouble(cellList.get(i-7).getCompareResults()));
cellij.setCellStyle(borderStyle);
}
}
else if(j==8){
cellij.setCellValue(cellList.get(i-7).getRemarks());
cellij.setCellStyle(borderStyle);
}
}
}
}else if(billList.get(b).getTableNum().equals("3")){
}
}
}
//输出Excel文件
FileOutputStream output;
try {
if (!src.exists()&& !src.isDirectory()) {
src.mkdir();
}
output = new FileOutputStream(src+"\\"+billList.get(b).getTableNum()+".xls");
wbb.write(output);
output.flush();
} catch (IOException e) {
e.printStackTrace();
}
}
addMessage(redirectAttributes, "生成数据包成功");
budgetDatabase.setNo("生成数据包");
budgetDatabase.setFilename(budgetDatabase.getOffice().getId());
budgetDatabase.setFileway(fileName);
budgetDataService.save(budgetDatabase);
}else{
addMessage(redirectAttributes, "生成数据包失败");
}
return "redirect:"+Global.getAdminPath()+"/data/budgetData/?repage";
}
/**
* 恢复数据包
* @param budgetDatabase
* @param model
* @param redirectAttributes
* @return
*/
@RequestMapping(value = "load")
public String load(MultipartFile file,BudgetData budgetDatabase, RedirectAttributes redirectAttributes,HttpServletRequest request,HttpServletResponse response,Model model,HttpSession httpSession) {
try {
String fileName=file.getOriginalFilename();
String filePath=request.getSession().getServletContext().getRealPath("/")+"upload/"+file.getOriginalFilename();
File uploadFile = new File(request.getSession().getServletContext().getRealPath("/")+"upload/");
File savePth=new File(filePath.substring(0, filePath.lastIndexOf("."))+"/"+fileName.substring(0, fileName.lastIndexOf(".")) + File.separator);
String nowPath=filePath.substring(0, filePath.lastIndexOf("."))+"/"+fileName.substring(0, fileName.lastIndexOf(".")) + File.separator;
File files=new File(filePath);
if (!uploadFile.exists()&& !uploadFile.isDirectory()) {
uploadFile.mkdir();
}
if(!files.exists()) {
file.transferTo(new File(filePath));
}
//解压到服务器文件夹
ZipUtil.unZip(filePath);
File[] tempList = savePth.listFiles();
System.out.println("该目录下对象个数:"+tempList.length);
for (int i = 0; i < tempList.length; i++) {
BudgetOutgoBill bill=new BudgetOutgoBill();
bill.setOffice(budgetDatabase.getOffice());
bill.setSqlMap(sqlMap);
bill.setHalfYear(budgetDatabase.getHalfYear());
PoiExcelHelper helper;
if(nowPath.indexOf(".xlsx")!=-1) {
helper = new PoiExcel2k7Helper();
}else {
helper = new PoiExcel2k3Helper();
}
if (tempList[i].isFile()) {
//System.out.println("文 件:"+tempList[i]);
//遍历文件夹,获取到所有的execl,循环导入
//System.out.println( tempList[i].getName());
String type=tempList[i].getName();
}else{
System.out.println("false");
}
}
if (tempList[i].isDirectory()) {
System.out.println("文件夹:"+tempList[i]);
}
}
budgetDatabase.setNo("恢复数据包");
budgetDatabase.setFilename(budgetDatabase.getOffice().getId());
budgetDatabase.setFileway(fileName);
budgetDataService.save(budgetDatabase);
addMessage(redirectAttributes, "数据恢复成功");
} catch (Exception e) {
e.printStackTrace();
}
return true;
}
}