excel模板下载,模板导出excel

问题:有何地能够下载到曾经成型的excel工作模板。例如:
已经设定好公式和图纸的花色进程监察和控制表或数额分析表。不肯定要那么些宏观。感激!

//那几个用模板导出,省略了创办表头的章程,能够把模版里面包车型客车全方位复制下来,放到要导出的excel里面

   
本示例中使用了StringTemplate模板技术,实行excel数据导出的操作。

string filePath = Server.MapPath(“~/model/模板.xls”);//路径
FileInfo fileInfo = new FileInfo(filePath);
System.Web.HttpContext.Current.Response.Charset = “GB2312”;
System.Web.HttpContext.Current.Response.ContentEncoding =
System.Text.Encoding.UTF8;
System.Web.HttpContext.Current.Response.AppendHeader(“Content-Disposition”,
“attachment;filename=” + HttpUtility.UrlEncode(“模板.xls”,
System.Text.Encoding.UTF8).ToString());
System.Web.HttpContext.Current.Response.ContentType =
“application/ms-excel”;
excel模板下载,模板导出excel。System.Web.HttpContext.Current.Response.WriteFile(fileInfo.FullName);
System.Web.HttpContext.Current.Response.Flush();
System.Web.HttpContext.Current.Response.End();

回答:

//首先要记住,excel 第贰行是从0初叶的,列也是如此

     1.首开头入所供给的jar包,本示例中通过maven引入

本人看了上边亲们的应对。感觉楼主这么问没不平常。现在是1个跟时间赛跑的时期,工作追求质量的同时,一定要追求功效,才能干活更理想,生活又不延误。见谅作者的碎嘴。

//访问方法

public String na(HttpServletResponse response,HttpServletRequest
request){

    //查询要四处的数据

List<Map<string,string>> dataSourceList= null;

try {

        ExcelDownloadUtil.ExcelByModel(“测试模板导出”, modelUGL450LString,
dataSourceList, response, sheetNameStrings, keysStrings, 6);

} catch (Exception e) {

        e.printStackTrace();

}

    return “SUCESS”;

}


     

回应楼主的题材,小编在用的office网络工具中,有各项图片的沙盘。就是图表都是现成做好的,你想用哪个图表,直接选拔这么些图片并输入数据,就自动生成你想要的图片了。那么些工具叫「图表秀」,是一款在线制作图纸的工具,可免费使用。上海图书馆说雀巢(Nestle)下:

在模板中添加多少

public static void ExcelByModel(String ExcelName, String ModelURl,
List<Map<String,String>> dataSource,HttpServletResponse
response, String[] sheetNames, String[] keyNames, int rowNum) throws
Exception {

// 设置导出Excel报表的导出方式

response.setContentType(“application/vnd.ms-excel”);

// 设置导出Excel报表的响应文件名

String fileName = new String(ExcelName.getBytes(“utf-8”), “ISO-8859-1”);

response.setHeader(“Content-disposition”, “attachment;filename=” +
fileName + “.xls”);//导出的文件名称

// 创设三个输出流

OutputStream fileOut = response.getOutputStream();

// 读取模板文件路径

File file = new File(ModelURl);

FileInputStream fins = new FileInputStream(file);

POIFSFileSystem fs = new POIFSFileSystem(fins);

// 读取Excel模板

HSSFWorkbook wb = new HSSFWorkbook(fs);

HSSFSheet sheet = wb.getSheetAt(0);//获取第叁页sheet页

sheet.autoSizeColumn(1);

HSSFRow rowCellStyle1 = sheet.getRow(2);//sheet页的第②行

HSSFCellStyle columnOne01 =
rowCellStyle1.getCell(0).getCellStyle();//获取sheet页第三行的体裁

// 设置边框样式(样式自身选拔)

//        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.setBottomBorderColor(HSSFColor.BLACK.index);

//        style.setLeftBorderColor(HSSFColor.BLACK.index);

//        style.setRightBorderColor(HSSFColor.BLACK.index);

//        style.setTopBorderColor(HSSFColor.BLACK.index);

for (int j = 0; j <20; j++) {

HSSFRow row = sheet.getRow(j+3);// 创造第三行

HSSFCell cellHeard1 = row.getCell(1);    //获取模板的第二个单元格b

HSSFCell cellHeard2 = row.getCell(2);

HSSFCell cellHeard3 = row.getCell(3);

HSSFCell cellHeard4 = row.getCell(4);

HSSFCell cellHeard5 = row.getCell(5);

HSSFCell cellHeard6 = row.getCell(6);

HSSFCell cellHeard7 = row.getCell(7);

// 在该单元格内输入内容

cellHeard1.setCellValue(j+1); //序号

cellHeard1.setCellStyle(columnOne01);//获取模板单元格样式

//单元格添加数据

cellHeard2.setCellValue(“1”);

cellHeard2.setCellStyle(columnOne01);

//单元格添加数据

cellHeard3.setCellValue(“2”);

cellHeard3.setCellStyle(columnOne01);

//单元格添加数据

cellHeard4.setCellValue(3);

cellHeard4.setCellStyle(columnOne01);

//单元格添加数据

cellHeard5.setCellValue(4);

cellHeard5.setCellStyle(columnOne01);

//单元格添加数据

cellHeard6.setCellValue(5);

cellHeard6.setCellStyle(columnOne01);

//单元格添加数据

cellHeard7.setCellValue(6);

cellHeard7.setCellStyle(columnOne01);

}

// 写入流

wb.write(fileOut);

// 关闭流

fileOut.close();

}

<dependency>
            <groupId>org.antlr</groupId>
            <artifactId>stringtemplate</artifactId>
            <version>3.2</version>
        </dependency>

图片 1那个是「图表秀」制作图纸的操作页面,红框内就有一百多种图表,想用哪个能够随意采纳,点击右下角的图片编辑,就能够进来那几个图片,输入本身的数据了。

  2.示范代码如下:

图片 2

package yang.zheng.util.excel;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

import org.antlr.stringtemplate.StringTemplate;
import org.antlr.stringtemplate.StringTemplateGroup;



class Student{
    private String name;

    private int age;

    private List<String> hobbys = new ArrayList<String>();

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public List<String> getHobbys() {
        return hobbys;
    }

    public void setHobbys(List<String> hobbys) {
        this.hobbys = hobbys;
    }
}

class Row{
    private String name1;

    private String name2;

    private String name3;

    public String getName1() {
        return name1;
    }

    public void setName1(String name1) {
        this.name1 = name1;
    }

    public String getName2() {
        return name2;
    }

    public void setName2(String name2) {
        this.name2 = name2;
    }

    public String getName3() {
        return name3;
    }

    public void setName3(String name3) {
        this.name3 = name3;
    }


}

class Worksheet{
    private String sheet;

    private int columnNum;

    private int rowNum;

    private List<Row> rows;

    public String getSheet() {
        return sheet;
    }

    public void setSheet(String sheet) {
        this.sheet = sheet;
    }

    public List<Row> getRows() {
        return rows;
    }

    public void setRows(List<Row> rows) {
        this.rows = rows;
    }

    public int getColumnNum() {
        return columnNum;
    }

    public void setColumnNum(int columnNum) {
        this.columnNum = columnNum;
    }

    public int getRowNum() {
        return rowNum;
    }

    public void setRowNum(int rowNum) {
        this.rowNum = rowNum;
    }

}

public class ExcelTemplate{

    public static void main(String[] args) throws FileNotFoundException{
        ExcelTemplate template = new ExcelTemplate();
        template.output2();
    }

    /**
     * @param args
     * @throws FileNotFoundException 
     */
    public void template() throws FileNotFoundException {
        StringTemplate st = new StringTemplate("hello,$name$");
        st.setAttribute("name", "china");
        System.out.println(st.toString());

        StringTemplate st2 = new StringTemplate("select $columns:{<i>$it$</i>\n}$ from users"); 
        List<String> columns = new ArrayList<String>();
        columns.add("a");
        columns.add("b");
        columns.add("c");
        columns.add("d");
        columns.add("e");
        st2.setAttribute("columns",columns);
        System.out.println(st2.toString());

        StringTemplate st3 = new StringTemplate("$students:{" +
                "$it.name$," +
                "$it.age$," +
                "$it.hobbys:{$it$,}$" +
            "}$"); 
        List<Student> students = new ArrayList<Student>();
        Student student = new Student();
        student.setName("hunter");
        student.setAge(24);
        List<String> hobbyList = new ArrayList<String>();
        hobbyList.add("sports");
        hobbyList.add("grils");
        hobbyList.add("money");
        student.setHobbys(hobbyList);
        students.add(student);

        student = new Student();
        student.setName("xiaoming");
        student.setAge(25);
        hobbyList = new ArrayList<String>();
        hobbyList.add("movie");
        hobbyList.add("coding");
        student.setHobbys(hobbyList);
        students.add(student);

        st3.setAttribute("students", students);

        System.out.println(st3.toString());

    }

    /**
     * 生成数据量大的时候,该方法会出现内存溢出
     * @throws FileNotFoundException
     */
    public void output1() throws FileNotFoundException{
        StringTemplateGroup stGroup = new StringTemplateGroup("stringTemplate");         
        StringTemplate st4 =  stGroup.getInstanceOf("excelTemplate/test");
        List<Worksheet> worksheets = new ArrayList<Worksheet>();

        File file = new File("D:/output.xls");
        PrintWriter writer = new PrintWriter(new BufferedOutputStream(new FileOutputStream(file)));

        for(int i=0;i<30;i++){
            Worksheet worksheet = new Worksheet();
            worksheet.setSheet("第"+(i+1)+"页");
            List<Row> rows = new ArrayList<Row>();
            for(int j=0;j<6000;j++){
                Row row = new Row();
                row.setName1("zhangzehao");
                row.setName2(""+j);
                row.setName3(i+" "+j);
                rows.add(row);
            }
            worksheet.setRows(rows);
            worksheets.add(worksheet);
        }

        st4.setAttribute("worksheets", worksheets);
        writer.write(st4.toString());
        writer.flush();
        writer.close();
        System.out.println("生成excel完成");
    }

    /**
     * 该方法不管生成多大的数据量,都不会出现内存溢出,只是时间的长短
     * 
     * 经测试,生成1800万数据,6~10分钟之间,3G大的文件,打开大文件就看内存是否足够大了
     * 
     * 数据量小的时候,推荐用jxls的模板技术生成excel文件,谁用谁知道,大数据量可以结合该方法使用
     * 
     * @throws FileNotFoundException
     */
    public void output2() throws FileNotFoundException{
        long startTimne = System.currentTimeMillis();

        StringTemplateGroup stGroup = new StringTemplateGroup("stringTemplate");   

        //写入excel文件头部信息
        StringTemplate head =  stGroup.getInstanceOf("excelTemplate/head");
        File file = new File("D:/output.xls");
        PrintWriter writer = new PrintWriter(new BufferedOutputStream(new FileOutputStream(file)));
        writer.print(head.toString());
        writer.flush();

        int sheets = 300;
        //excel单表最大行数是65535
        int maxRowNum = 60000;

        //写入excel文件数据信息
        for(int i=0;i<sheets;i++){
            StringTemplate body =  stGroup.getInstanceOf("excelTemplate/body");
            Worksheet worksheet = new Worksheet();
            worksheet.setSheet(" "+(i+1)+" ");
            worksheet.setColumnNum(3);
            worksheet.setRowNum(maxRowNum);
            List<Row> rows = new ArrayList<Row>();
            for(int j=0;j<maxRowNum;j++){
                Row row = new Row();
                row.setName1(""+new Random().nextInt(100000));
                row.setName2(""+j);
                row.setName3(i+""+j);
                rows.add(row);
            }
            worksheet.setRows(rows);
            body.setAttribute("worksheet", worksheet);
            writer.print(body.toString());
            writer.flush();
            rows.clear();
            rows = null;
            worksheet = null;
            body = null;
            Runtime.getRuntime().gc();
            System.out.println("正在生成excel文件的 sheet"+(i+1));
        }

        //写入excel文件尾部
        writer.print("</Workbook>");
        writer.flush();
        writer.close();
        System.out.println("生成excel文件完成");
        long endTime = System.currentTimeMillis();
        System.out.println("用时="+((endTime-startTimne)/1000)+"秒");
    }

}

其一是输入数据的页面,把数据输入到excel表格里就自动生成你想要的表了。或然也能够一贯上传你的数指标excel,不用手动输入,更简便了。

 3.内容模板(body)

图片 3

 $worksheet:{
 <Worksheet ss:Name="$it.sheet$">
  <Table ss:ExpandedColumnCount="$it.columnNum$" ss:ExpandedRowCount="$it.rowNum$" x:FullColumns="1"
   x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">
 $it.rows:{
   <Row>
    <Cell><Data ss:Type="String">$it.name1$</Data></Cell>
    <Cell><Data ss:Type="String">$it.name2$</Data></Cell>
    <Cell><Data ss:Type="String">$it.name3$</Data></Cell>
   </Row>
 }$
  </Table>
 </Worksheet>
}$