C# 利用epplus导出excel,自动求和

        /// <summary>
        /// 生成xlsx
        /// </summary>
        /// <param name="dvLine">数据视图</param>
        /// <param name="sheetName">sheetName</param>
        /// <param name="englishName">要导出的视图对应列名称</param>
        /// <param name="chinseName">要导出的数据对应列在excel里显示的中文名称</param>
        /// <param name="sumColNames">要求合的列名称</param>
        /// <param name="totNameIndex">显示"合计:"的列的序号，比如对a-c列合并显示"合计:"传3(c)</param>
        public ExcelPackage CreateXlsx(DataView dvLine, string sheetName, string[] englishName, string[] chinseName, List<string> sumColNames, int totNameIndex)
        {
            if (dvLine.Count <= 0) return null;
            ExcelPackage pck = new ExcelPackage();
            var ws = pck.Workbook.Worksheets.Add(sheetName);
            ExcelWorksheet worksheet = ws as ExcelWorksheet;
            int rowIndex = 1;
            int colIndex = 1;
            ExcelRange rg = null;
            //标题
            rg = worksheet.Cells[rowIndex, colIndex, rowIndex, colIndex + chinseName.Length];
            rg.Value = sheetName;
            rg.Merge = true;
            rg.Style.Font.Bold = true;
            rg.Style.Font.Size = 14;
            rowIndex++;
            //列标题
            rg = worksheet.Cells[rowIndex, colIndex++];
            rg.Value = "序号";
            rg.Style.Font.Bold = true;
            rg.Style.Font.Size = 13;
            for (int i = 0; i < chinseName.Length; i++)
            {
                rg = worksheet.Cells[rowIndex, colIndex++];
                rg.Value = chinseName[i];
                rg.Style.Font.Bold = true;
                rg.Style.Font.Size = 13;
            }
            //填充数据
            rowIndex++;
            Dictionary<string, Decimal> dic = new Dictionary<string, Decimal>(); //存放求和列的合计值
            //求和初始值设为0
            foreach (string col in sumColNames)
            {
                dic.Add(col, 0);
            }
            int rowNum = 1;
            for (int i = 0; i < dvLine.Count; i++) //循环数据表
            {
                colIndex = 1;
                //序号
                rg = worksheet.Cells[rowIndex, colIndex++];
                rg.Value = rowNum++;
                rg.Style.Font.Size = 12;
                //数据
                for (int j = 0; j < englishName.Length; j++)  //对视图里指定的列填充数据
                {
                    rg = worksheet.Cells[rowIndex, colIndex];
                    string data = (dvLine[i][englishName[j]] != null) ? dvLine[i][englishName[j]].ToString() : "";
                    //如果是需要求和的列，将值转换为数字型
                    if (sumColNames.Contains(englishName[j]))
                    {
                        Decimal rtnV = 0;
                        Decimal.TryParse(data, out rtnV);
                        rg.Value = rtnV;
                        dic[englishName[j]] += rtnV;//未和
                        //循环到DV的最后一行时，填充合计值
                        if (i == dvLine.Count - 1)
                        {
                            rg = worksheet.Cells[rowIndex + 1, colIndex];
                            rg.Value = dic[englishName[j]];
                        }
                    }
                    else
                    {
                        rg.Value = data;
                    }
                    rg.Style.Font.Size = 12;
                    colIndex++;
                }
                rowIndex++;
            }

            rg = worksheet.Cells[rowIndex, totNameIndex];
            rg.Value = "合计:";

            worksheet.Cells.AutoFitColumns();
            worksheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;  //居中
            worksheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;  //居中


            return pck;
        }

/// <summary>
        /// 保存为xlsx文件
        /// </summary>
        /// <param name="dvLine">数据视图</param>
        /// <param name="fileName">文件名称不带后缀名</param>
        /// <param name="englishName">要导出的视图对应列名称</param>
        /// <param name="chinseName">要导出的数据对应列在excel里显示的中文名称</param>
        /// <param name="sumColNames">要求合的列名称</param>
        /// <param name="totNameIndex">显示"合计:"的列的序号，比对a-c列合并显示"合计:"传3(c)</param>
        /// <param name="filePath">导出文件路径,D:\1\</param>
        public void SaveAsXlsx(DataView dvLine, string fileName, string[] englishName, string[] chinseName, List<string> sumColNames, int totNameIndex, string filePath)
        {
            ExcelPackage pck = CreateXlsx(dvLine, fileName, englishName, chinseName, sumColNames, totNameIndex);
            if (!System.IO.Directory.Exists(filePath))
            {
                System.IO.Directory.CreateDirectory(filePath);
            }
            using (System.IO.FileStream file = new System.IO.FileStream(filePath + fileName + ".xlsx", System.IO.FileMode.Create))
            {
                byte[] byData = pck.GetAsByteArray();
                file.Seek(0, System.IO.SeekOrigin.Begin);
                file.Write(byData, 0, byData.Length);
                file.Close();
            }
            pck.Dispose();


            //web页面的导出方法
            /*
            System.Web.HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            System.Web.HttpContext.Current.Response.AddHeader("content-disposition", "attachment;  filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + ".xlsx");
            System.Web.HttpContext.Current.Response.BinaryWrite(pck.GetAsByteArray());
            System.Web.HttpContext.Current.Response.Flush();
            System.Web.HttpContext.Current.Response.Close();*/
        }

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51

/// <summary> /// 读取xlsx数据到DataTable /// </summary> /// <param name="fileName"></param> /// <param name="sheetIndex">要读取的excel文件里sheet索引</param> /// <returns></returns> public DataTable Read(string fileName, int sheetIndex) {     DataTable dt = null;     if (File.Exists(fileName))     {         try         {             FileInfo excel = new FileInfo(fileName);             ExcelPackage package = new ExcelPackage(excel);             int sheetCount = package.Workbook.Worksheets.Count; //获取总Sheet页             if (sheetCount >= sheetIndex)             {                 ExcelWorksheet worksheet = package.Workbook.Worksheets[sheetIndex];//选定指定页                 int maxColumnNum = worksheet.Dimension.End.Column;//最大列                 int minColumnNum = worksheet.Dimension.Start.Column;//最小列                 int maxRowNum = worksheet.Dimension.End.Row;//最小行                 int minRowNum = worksheet.Dimension.Start.Row;//最大行                 dt = new DataTable();                 for (int i = minColumnNum; i <= maxColumnNum; i++)                 {                     DataColumn dc = new DataColumn(i.ToString(), typeof(string));                     dt.Columns.Add(dc);                 }                 for (int i = minRowNum; i <= maxRowNum; i++)                 {                     DataRow dr = dt.NewRow();                     for (int j = minColumnNum; j <= maxColumnNum; j++)                     {                         ExcelRange range = worksheet.Cells[i, j];                         if (range != null && range.Value != null)                         {                             dr[j.ToString()] = range.Value.ToString();                         }                     }                     dt.Rows.Add(dr);                 }             }         }         catch (Exception ex)         {             Console.WriteLine(ex.Message);         }     }     return dt; }

　　epplus下载  http://epplus.codeplex.com/