import com.fh.dao.DaoSupport;
import com.fh.entity.Page;
import com.fh.service.task.WorksheetService;
import com.fh.util.PageData;
import jxl.write.WritableSheet;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.List;
@Service(“workSheetExportService”)
public class WorkSheetExportService {
@Resource(name = “daoSupport”)
private DaoSupport dao;
@Autowired
private WorksheetService worksheetService;
public void exportFillToExcel(String title,String[] titleList,String[] titleColumnList,List listProduct,HttpServletResponse response)throws Exception{
// FileOutputStream fout = new FileOutputStream(“e:/”+title+”.xls”);//下载路径
//表头长度
int cellNumber =titleList.length;
//创建webbook对象
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("sheet1"); //创建sheet
HSSFRow headerRow = sheet.createRow((short) 1);//在sheet第2行中添加表头
HSSFCellStyle style = getHeaderStyle(wb);//添加表头样式
HSSFCellStyle bodyStyle = getBodyStyle(wb);//添加表格内容样式
HSSFCellStyle lockedStyle = getLockedStyle(wb);//添加锁定部分单元格样式
sheet.protectSheet("工作薄的保护属性");//想要锁定必须开启保护属性
sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) (cellNumber-1) ));//合并单元格
sheet.createFreezePane(0,2,0,2);//冻结前两行窗口(前两个参数是你要用来拆分的列数和行数。后两个参数是下面窗口的可见象限,其中第三个参数是右边区域可见的左边列数,第四个参数是下面区域可见的首行)
// sheet.createFreezePane(1,0,1,0);//冻结第一列窗口
HSSFRow row=sheet.createRow(0);//第一行(标题)
row.setHeight((short)869);//第一行高度(标题)
// 标题样式
HSSFCellStyle headstyleTitle = wb.createCellStyle();
HSSFFont titlefont = wb.createFont();
titlefont.setFontName("宋体");
titlefont.setFontHeightInPoints((short) 20);// 字体大小
headstyleTitle.setFont(titlefont);
headstyleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
headstyleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
headstyleTitle.setLocked(true);//是否锁定单元格,即为只读
HSSFCell cell = row.createCell((short)0);//创建单元格
cell.setCellStyle(headstyleTitle);//添加样式
cell.setCellValue(title);//单元格标题添加值
//遍历表头
HSSFCell headerCell = null;//创建表头单元格
for (int i = 0; i < cellNumber; i++) {
headerCell = headerRow.createCell((short) i); //单元格titleList[i].
headerCell.setCellValue(titleList[i]);//给单元格赋值
sheet.setColumnWidth((short) i, (short) 3766);//设置单元格宽度
headerCell.setCellStyle(style);//单元格样式
}
//遍历填报内容
for (int i = 0; i < listProduct.size();i++) {
PageData single = listProduct.get(i);
HSSFRow dataRow = sheet.createRow((short) i + 2);//创建行
HSSFCell contentCell = null;//创建单元格
HSSFCell lockedCell = null;//创建要锁定的单元格
int count = 0;
for (int j = 0; j < cellNumber ; j++) {
String VALUE = "";
String TABLE_COLUMN = titleColumnList[j];
//判断map中是否存在key,并且判断key的值是否为空
if(single.containsKey(TABLE_COLUMN) && single.get(TABLE_COLUMN) != null && !single.get(TABLE_COLUMN).toString().equals("")){
VALUE = single.get(TABLE_COLUMN).toString();
}
if(single.containsKey(TABLE_COLUMN)){
count++;
}
contentCell = dataRow.createCell((short)j);//单元格
contentCell.setCellValue(VALUE);//单元格赋值
contentCell.setCellStyle(bodyStyle);//单元格样式
lockedCell = dataRow.getCell(count-1);
lockedCell.setCellStyle(lockedStyle);
}
}
// wb.write(fout);//excel模板内容写入到下载路径里
// //浏览器下载
OutputStream os=null;
try {
os = response.getOutputStream();// 取得输出流
response.reset();// 清空输出流
response.setHeader(“Content-disposition”, “attachment; filename=” + new String((title+”.xls”).getBytes(“UTF-8”), “ISO8859-1”));
response.setContentType(“text/html;charset=UTF-8”);
response.setContentType(“application/x-excel”);
wb.write(os);
os.close();
} catch (IOException e) {
e.printStackTrace();
}finally {
try {
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
public static HSSFCellStyle getHeaderStyle(HSSFWorkbook wb) {
HSSFCellStyle style = wb.createCellStyle();
//设置单元格的边框为粗体
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
//自动换行
style.setWrapText(true);
//是否锁定单元格
style.setLocked(true);
//设置背景颜色
style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//设置位置
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
//创建字体对象
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
font.setColor(HSSFColor.BLACK.index);
font.setFontHeightInPoints((short) 10);
font.setFontName("宋体");
style.setFont(font);
return style;
}
public static HSSFCellStyle getBodyStyle(HSSFWorkbook wb) {
//添加样式对象
HSSFCellStyle cellStyle = wb.createCellStyle();
//设置边框
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
//创建字体对象
HSSFFont font = wb.createFont();
font.setColor(HSSFColor.BLACK.index);//颜色
font.setFontHeightInPoints((short) 10);//大小
font.setFontName("宋体");//字体
cellStyle.setWrapText(true);//自动换行
cellStyle.setLocked(false); //是否锁定单元格
cellStyle.setFont(font);
return cellStyle;
}
public static HSSFCellStyle getLockedStyle(HSSFWorkbook wb) {
//表格部分单元格锁定样式
HSSFCellStyle lockedStyle = wb.createCellStyle();
//设置边框加粗
lockedStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
lockedStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
lockedStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
lockedStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
lockedStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
//创建字体对象
HSSFFont font = wb.createFont();
font.setColor(HSSFColor.BLACK.index);//颜色
font.setFontHeightInPoints((short) 10);//大小
font.setFontName("宋体");//字体
lockedStyle.setFont(font);
lockedStyle.setWrapText(true);//自动换行
lockedStyle.setLocked(true);//锁定单元格
return lockedStyle;
}
}