C#，.net,winform导入Excel功能以及下载Excel文件到本地，并使用SqlBulkCopy把DataTable类型的数据写入到sqlserver数据库中

背景

最近在项目中有一个导入Excel的功能，下文中将介绍如何在C#项目中导入Excel文件，以及实现此功能的宏观思路。

宏观思路

因为项目中的导入功能是用户根据自己的需要去导入Excel文件，那么对于用户上传的格式是有要求的。所以需要让用户先下载我们提供的模板，然后用户可以去编辑模板，再进行上传。

下载模板

下载功能是的数据类型是使用的DataTable，也就是我们在DataTable中为用户提供列的名称放在一个DataTable中，让用户去下载。

具体代码实现

通过我们在代码中设置列的标题（或者让用户可配置），已list集合的方式传入此方法中

 /// <summary>
        /// 定义导入数据的模板
        /// 通过for循环给空白的DataTable添加列名称
        /// </summary>
        /// <param name="ColumnNames">模板的列的集合</param>
        /// <returns></returns>
        public DataTable ImportTemplate(List<string> ColumnNames)
        {
            DataTable template = new DataTable();

            DataRow dr = template.NewRow() ;

            template.Rows.Add(dr);
            for(int i = 0; i < ColumnNames.Count; i++)
            {
                template.Columns.Add(ColumnNames[i]);
            }
            return template;
        }

导出文件的具体代码

public void Export(DataTable dtSource,string FileName)
        {
            try {
                SaveFileDialog fileDialog = new SaveFileDialog();
                fileDialog.Filter = "Excel|*.xls|TXT|*.txt|PDF|*.pdf";
                fileDialog.FileName = FileName+"-"+DateTime.Now.ToString("D");
                if (fileDialog.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
                {
                    return;
                }
                string FileSavePath = fileDialog.FileName;
                switch (fileDialog.FilterIndex)
                {
                    case 1:
                        ExportXls(FileSavePath, dtSource); //导出xls
                        break;
                    case 2:
                        ExportTxt(FileSavePath, dtSource); //导出txt
                        break;
                    case 3:
                        ExportPDF(FileSavePath, dtSource); //导出pdf
                        break;
                        //case 4:
                        //    ExportDocx(FileSavePath, dtSource); //导出docx
                        //    break;
                }
                MessageBox.Show("文件 "+ FileSavePath + " 导出成功");
            }
            catch(Exception e){
                MessageBox.Show("导出文件失败，请稍后重新尝试"+ e);
            }
        }

有了以上两个方法之后，我们可以在某事件中去触发此这些方法，然后下载文件到用户的本地。

导入文件

上文中已经把导入的下载模板的工作完成，接下来是需要用把编辑好的Excel文件上传到我们程序并同步到数据库中。

上传Excel这里使用的NPOI，在“管理NuGet程序包”中，搜索NPOI找到，下载并安装。

这里可能出现问题是

解决办法：先安装“SixLabors.Fonts”，需要注意的是：如果直接搜索的话可能找不到，需要把“包括预发行版”勾选上

 

 安装好之后，再去下载NPOI。

在项目中添加类ExcelHelper

internal class ExcelHelper
    {
        /// <summary>
        /// 从Excel读取数据，只支持单表
        /// </summary>
        /// <param name="FilePath">文件路径</param>
        public static DataTable ReadFromExcel(string FilePath)
        {
            IWorkbook wk = null;
            string extension = System.IO.Path.GetExtension(FilePath); //获取扩展名
            try
            {
                using (FileStream fs = File.OpenRead(FilePath))
                {
                    if (extension.Equals(".xls")) //2003
                    {
                        wk = new HSSFWorkbook(fs);
                    }
                    else                         //2007以上
                    {
                        wk = new XSSFWorkbook(fs);
                    }
                }

                //读取当前表数据
                ISheet sheet = wk.GetSheetAt(0);
                //构建DataTable
                IRow row = sheet.GetRow(0);
                DataTable result = BuildDataTable(row);
                if (result != null)
                {
                    if (sheet.LastRowNum >= 1)
                    {
                        for (int i = 1; i < sheet.LastRowNum + 1; i++)
                        {
                            IRow temp_row = sheet.GetRow(i);
                            if (temp_row == null) { continue; }//2019-01-14 修复 行为空时会出错
                            List<object> itemArray = new List<object>();
                            for (int j = 0; j < result.Columns.Count; j++)//解决Excel超出DataTable列问题    lqwvje20181027
                            {
                                //itemArray.Add(temp_row.GetCell(j) == null ? string.Empty : temp_row.GetCell(j).ToString());
                                itemArray.Add(GetValueType(temp_row.GetCell(j)));//解决 导入Excel  时间格式问题  lqwvje 20180904
                            }

                            result.Rows.Add(itemArray.ToArray());
                        }
                    }
                }
                return result;
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        /// <summary>
        /// 从Excel读取数据，支持多表
        /// </summary>
        /// <param name="FilePath">文件路径</param>
        public static DataSet ReadFromExcels(string FilePath)
        {
            DataSet ds = new DataSet();
            IWorkbook wk = null;
            string extension = System.IO.Path.GetExtension(FilePath); //获取扩展名
            try
            {
                using (FileStream fs = File.OpenRead(FilePath))
                {
                    if (extension.Equals(".xls")) //2003
                    {
                        wk = new HSSFWorkbook(fs);
                    }
                    else                         //2007以上
                    {
                        wk = new XSSFWorkbook(fs);
                    }
                }

                int SheetCount = wk.NumberOfSheets;//获取表的数量
                if (SheetCount < 1)
                {
                    return ds;
                }
                for (int s = 0; s < SheetCount; s++)
                {
                    //读取当前表数据
                    ISheet sheet = wk.GetSheetAt(s);
                    //构建DataTable
                    IRow row = sheet.GetRow(0);
                    if (row == null) { continue; }
                    DataTable tempDT = BuildDataTable(row);
                    tempDT.TableName = wk.GetSheetName(s);
                    if (tempDT != null)
                    {
                        if (sheet.LastRowNum >= 1)
                        {
                            for (int i = 1; i < sheet.LastRowNum + 1; i++)
                            {
                                IRow temp_row = sheet.GetRow(i);
                                if (temp_row == null) { continue; }//2019-01-14 修复 行为空时会出错
                                List<object> itemArray = new List<object>();
                                for (int j = 0; j < tempDT.Columns.Count; j++)//解决Excel超出DataTable列问题    lqwvje20181027
                                {
                                    itemArray.Add(GetValueType(temp_row.GetCell(j)));//解决 导入Excel  时间格式问题  lqwvje 20180904
                                }
                                tempDT.Rows.Add(itemArray.ToArray());
                            }
                        }
                        ds.Tables.Add(tempDT);
                    }
                }
                return ds;
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        /// <summary>
        /// 将DataTable数据导入到excel中
        /// </summary>
        /// <param name="data">要导入的数据</param>
        /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
        /// <param name="sheetName">要导入的excel的sheet的名称</param>
        /// <param name="fileName">导出的文件途径</param>
        /// <returns>导入数据行数(包含列名那一行)</returns>
        public static int DataTableToExcel(DataTable data, string sheetName, string fileName, bool isColumnWritten = true)
        {
            IWorkbook workbook = null;
            using (FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite))
            {
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                {
                    workbook = new XSSFWorkbook();
                }
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                {
                    workbook = new HSSFWorkbook();
                }
                if (workbook == null) { return -1; }

                try
                {
                    ISheet sheet = workbook.CreateSheet(sheetName);
                    int count = 0;
                    if (isColumnWritten) //写入DataTable的列名
                    {
                        IRow row = sheet.CreateRow(0);
                        for (int j = 0; j < data.Columns.Count; ++j)
                        {
                            row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
                        }
                        count = 1;
                    }

                    for (int i = 0; i < data.Rows.Count; ++i)
                    {
                        IRow row = sheet.CreateRow(count);
                        for (int j = 0; j < data.Columns.Count; ++j)
                        {
                            row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
                        }
                        count++;
                    }
                    workbook.Write(fs,true); //写入到excel

                    return count;
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Exception: " + ex.Message);
                    return -1;
                }
            }
        }
        /// <summary>
        /// 将DataSet数据导入到excel中   每个datatable一个sheet,sheet名为datatable名
        /// </summary>
        /// <param name="ds">要导入的数据</param>
        /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
        /// <param name="fileName">导出的文件途径</param>
        public static bool DataTableToExcel(DataSet ds, string fileName, bool isColumnWritten = true)
        {
            if (ds == null || ds.Tables.Count < 1)
            {
                return false;
            }
            IWorkbook workbook = null;
            using (FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite))
            {
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                {
                    workbook = new XSSFWorkbook();
                }
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                {
                    workbook = new HSSFWorkbook();
                }
                if (workbook == null) { return false; }
                try
                {
                    foreach (DataTable dt in ds.Tables)
                    {
                        ISheet sheet = workbook.CreateSheet(dt.TableName);
                        if (isColumnWritten) //写入DataTable的列名
                        {
                            IRow row = sheet.CreateRow(0);
                            for (int j = 0; j < dt.Columns.Count; ++j)
                            {
                                row.CreateCell(j).SetCellValue(dt.Columns[j].ColumnName);
                            }
                        }

                        for (int i = 0; i < dt.Rows.Count; ++i)
                        {
                            IRow row = sheet.CreateRow(isColumnWritten ? i + 1 : i);
                            for (int j = 0; j < dt.Columns.Count; ++j)
                            {
                                row.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
                            }
                        }
                    }
                    workbook.Write(fs,true); //写入到excel
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Exception: " + ex.Message);
                    return false;
                }
            }
            return true;
        }

        private static DataTable BuildDataTable(IRow Row)
        {
            DataTable result = null;
            if (Row.Cells.Count > 0)
            {
                result = new DataTable();
                for (int i = 0; i < Row.LastCellNum; i++)
                {
                    if (Row.GetCell(i) != null)
                    {
                        result.Columns.Add(Row.GetCell(i).ToString());
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// 获取单元格类型
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetValueType(ICell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.Blank: //BLANK:  
                    return null;
                case CellType.Boolean: //BOOLEAN:  
                    return cell.BooleanCellValue;
                case CellType.Numeric: //NUMERIC:  
                    if (DateUtil.IsCellDateFormatted(cell))
                    {
                        return cell.DateCellValue;
                    }
                    return cell.NumericCellValue;
                case CellType.String: //STRING:  
                    return cell.StringCellValue;
                case CellType.Error: //ERROR:  
                    return cell.ErrorCellValue;
                case CellType.Formula: //FORMULA:  
                    cell.SetCellType(CellType.String);
                    return cell.StringCellValue;
                default:
                    return "=" + cell.CellFormula;
            }
        }
    }

在winform的中某触发事件中代码，调用刚刚的ExcelHelper对象，（这里还可以返回一个DataTable对象）

private void ExcelFile()
        {
            OpenFileDialog openFile = new OpenFileDialog();
            if (openFile.ShowDialog() == DialogResult.OK)
            {
                string filePath = openFile.FileName;
//获取本地的Excel文件
                DataTable excelDt = ExcelHelper.ReadFromExcel(filePath);
               
            }
        }

需要注意的是，在导入的时候如果有空白行的话，会把空白行也插入到数据库中，所以在读取本地的Excel数据之后，需要把空白行也去除掉。

List<DataRow> removelist = new List<DataRow>();
                    for (int i = 0; i < excelDt.Rows.Count; i++)
                    {
                        bool IsNull = true;
                        for (int j = 0; j < excelDt.Columns.Count; j++)
                        {
                            if (!string.IsNullOrEmpty(excelDt.Rows[i][j].ToString().Trim()))
                            {
                                IsNull = false;
                            }
                        }
                        if (IsNull)
                        {
                            removelist.Add(excelDt.Rows[i]);
                        }
                    }
                    for (int i = 0; i < removelist.Count; i++)
                    {
                        excelDt.Rows.Remove(removelist[i]);
                    }

到这里以上的操作步骤是获取本地的Excel数据了，那么接下来就可以去写入数据库中，需要注意的是：读取的数据，需要和数据库中的字段一一对应，也就是位置、名称都要对应上才行。

 开发中遇到的问题：

1、用户需要提供的数据和数据库少于数据库中的字段，比如：用户需要提供姓名、手机号这两个字段，但是数据库中还需要有地址这个字段，那怎么办呢？我们可以在代码中给把这些信息补全。再写入数据库中。

示例代码

ProductTable.Columns.Add("creator");
                        ProductTable.Columns["creator"].SetOrdinal(26);//在第26列插入字段
ProductTable.Columns.Add("create_time");
                        ProductTable.Columns["create_time"].SetOrdinal(27);

2、在写数据库时，遇到数据类型不匹配。因为从本地获取的Excel数据基本上是string类型，那么数据库中有写字段是bool类型或者其他，如果插库的话就会报错，我这里解决这个问题的思路大概是在代码中把类型给转换一下，这样插入数据库时就不会出错了。

解决思路：比较笨的办法是把获取本地的数据（存放到了DataTable对象中）克隆出来一份，在克隆出来的那份中进行数据转换，因为在原来的数据不能进行转换，而且这里克隆不会把原来的数据进行克隆，所以在完成数据类型转换之后，还要把旧表中的数据同步到克隆出来的表中。

示例代码：

表克隆：

DataTable NewTable = new DataTable();
                        NewTable = ProductTable.Clone();//把原来的表进行克隆

修改类型： 

//把新表中的表结构进行修改数据类型，和数据库中表字段进行保持一致
                        foreach (DataColumn col in NewTable.Columns)
                        {
                            #region ifelse
                            if (col.ColumnName == "conveyor_sel")
                            {
                                col.DataType = typeof(int);
                            }
                            else if (col.ColumnName == "p_length")
                            {
                                col.DataType = typeof(int);
                            }
                            else if (col.ColumnName == "p_width")
                            {
                                col.DataType = typeof(int);
                            }
}

旧表中的数据同步到新表中 

foreach (DataRow item in ProductTable.Rows)
                            {
                                DataRow NewDtRow = NewTable.NewRow();
                                //获取对应行的产品代码的值
                                data = item.ItemArray[0];
                                NewDtRow["recipe"] = item["recipe"].ToString();
                                NewDtRow["p_name"] = item["p_name"].ToString();
                                NewDtRow["cust_name"] = item["cust_name"].ToString();
                                NewDtRow["cust_abbr"] = item["cust_abbr"].ToString();
                                NewDtRow["cust_field"] = item["cust_field"].ToString();
}

 

3、读取到本地Excel表中的的列标题是中文，但是数据库的形式是英文。这里也是用代码转换

示例代码：

ProductTable.Columns["产品代码"].ColumnName = "recipe";
                        ProductTable.Columns["产品名称"].ColumnName = "p_name";

使用SqlBulkCopy，把DataTable的数据写入数据库

这里用的EF框架。

public long AddDataTable(DataTable dt, String TableName)
        {

            PmsTestEntities db = new PmsTestEntities();

            SqlBulkCopy copy = new SqlBulkCopy(db.Database.Connection.ConnectionString, SqlBulkCopyOptions.UseInternalTransaction);//把本地文件复制
                
            copy.BatchSize = 100;//每次传输行数
            copy.NotifyAfter = dt.Rows.Count;//传输多少行后提示
            copy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
            copy.DestinationTableName = TableName;//表名
            copy.WriteToServer(dt);
            return DataIncoming;

        }
//显示传入了多少的数据
private void OnSqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
        {
            long count = e.RowsCopied;

            DataIncoming = count;
        }