C#实现Excel模板导出和从Excel导入数据

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Data;
  4 using System.Data.OleDb;
  5 using System.IO;
  6 using System.Linq;
  7 using System.Runtime.InteropServices;
  8 using System.Text;
  9 using System.Text.RegularExpressions;
 10 using System.Windows.Forms;
 11 namespace ExcelDemo
 12 {
 13     /// <summary>
 14     /// Excel帮助类
 15     /// string column = "商品编码,商品名称,刊登单号,门店名称";
 16     /// 导入数据
 17     /// var action = new Action<string, DataTable>((str, dtExcel) =>
 18     /// {
 19     /// this.dgvData.DataSource = dtExcel;
 20     /// });
 21     /// excelHelper.ImportExcelToDataTable(this, action, "Ebay侵权下线");
 22     /// 导出模版
 23     /// string message = string.Empty;
 24     //  excelHelper.SaveExcelTemplate(column.Split(','), "Ebay侵权下线", "Ebay侵权下线", ref message);
 25     /// </summary>
 26     public class ExcelHelper
 27     {
 28         [DllImport("User32.dll", CharSet = CharSet.Auto)]
 29         public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
 30 
 31         /// <summary>
 32         /// 保存Excel模版
 33         /// </summary>
 34         /// <param name="columns">列名,例如：商品编码,商品名称,刊登单号,门店名称</param>
 35         /// <param name="FileName">文件名，例如：Ebay侵权下线</param>
 36         /// <param name="SheetName">工作表名称，例如：Ebay侵权下线</param>
 37         /// <param name="message">错误信息</param>
 38         public void SaveExcelTemplate(string[] columns, string FileName, string SheetName, ref string message)
 39         {
 40             string Filter = "Excel文件|*.csv|Excel文件|*.xls|Excel文件|*.xlsx";
 41 
 42             SaveFileDialog saveFileDialog1 = new SaveFileDialog();
 43             saveFileDialog1.DefaultExt = "csv";
 44             saveFileDialog1.FileName = FileName;
 45             saveFileDialog1.Filter = Filter;
 46             saveFileDialog1.FilterIndex = 0;
 47             saveFileDialog1.RestoreDirectory = true;
 48             saveFileDialog1.CreatePrompt = true;
 49             saveFileDialog1.Title = "Excel文件";
 50             saveFileDialog1.InitialDirectory = Directory.GetCurrentDirectory();
 51 
 52             if (saveFileDialog1.ShowDialog() != DialogResult.OK)
 53                 return;
 54 
 55             //获得文件路径
 56             string localFilePath = saveFileDialog1.FileName.ToString();
 57             if (Regex.IsMatch(localFilePath, @"\.csv$"))
 58             {
 59                 localFilePath = Regex.Replace(saveFileDialog1.FileName, @"\.csv$", "", RegexOptions.IgnoreCase) + ".csv";
 60                 File.WriteAllText(localFilePath, string.Join(",", columns), Encoding.Default);
 61             }
 62             else
 63             {
 64                 //获取文件路径，不带文件名
 65                 ArrayToExcelTemplate(columns, localFilePath, SheetName, ref message);
 66             }
 67 
 68             if (string.IsNullOrEmpty(message))
 69                 MessageBox.Show("\n\n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
 70         }
 71 
 72         /// <summary>
 73         /// 导出模版
 74         /// </summary>
 75         /// <param name="columns">列名,例如：商品编码,商品名称,刊登单号,门店名称</param>
 76         /// <param name="localFilePath">本地路径</param>
 77         /// <param name="SheetName">工作表名称，例如：Ebay侵权下线</param>
 78         /// <param name="message">错误信息</param>
 79         public void ArrayToExcelTemplate(string[] columns, string localFilePath, string SheetName, ref string message)
 80         {
 81             Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
 82             if (xlApp == null)
 83             {
 84                 message = "无法创建Excel对象，可能计算机未安装Excel！";
 85                 return;
 86             }
 87 
 88             //創建Excel對象
 89             Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
 90             Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
 91             Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
 92             if (worksheet == null) worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
 93             Microsoft.Office.Interop.Excel.Range range = null;
 94 
 95             long totalCount = columns.Length;
 96             worksheet.Name = SheetName;//第一个sheet在Excel中显示的名称
 97             int c;
 98             c = 0;
 99             ////写入标题
100             for (int i = 0, count = columns.Length; i < count; i++)
101             {
102                 //if (string.IsNullOrEmpty(columns[i])) continue;
103                 worksheet.Cells[1, c + 1] = columns[i];
104                 range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, c + 1];
105                 range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//居中  
106                 c++;
107 
108             }
109 
110             try
111             {
112                 localFilePath = Regex.Replace(localFilePath, ".xls$|.xlsx$", "", RegexOptions.IgnoreCase);
113                 localFilePath += xlApp.Version.CompareTo("11.0") == 0 ? ".xls" : ".xlsx";
114                 workbook.SaveCopyAs(localFilePath);
115             }
116             catch (Exception ex)
117             {
118                 message = "生成Excel附件过程中出现异常，详细信息如：" + ex.ToString();
119             }
120 
121 
122             try
123             {
124                 if (xlApp != null)
125                 {
126 
127                     int lpdwProcessId;
128                     GetWindowThreadProcessId(new IntPtr(xlApp.Hwnd), out lpdwProcessId);
129                     System.Diagnostics.Process.GetProcessById(lpdwProcessId).Kill();
130                 }
131             }
132             catch (Exception ex)
133             {
134                 message = "Delete Excel Process Error:" + ex.Message;
135             }
136 
137         }
138 
139         /// <summary>
140         /// 导入Excel
141         /// </summary>
142         /// <param name="form"></param>
143         /// <param name="callback"></param>
144         public void ImportExcelToDataTable(Form form, Action<string, DataTable> callback, string SheetName = "Sheet1")
145         {
146             string Filter = "Excel文件|*.csv|Excel文件|*.xls|Excel文件|*.xlsx";
147 
148             OpenFileDialog openFileDialog1 = new OpenFileDialog();
149             openFileDialog1.Title = "Excel文件";
150             openFileDialog1.Filter = Filter;
151             openFileDialog1.ValidateNames = true;
152             openFileDialog1.CheckFileExists = true;
153             openFileDialog1.CheckPathExists = true;
154 
155             if (openFileDialog1.ShowDialog() != DialogResult.OK)
156                 return;
157 
158             var action = new Action(() =>
159             {
160                 string localFilePath = openFileDialog1.FileName;
161                 if (File.Exists(localFilePath))
162                 {
163                     string message = string.Empty;
164                     string fileExten = Path.GetExtension(localFilePath);
165 
166                     DataTable dtExcel;
167                     if (fileExten.ToLower().Contains(".csv"))
168                     {
169                         dtExcel = ImportCSVFile(localFilePath, "Table1", ref message);
170                     }
171                     else
172                     {
173                         dtExcel = ImportExcelFile(localFilePath, "Table1", SheetName, ref message);
174                     }
175 
176                     if (callback != null)
177                     {
178                         if (form.InvokeRequired)
179                         {
180                             form.Invoke(callback, message, dtExcel);
181                         }
182                         else
183                         {
184                             callback(message, dtExcel);
185                         }
186                     }
187                 }
188             });
189 
190             action.BeginInvoke(null, null);
191         }
192 
193         /// <summary>
194         /// 执行导入
195         /// </summary>
196         /// <param name="strFileName">对应文件路径</param>
197         /// <param name="typeName">返回的Table名称</param>
198         /// <param name="message">返回的错误</param>
199         /// <returns>DataTable</returns>
200         public DataTable ImportCSVFile(string strFileName, string typeName, ref string message)
201         {
202             if (string.IsNullOrEmpty(strFileName)) return null;
203 
204             string line = string.Empty;
205             string[] split = null;
206             bool isReplace;
207             int subBegion;
208             int subEnd;
209             string itemString = string.Empty;
210             string oldItemString = string.Empty;
211             DataTable table = new DataTable(typeName);
212             DataRow row = null;
213             StreamReader sr = new StreamReader(strFileName, System.Text.Encoding.Default);
214             //创建与数据源对应的数据列 
215             line = sr.ReadLine();
216             split = line.Split(',');
217             foreach (String colname in split)
218             {
219                 table.Columns.Add(colname, System.Type.GetType("System.String"));
220             }
221             //将数据填入数据表 
222             int j = 0;
223             while ((line = sr.ReadLine()) != null)
224             {
225                 subEnd = 0;
226                 subBegion = 0;
227 
228                 if (line.IndexOf('\"') > 0)
229                 {
230                     isReplace = true;
231                 }
232                 else
233                 {
234                     isReplace = false;
235                 }
236                 itemString = string.Empty;
237                 while (isReplace)
238                 {
239 
240                     subBegion = line.IndexOf('\"');
241                     subEnd = line.Length - 1;
242                     if (line.Length - 1 > subBegion)
243                     {
244                         subEnd = line.IndexOf('\"', subBegion + 1);
245                     }
246 
247                     if (subEnd - subBegion > 0)
248                     {
249                         itemString = line.Substring(subBegion, subEnd - subBegion + 1);
250                         oldItemString = itemString;
251                         itemString = itemString.Replace(',', '|').Replace("\"", string.Empty);
252                         line = line.Replace(oldItemString, itemString);
253 
254                     }
255 
256                     if (line.IndexOf('\"') == -1)
257                     {
258                         isReplace = false;
259                     }
260 
261                 }
262 
263                 j = 0;
264                 row = table.NewRow();
265                 split = line.Split(',');
266                 foreach (String colname in split)
267                 {
268                     row[j] = colname.Replace('|', ',');
269                     j++;
270                 }
271                 table.Rows.Add(row);
272             }
273             sr.Close();
274             //显示数据 
275 
276             return table;
277         }
278 
279 
280         /// <summary>
281         /// Excel执行导入
282         /// </summary>
283         /// <param name="strFileName">对应文件路径</param>
284         /// <param name="typeName">返回的Table名称</param>
285         /// <param name="message">返回的错误</param>
286         /// <returns></returns>
287         public DataTable ImportExcelFile(string strFileName, string typeName, string SheetName, ref string message)
288         {
289             if (string.IsNullOrEmpty(strFileName)) return null;
290             DataSet Exceldt;
291             Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
292             OleDbConnection con = new OleDbConnection();
293             try
294             {
295                 //OleDbDataAdapter ExcelO = new OleDbDataAdapter(selectStr, @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + strFileName + ";Extended Properties=Excel 8.0;");
296                 string ConnStr = xlApp.Version.CompareTo("11.0") == 0 ? @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + strFileName + ";Extended Properties='Excel 8.0;IMEX=1;HDR=YES;'" : @"Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 8.0;IMEX=1;HDR=YES'; Data Source=" + strFileName;
297                 con.ConnectionString = ConnStr;
298                 con.Open();
299                 DataTable dtOle = con.GetSchema("Tables");
300                 DataTableReader dtReader = new DataTableReader(dtOle);
301                 string TableName = "";
302                 while (dtReader.Read())
303                 {
304                     TableName = dtReader["Table_Name"].ToString();
305                     break;
306                 }
307                 OleDbDataAdapter excel = new OleDbDataAdapter(string.Format("select * from [" + SheetName + "$];", TableName), ConnStr);
308                 //OleDbDataAdapter excel = new OleDbDataAdapter(selectStr, @"Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 8.0;IMEX=1;HDR=YES'; Data Source=" + strFileName);
309 
310                 Exceldt = new DataSet();
311                 excel.Fill(Exceldt, typeName);
312                 return Exceldt.Tables.Count > 0 ? Exceldt.Tables[0] : null;
313             }
314             catch (OleDbException ex)
315             {
316                 message = ex.Message;
317                 return null;
318             }
319             catch (Exception ex)
320             {
321                 message = ex.Message;
322                 return null;
323             }
324             finally
325             {
326                 con.Close();
327             }
328         }
329     }
330 }

  1 using System;
  2 using System.Collections.Generic;
  3 using System.ComponentModel;
  4 using System.Data;
  5 using System.Drawing;
  6 using System.Linq;
  7 using System.Text;
  8 using System.Windows.Forms;
  9 
 10 namespace ExcelDemo
 11 {
 12     /// <summary>
 13     /// 针对Excel写的帮助模型
 14     /// </summary>
 15     public partial class Form1 : Form
 16     {
 17         #region 变量
 18         /// <summary>
 19         /// 导出模板列集合
 20         /// </summary>
 21         List<string> columnListOut = new List<string>()
 22         {
 23             "列1",
 24             "列2",
 25             "列3",
 26             "列4"
 27         };
 28 
 29         /// <summary>
 30         /// 导出模板文件名称
 31         /// </summary>
 32         string FileName = "导出模板";
 33 
 34         /// <summary>
 35         /// Excel底层页签名称
 36         /// </summary>
 37         string SheetName = "Excel页签名称";
 38 
 39         /// <summary>
 40         /// ExcelHelper实例化
 41         /// </summary>
 42         ExcelHelper excelHelper = new ExcelHelper();
 43 
 44         #endregion
 45 
 46         #region 初始化、数据加载
 47         public Form1()
 48         {
 49             InitializeComponent();
 50         }
 51         #endregion
 52 
 53         #region 控件事件
 54         /// <summary>
 55         /// 选择文件
 56         /// </summary>
 57         /// <param name="sender"></param>
 58         /// <param name="e"></param>
 59         private void btnChooseFile_Click(object sender, EventArgs e)
 60         {
 61             //对于选择文件转化的DataTable和提示信息msg的委托
 62             Action<string, DataTable> action = new Action<string, DataTable>((string str, DataTable dt) =>
 63             {
 64                 if (dt == null || dt.Rows.Count == 0)
 65                 {
 66                     MessageBox.Show("dt为空的");
 67                     return;
 68                 }
 69 
 70                 if (dt.Rows.Count > 1000)
 71                 {
 72                     MessageBox.Show("导入的数据已超过最大限制1000条");
 73                     return;
 74                 }
 75                 if (!this.columnListOut.ToArray().All(t => dt.Columns.Contains(t)))
 76                 {
 77                     MessageBox.Show("导入的数据字段不匹配");
 78                     return;
 79                 }
 80 
 81                 //获取列1的可枚举集合
 82                 IEnumerable<string> column1List = dt.Rows.Cast<DataRow>().Select(r => r["列1"].ToString());
 83 
 84                 //验证列1必须是整数切不能是负数
 85                 decimal isDecimal = 0;
 86                 foreach (var item in column1List)
 87                 {
 88                     if ((!decimal.TryParse(item, out isDecimal)) && !string.IsNullOrEmpty(item))
 89                     {
 90                         MessageBox.Show("列1必须是Decimal类型");
 91                         return;
 92                     }
 93                     if (isDecimal < 0)
 94                     {
 95                         MessageBox.Show("列1不允许是负数");
 96                         return;
 97                     }
 98                 }
 99                 dt.AcceptChanges();
100                 this.dgv.DataSource = dt;
101             });
102             this.excelHelper.ImportExcelToDataTable(this, action, this.SheetName);
103         }
104 
105         /// <summary>
106         /// 导出模板
107         /// </summary>
108         /// <param name="sender"></param>
109         /// <param name="e"></param>
110         private void btnOut_Click(object sender, EventArgs e)
111         {
112             string[] columnList = this.columnListOut.ToArray();
113             string msg = string.Empty;
114             this.excelHelper.SaveExcelTemplate(columnList, this.FileName, this.SheetName, ref msg);
115         }
116 
117         #endregion
118     }
119 }