.net导入，导出，操作EXCEL汇总

╰半橙微兮° 2021-11-17 17:22 434阅读 0赞

最近项目中做了基本所有对EXCEL的操作，做个笔记，方便以后使用。

一：先来个导入EXCEL的

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

/// <summary>
            /// Excel转Sql方法
            /// </summary>
            /// <param name="filePath">文件路径</param>
            /// <param name="sheetName">单元簿名称</param>
            /// <returns></returns>
            public static DataTable ExcelDataTable(string filePath, string sheetName)
            {
                DataTable dt = null;
                //HDR=Yes:第一行是列名        
                //IMEX=1:把数据作为text 类型       
                string conStr = string.Format("Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';", filePath);
                string strSql = string.Format("select * from [{0}$]", sheetName);
                OleDbConnection con = new OleDbConnection(conStr);
                OleDbDataAdapter oda = new OleDbDataAdapter(strSql, conStr);
                try
                {
                    dt = new DataTable();
                    oda.Fill(dt);
                    return dt;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    oda.Dispose();
                    con.Close();
                    con.Dispose();
                }
            }
    }

上面是封装的Common层的工具类，方便将Excel转换为DataTable.

下面上调用的代码

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

string conString = "server=192.168.1.44;database=DataTest;uid=sa;pwd=sa";
                    //调用Excel转Sql方法   
    
                    DataTable dt = OutExcel.ExcelDataTable(imageUrl, "sheet1");
                    //创建批量DataTable导入Sql   
                    using (SqlBulkCopy copy = new SqlBulkCopy(conString, SqlBulkCopyOptions.UseInternalTransaction))
                    {
                        //指定数据库表名      
                        copy.DestinationTableName = "BaseTags";
                        //写入Sql    
                        copy.WriteToServer(dt);
                    }

其中 conString为要插入的数据库配置，再指定表明，就可以插入数据了，插入的方式为追加在数据表中。  
二：再来个导出Excel的

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

public class Util<T>
        {
            /// <summary>
            /// 导出Excel
            /// </summary>
            /// <param name="response">返回类型</param>
            /// <param name="list">要导出的数据</param>
            /// <param name="columnName">列名称</param>
            /// <param name="propertyName">属性名称</param>
            /// <param name="ExcelTitle">Excel标题</param>
            /// <param name="strUserMsg"></param>
            public static void DataBindTitleExcel(System.Web.HttpResponseBase response, List<T> list, List<string> columnName, List<string> propertyName, string ExcelTitle, string strUserMsg)
            {
    
                if (list.Count == 0 || list == null)
                {
                    response.Write("<script>alert('对不起,没有查询到任何记录,导出失败!')</script>");
                    response.End();
                }
                response.ContentEncoding = Encoding.GetEncoding("GB2312");
                response.ContentType = "application/ms-excel";
                response.AppendHeader("Content-Disposition", "attachment;filename=Export.xls");
                int count = list.Count;
                StringBuilder builder = new StringBuilder();
                builder.Append("<html><head>\n");
                builder.Append("<meta http-equiv=\"Content-Language\" content=\"zh-cn\">\n");
                builder.Append("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=gb2312\">\n");
                builder.Append("</head>\n");
                builder.Append("<table border=1>");
                if (ExcelTitle != "")
                {
                    string str = "<font size=4><b>" + ExcelTitle + "</b></font>";
                    if (strUserMsg != "")
                    {
                        str = str + "（" + strUserMsg + "）";
                    }
                    builder.Append(string.Concat(new object[] { "<tr><td colspan=", count, ">", str, "</td></tr>" }));
                }
                builder.Append("<tr><td colspan=" + count + " valign=middle height=24>");
                builder.Append("查询时间：" + DateTime.Now.ToString("G") + "</td></tr>");
                builder.Append("<tr>\n");
                for (int i = 0; i < columnName.Count; i++)
                {
    
                    builder.Append("<td bgcolor=#969696><b>" + columnName[i] + "</b></td>\n");
    
                }
                Type objType = typeof(T);
                BindingFlags bf = BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Static;//反射标识 
                PropertyInfo[] propInfoArr = objType.GetProperties(bf);
    
                foreach (T model in list)
                {
    
                    builder.Append("<tr>");
    
                    foreach (PropertyInfo propInfo in propInfoArr)
                    {
    
                        foreach (string propName in propertyName)
                        {
    
                            if (string.Compare(propInfo.Name.ToUpper(), propName.ToUpper()) == 0)
                            {
    
                                PropertyInfo modelProperty = model.GetType().GetProperty(propName);
                                if (modelProperty != null)
                                {
    
                                    object objResult = modelProperty.GetValue(model, null);
                                    builder.Append("<td style='vnd.ms-excel.numberformat:@'>" + ((objResult == null) ? string.Empty : objResult) + "</td>");
                                }
                                else
                                {
                                    throw new Exception("Property name may be not exists!");
                                }
                            }
                        }
                    }
                    builder.Append("</tr>\n");
                }
                builder.Append("</table>\n");
                response.Write(builder.ToString());
                response.End();
            }
           
    
    
    
        }

我调用时候写的

Util<BaseTags>.DataBindTitleExcel(Response, listbt, columnName, propertyName, "Test系统数据管理", "");  
其中第一个参数MVC项目里为Response，ashx里为context.response,第二个参数为你要导出的数据列表，一般为泛型LIST类型。第三个参数columnName为Excel里的每一列的表头，第四个参数propertyName为对应数据库的列，第五个参数为导出的Excel标题，第六个参数为操作人员。

PS：附上一个DEMO做参考。

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

public ActionResult ToExcel(int id)
            {
                List<BaseTags> listbt = db.Set<BaseTags>().Where<BaseTags>(x => x.CollectorId == id).ToList();
                //这里是EXCEL里的内容
                List<string> columnName = new List<string>();
                columnName.Add("Tagname");
                columnName.Add("ExtendId");
                columnName.Add("Description");
                columnName.Add("Comment");
                columnName.Add("EngUnits");
                columnName.Add("DataType");
                columnName.Add("CollectorId");
                columnName.Add("GroupNumber");
                columnName.Add("SourceAddress");
                columnName.Add("CollectionInterval");
                columnName.Add("HiEngUnits");
                columnName.Add("LoEngUnits");
                columnName.Add("Expression");
                columnName.Add("IsSaveHistorian");
                columnName.Add("IsArchive");
                //这是是对应数据库的列
                List<string> propertyName = new List<string>();
                propertyName.Add("Tagname");
                propertyName.Add("ExtendId");
                propertyName.Add("Description");
                propertyName.Add("Comment");
                propertyName.Add("EngUnits");
                propertyName.Add("DataType");
                propertyName.Add("CollectorId");
                propertyName.Add("GroupNumber");
                propertyName.Add("SourceAddress");
                propertyName.Add("CollectionInterval");
                propertyName.Add("HiEngUnits");
                propertyName.Add("LoEngUnits");
                propertyName.Add("Expression");
                propertyName.Add("IsSaveHistorian");
                propertyName.Add("IsArchive");
                Util<BaseTags>.DataBindTitleExcel(Response, listbt, columnName, propertyName, "Test系统数据管理", "");
                return View();
            }

三：对Excel的操作（NPOI）

使用NPOI可以百度搜索NPOI直接Down最新版本，最好全部引用，缺少引用将会导入无法使用某些类

其中

HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls

XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

StringBuilder sb = new StringBuilder();
    
    
                   
                    using (Stream stream = File.OpenRead(PATH))
                    {
                        XSSFWorkbook workbook = new XSSFWorkbook(stream);
                        
                        //j为行数
                        int j = workbook.GetSheetAt(1).LastRowNum;
                                                                  //固定内容
                            sb.Append(@"MEPMD01,19970819,Schneider Electric,,,""SSTJEC\Sino Singapore TianJin Eco City|TianJin_Eco_City\TianJin Eco City"",");
                            string UtcNow = changeDateTimeFormat(DateTime.UtcNow);
                            //时间为文件上传FTP时间（UTC时间）
                            sb.Append(UtcNow);
                            sb.Append(',');
                            //用“EO系统名称”一行的点名替代
                            sb.Append(workbook.GetSheetAt(1).GetRow(4).GetCell(1).StringCellValue);
                            sb.Append('|');
                            //用点表里“地址”一行相应内容替代
                            sb.Append(workbook.GetSheetAt(1).GetRow(5).GetCell(1).NumericCellValue);
                            sb.Append(',');
                            //如果数据在合理的范围内，即为有效数据时此处填写OK
                            sb.Append("OK");
                            sb.Append(',');
                            //点表中“点类型”一行相应内容替代
                            sb.Append(workbook.GetSheetAt(1).GetRow(6).GetCell(1).StringCellValue);
                            sb.Append(',');
                            //用点表中“单位”一行内容替代
                            sb.Append(workbook.GetSheetAt(1).GetRow(3).GetCell(1).StringCellValue);
                            sb.Append(",1,00000005,3,");
                            //为后面第一个采样值的采样时间
                            string firsttime = DateTime.FromOADate(workbook.GetSheetAt(1).GetRow(10).GetCell(0).NumericCellValue).ToString();
                            string UtcFirst = changeDateTimeFormat(Convert.ToDateTime(firsttime));
                            sb.Append(UtcFirst);
                            sb.Append(",,");
                            //第一个采样值
                            sb.Append(workbook.GetSheetAt(1).GetRow(8).GetCell(1).NumericCellValue);
                            sb.Append(",,,");
                            //第二个采样值
                            sb.Append(workbook.GetSheetAt(1).GetRow(9).GetCell(1).NumericCellValue);
                            sb.Append(",,,");
                            //第三个采样值
                            sb.Append(workbook.GetSheetAt(1).GetRow(10).GetCell(1).NumericCellValue);
                            sb.Append(",");
                            CreateTxt("E:\\" + dirs[intFile].Name.Split('.')[0] + ".cemp", sb.ToString());
    
    
                                     }

其中PATH为文件路径，workbook即为工作薄，GetRow(X)控制第几行（默认从0开始）,GetCell(X)控制第几列（默认从0开始），

Excel中string类型数据采用StringCellValue获取，数值类型采用NumericCellValue获取，Datetime类型直接获取到得是数值，并非时间，要获得时间通过DateTime.FromOADate(workbook.GetSheetAt(1).GetRow(10).GetCell(0).NumericCellValue).ToString();方式获取

转载于:https://www.cnblogs.com/ckblogs/p/4029233.html

[ContractedBlock.gif]: https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif
[ExpandedBlockStart.gif]: /images/20211116/c93b722a4964434d9e0a7a8baf07793b.png