导入，导出excel

阅读原文时间：2023年07月10日阅读：1

///

/// 导出数据 ///

///
///
///
///
///
///
[HttpPost]
public void ReportWorkData(string XMMC, string HTBH, string KHMC, string FHZT)
{
//模板文件
string TempletFileName = Server.MapPath("/Template/XX的模板.xls");
string ReportFileName = Server.MapPath("/ExeclFile/XX.xls");
//导出文件
FileStream file = new FileStream(TempletFileName, FileMode.Open, FileAccess.Read);
//将文件流中模板加载到工作簿对象中
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
//建立一个名为Sheet1的工作表
ISheet ws = (ISheet)hssfworkbook.GetSheet("表名");//添加或修改WorkSheet里的数据
List list = contractapp.GetSqlReportList();//获取list
if (list.Count > 0)
{
for (int n = 0; n < list.Count; n++)
{
IRow r = ws.CreateRow(2 + n);
r.CreateCell(0).SetCellValue(n + 1);
r.CreateCell(1).SetCellValue(list[n].列头1);
r.CreateCell(2).SetCellValue(list[n].列头2);
r.CreateCell(3).SetCellValue(list[n].列头3);
r.CreateCell(4).SetCellValue(list[n].列头4);
r.CreateCell(5).SetCellValue(list[n].列头5);
r.CreateCell(6).SetCellValue(list[n].列头6);
r.CreateCell(7).SetCellValue(list[n].列头7);
r.CreateCell(8).SetCellValue(list[n].列头8);
r.CreateCell(9).SetCellValue(list[n].列头9);
r.CreateCell(10).SetCellValue(list[n].列头10);
r.CreateCell(11).SetCellValue(list[n].列头11);
r.CreateCell(12).SetCellValue(list[n].列头12);

            }  
        }

        ws.ForceFormulaRecalculation = true;

        using (FileStream filess = System.IO.File.OpenWrite(ReportFileName))  
        {  
            hssfworkbook.Write(filess);  
        }  
        System.IO.FileInfo filet = new System.IO.FileInfo(ReportFileName);  
        Response.Clear();  
        Response.Charset = "GB2312";  
        Response.ContentEncoding = System.Text.Encoding.UTF8;  
        // 添加头信息，为"文件下载/另存为"对话框指定默认文件名  
        Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode("XX.xls"));  
        // 添加头信息，指定文件大小，让浏览器能够显示下载进度  
        Response.AddHeader("Content-Length", filet.Length.ToString());  
        // 指定返回的是一个不能被客户端读取的流，必须被下载  
        Response.ContentType = "application/ms-excel";  
        //把文件流发送到客户端  
        Response.WriteFile(filet.FullName);  
        // 停止页面的执行  
        Response.End();  
    }

导入

存入模板

function uploaderXK() {
var uploader = new plupload.Uploader({
runtimes: 'html5,flash,silverlight,html4',
browse_button: 'uploadFileXK',
url: "/SystemManage/Contract/UpLoad?type=1",
chunk_size: '10mb',
filters: {
max_file_size: '10mb',
mime_types: [
{ title: "text files", extensions: "xls,xlsx" }
]
},
init: {
FileUploaded: function (up, file, info) {

                $.modalMsg("正在生成!", "success");  
                var ret = $.parseJSON(info.response);  
                console.log(ret);  
                if (ret.msg != "" && ret.msg != undefined) {  
                    $.modalMsg(ret.msg, "error");  
                }  
                if (ret.tab0.length > 0) {  
                    var tr = '';  
                    for (var i = 0; i < ret.tab0.length; i++) {  
                        var CPGG1 = ret.tab0\[i\].列头1; if (CPGG1 == undefined || CPGG1 == null) { CPGG1 = ""; }  
                        var CPGG2 = ret.tab0\[i\].列头2; if (CPGG2 == undefined || CPGG2 == null) { CPGG2 = ""; }

                        tr += '<tr>\\  
                                    <td><input type="text" class="form-control" style="border:0px" placeholder="" value="' + CPGG1 + '" /></td>\\  
                                    <td><input type="text" class="form-control" style="border:0px" placeholder="" value="' + CPGG2 + '" /></td>\\  
                                    <td><input type="text" class="form-control" style="border:0px" placeholder="" value="' + CPGG3 + '" /></td>\\  
                                    <td><input type="text" class="form-control" style="border:0px" placeholder="" value="' + CPGG4 + '" /></td>\\  
                                    <td><input type="text" class="form-control" style="border:0px" placeholder="" value="' + CPGG5 + '" /></td>\\  
                                    <td><input type="text" class="form-control" style="border:0px" placeholder="" value="' + CPGG6 + '" /></td>\\  
                                    <td><input type="text" class="form-control" style="border:0px" placeholder="" value="' + CPGG7 + '" /></td>\\  
                                    <td><a class="sc" onclick="lxrsc(this)"><i class="fa fa-trash-o"></i></a></td>\\  
                                </tr>';  
                    }  
                    $("#table").append(tr);//添加显示在table  
                }  
            },  
            PostInit: function () { },  
            FilesAdded: function (up, files) {  
                $('#loading').modal('show');  
                plupload.each(files, function (file) { });  
                uploader.start();  
            },  
            UploadProgress: function (up, file) { },  
            Error: function (up, err) {  
                $('#loading').modal('hide');  
                $.modalMsg("文件仅支持（xls,xlsx）且不超过10M!", "error");  
            },  
            UploadComplete: function (up, data) { },  
            UploadSuccess: function (file, data, response) { }  
        }  
    });  
    uploader.init();  
}

using System.IO;
using NPOI;
using NPOI.SS.UserModel;

private void btn_NPOI_Click(object sender, EventArgs e)
{
string importExcelPath = "E:\\import.xlsx";
string exportExcelPath = "E:\\export.xlsx";
IWorkbook workbook = WorkbookFactory.Create(importExcelPath);
ISheet sheet = workbook.GetSheetAt(0);//获取第一个工作薄
IRow row = (IRow)sheet.GetRow(0);//获取第一行

//设置第一行第一列值,更多方法请参考源官方Demo
row.CreateCell(0).SetCellValue("test");//设置第一行第一列值

//导出excel
FileStream fs = new FileStream(exportExcelPath, FileMode.Create, FileAccess.ReadWrite);
workbook.Write(fs);
fs.Close();
}

手机扫一扫

移动阅读更方便