C#如何创建Execel文件

自己写了一个使用C#创建Excel文件的方法,这里面纪录一下,作为备忘,也为其他正在做这方面事情的同志提供一些思路.

 

第一步,要使用操作Excel的相关方法，需要包含以下引用

using Microsoft.Office.Interop; using Microsoft.Office.Core;

 

第二步，创建Excel文件的表头

private void CreateExcelForm(ref Microsoft.Office.Interop.Excel.Application xlApp, ref Microsoft.Office.Interop.Excel.Workbooks wBooks, ref Microsoft.Office.Interop.Excel.Workbook wBook, ref Microsoft.Office.Interop.Excel.Worksheet wSheet) { xlApp = new Microsoft.Office.Interop.Excel.Application(); wBooks = xlApp.Workbooks; wBook = wBooks.Add(System.Reflection.Missing.Value); wSheet = (Microsoft.Office.Interop.Excel.Worksheet)wBook.ActiveSheet; Microsoft.Office.Interop.Excel.Range rg = null; for (int ix = 0; ix < 5; ix++) { switch (ix) { case 0: rg = wSheet.get_Range("A" + 1, "L" + 1); rg.Interior.ColorIndex = 34; rg.Cells[1, 1] = "完成情况"; break; case 1: rg = wSheet.get_Range("A" + 2, "B" + 2); rg.Interior.ColorIndex = 8; rg.Cells[1, 1] = "姓名"; break; case 2: rg = wSheet.get_Range("C" + 2, "I" + 2); rg.Interior.ColorIndex = 6; rg.Cells[1, 1] = "项目名称"; break; case 3: rg = wSheet.get_Range("J" + 2, "J" + 2); rg.Interior.ColorIndex = 19; rg.Cells[1, 1] = "时间"; break; case 4: rg = wSheet.get_Range("K" + 2, "L" + 2); rg.Interior.ColorIndex = 7; rg.Cells[1, 1] = "总时间"; break; } rg.Merge(0); rg.Font.Bold = true; rg.Font.Size = 12; rg.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; rg.Cells.VerticalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; System.Runtime.InteropServices.Marshal.ReleaseComObject(rg); } 

 

第三步，写实际数据

private int SetExcelData(int iSetFlag, //标识 int iStartRow, //起始行数 int iMaxRow, //最大行数，仅在设置姓名与总积分时使用 Microsoft.Office.Interop.Excel.Worksheet wSheet, //表句柄 string strMsg) //待写入的消息 { Microsoft.Office.Interop.Excel.Range rg = null; int iPos = iStartRow + 1; string strMG = strMsg; switch ( iSetFlag ) { //表示设置姓名 case 0: rg = wSheet.get_Range("A" + iStartRow, "B" + iMaxRow); rg.Font.Bold = true; rg.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; rg.Cells.VerticalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; break; //表示设置项目名称 case 1: rg = wSheet.get_Range("C" + iStartRow, "I" + iStartRow); rg.Cells.VerticalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; break; //表示设置时间 case 2: rg = wSheet.get_Range("J" + iStartRow, "J" + iStartRow); rg.Cells.VerticalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; break; //表示设置总时间 case 3: rg = wSheet.get_Range("K" + iStartRow, "L" + iMaxRow); rg.Font.Bold = true; rg.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; rg.Cells.VerticalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; strMG = "=Sum(J" + iStartRow.ToString() + ": J" + iMaxRow.ToString() + ")"; break; } rg.Merge(0); rg.Font.Size = 12; rg.Cells[1, 1] = strMG; System.Runtime.InteropServices.Marshal.ReleaseComObject(rg); return iPos; 

 

 第四步，需要将创建的文件句柄释放

private void ReleaseSource(string strFilePath, Microsoft.Office.Interop.Excel.Application xlApp, Microsoft.Office.Interop.Excel.Workbooks wBooks, Microsoft.Office.Interop.Excel.Workbook wBook, Microsoft.Office.Interop.Excel.Worksheet wSheet) { System.Runtime.InteropServices.Marshal.ReleaseComObject(wSheet); wBook.Close(true, strFilePath, System.Reflection.Missing.Value); System.Runtime.InteropServices.Marshal.ReleaseComObject(wBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(wBooks); xlApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); }

 

第五步，调用

private void SaveExcel(string strFilePath) { Microsoft.Office.Interop.Excel.Application xlApp = null; Microsoft.Office.Interop.Excel.Workbooks wBooks = null; Microsoft.Office.Interop.Excel.Workbook wBook = null; Microsoft.Office.Interop.Excel.Worksheet wSheet = null; int iSTPos = 0; int iMaxPos = 0; CreateExcelForm(ref xlApp, ref wBooks, ref wBook, ref wSheet); //读取数据库数据 iSTPos = 3; iMaxPos = GetDataBase(30, iSTPos, wSheet); if (iMaxPos != iSTPos) { SetExcelData(0, iSTPos, iMaxPos - 1, wSheet, "张三"); SetExcelData(3, iSTPos, iMaxPos - 1, wSheet, null); } iSTPos = iMaxPos; iMaxPos = GetDataBase(88, iSTPos, wSheet); if (iMaxPos != iSTPos) { SetExcelData(0, iSTPos, iMaxPos - 1, wSheet, "李四"); SetExcelData(3, iSTPos, iMaxPos - 1, wSheet, null); } iSTPos = iMaxPos; iMaxPos = GetDataBase(95, iSTPos, wSheet); if (iMaxPos != iSTPos) { SetExcelData(0, iSTPos, iMaxPos - 1, wSheet, "王二麻"); SetExcelData(3, iSTPos, iMaxPos - 1, wSheet, null); } ReleaseSource(strFilePath, xlApp, wBooks, wBook, wSheet); }

 

第六步，大功告成啦