C#实现Excel模板导出和从Excel导入数据
午休时间写了一个Demo关于Excel导入导出的简单练习
1.窗体
2.引用office命名空间
添加引用-程序集-扩展-Microsoft.Office.Interop.Excel
3.封装的ExcelHelper.cs关键类
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 }
4.演示代码
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 }
5.演示
6.源代码下载
