import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.HSSFColor;
import org.springframework.web.servlet.view.document.AbstractExcelView;
import com.afmobi.util.CommonUtil;
/**
*
* @author Administrator
*
*/
public class ExcelView extends AbstractExcelView {
public static final Logger _log = Logger.getLogger(ExcelView.class);
@Override
protected void buildExcelDocument(Map<String, Object> map, HSSFWorkbook workbook, HttpServletRequest req,
HttpServletResponse resp) throws Exception {
@SuppressWarnings("unchecked")
List<Map<String,Object>> rows=(List<Map<String, Object>>) map.get("rows");
String title = (String)map.get("title");
String[] h1=(String[])map.get("h1");
String[] h2=(String[])map.get("h2");
String excelName=title+".xls";
resp.setContentType("APPLICATION/OCTET-STREAM");
resp.setHeader("Content-Disposition", "attachment; filename="+ URLEncoder.encode(excelName, "UTF-8"));
HSSFSheet sheet = workbook.createSheet(title);
// 设置表格默认列宽度为15字节
sheet.setDefaultColumnWidth(25);
// 生成一个样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.SKY_BLUE.index); //设置表格背景色
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 生成一个字体
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前样式
style.setFont(font);
// 设置另外一个样式
HSSFCellStyle style2 = workbook.createCellStyle();
style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 生成另一个字体
HSSFFont font2 = workbook.createFont();
//font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 设置粗体
style2.setFont(font2);
HSSFRow header1 = sheet.createRow(0);
for(int i=0;i<h1.length;i++){
HSSFCell cell = header1.createCell(i);
cell.setCellStyle(style);
cell.setCellValue(h2[i]);
}
if(rows!=null){
for(int i=0;i<rows.size();i++){
HSSFRow row=sheet.createRow(i+1);
Map<String,Object> content=rows.get(i);
for(int j=0;j<h1.length;j++){
HSSFCell cell = row.createCell(j);
cell.setCellStyle(style2);
String key=h1[j];
Object c=content.get(key);
try{
if(c instanceof Integer || c instanceof BigDecimal){
if("status".equals(key)){
if((int)c == 1){
cell.setCellValue("Uploaded");
}else {
cell.setCellValue("Pending");
}
}else {
cell.setCellValue(c+"");
}
}else if(c instanceof Date){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
cell.setCellValue(sdf.format(c));
} else{
if("countryCode".equals(key)){
String countryCode = (String) c;
String countryName = CommonUtil.getProperty(countryCode);
if(StringUtils.isNotEmpty(countryName)){
cell.setCellValue(countryName);
}else {
cell.setCellValue(countryCode);
}
}else {
cell.setCellValue((String)c);
}
}
}catch(Exception e){
_log.info(e.getMessage());
}
}
}
}
}
}
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.HSSFColor;
import org.springframework.web.servlet.view.document.AbstractExcelView;
import com.afmobi.util.CommonUtil;
/**
*
* @author Administrator
*
*/
public class ExcelView extends AbstractExcelView {
public static final Logger _log = Logger.getLogger(ExcelView.class);
@Override
protected void buildExcelDocument(Map<String, Object> map, HSSFWorkbook workbook, HttpServletRequest req,
HttpServletResponse resp) throws Exception {
@SuppressWarnings("unchecked")
List<Map<String,Object>> rows=(List<Map<String, Object>>) map.get("rows");
String title = (String)map.get("title");
String[] h1=(String[])map.get("h1");
String[] h2=(String[])map.get("h2");
String excelName=title+".xls";
resp.setContentType("APPLICATION/OCTET-STREAM");
resp.setHeader("Content-Disposition", "attachment; filename="+ URLEncoder.encode(excelName, "UTF-8"));
HSSFSheet sheet = workbook.createSheet(title);
// 设置表格默认列宽度为15字节
sheet.setDefaultColumnWidth(25);
// 生成一个样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.SKY_BLUE.index); //设置表格背景色
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 生成一个字体
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前样式
style.setFont(font);
// 设置另外一个样式
HSSFCellStyle style2 = workbook.createCellStyle();
style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 生成另一个字体
HSSFFont font2 = workbook.createFont();
//font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 设置粗体
style2.setFont(font2);
HSSFRow header1 = sheet.createRow(0);
for(int i=0;i<h1.length;i++){
HSSFCell cell = header1.createCell(i);
cell.setCellStyle(style);
cell.setCellValue(h2[i]);
}
if(rows!=null){
for(int i=0;i<rows.size();i++){
HSSFRow row=sheet.createRow(i+1);
Map<String,Object> content=rows.get(i);
for(int j=0;j<h1.length;j++){
HSSFCell cell = row.createCell(j);
cell.setCellStyle(style2);
String key=h1[j];
Object c=content.get(key);
try{
if(c instanceof Integer || c instanceof BigDecimal){
if("status".equals(key)){
if((int)c == 1){
cell.setCellValue("Uploaded");
}else {
cell.setCellValue("Pending");
}
}else {
cell.setCellValue(c+"");
}
}else if(c instanceof Date){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
cell.setCellValue(sdf.format(c));
} else{
if("countryCode".equals(key)){
String countryCode = (String) c;
String countryName = CommonUtil.getProperty(countryCode);
if(StringUtils.isNotEmpty(countryName)){
cell.setCellValue(countryName);
}else {
cell.setCellValue(countryCode);
}
}else {
cell.setCellValue((String)c);
}
}
}catch(Exception e){
_log.info(e.getMessage());
}
}
}
}
}
}