C# 之 EXCEL导入导出

最新推荐文章于 2024-10-21 15:06:42 发布

weixin_30701575

最新推荐文章于 2024-10-21 15:06:42 发布

阅读量306

点赞数

CC 4.0 BY-SA版权

文章标签： c# 数据库

原文链接：http://www.cnblogs.com/lxhbky/p/6133778.html

本文分享了在Web项目和WebAPI项目中实现EXCEL数据导出和导入的具体方法，包括使用C#代码导出EXCEL、利用NPOI插件进行EXCEL处理等实用技巧。

以下方式是本人总结的一些经验，肯定有很多种方法，在此先记下，留待以后补充...

希望朋友们一起来探讨相关想法，请在下方留言.

A-1：EXCEL模板导出

非常简单,将EXCEL模板上传到项目中后，将其浏览URL保存下来（excelUrl）,然后：

window.location.href="http://localhost:10086/yuanxin/Resources/BusRoute.xlsx"  //EXCEL浏览路径

或者使用<a href="excelUrl"></a>都行。

A-2：EXCEL数据导出--Web项目--C#代码导出：

/// <summary>
    /// EXCEL帮助类
    /// </summary>
    /// <typeparam name="T">泛型类</typeparam>
    /// <typeparam name="TCollection">泛型类集合</typeparam>
    public class ExcelHelp<T, TCollection> where T : new() where TCollection : List<T>, new()
    {
        //http请求Request对象
        public static HttpRequest baseRequest = HttpContext.Current.Request;
        //http请求Response对象
        public static HttpResponse baseResponse = HttpContext.Current.Response;/// <summary>
        /// 将数据导出EXCEL
        /// </summary>
        /// <param name="columnNameAndShowNameDic">列名+显示名</param>
        /// <param name="tColl">数据集（tColl里的类属性名必须和字典中的列名一致）</param>
        public static void ExportExcelData(Dictionary<string, string> columnNameAndShowNameDic, TCollection tColl)
        {
            WorkBook workbook = WorkBook.CreateNew();
            WorkSheet worksheet = workbook.Sheets["sheet1"]; List<string> columnNameList = columnNameAndShowNameDic.Keys.ToList(); List<string> showNameList = columnNameAndShowNameDic.Values.ToList(); //暂定26行 string[] zm = new string[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" }; //设置首列显示 for (var i = 0; i < columnNameList.Count; i++) { worksheet.Cells[zm[i] + "1"].Value = showNameList[i]; //加粗 worksheet.Cells[zm[i] + "1"].Style.Font.Bold = true; } for (int i = 0; i < tColl.Count; i++) { for (int j = 0; j < columnNameList.Count; j++) { worksheet.Cells[zm[j] + (i + 2)].Value = getPropertyValue(tColl[i], columnNameList[j]); } } byte[] buffer = workbook.SaveAsBytes(); baseResponse.Clear(); baseResponse.Buffer = true; baseResponse.ContentEncoding = System.Text.Encoding.UTF8; //baseResponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; baseResponse.ContentType = "application/vnd.ms-excel"; //设置导出文件名 baseResponse.AddHeader("content-disposition", "attachment; filename=" + "MaintainReport" + ".xlsx"); baseResponse.AddHeader("Content-Length", buffer.Length.ToString()); baseResponse.BinaryWrite(buffer); baseResponse.Flush(); baseResponse.End(); } /// <summary> /// 获取属性值 /// </summary> /// <param name="t">T对象实例</param> /// <param name="propertyName">属性名</param> /// <returns></returns> public static string getPropertyValue(T t, string propertyName) { PropertyInfo info = t.GetType().GetProperty(propertyName); //获取属性值转换暂设置如下字段，可根据实际情况添加 if (info.PropertyType == typeof(DateTime)) { return Convert.ToDateTime(info.GetValue(t)).ToString("yyyy-MM-dd HH:mm"); } return info.GetValue(t).ToString(); } }

A-2--EXCEL数据导出--WebAPI项目--NPOI--接口导出：

说明：以下方法借助第三方插件：NPOI专门处理EXCEL等办公文档的类库，因此项目中需引用该插件，NuGet--所有NPOI即可安装。

NPOI百度百科

/// <summary>
        /// 班车数据导出成EXCEL
        /// </summary>
        /// <param name="conferenceID"></param>
        /// <returns></returns>
        [HttpGet]
        public HttpResponseMessage ExportExcelDataForBusRoute(string conferenceID)
        {
            HttpResponseMessage result = new HttpResponseMessage();
            ControllerHelp.RunAction(() =>
            {
                BusRouteModelCollection busColl = BusRouteModelAdapter.Instance.Load(m => m.AppendItem("ConferenceID", conferenceID));
                //如果想要某个单元格内容显示多列，在内容中加入： "\n" 换行字符
                Dictionary<string, string> dicColl = new Dictionary<string, string>() { {"路线标题","Title" }, {"发车时间","DepartDate" }, {"出发地","BeginPlace" }, {"对接人","ContactsName" }, {"对接人电话","ContactsPhone" } }; result = ExcelHelp<BusRouteModel, BusRouteModelCollection>.ExportExcelData(dicColl, busColl, "BusRoute"); }); return result; }

　　　　　/// <summary>
        /// 得到excel文件流
        /// </summary>
        /// <returns></returns>
        private MemoryStream ExcelStream(Dictionary<string, string> displayAndColumnName, TCollection tlist)
        {
            //var list = dc.v_bs_dj_bbcdd1.Where(eps).ToList();
            HSSFWorkbook workbook = new HSSFWorkbook();

            ISheet sheet1 = workbook.CreateSheet("Sheet1");

            //1-创建首行
            IRow headRow = sheet1.CreateRow(0);
            //1.1-设置首行样式
            IFont font = workbook.CreateFont();
            font.Boldweight = (short)FontBoldWeight.Bold;   //字体加粗
            ICellStyle headCellStyle = workbook.CreateCellStyle(); headCellStyle.SetFont(font); headCellStyle.Alignment = HorizontalAlignment.Center; //字体居中  List<string> displayNameList = displayAndColumnName.Keys.ToList(); //1.2-设置首行标题 for (var i = 0; i < displayAndColumnName.Keys.Count; i++) { ICell cell = headRow.CreateCell(i); cell.SetCellValue(displayNameList[i].ToString()); cell.CellStyle = headCellStyle; } //2-设置内容列 List<string> columnNameList = displayAndColumnName.Values.ToList(); //2.1-内容行样式 ICellStyle contentCellStyle = workbook.CreateCellStyle(); contentCellStyle.Alignment = HorizontalAlignment.Center; //字体居中 contentCellStyle.WrapText = true; //自动换行 for (var i = 0; i < tlist.Count; i++) { IRow row = sheet1.CreateRow(i + 1); //设置内容 for (var j = 0; j < columnNameList.Count; j++) { ICell cell = row.CreateCell(j); cell.SetCellValue(getPropertyValue(tlist[i], columnNameList[j])); cell.CellStyle = contentCellStyle; } } //3-自动列宽-根据内容长度自动展开 for (int i = 0; i < displayNameList.Count; i++) { sheet1.AutoSizeColumn(i); } MemoryStream file = new MemoryStream(); workbook.Write(file); //这句代码非常重要，如果不加，会报：打开的EXCEL格式与扩展名指定的格式不一致 file.Seek(0, SeekOrigin.Begin); return file; } /// <summary> /// 导出excel功能控制器 /// </summary> /// <returns></returns> public HttpResponseMessage ExportExcelDataForWebAPI(Dictionary<string, string> displayAndColumnName, TCollection tlist,string exportFileName) { var file = ExcelStream(displayAndColumnName, tlist); HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK); result.Content = new StreamContent(file); result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd.ms-excel"); result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment"); result.Content.Headers.ContentDisposition.FileName = exportFileName+".xls"; return result; }

B-1：EXCEL数据导入--C#获取数据：

public class ExcelHelp
    {
        //获取http请求Request对象
        public static HttpRequest baseRequest = HttpContext.Current.Request;
        //获取http请求Response对象
        public static HttpResponse baseResponse = HttpContext.Current.Response;
        /// <summary>
        /// 获取Post请求EXCEL文件数据
        /// </summary>
        /// <param name="fileFormName">EXCEL的表单名称</param>
        /// <returns></returns>
        public static DataTable GetExcelData(string fileFormName)
        {
            //获取Post请求--表单文件的文件名
            string uploadFileName = baseRequest.Files[fileFormName].FileName;
            //上传文件到指定目录（项目根目录/Resources/uploadFileName）--可根据项目文件目录进行调整
            string path = HttpContext.Current.Server.MapPath("~/Resources/" + uploadFileName);
            baseRequest.Files[fileFormName].SaveAs(path);

            DataTable dataTable = new DataTable();
            DataSet dataSet = new DataSet();
            //解析文件数据，并存入服务器
            //备注： "HDR=yes;"是说Excel文件的第一行是列名而不是数据，"HDR=No;"正好与前面的相反。
            //"IMEX=1 "如果列中的数据类型不一致，使用"IMEX=1"可必免数据类型冲突。 
            string strConn = string.Format("Provider = Microsoft.ACE.OLEDB.12.0; Data Source = {0}; Extended Properties = 'Excel 8.0;HDR=Yes;IMEX=1';", path);
            OleDbDataAdapter oada = new OleDbDataAdapter("select * from [Sheet1$]", strConn);
            oada.Fill(dataSet);

            if (dataSet.Tables.Count > 0)
            {
                dataTable = dataSet.Tables[0];
            }
            if (System.IO.File.Exists(path))
            {
                //如果存在则删除
                System.IO.File.Delete(path);
            }
            return dataTable;
        }
    }

B-2--EXCEL数据导入--NPOI--C#获取数据：

说明：以下方法借助第三方插件：NPOI专门处理EXCEL等办公文档的类库，因此项目中需引用该插件，NuGet--所有NPOI即可安装。

NPOI百度百科

public HSSFWorkbook GetExcelDataByNPOI(string fileFormName)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();

            //获取Post提交的EXCEL文件
            HttpPostedFile postFile = baseRequest.Files[fileFormName];
            string uploadFileName = postFile.FileName;
            //设置Excel临时保存路径（路径可修改，可作为参数传入）
            string path = HttpContext.Current.Server.MapPath("~/Resources/" + uploadFileName);
            postFile.SaveAs(path);
            System.IO.File.Delete(path);

            //文件流
            using (Stream stream = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                workbook = new HSSFWorkbook(stream);

                ISheet sheet = workbook.GetSheetAt(0);
                //Execel第一行是标题，不是要导入数据库的数据
                //for (int i = 1; i <= sheet.LastRowNum; i++)
                //{
                //    IRow row = sheet.GetRow(i);

                //    //获取行的文本内容
                //    string UserName = row.GetCell(0).StringCellValue;
                //    //获取行的时间内容
                //    DateTime time = row.GetCell(4).DateCellValue;
                //    //获取行的数字内容
                //    double num = row.GetCell(3).NumericCellValue;
                //    //获取行的bool内容
                //    bool b = row.GetCell(3).BooleanCellValue;
                //}
            }
            return workbook;
        }
        #endregion