C# Winform的Excel导入+生成导入模板Demo

1.导入

 #region 导入Excel
        /// <summary>
        /// 
        /// </summary>
        private void Import()
        {
            try
            {
                //打开excel选择框
                OpenFileDialog frm = new OpenFileDialog();
                frm.Filter = "Excel文件(*.xls,xlsx)|*.xls;*.xlsx";//打开文件夹显示的文件类型
                if (frm.ShowDialog() == DialogResult.OK)//是否选择了文件
                {
                    string excelName = frm.FileName;//文件路径
                    DataSet ds = ImportExcel(excelName);
                    if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                    {
                        DataTable dt = ds.Tables[0];
                        string errMsg = "";           
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            DataRow item = dt.Rows[i];
                            int SumColumn=item.ItemArray.Length;//Excel列数
                            if (SumColumn==1)
                            {
                                //校验
                                string Status = item["状态"].ToString();
                                if (Status != "Y" && Status != "N")
                                    errMsg += "第" + (i + 1) + "行[状态]只能为Y或N！";                           
                            }
                            else
                            {
                                MessageBox.Show("导入失败：Excle表头对应列数不足", "系统提示");
                                return;
                            }                           
                        }
                        if (errMsg != "")
                        {
                            MessageBox.Show("以下数据验证错误添加失败:\n" + errMsg, "系统提示");
                            return;
                        }
                        foreach (DataRow item in dt.Rows)
                        {
                            var Status=item["状态"].ToString();
                            //对数据库进行添加....代码省略
                        }
                        MessageBox.Show("导入成功", "系统提示");
                    }
                    else
                    {
                        MessageBox.Show("导入失败：Excel格式错误或为空", "系统提示");
                        return;
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Import执行错误：" + ex.Message);
                throw;
            }
        }
        /// <summary>
        /// 导入Excel数据
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public DataSet ImportExcel(string filePath)
        {
            DataSet ds = null;
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook workbook = null;
            Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
            Microsoft.Office.Interop.Excel.Sheets sheets = null;
            Microsoft.Office.Interop.Excel.Range range = null;
            object missing = System.Reflection.Missing.Value;
            try
            {
                if (excel == null) { return null; }
                //打开Excel文件
                workbook = excel.Workbooks.Open(filePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                //获取所有的sheet表
                sheets = workbook.Worksheets;
                ds = new DataSet();
                for (int i = 1; i <= sheets.Count; i++)
                {
                    //获取第一个表
                    worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(i);
                    int rowCount = worksheet.UsedRange.Rows.Count;
                    int colCount = worksheet.UsedRange.Columns.Count;
                    int rowIndex = 1;
                    int colIndex = 1;
                    DataTable dt = new DataTable();
                    dt.TableName = "table" + i.ToString();
                    //读取列名
                    for (int j = 0; j < colCount; j++)
                    {
                        range = worksheet.Cells[rowIndex, colIndex + j];
                        DataColumn dc = new DataColumn();
                        dc.DataType = Type.GetType("System.String");
                        dc.ColumnName = range.Text.ToString().Trim();
                        //添加列
                        dt.Columns.Add(dc);
                    }

                    //读取行数据
                    for (int k = 1; k < rowCount; k++)
                    {
                        DataRow dr = dt.NewRow();
                        for (int index = 0; index < colCount; index++)
                        {
                            range = worksheet.Cells[rowIndex + k, colIndex + index];
                            dr[index] = range.Text.ToString();
                        }
                        dt.Rows.Add(dr);
                    }

                    ds.Tables.Add(dt);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("导入失败：" + ex.Message, "系统提示");
            }
            workbook.Close();
            excel.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
            worksheet = null;
            workbook = null;
            excel = null;
            GC.Collect();
            return ds;
        }
        /// <summary>
        /// 导入Excle按钮
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnImportExcel_Click(object sender, EventArgs e)
        {
            Import();
        }
#endregion

1.生成导入Excel

 #region 下载生成Excle模板
        /// <summary>
        /// 生成Excel按钮
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void label1_Click(object sender, EventArgs e)
        {
            Export();
        }
        public void Export()
        {
            //将dt加工成可以导出的数据集--需要多少列和列标题可在下面添加
            DataTable dtExport = new DataTable();
            dtExport.Columns.Add("状态");
            //dtExport.Columns.Add("标题1");
            //dtExport.Columns.Add("标题2");
            dtExport.TableName = "dtModel";
            dtExport.AcceptChanges();
            ExportToExcel("生成导入模板的名称", dtExport, "生成导入模板的名称");
        }

        private void ExportToExcel(string filename, System.Data.DataTable dt, string excelname)
        {
            if (dt == null) return;
            string saveFileName = "";
            bool fileSaved = false;
            SaveFileDialog saveDialog = new SaveFileDialog();
            saveDialog.DefaultExt = "xlsx";
            saveDialog.Filter = "Excel文件|*.xlsx";
            saveDialog.FileName = filename;
            saveDialog.ShowDialog();
            saveFileName = saveDialog.FileName;
            if (saveFileName.IndexOf(":") < 0) return;   //被点了取消  
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null)
            {
                MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
                return;
            }
            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1    
            Microsoft.Office.Interop.Excel.Range range;
            long totalCount = dt.Rows.Count;
            long rowRead = 0;
            float percent = 0;
            //写入字段    
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
                range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
                range.Interior.ColorIndex = 15;
                range.Font.Bold = true;
            }
            //写入数值    
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    worksheet.Cells[r + 2, i + 1] = "'" + dt.Rows[r][i];
                }
                rowRead++;
                percent = ((float)(100 * rowRead)) / totalCount;
                System.Windows.Forms.Application.DoEvents();
            }
            range = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[dt.Rows.Count + 1, dt.Columns.Count]];
            range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
            if (dt.Columns.Count > 1)
            {
                range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
                range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
            }
            if (saveFileName != "")
            {
                try
                {
                    workbook.Saved = true;
                    workbook.SaveCopyAs(saveFileName);
                    fileSaved = true;
                }
                catch (Exception ex)
                {
                    fileSaved = false;
                    return;
                }
            }
            else
            {
                fileSaved = false;
            }
            xlApp.Quit();
            GC.Collect();//强行销毁    
            if (fileSaved)
            {
                MessageBox.Show("下载成功", "系统提示");
            }

        }
        #endregion