String sFileName = "采购执行导入模板.xls";
response.setHeader("Content-Disposition", "attachment;filename=".concat(String.valueOf(URLEncoder.encode(sFileName, "UTF-8"))));
response.setHeader("Connection", "close");
response.setHeader("Content-Type", "application/vnd.ms-excel");
String title = "采购执行导入模板";//名称
String[] headers = new String[]{"省公司代码","采购订单编号","订单创建时间","组织标识","OU名称","供应商编号","供应商名称","统一供应商编码","币种",
"采购订单总金额","总部框架协议编码","省公司合同编号","合同名称","行号","统一物料编码","物料说明","数量","计量单位","单价"};//表头
String[] fields = new String[]{"userId","phoneNum","exchangeIntegral","cardNum","applyDate","exchangeState","exchangeDate"};//字段名字
//List<?> list = this.customerExchangeManageService.queryExportCustomerExchangeList(params);//数值list
OutputStream out = response.getOutputStream();
response.setHeader("Content-Disposition", "attachment;filename=".concat(String.valueOf(URLEncoder.encode(sFileName, "UTF-8"))));
response.setHeader("Connection", "close");
response.setHeader("Content-Type", "application/vnd.ms-excel");
String title = "采购执行导入模板";//名称
String[] headers = new String[]{"省公司代码","采购订单编号","订单创建时间","组织标识","OU名称","供应商编号","供应商名称","统一供应商编码","币种",
"采购订单总金额","总部框架协议编码","省公司合同编号","合同名称","行号","统一物料编码","物料说明","数量","计量单位","单价"};//表头
String[] fields = new String[]{"userId","phoneNum","exchangeIntegral","cardNum","applyDate","exchangeState","exchangeDate"};//字段名字
//List<?> list = this.customerExchangeManageService.queryExportCustomerExchangeList(params);//数值list
OutputStream out = response.getOutputStream();
CreateExcel.createExcel(title,headers,fields, new ArrayList(),out,0);
开源代码
- /**
- * 利用开源组件POI3.0.2动态导出EXCEL文档
- * 转载时请保留以下信息,注明出处!
- * @author leno
- * @version v1.0
- * @param <T> 应用泛型,代表任意一个符合javabean风格的类
- * 注意这里为了简单起见,boolean型的属性xxx的get器方式为getXxx(),而不是isXxx()
- * byte[]表jpg格式的图片数据
- */
- public class ExportExcel<T> {
- public void exportExcel(Collection<T> dataset, OutputStream out) {
- exportExcel("测试POI导出EXCEL文档", null, dataset, out, "yyyy-MM-dd");
- }
- public void exportExcel(String[] headers, Collection<T> dataset,
- OutputStream out) {
- exportExcel("测试POI导出EXCEL文档", headers, dataset, out, "yyyy-MM-dd");
- }
- public void exportExcel(String[] headers, Collection<T> dataset,
- OutputStream out, String pattern) {
- exportExcel("测试POI导出EXCEL文档", headers, dataset, out, pattern);
- }
- @SuppressWarnings("unchecked")
- public void exportExcel(String title, String[] headers,
- Collection<T> dataset, OutputStream out, String pattern) {
- // 声明一个工作薄
- HSSFWorkbook workbook = new HSSFWorkbook();
- // 生成一个表格
- HSSFSheet sheet = workbook.createSheet(title);
- // 设置表格默认列宽度为15个字节
- sheet.setDefaultColumnWidth((short) 15);
- // 生成一个样式
- 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.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
- 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_NORMAL);
- // 把字体应用到当前的样式
- style2.setFont(font2);
- // 声明一个画图的顶级管理器
- HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
- // 定义注释的大小和位置,详见文档
- HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
- 0, 0, 0, (short) 4, 2, (short) 6, 5));
- // 设置注释内容
- comment.setString(new HSSFRichTextString("可以在POI中添加注释!"));
- // 设置注释作者,当鼠标移动到单元格上是可以在状态栏中看到该内容.
- comment.setAuthor("leno");
- //产生表格标题行
- HSSFRow row = sheet.createRow(0);
- for (short i = 0; i < headers.length; i++) {
- HSSFCell cell = row.createCell(i);
- cell.setCellStyle(style);
- HSSFRichTextString text = new HSSFRichTextString(headers[i]);
- cell.setCellValue(text);
- }
- //遍历集合数据,产生数据行
- Iterator<T> it = dataset.iterator();
- int index = 0;
- while (it.hasNext()) {
- index++;
- row = sheet.createRow(index);
- T t = (T) it.next();
- //利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值
- Field[] fields = t.getClass().getDeclaredFields();
- for (short i = 0; i < fields.length; i++) {
- HSSFCell cell = row.createCell(i);
- cell.setCellStyle(style2);
- Field field = fields[i];
- String fieldName = field.getName();
- String getMethodName = "get"
- + fieldName.substring(0, 1).toUpperCase()
- + fieldName.substring(1);
- try {
- Class tCls = t.getClass();
- Method getMethod = tCls.getMethod(getMethodName,
- new Class[] {});
- Object value = getMethod.invoke(t, new Object[] {});
- //判断值的类型后进行强制类型转换
- String textValue = null;
- if (value instanceof Boolean) {
- boolean bValue = (Boolean) value;
- textValue = "男";
- if (!bValue) {
- textValue = "女";
- }
- } else if (value instanceof Date) {
- Date date = (Date) value;
- SimpleDateFormat sdf = new SimpleDateFormat(pattern);
- textValue = sdf.format(date);
- } else if (value instanceof byte[]) {
- // 有图片时,设置行高为60px;
- row.setHeightInPoints(60);
- // 设置图片所在列宽度为80px,注意这里单位的一个换算
- sheet.setColumnWidth(i, (short) (35.7 * 80));
- // sheet.autoSizeColumn(i);
- byte[] bsValue = (byte[]) value;
- HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0,
- 1023, 255, (short) 6, index, (short) 6, index);
- anchor.setAnchorType(2);
- patriarch.createPicture(anchor, workbook.addPicture(
- bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
- } else {
- //其它数据类型都当作字符串简单处理
- textValue = value.toString();
- }
- //如果不是图片数据,就利用正则表达式判断textValue是否全部由数字组成
- if (textValue != null) {
- Pattern p = Pattern.compile("^//d+(//.//d+)?$");
- Matcher matcher = p.matcher(textValue);
- if (matcher.matches()) {
- //是数字当作double处理
- cell.setCellValue(Double.parseDouble(textValue));
- } else {
- HSSFRichTextString richString = new HSSFRichTextString(
- textValue);
- HSSFFont font3 = workbook.createFont();
- font3.setColor(HSSFColor.BLUE.index);
- richString.applyFont(font3);
- cell.setCellValue(richString);
- }
- }
- } catch (SecurityException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- } catch (NoSuchMethodException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- } catch (IllegalArgumentException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- } catch (IllegalAccessException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- } catch (InvocationTargetException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- } finally {
- //清理资源
- }
- }
- }
- try {
- workbook.write(out);
- } catch (IOException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- }
- }
- public class ParseXls {
- public List<PersonVO> readXls() throws IOException, IllegalArgumentException,
- IllegalAccessException, InvocationTargetException {
- InputStream is = new FileInputStream("D:\\excel\\xls_test2.xls");
- HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
- List<PersonVO> personList = new ArrayList<PersonVO>();
- // 循环工作表Sheet
- for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
- HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
- if (hssfSheet == null) {
- continue;
- }
- // 循环行Row
- for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
- HSSFRow hssfRow = hssfSheet.getRow(rowNum);
- if (hssfRow == null) {
- continue;
- }
- PersonVO personVO = new PersonVO();
- // 循环列Cell
- for (int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++) {
- HSSFCell hssfCell = hssfRow.getCell(cellNum);
- if (hssfCell == null) {
- continue;
- }
- personVO.setName(getValue(hssfRow.getCell(0)));
- personVO.setSex(getValue(hssfRow.getCell(1)));
- personVO.setAge(getValue(hssfRow.getCell(2)));
- personVO.setPosition(getValue(hssfRow.getCell(3)));
- personVO.setDept(getValue(hssfRow.getCell(4)));
- System.out.print(" " + getValue(hssfCell));
- }
- personList.add(personVO);
- System.out.println();
- }
- }
- return personList;
- }
- @SuppressWarnings("static-access")
- private String getValue(HSSFCell hssfCell) {
- if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
- return String.valueOf(hssfCell.getBooleanCellValue());
- } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
- return String.valueOf(hssfCell.getNumericCellValue());
- } else {
- return String.valueOf(hssfCell.getStringCellValue());
- }
- }
- }