.NET Web项目Excel导入与导出

这是基于EntityFrameWork的.NET Web项目,View层使用JQuery借助 Formdata和Ajax实现Excel导入和导出功能

文件导入

View层:

<input id="importFile" name="excelFile" type="file" value="选择要导入的文件">    //选择文件按钮

 <input type="button" id="saveExcelData" value="导入银行流水" />                       //触发文件导入功能的按钮

这里借用Formdata传递file对象,实际上传递的是一个二进制文件

 $("#saveExcelData").click(function () {
            var formData = new FormData();
            var name = $("#importFile").val();
            console.log($("#importFile")[0].files[0])
            formData.append("file", $("#importFile")[0].files[0]);
            formData.append("name", name); //可以使用formData.append()传递多个参数
            console.log(formData);
            if (isNull(name)) {
                alert("未选择导入文件!");
                return;
            }
            //var xhr = new XMLHttpRequest();
            //xhr.open("post","/计提费用表/UploadExcel");
            //xhr.setRequestHeader("Content-Type","application/x-www-form-urlencoded");
            //xhr.send(formData);
            $.ajax({
                url: "/计提费用表/UploadExcel",
                datatype: "json",
                enctype: "multipart/form-data",
                data: formData,
                type: "post",
                // 告诉jQuery不要去处理发送的数据
                processData: false,
                // 告诉jQuery不要去设置Content-Type请求头
                contentType: false,
                success: function (result) {
                        alert(result);
                        location.href = document.URL;
                }
            });


        });

控制器接口:

//Excel上传
public string UploadExcel()
{
    var tran = db.Database.BeginTransaction();
    try
    {
        Stream uploadStream = null;
        //获取前台传递的File
        HttpPostedFileBase postFileBase = Request.Files["file"];

        uploadStream = postFileBase.InputStream;

        //设置上传文件名称默认为 银行流水+时间戳
        string fileName = string.Format("银行流水-{0}{1}", DateTime.Now.ToString("yyyyMMddHHmmss"), Path.GetExtension(postFileBase.FileName));
        //获取文件类型
        var dd = Path.GetExtension(postFileBase.FileName);
        //获取项目本地存储路径
        string baseUrl = Server.MapPath("~");
        //设置文件存储地址(此项目的Upload文件夹下)
        string uploadPath = baseUrl + @"Upload\";
        //判断此文件夹是否存在,没有则新建该文件夹
        if (!Directory.Exists(uploadPath)) Directory.CreateDirectory(uploadPath);
        //获取文件绝对路径  路径+文件名
        var filename = Path.Combine(uploadPath, fileName);
        //如果已经存在该文件,删除
        if (System.IO.File.Exists(filename))
        {
            System.IO.File.Delete(filename);
        }
        //在本地创建该文件
        using (FileStream fs1 = new FileStream(filename, FileMode.Create, FileAccess.ReadWrite))
        {
            int bufferLen = 10240;
            byte[] buffer = new byte[bufferLen];
            int contentLen = 0;

            while ((contentLen = uploadStream.Read(buffer, 0, bufferLen)) != 0)
            {
                fs1.Write(buffer, 0, bufferLen);
                fs1.Flush();
            }
        }

        //把Excel文件写入到DataTable中
        DataTable dt = ExcelToDataTable(filename, true);
        var _rows = dt.AsEnumerable().AsQueryable();
        //然后后面操作_rows写库什么的就不写了,毕竟每个人的数据都不一样
        if (_rows.Count() == 0)
        {
            return "文件内容为空!";
        }
        DateTime lastData;
        DateTime firstData;       
        //获取文件第一条数据和最后一条数据的交易日期,判断一共上传了几个月的流水

        firstData = Convert.ToDateTime(_rows.FirstOrDefault()[0]);
        lastData = Convert.ToDateTime(_rows.ToList()[_rows.Count() - 1][0]);
        //第一个日期的第一天
        DateTime firstDayOfminMonth = new DateTime(firstData.Year, firstData.Month, 1);
        //第一个日期的最后一天
        DateTime lastDayOfminMonth = firstDayOfminMonth.AddMonths(1).AddDays(-1);
        //判断两个日期相差几个月
        int months = (lastData.Year - firstData.Year) * 12 + (lastData.Month - firstData.Month);
       
        db.SaveChanges();
        tran.Commit();
        db.Dispose();
        return "导入成功!";
    }
    catch (Exception ex)
    {
        tran.Rollback();
        return ex.Message + "请联系开发人员!";
    }
}

 

这里有一个Excel转成DateTable对象的方法

这里会安装一些Nuget包,按照提示安装就好了,但是安装完 之后可能会遇到这个错误

.NET Web项目Excel导入与导出

这是因为 ICSharpCode.SharpZipLib 和 NPOI.OpenXml4Net 版本冲突,两者最好升级到最新版本冲突就会消失了

/// <summary>  
        /// 将excel导入到datatable  
        /// </summary>  
        /// <param name="filePath">excel路径</param>  
        /// <param name="isColumnName">第一行是否是列名</param>  
        /// <returns>返回datatable</returns>  
        public static DataTable ExcelToDataTable(string filePath, bool isColumnName)
        {
            DataTable dataTable = null;
            FileStream fs = null;
            DataColumn column = null;
            DataRow dataRow = null;
            IWorkbook workbook = null;
            ISheet sheet = null;
            IRow row = null;
            ICell cell = null;
            int startRow = 0;
            try
            {
                using (fs = System.IO.File.OpenRead(filePath))
                {
                    // 2007版本  
                    if (filePath.IndexOf(".xlsx") > 0)
                        workbook = new XSSFWorkbook(fs);
                    // 2003版本  
                    else if (filePath.IndexOf(".xls") > 0)
                        workbook = new HSSFWorkbook(fs);

                    if (workbook != null)
                    {
                        //读取第一个sheet,当然也可以循环读取每个sheet  
                        sheet = workbook.GetSheetAt(0);
                        dataTable = new DataTable();
                        if (sheet != null)
                        {
                            int rowCount = sheet.LastRowNum;//总行数  
                            if (rowCount > 0)
                            {
                                IRow firstRow = sheet.GetRow(0);//第一行  
                                int cellCount = firstRow.LastCellNum;//列数  

                                //构建datatable的列  
                                if (isColumnName)
                                {
                                    startRow = 1;//如果第一行是列名,则从第二行开始读取  
                                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                    {
                                        //获取各个列内的值
                                        cell = firstRow.GetCell(i);
                                        if (cell != null)
                                        {
                                            if (cell.StringCellValue != null)
                                            {
                                                column = new DataColumn(cell.StringCellValue);
                                                dataTable.Columns.Add(column);
                                            }
                                        }
                                    }
                                }
                                else
                                {
                                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                    {
                                        column = new DataColumn("column" + (i + 1));
                                        dataTable.Columns.Add(column);
                                    }
                                }

                                //填充行  
                                for (int i = startRow; i <= rowCount; ++i)
                                {
                                    row = sheet.GetRow(i);
                                    if (row == null) continue;

                                    dataRow = dataTable.NewRow();
                                    for (int j = row.FirstCellNum; j < cellCount; ++j)
                                    {
                                        cell = row.GetCell(j);
                                        if (cell == null || cell.CellType.ToString() == "Blank")
                                        {
                                            dataRow[j] = "";
                                        }
                                        else
                                        {
                                            //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)  
                                            switch (cell.CellType)
                                            {
                                                case CellType.Blank:
                                                    dataRow[j] = "";
                                                    break;
                                                case CellType.Numeric:
                                                    short format = cell.CellStyle.DataFormat;
                                                    //对时间格式(2015.12.5、2015/12/5、2015-12-5等)的处理  
                                                    if (format == 14 || format == 31 || format == 57 || format == 58)
                                                        dataRow[j] = cell.DateCellValue.ToString("yyyy-MM-dd");
                                                    else
                                                        dataRow[j] = cell.NumericCellValue;
                                                    break;
                                                case CellType.String:
                                                    dataRow[j] = cell.StringCellValue;
                                                    break;
                                            }
                                        }
                                    }
                                    dataTable.Rows.Add(dataRow);
                                }
                            }
                        }
                    }
                }
                return dataTable;
            }
            catch (Exception e)
            {
                if (fs != null)
                {
                    fs.Close();
                }
                throw e;
            }
        }

 

导出Excel

view:

 <input type="button" id="btnExport" value="导出计提费用核算表" />

  $("#btnExport").click(function () {
            $.ajax({
                url: "/计提费用表/导出计提费用核算表",
                type: "get",
                data: { "productId": $("#productId").val(), "sysDate1": $("#sysDate1").val(), "sysDate2": $("#sysDate2").val() },
                datatype: "json",
                success: function (data) {
                    if (data) {
                        if (data.length > 100)
                            location.href = "/Account/AuthorizeFail";
                        else
                            location.href = "../../Excels/Common/" + data;
                    }
                    else {
                        alert("没有查询到任何数据.");
                    }
                }
            });
        });

 

接口:

  //Excel导出
        public string 导出计提费用核算表(string productId, string sysDate1, string sysDate2)
        {
            DateTime dt1;
            DateTime dt2;
            // var data = new 计提费用公共类().获取计提费用核算数据集1(productId, account, sysDate1, sysDate2, false).OrderBy(c => c.流水变动日期).ToList();
            var data = db.计提费用核算表.OrderBy(x => x.产品名称).ThenBy(x => x.流水变动日期).ThenBy(x => x.计提起始日).ThenBy(x => x.计提截止日).AsEnumerable();
            if (!string.IsNullOrEmpty(productId))
            {
                data = data.Where(x => x.产品名称 == productId).OrderBy(x => x.产品名称).ThenBy(x => x.流水变动日期).ThenBy(x => x.计提起始日).ThenBy(x => x.计提截止日).AsEnumerable();
            }
            if (!string.IsNullOrEmpty(sysDate1))
            {
                DateTime.TryParse(sysDate1, out dt1);
                data = data.Where(x => x.流水变动日期 >= dt1).OrderBy(x => x.产品名称).ThenBy(x => x.流水变动日期).ThenBy(x => x.计提起始日).ThenBy(x => x.计提截止日).AsEnumerable();
            }
            if (!string.IsNullOrEmpty(sysDate2))
            {
                DateTime.TryParse(sysDate2, out dt2);
                data = data.Where(x => x.流水变动日期 <= dt2).OrderBy(x => x.产品名称).ThenBy(x => x.流水变动日期).ThenBy(x => x.计提起始日).ThenBy(x => x.计提截止日).AsEnumerable();
            }
            if (data.Count() == 0)
                return "没有查询到相关数据";

            //导出文件的名称
            string FileName = "计提费用核算表.xls";
            //获取要输出的列数
            PropertyInfo[] propertys = data.ToList()[0].GetType().GetProperties();
            IWorkbook wb = new HSSFWorkbook();
            //设置不同的列的格式(日期,金额,数字等)
            ICellStyle ContentStyle = WriteExcel.Getcellstyle(wb, stylexls.默认, true);
            ICellStyle BoldStyle = WriteExcel.Getcellstyle(wb, stylexls.加粗居中, true);
            ICellStyle MoneyStyle = WriteExcel.Getcellstyle(wb, true, false, 10, HorizontalAlignment.Center, VerticalAlignment.Center, BorderStyle.Thin, BorderStyle.Thin, BorderStyle.Thin, BorderStyle.Thin, stylexls.数字);
            ICellStyle BoldMoneyStyle = WriteExcel.Getcellstyle(wb, true, true, 10, HorizontalAlignment.Center, VerticalAlignment.Center, BorderStyle.Thin, BorderStyle.Thin, BorderStyle.Thin, BorderStyle.Thin, stylexls.数字);
            ICellStyle StrStyle = WriteExcel.Getcellstyle(wb, true, false, 10, HorizontalAlignment.Center, VerticalAlignment.Center, BorderStyle.Thin, BorderStyle.Thin, BorderStyle.Thin, BorderStyle.Thin);
            ICellStyle NonBoldStyle = WriteExcel.Getcellstyle(wb, false, true, 10, HorizontalAlignment.Right, VerticalAlignment.Center, BorderStyle.None, BorderStyle.None, BorderStyle.None, BorderStyle.None);
            ICellStyle TitleStyle = WriteExcel.Getcellstyle(wb, false, false, 24, HorizontalAlignment.Center, VerticalAlignment.Center, BorderStyle.None, BorderStyle.None, BorderStyle.None, BorderStyle.None);

            int rowindex = 0;
            int ColumnLength = propertys.Count();
            //sheet页名称
            string Sheet = "计提费用核算表";
            ISheet sh = wb.CreatePrintSetupSheet(Sheet);
            IRow row = sh.CreateRow(rowindex);
            ICell icelltop = row.CreateCell(0);

            //设置标题
            sh.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowindex, rowindex, 0, ColumnLength - 4));
            row.Height = 26 * 26;
            WriteExcel.WriteCell(row, 0, TitleStyle, Sheet);
            rowindex++;

            //设置表头列明
            row = sh.CreateRow(rowindex);
            row.Height = 25 * 20;
            int k = 0;
            foreach (PropertyInfo pi in propertys)
            {
                if (pi.Name == "id"|| pi.Name == "申购或赎回" || pi.Name == "isMaintain")
                {
                    continue;
                }
                icelltop = row.CreateCell(k);
                icelltop.CellStyle = WriteExcel.Getcellstyle(wb, stylexls.加粗居中, true);
                icelltop.CellStyle.VerticalAlignment = VerticalAlignment.Center;
                icelltop.SetCellValue(pi.Name);
                sh.SetColumnWidth(k, 25 * 206);
                k++;
            }
            rowindex++;

            var loopDataTime = data.OrderBy(x => x.流水变动日期);
            //查询结果的流水起始变动日期
            DateTime minDateTime = loopDataTime.FirstOrDefault().流水变动日期;
            //当月第一天
            DateTime firstDayOfminMonth = new DateTime(minDateTime.Year, minDateTime.Month, 1);
            //当月最后一天
            DateTime lastDayOfminMonth = firstDayOfminMonth.AddMonths(1).AddDays(-1);
            //查询结果的流水变动结束的日期
            DateTime maxDateTime = loopDataTime.ToList()[data.Count() - 1].流水变动日期;
            //两个日期相差几个月,就生成sheet表里几段 数据集
            int months = (maxDateTime.Year - minDateTime.Year) * 12 + (maxDateTime.Month - minDateTime.Month);

            //用于判断是否应该加入统计行
            int newRowIndex = 2;
            //用于判断应该写入Excel的第几行
            int addIndex = 2;
            int newDataCount = 0;
            //第几月
            string thisMonth;
            for (int i = 0; i <= months; i++)
            {
                //当前月第一天
                DateTime thisMonthFirstDay = firstDayOfminMonth.AddMonths(i);
                thisMonth = thisMonthFirstDay.Month.ToString() + "月合计";
                //当前月第最后一天
                DateTime thisMonthLastDay = lastDayOfminMonth.AddMonths(i);
                //当月所有产品数据
                var newData = data.Where(x => x.流水变动日期 >= thisMonthFirstDay && x.流水变动日期 <= thisMonthLastDay).OrderBy(x => x.产品名称).OrderBy(x => x.流水变动日期).AsEnumerable();
                if (newData.Count() > 0)
                {
                    //同一月份有几个产品 就生成几个数据集
                    var 产品名称集合 = newData.OrderBy(x => x.产品名称).Select(x => x.产品名称).Distinct().AsEnumerable();
                    foreach (var 产品名称 in 产品名称集合)
                    {
                        var 单个产品 = newData.Where(x => x.产品名称 == 产品名称).OrderBy(x => x.流水变动日期).AsEnumerable();
                        newDataCount = newRowIndex + 单个产品.Count();
                        int 规模变动合计 = 0;
                        int 计提天数合计 = 0;
                        decimal 管理费合计 = 0;
                        decimal 托管费合计 = 0;
                        decimal 行管费合计 = 0;
                        decimal 计提费用合计 = 0;
                        string 规模变动;
                        foreach (var 计提记录 in 单个产品)
                        {
                            int columnindex = 0;
                            row = sh.CreateRow(addIndex);
                            row.Height = 25 * 20;
                            规模变动 = 计提记录.申购或赎回 == "赎回" ? "-" + 计提记录.规模变动.ToString() : 计提记录.规模变动.ToString();
                            WriteExcel.WriteCell(row, columnindex++, StrStyle, 计提记录.流水变动日期.ToString("yyyy-MM-dd"));
                            WriteExcel.WriteCell(row, columnindex++, StrStyle, 计提记录.产品名称);
                            WriteExcel.WriteCell(row, columnindex++, StrStyle, 规模变动);
                            WriteExcel.WriteCell(row, columnindex++, StrStyle, 计提记录.计提起始日.ToString("yyyy-MM-dd"));
                            WriteExcel.WriteCell(row, columnindex++, StrStyle, 计提记录.计提截止日.ToString("yyyy-MM-dd"));
                            WriteExcel.WriteCell(row, columnindex++, StrStyle, 计提记录.计提天数);
                            WriteExcel.WriteCell(row, columnindex++, StrStyle, 计提记录.计提规模);
                            WriteExcel.WriteCell(row, columnindex++, StrStyle, 计提记录.管理费率);
                            WriteExcel.WriteCell(row, columnindex++, StrStyle, 计提记录.托管费率);
                            WriteExcel.WriteCell(row, columnindex++, StrStyle, 计提记录.行管费率);
                            WriteExcel.WriteCell(row, columnindex++, MoneyStyle, 计提记录.管理费);
                            WriteExcel.WriteCell(row, columnindex++, MoneyStyle, 计提记录.托管费);
                            WriteExcel.WriteCell(row, columnindex++, MoneyStyle, 计提记录.行管费);
                            WriteExcel.WriteCell(row, columnindex++, MoneyStyle, 计提记录.计提费用合计);
                            规模变动合计 = 计提记录.申购或赎回 == "申购" ? 规模变动合计 + 计提记录.规模变动 : 规模变动合计 - 计提记录.规模变动;
                            计提天数合计 = 计提天数合计 + 计提记录.计提天数;
                            管理费合计 = 管理费合计 + 计提记录.管理费;
                            托管费合计 = 托管费合计 + 计提记录.托管费;
                            行管费合计 = 行管费合计 + 计提记录.行管费;
                            计提费用合计 = 管理费合计 + 托管费合计 + 行管费合计;
                            newRowIndex++;
                            addIndex++;
                            //生成最后一行统计行
                            if (newRowIndex == newDataCount)
                            {
                                columnindex = 0;
                                row = sh.CreateRow(addIndex);
                                row.Height = 25 * 20;
                                WriteExcel.WriteCell(row, columnindex++, StrStyle, thisMonth);
                                WriteExcel.WriteCell(row, columnindex++, StrStyle, null);
                                WriteExcel.WriteCell(row, columnindex++, StrStyle, 规模变动合计);
                                WriteExcel.WriteCell(row, columnindex++, StrStyle, null);
                                WriteExcel.WriteCell(row, columnindex++, StrStyle, null);
                                WriteExcel.WriteCell(row, columnindex++, StrStyle, 计提天数合计);
                                WriteExcel.WriteCell(row, columnindex++, StrStyle, null);
                                WriteExcel.WriteCell(row, columnindex++, StrStyle, null);
                                WriteExcel.WriteCell(row, columnindex++, StrStyle, null);
                                WriteExcel.WriteCell(row, columnindex++, StrStyle, null);
                                WriteExcel.WriteCell(row, columnindex++, MoneyStyle, 管理费合计);
                                WriteExcel.WriteCell(row, columnindex++, MoneyStyle, 托管费合计);
                                WriteExcel.WriteCell(row, columnindex++, MoneyStyle, 行管费合计);
                                WriteExcel.WriteCell(row, columnindex++, MoneyStyle, 计提费用合计);
                            }
                        }
                        //空格2行生成下个月份的数据集
                        addIndex++;
                        addIndex++;
                        addIndex++;
                        addIndex++;
                    }
                }
                else
                {
                    continue;
                }
            }

            string excelpath = Server.MapPath(@"~/Excels/Common/");
            if (!Directory.Exists(excelpath))
                Directory.CreateDirectory(excelpath);
            using (FileStream fs = System.IO.File.OpenWrite(excelpath + FileName))
            {
                wb.Write(fs);   //向打开的这个xls文件中写入mySheet表并保存。
            }

            Logger.WriteLogs(User.Identity.Name, "计提费用核算表导出", FundClear.Models.操作类型.导出);
            return "../../Excels/Common/" + FileName;
        }

这里写了一个WriteExcell方法,因为数据类型不一样,对这个方法写了一些重载方法

        /// <summary>
        /// 创建并填写单元格
        /// </summary>
        /// <param name="row">单元行</param>
        /// <param name="ColumnIndex">单元格索引值</param>
        /// <param name="CellStyle">单元格样式</param>
        /// <param name="Value">值</param>
        public static void WriteCell(IRow row, int ColumnIndex, ICellStyle CellStyle, string Value)
        {
            ICell icelltop = row.CreateCell(ColumnIndex);
            icelltop.CellStyle = CellStyle;
            icelltop.SetCellValue(Value);
        }

 

上一篇:Mybatis --- 自定义类型转换


下一篇:Python生成Excel列标题(A~ZZ)