網頁導出EXCEL，調用該方法 前臺會下載EXCEL

/// <summary>/// Excle導出數據/// </summary>/// <typeparam name="T">類對象<\typeparam>/// <param name="list">對象數據</param>/// <param name="column">類字段，字段對應列名</param>/// <param name="filename">excel表名</param>public void OutExcel<T>(List<T> list, Dictionary<string, string> column, string filename){if (list == null || list.Count == 0 || column == null || column.Count == 0){return;}StringWriter sw = new StringWriter();//-------------------------------表頭讀取開始------------------------------------------------string title = string.Empty;foreach (KeyValuePair<string, string> kvp in column){title += kvp.Value + "\t";}title = title.Substring(0, title.LastIndexOf("\t"));sw.WriteLine(title);//-------------------------------表頭讀取結束--------------------------------------------------------//--------------------------------數據讀取start----------------------------------------------------------------------------------Type objType = typeof(T);BindingFlags bf = BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Static;//反射標識 PropertyInfo[] propInfoArr = objType.GetProperties(bf); //獲取映射列表foreach (T model in list){System.Text.StringBuilder data = new System.Text.StringBuilder();foreach (string key in column.Keys){foreach (PropertyInfo propInfo in propInfoArr){if (key == propInfo.Name)//判斷頭相對應的字段 {PropertyInfo modelProperty = model.GetType().GetProperty(propInfo.Name);if (modelProperty != null){object objResult = modelProperty.GetValue(model, null);//獲取值 data.Append(((objResult == null) ? string.Empty : objResult) + "\t");}}}}var temp = data.ToString();temp = temp.Substring(0, temp.LastIndexOf("\t"));sw.WriteLine(temp);}//------------------------------------------end----------------------------------------------------------------------------------sw.Close();//讀取數據結束//-----------------------------------輸出excel-------------------------------------------------------------HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + filename + ".xls");HttpContext.Current.Response.ContentType = "application/ms-excel";HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GBK");HttpContext.Current.Response.Write(sw.ToString());HttpContext.Current.Response.End();//------------------------------------------------------------------------------------------------------------- }調用例子
List<easy_CX> es = a.ToObjectFromJson<List<easy_CX>>();Dictionary<string, string> mydict = new Dictionary<string, string>();OutExcel(es,mydict,"all_down");

DataGridview中的數據導出EXCEL public bool ExportDataGridview(DataGridView gridView, bool isShowExcle){if (gridView.Rows.Count == 0){return false;}//創建Excel對象Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();excel.Application.Workbooks.Add(true);//生成字段名稱for (int i = 0; i < gridView.ColumnCount; i++){excel.Cells[1, i + 1] = gridView.Columns[i].HeaderText;}//填充數據for (int i = 0; i < gridView.RowCount - 1; i++) //循環行{for (int j = 0; j < gridView.ColumnCount; j++) //循環列{if (gridView[j, i].ValueType == typeof(string)){excel.Cells[i + 2, j + 1] = "'" + gridView.Rows[i].Cells[j].Value.ToString();}else{excel.Cells[i + 2, j + 1] = gridView.Rows[i].Cells[j].Value.ToString();}}} excel.Visible = false;excel.DisplayAlerts = false;excel.AlertBeforeOverwriting = false;excel.Save("c:\\321.xls");excel.Quit();return true;}

把DataTable導出為EXCEL

public void DataTabletoExcel(System.Data.DataTable tmpDataTable, string strFileName){if (tmpDataTable == null)return;int rowNum = tmpDataTable.Rows.Count;int columnNum = tmpDataTable.Columns.Count;int rowIndex = 1;int columnIndex = 0;Excel.Application xlApp = new Excel.Application();xlApp.DefaultFilePath = "";xlApp.DisplayAlerts = true;xlApp.SheetsInNewWorkbook = 1;Excel.Workbook xlBook = xlApp.Workbooks.Add(true);//將DataTable的列名導入Excel表第一行foreach (DataColumn dc in tmpDataTable.Columns){columnIndex++;xlApp.Cells[rowIndex, columnIndex] = dc.ColumnName;}//將DataTable中的數據導入Excel中for (int i = 0; i < rowNum; i++){rowIndex++;columnIndex = 0;for (int j = 0; j < columnNum; j++){columnIndex++;xlApp.Cells[rowIndex, columnIndex] = "'" + tmpDataTable.Rows[i][j].ToString();}}//xlBook.SaveCopyAs(HttpUtility.UrlDecode(strFileName, System.Text.Encoding.UTF8));xlBook.SaveCopyAs(strFileName);xlBook.Close(false);}

將EXCEL轉換為DataTable ?之后存入數據庫常用于批量導入

// <summary>/// 將Excel文件導出至DataTable(第一行作為表頭)/// </summary>/// <param name="ExcelFilePath">Excel文件路徑</param>/// <param name="TableName">數據表名，如果數據表名錯誤，默認為第一個數據表名</param>public static DataTable InputFromExcel(string ExcelFilePath, string TableName){if (!File.Exists(ExcelFilePath)){throw new Exception("Excel文件不存在！");}//如果數據表名不存在，則數據表名為Excel文件的第一個數據表ArrayList TableList = new ArrayList();TableList = GetExcelTables(ExcelFilePath);if (TableName.IndexOf(TableName) < 0){TableName = TableList[0].ToString().Trim();}DataTable table = new DataTable();OleDbConnection dbcon = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0");OleDbCommand cmd = new OleDbCommand("select * from [" + TableName + "$]", dbcon);OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);try{if (dbcon.State == ConnectionState.Closed){dbcon.Open();}adapter.Fill(table);}catch (Exception exp){throw exp;}finally{if (dbcon.State == ConnectionState.Open){dbcon.Close();}}return table;}public static ArrayList GetExcelTables(string ExcelFileName){DataTable dt = new DataTable();ArrayList TablesList = new ArrayList();if (File.Exists(ExcelFileName)){using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 8.0;Data Source=" + ExcelFileName)){try{conn.Open();dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });}catch (Exception exp){throw exp;}//獲取數據表個數int tablecount = dt.Rows.Count;for (int i = 0; i < tablecount; i++){string tablename = dt.Rows[i][2].ToString().Trim().TrimEnd('$');if (TablesList.IndexOf(tablename) < 0){TablesList.Add(tablename);}}}}return TablesList;}

總結

以上是生活随笔為你收集整理的C#EXCEL操作大全的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。