c#读取生成excel表格文件xls xlsx格式文件

全栈工程师开发手册 （作者：栾鹏）

c#教程全解

c#存储生成excel表格格式xls、xlsx格式的文件

需要电脑安装对应版本的office，并且在项目中引用excel

测试代码

static void Main(){List<string> alldata = new List<string>();alldata.Add("第一行");alldata.Add("第2行");alldata.Add("第叁行");list2excel(alldata,"列名","test.xls");}

将list<string>生成表格的函数

//导出到execl，需要导入Microsoft.Office.Interop.Excelpublic static void list2excel(List<String> strarray,string column_name,string path){System.Data.DataTable dt = new System.Data.DataTable();dt.Columns.Add(column_name, System.Type.GetType("System.String"));foreach ( String str in strarray){DataRow dr = dt.NewRow();dr[0] = str;dt.Rows.Add(dr);}DataTabletoExcel(dt, path);}

将DataTable生成excel函数

public static bool DataTabletoExcel(System.Data.DataTable dt, string AbosultedFilePath){if (dt == null)return false;Microsoft.Office.Interop.Excel.Application excApp = new Microsoft.Office.Interop.Excel.Application();if (excApp==null){MessageBox.Show("无法创建excel对象，可能您的电脑未安装excel");return false;}Microsoft.Office.Interop.Excel.Workbook workBook = excApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);Microsoft.Office.Interop.Excel.Worksheet workSeet = workBook.Worksheets[1]; //取得sheet1Microsoft.Office.Interop.Excel.Range range = null;int tableCount = dt.Rows.Count;int rowRead = 0;float percent = 0;for (int i = 0; i < dt.Columns.Count; i++){workSeet.Cells[1, i + 1] = dt.Columns[i].ColumnName;//设置标题的样式range = (Microsoft.Office.Interop.Excel.Range)workSeet.Cells[1, i + 1];range.Font.Bold = true; //粗体range.Font.Size = "12";//字体大小range.Font.Name = "宋体";range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //居中range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null); //背景色range.EntireColumn.AutoFit(); //自动设置列宽range.EntireRow.AutoFit(); //自动设置行高}for (int r = 0; r < dt.Rows.Count; r++){for (int c = 0; c < dt.Columns.Count; c++){//写入内容workSeet.Cells[r + 2, c + 1] = "'" + dt.Rows[r][c].ToString();//设置样式range = (Microsoft.Office.Interop.Excel.Range)workSeet.Cells[r + 2, c + 1];range.Font.Size = 9; //字体大小range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null); //加边框range.EntireColumn.AutoFit(); //自动调整列宽}rowRead++;percent = ((float)(100 * rowRead)) / tableCount;System.Windows.Forms.Application.DoEvents();}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].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;}try{workBook.Saved = true;workBook.SaveCopyAs(AbosultedFilePath);}catch { }workBook.Close();if (excApp != null){excApp.Workbooks.Close();excApp.Quit();int generation = System.GC.GetGeneration(excApp);System.Runtime.InteropServices.Marshal.ReleaseComObject(excApp);excApp = null;System.GC.Collect(generation);}GC.Collect(); //强行销毁#region 强行杀死最近打开的Excel进程System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");System.DateTime startTime = new DateTime();int m, killID = 0;for (m = 0; m < excelProc.Length; m++){if (startTime < excelProc[m].StartTime){startTime = excelProc[m].StartTime;killID = m;}}if (excelProc[killID].HasExited == false){excelProc[killID].Kill();}#endregionreturn true;}