MySQL数据表导入Excel模板

package com.pl.dcloud.controller;


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.dom4j.DocumentException;
import org.springframework.stereotype.Controller;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.pl.dcloud.model.Member;
import com.pl.dcloud.model.Quote;
import com.pl.dcloud.service.QuoteService;
import com.pl.dcloud.xmlParse.XmltoExcelUtil;

/**
 * @author ycw
 * mySQL导出Excel
 */
@Controller
@RequestMapping(value="/quote")
public class QuoteDbToExcelController extends ControllerImpl {
@Resource
private QuoteService quoteService;

@ResponseBody
@RequestMapping(value="/quoteToExcel",produces="text/plain;charset=UTF-8")
public void export(HttpServletRequest request, HttpServletResponse response) {
Map<String, Object> map = new HashMap<String, Object>();
        Member member = getMember(request);
        if (!StringUtils.isEmpty(member)) {
        map.put("companyId",member.getOrgId());
}else{
map.put("companyId",1);
}
        //得到报价列表
List<Quote> quoteList = quoteService.getList(map);
//列表转Excel
quoteService.getQuoteToExcel(quoteList,response);
}

}

package com.pl.dcloud.service.impl;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Iterator;
import java.util.List;


import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.dom4j.DocumentException;
import org.springframework.stereotype.Service;

import com.pl.dcloud.model.Quote;
import com.pl.dcloud.service.QuoteService;
import com.pl.dcloud.xmlParse.XmltoExcelUtil;
import com.xk.core.service.BaseServiceImpl;

/**o
 * @author ycw
 * 报价表规则类接口
 * 根据查询条件查询出所有的记录,用于excel导出功能  
 */
@Service
public class QuoteServiceImpl extends BaseServiceImpl<Quote> implements QuoteService{

    private static final String EXCEL_XLS = "xls";  
    private static final String EXCEL_XLSX = "xlsx";
    private static final String finalXlsxPath = "E:\\test\\baojia\\Quote模板-3-加载宏.xls" ;

@Override
public boolean getQuoteToExcel(List<Quote> quotes,HttpServletResponse response) {

String[] headers = {"分类1关键字","分类2关键字", "分类3关键字", "零件名称", "备注关键字", "材质", "面材", "显示名字", "长", "宽", "厚", "是否独立计价", "算法", "起点量","基准单价", "异性调价比" };  
String fileName = "材价表";  

//xml 转 Excel 导出BOM数据
    try {
XmltoExcelUtil.xmltoexcel("E:\\test\\XML文件-0112.xml",finalXlsxPath);
System.out.println("BOM已生成！！");
} catch (DocumentException e1) {
System.out.println("生成BOM表格出错");
e1.printStackTrace();
}

OutputStream out = null;
        try {
        // 读取Excel文档  
        File finalXlsxFile = new File(finalXlsxPath);  
Workbook workBook = getWorkbok(finalXlsxFile);


// 生成一个表格  
Sheet sheet = workBook.createSheet(fileName);
// 设置表格默认列宽度为10个字节  
sheet.setDefaultColumnWidth((short) 10);  
// 产生表格标题行  
Row row = sheet.createRow(0);
for (short i = 0; i < headers.length; i++) {  
Cell cell = row.createCell(i);  
//判断 Excel版本
if (finalXlsxFile.getName().endsWith(EXCEL_XLS)){//Excel 2003
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);  
}else if(finalXlsxFile.getName().endsWith(EXCEL_XLSX)){  // Excel 2007/2010  
XSSFRichTextString text = new XSSFRichTextString(headers[i]); 
cell.setCellValue(text);
       } 
}  
try {  
// 遍历集合数据，产生数据行  
Iterator<Quote> it = quotes.iterator();  
int index = 0;  
while (it.hasNext()) {  
index++;  
row = sheet.createRow(index);  
Quote t = (Quote) it.next();  
//判断 Excel版本
if (finalXlsxFile.getName().endsWith(EXCEL_XLS)){//Excel 2003
row.createCell(0).setCellValue(new HSSFRichTextString(t.getSceneBom()));
row.createCell(1).setCellValue(new HSSFRichTextString(t.getSingleGroup()));
row.createCell(2).setCellValue(new HSSFRichTextString(t.getSubassembly()));
row.createCell(3).setCellValue(new HSSFRichTextString(t.getObjectName()));
row.createCell(4).setCellValue(new HSSFRichTextString(t.getRemarks()));
row.createCell(5).setCellValue(new HSSFRichTextString(t.getMaterial()));
row.createCell(6).setCellValue(new HSSFRichTextString(t.getFacestock()));
row.createCell(7).setCellValue(new HSSFRichTextString(t.getShowName()));
row.createCell(8).setCellValue(new HSSFRichTextString(t.getWidth()==null?"":t.getWidth().toString()));
row.createCell(9).setCellValue(new HSSFRichTextString(t.getHeight()==null?"":t.getHeight().toString()));
row.createCell(10).setCellValue(new HSSFRichTextString(t.getDepth()==null?"":t.getDepth().toString()));
row.createCell(11).setCellValue(new HSSFRichTextString(String.valueOf(t.getIsValuation())));
row.createCell(12).setCellValue(new HSSFRichTextString(t.getAlgorithm()));
row.createCell(13).setCellValue(new HSSFRichTextString(String.valueOf(t.getMinimum())));
row.createCell(14).setCellValue(new HSSFRichTextString(String.valueOf(t.getPrice())));
row.createCell(15).setCellValue(new HSSFRichTextString(String.valueOf(t.getYiXing())));
}else if(finalXlsxFile.getName().endsWith(EXCEL_XLSX)){  // Excel 2007/2010  
row.createCell(0).setCellValue(new XSSFRichTextString(t.getSceneBom()));
row.createCell(1).setCellValue(new XSSFRichTextString(t.getSingleGroup()));
row.createCell(2).setCellValue(new XSSFRichTextString(t.getSubassembly()));
row.createCell(3).setCellValue(new XSSFRichTextString(t.getObjectName()));
row.createCell(4).setCellValue(new XSSFRichTextString(t.getRemarks()));
row.createCell(5).setCellValue(new XSSFRichTextString(t.getMaterial()));
row.createCell(6).setCellValue(new XSSFRichTextString(t.getFacestock()));
row.createCell(7).setCellValue(new XSSFRichTextString(t.getShowName()));
row.createCell(8).setCellValue(new XSSFRichTextString(t.getWidth()==null?"":t.getWidth().toString()));
row.createCell(9).setCellValue(new XSSFRichTextString(t.getHeight()==null?"":t.getHeight().toString()));
row.createCell(10).setCellValue(new XSSFRichTextString(t.getDepth()==null?"":t.getDepth().toString()));
row.createCell(11).setCellValue(new XSSFRichTextString(String.valueOf(t.getIsValuation())));
row.createCell(12).setCellValue(new XSSFRichTextString(t.getAlgorithm()));
row.createCell(13).setCellValue(new XSSFRichTextString(String.valueOf(t.getMinimum())));
row.createCell(14).setCellValue(new XSSFRichTextString(String.valueOf(t.getPrice())));
row.createCell(15).setCellValue(new XSSFRichTextString(String.valueOf(t.getYiXing())));
       } 
}  
BufferedOutputStream fos = null;  
try {  
//String fileName = name+DateUtil.parseSmallDate(new Date()) + ".xlsx";  
response.setContentType("application/x-msdownload");  
response.setHeader("Content-Disposition", "attachment;filename=" + new String("报价单.xls".getBytes("gb2312"), "ISO8859-1" ));  
fos = new BufferedOutputStream(response.getOutputStream());  
workBook.write(fos);  
} catch (Exception e) {  
e.printStackTrace();  
} finally {  
if (fos != null) {  
fos.close();  
}  
}  
} catch (Exception e) {  
e.printStackTrace();  
}

System.out.println("============报价----Successfully=============");

} catch (Exception e) {  
            e.printStackTrace();
System.out.println("读取Excel文档 出错");
} 
return true;
}


    /** 
     * 判断Excel的版本,获取Workbook 
     * @param in 
     * @param filename 
     * @return 
     * @throws IOException 
     */  
    public static Workbook getWorkbok(File file) throws IOException{  
        Workbook wb = null;  
        FileInputStream in = new FileInputStream(file);  
        if(file.getName().endsWith(EXCEL_XLS)){  //Excel 2003  
            wb = new HSSFWorkbook(in);  
        }else if(file.getName().endsWith(EXCEL_XLSX)){  // Excel 2007/2010  
            wb = new XSSFWorkbook(in);  
        }  
        return wb;  
    } 
}