excel導出在C#代碼中應用己經很廣泛了，我這里就做些總結，供自己和讀者學習用。
Excel知識點。
一、添加引用和命名空間
添加Microsoft.Office.Interop.Excel引用，它的默認路徑是C:\Program Files\Microsoft Visual Studio 9.0\Visual Studio Tools for Office\PIA\Office12\Microsoft.Office.Interop.Excel.dll
代碼中添加引用using Microsoft.Office.Interop.Excel;
二、Excel類的簡單介紹
此命名空間下關于Excel類的結構分別為：
ApplicationClass - 就是我們的excel應用程序。
Workbook - 就是我們平常見的一個個excel文件，經常是使用Workbooks類對其進行操作。
Worksheet - 就是excel文件中的一個個sheet頁。
Worksheet.Cells[row, column] - 就是某行某列的單元格，注意這里的下標row和column都是從1開始的，跟我平常用的數組或集合的下標有所不同。
知道了上述基本知識后，利用此類來操作excel就清晰了很多。
三、Excel的操作
任何操作Excel的動作首先肯定是用excel應用程序，首先要new一個ApplicationClass 實例，并在最后將此實例釋放。
| 1 |
ApplicationClass xlsApp = new ApplicationClass(); // 1. 創建Excel應用程序對象的一個實例，相當于我們從開始菜單打開Excel應用程序。 |
| 4 |
//對此實例進行驗證，如果為null則表示運行此代碼的機器可能未安裝Excel |
1. 打開現有的Excel文件
| 1 |
Workbook workbook = xlsApp.Workbooks.Open(excelFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); |
| 2 |
Worksheet mySheet = workbook.Sheets[1] as Worksheet; //第一個sheet頁 |
| 3 |
mySheet.Name = "testsheet"; //這里修改sheet名稱 |
2.復制sheet頁
| 1 |
mySheet.Copy(Type.Missing, workbook.Sheets[1]); //復制mySheet成一個新的sheet頁，復制完后的名稱是mySheet頁名稱后加一個(2)，這里就是testsheet(2)，復制完后，Worksheet的數量增加一個 |
注意 這里Copy方法的兩個參數，指是的復制出來新的sheet頁是在指定sheet頁的前面還是后面，上面的例子就是指復制的sheet頁在第一個sheet頁的后面。
3.刪除sheet頁
| 1 |
xlsApp.DisplayAlerts = false; //如果想刪除某個sheet頁，首先要將此項設為fasle。 |
| 2 |
(xlsApp.ActiveWorkbook.Sheets[1] as Worksheet).Delete(); |
4.選中sheet頁
| 1 |
(xlsApp.ActiveWorkbook.Sheets[1] as Worksheet).Select(Type.Missing); //選中某個sheet頁 |
5.另存excel文件
| 2 |
workbook.SaveCopyAs(filepath); |
6.釋放excel資源
| 1 |
workbook.Close(true, Type.Missing, Type.Missing); |
一般的我們傳入一個DataTable生成Excel代碼
| 04 |
/// <param name="dt"></param> |
| 05 |
protected void ExportExcel(DataTable dt) |
| 07 |
if (dt == null||dt.Rows.Count==0) return; |
| 08 |
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); |
| 14 |
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture; |
| 15 |
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); |
| 16 |
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks; |
| 17 |
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); |
| 18 |
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; |
| 19 |
Microsoft.Office.Interop.Excel.Range range; |
| 20 |
long totalCount = dt.Rows.Count; |
| 23 |
for (int i = 0; i < dt.Columns.Count; i++) |
| 25 |
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName; |
| 26 |
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1]; |
| 27 |
range.Interior.ColorIndex = 15; |
| 28 |
range.Font.Bold = true; |
| 30 |
for (int r = 0; r < dt.Rows.Count; r++) |
| 32 |
for (int i = 0; i < dt.Columns.Count; i++) |
| 34 |
worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString(); |
| 37 |
percent = ((float)(100 * rowRead)) / totalCount; |
如果要在excel中插入圖片，我們需要把代碼加入一行即可，如下所示
| 01 |
protected void ExportExcel(DataTable dt) |
| 03 |
if (dt == null || dt.Rows.Count == 0) return; |
| 04 |
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); |
| 10 |
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture; |
| 11 |
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); |
| 12 |
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks; |
| 13 |
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); |
| 14 |
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; |
| 15 |
Microsoft.Office.Interop.Excel.Range range; |
| 16 |
long totalCount = dt.Rows.Count; |
| 19 |
for (int i = 0; i < dt.Columns.Count; i++) |
| 21 |
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName; |
| 22 |
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1]; |
| 23 |
range.Interior.ColorIndex = 15; |
| 25 |
for (int r = 0; r < dt.Rows.Count; r++) |
| 27 |
for (int i = 0; i < dt.Columns.Count; i++) |
| 31 |
worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString(); |
| 35 |
worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString().Replace("=", ""); |
| 39 |
percent = ((float)(100 * rowRead)) / totalCount; |
| 42 |
worksheet.Shapes.AddPicture("C:\\Users\\spring\\Desktop\\1.gif", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 100, 200, 200, 300); |
| 43 |
worksheet.Shapes.AddTextEffect(Microsoft.Office.Core.MsoPresetTextEffect.msoTextEffect1, "123456", "Red", 15, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, 150, 200); |
我們調用如下:
| 01 |
public void GenerateExcel() |
| 03 |
DataTable dt = new DataTable(); |
| 04 |
dt.Columns.Add("Name", typeof(string)); |
| 05 |
dt.Columns.Add("Age", typeof(string)); |
| 06 |
DataRow dr = dt.NewRow(); |
| 07 |
dr["Name"] = "spring"; |
運行結果如下所示:
其中如下代碼的作用是
| 1 |
worksheet.Shapes.AddPicture("C:\\Users\\spring\\Desktop\\1.gif", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 100, 200, 200, 300); |
在Excel的指定位置加入圖片
| 1 |
worksheet.Shapes.AddTextEffect(Microsoft.Office.Core.MsoPresetTextEffect.msoTextEffect1, "123456", "Red", 15, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, 150, 200); |
在Excel的指定位置加入文本框，和里面的內容.
我們可以這樣來設計一個ExcelBase的基類:
先創建一個ExcelBE.cs：
| 05 |
private string _text = string.Empty; |
| 06 |
private string _startCell = string.Empty; |
| 07 |
private string _endCell = string.Empty; |
| 08 |
private string _interiorColor = string.Empty; |
| 09 |
private bool _isMerge = false; |
| 10 |
private int _size = 0; |
| 11 |
private string _fontColor = string.Empty; |
| 12 |
private string _format = string.Empty; |
| 14 |
public ExcelBE(int row, int col, string text, string startCell, string endCell, string interiorColor, bool isMerge, int size, string fontColor, string format) |
| 19 |
_startCell = startCell; |
| 21 |
_interiorColor = interiorColor; |
| 24 |
_fontColor = fontColor; |
| 46 |
set { _text = value; } |
| 49 |
public string StartCell |
| 51 |
get { return _startCell; } |
| 52 |
set { _startCell = value; } |
| 57 |
get { return _endCell; } |
| 58 |
set { _endCell = value; } |
| 61 |
public string InteriorColor |
| 63 |
get { return _interiorColor; } |
| 64 |
set { _interiorColor = value; } |
| 69 |
get { return _isMerge; } |
| 70 |
set { _isMerge = value; } |
| 76 |
set { _size = value; } |
| 79 |
public string FontColor |
| 81 |
get { return _fontColor; } |
| 82 |
set { _fontColor = value; } |
| 87 |
get { return _format; } |
| 88 |
set { _format = value; } |
接下來創建ExcelBase.cs:
| 01 |
public class ExcelBase |
| 03 |
private Microsoft.Office.Interop.Excel.Application app = null; |
| 04 |
private Microsoft.Office.Interop.Excel.Workbook workbook = null; |
| 05 |
private Microsoft.Office.Interop.Excel.Worksheet worksheet = null; |
| 06 |
private Microsoft.Office.Interop.Excel.Range workSheet_range = null; |
| 13 |
public void createDoc() |
| 17 |
app = new Microsoft.Office.Interop.Excel.Application(); |
| 19 |
workbook = app.Workbooks.Add(1); |
| 20 |
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[1]; |
| 24 |
Console.Write("Error"); |
| 31 |
public void InsertData(ExcelBE be) |
| 33 |
worksheet.Cells[be.Row, be.Col] = be.Text; |
| 34 |
workSheet_range = worksheet.get_Range(be.StartCell, be.EndCell); |
| 35 |
workSheet_range.MergeCells = be.IsMerge; |
| 36 |
workSheet_range.Interior.Color = GetColorValue(be.InteriorColor); |
| 37 |
workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb(); |
| 38 |
workSheet_range.ColumnWidth = be.Size; |
| 39 |
workSheet_range.Font.Color = string.IsNullOrEmpty(be.FontColor) ? System.Drawing.Color.White.ToArgb() : System.Drawing.Color.Black.ToArgb(); |
| 40 |
workSheet_range.NumberFormat = be.Formart; |
| 43 |
private int GetColorValue(string interiorColor) |
| 45 |
switch (interiorColor) |
| 48 |
return System.Drawing.Color.Yellow.ToArgb(); |
| 50 |
return System.Drawing.Color.Gray.ToArgb(); |
| 52 |
return System.Drawing.Color.Gainsboro.ToArgb(); |
| 54 |
return System.Drawing.Color.Turquoise.ToArgb(); |
| 56 |
return System.Drawing.Color.PeachPuff.ToArgb(); |
| 59 |
return System.Drawing.Color.White.ToArgb(); |
調用的代碼如下:
| 01 |
private void btnRun_Click(object sender, EventArgs e) |
| 03 |
ExcelBase excel = new ExcelBase(); |
| 04 |
//creates the main header |
| 06 |
be = new ExcelBE (5, 2, "Total of Products", "B5", "D5", "YELLOW", true, 10, "n",null); |
| 09 |
be = new ExcelBE (6, 2, "Sold Product", "B6", "B6", "GRAY", true, 10, "",null); |
| 11 |
be=new ExcelBE(6, 3, "", "C6", "C6", "GRAY", true, 10, "",null); |
| 13 |
be=new ExcelBE (6, 4, "Initial Total", "D6", "D6", "GRAY", true, 10, "",null); |
| 16 |
be=new ExcelBE (7, 2, "114287", "B7", "B7",null,false,10,"", "#,##0"); |
| 18 |
be=new ExcelBE (7, 3, "", "C7", "C7", null,false,10,"",null); |
| 20 |
be = new ExcelBE(7, 4, "129121", "D7", "D7", null, false, 10, "", "#,##0"); |
| 23 |
be = new ExcelBE(8, 2, "", "B8", "B8", null, false, 10, "", ""); |
| 25 |
be = new ExcelBE(8, 3, "=B7/D7", "C8", "C8", null, false, 10, "", "0.0%"); |
| 27 |
be = new ExcelBE(8, 4, "", "D8", "D8", null, false, 10, "", ""); |
| 30 |
be = new ExcelBE(9, 2, "", "B9", "D9", "GAINSBORO", true, 10, "",null); |
結果如下圖所示：
標簽：
本站文章除注明轉載外，均為本站原創或翻譯。歡迎任何形式的轉載，但請務必注明出處、不得修改原文相關鏈接，如果存在內容上的異議請郵件反饋至chenjj@ke049m.cn
文章轉載自：博客園
半島外圍網上直營 
137次
半島外圍網上直營相關的文章 
微信
在線咨詢
渝公網安備
50010702500608號
客服熱線