package cn.vit.auto.proto.utils;

import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.hibernate.Session;
import cn.vit.auto.proto.entity.BookDetails;

/**
?*
?* @author Aubergine_kang
?*
?*/
public class DownLoadUtil {
?private static String userName = "";
?private static double sumCount = 0;
?//单元格样式
?private static CellStyle cs1;
?private static CellStyle cs2;
?private static CellStyle cs3;
?
??? //创建单元格样式
?public static void newCellStyle(Workbook Book_details) {
??cs1 = Book_details.createCellStyle();
??cs1.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
??cs1.setFillPattern(CellStyle.SOLID_FOREGROUND);

??cs2 = Book_details.createCellStyle();
??cs2.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
??cs2.setFillPattern(CellStyle.SOLID_FOREGROUND);

??cs3 = Book_details.createCellStyle();
??cs3.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
??cs3.setFillPattern(CellStyle.SOLID_FOREGROUND);

?}

?// 第一个sheet需要的list从数据库读取
?public static List<BookDetails> getDownLoadList(Session session) {
??String sql = " select id,Book,user,count,month from Book_details "
????+ " where month(month) = month(SYSDATE()) "
????+ " group by user,Book";
??List<BookDetails> list = (List<BookDetails>) session
????.createSQLQuery(sql).addEntity(BookDetails.class).list();
??return list;
?}

?// 写第一个sheet的内容
?public static void writeDownLoadExcel(List<BookDetails> list, Sheet sheet,
???Session session) throws IOException {
??writeExcelHead(sheet);
??for (int i = 0; i < list.size(); i++) {
????BooksDetails gds = (BooksDetails) list.get(i);
????????? String name = gds.getUser().getUserName();
????int rows = sheet.getPhysicalNumberOfRows();
????Row row = sheet.createRow(rows);
????row.createCell(0).setCellValue(userName);
????row.getCell(0).setCellStyle(cs3);
????row.createCell(1).setCellValue(userName2);
????row.getCell(1).setCellStyle(cs2);
????session.flush();
????session.clear();
????}
???}
??}

?}
?// 写第一个sheet的头部
?private static void writeExcelHead(Sheet sheet) {

??Row header = sheet.createRow(0);
??header.createCell(0).setCellValue("name");
??header.createCell(1).setCellValue("Book");
??header.createCell(2).setCellValue("count");
??header.createCell(3).setCellValue("sum");

??header.getCell(0).setCellStyle(cs1);
??header.getCell(1).setCellStyle(cs1);
??header.getCell(