本文分享了c#操作Excel的相关代码，还是比较全面的，其实无外乎存取，增删改查等操作，参考下。
具体代码：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
|
//引用Microsoft.Office.Interop.Excel.dll文件 //添加usingusing Microsoft.Office.Interop.Excel;using Excel=Microsoft.Office.Interop.Excel; //设置程序运行语言System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");//创建ApplicationExcel.Application xlApp = new Excel.Application();//设置是否显示警告窗体excelApp.DisplayAlerts = false;//设置是否显示ExcelexcelApp.Visible = false;//禁止刷新屏幕excelApp.ScreenUpdating = false;//根据路径path打开Excel.Workbook xlsWorkBook = excelApp.Workbooks.Open(path, System.Type.Missing, System.Type.Missing, System.Type.Missing,System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing,System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);//获取Worksheet对象Excel.Worksheet xlsWorkSheet = (Worksheet)xlsWorkBook.Worksheets["sales plan"]; ***获取最后一行、一列的两种方法***//获取已用的范围数据int rowsCount = xlsWorkSheet.UsedRange.Rows.Count;int colsCount = xlsWorkSheet.UsedRange.Columns.Count;int rowsCount = xlsWorkSheet.get_Range("A65536", "A65536").get_End(Microsoft.Office.Interop.Excel.XlDirection.xlUp).Row;int colsCount = xlsWorkSheet.get_Range("ZZ1", "ZZ1").get_End(Microsoft.Office.Interop.Excel.XlDirection.xlToLeft).Column; ***将Excel数据存入二维数组***//rowsCount：最大行 colsCount：最大列Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)xlsWorkSheet.Cells[1, 1];Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)xlsWorkSheet.Cells[rowsCount, colsCount];Range rng = (Microsoft.Office.Interop.Excel.Range)xlsWorkSheet.get_Range(c1, c2);object[,] exceldata = (object[,])rng.get_Value(Microsoft.Office.Interop.Excel.XlRangeValueDataType.xlRangeValueDefault); //在第一列的左边插入一列Excel.Range xlsColumns = (Excel.Range)xlsWorkSheet.Columns[1, System.Type.Missing];xlsColumns.Insert(XlInsertShiftDirection.xlShiftToRight, Type.Missing);//xlsSheetTemplateMajor_Meisai.Cells.get_Range(xlsSheetTemplateMajor_Meisai.Cells[1, 1], xlsSheetTemplateMajor_Meisai.Cells[65535, 1]).Insert(Type.Missing, Type.Missing); Excel.Range rng; rng = worksheet.get_Range("A:A", "A:A"); rng.Insert(Excel.XlDirection.xlToRight, Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);Excel.Range rng = (Microsoft.Office.Interop.Excel.Range)xlsWorkSheet.Columns[12, Type.Missing];rng.Insert(XlInsertShiftDirection.xlShiftToRight, XlInsertFormatOrigin.xlFormatFromLeftOrAbove); //删除行Range deleteRng = (Range)xlsWorkSheetSapExcel.Rows[2, System.Type.Missing];deleteRng.Delete(Excel.XlDeleteShiftDirection.xlShiftUp); //删除一列数据((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, 11]).Select();((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, 11]).EntireColumn.Delete(0);((Excel.Range)xlsSheetShareMajor_Meisai.Cells[1, 3]).EntireColumn.Delete (0); //设置背景色为红色xlsWorkSheet.get_Range("A1", "A1").Interior.ColorIndex = 3; //设置Format属性 属性值可以通过在vba中录宏得到Microsoft.Office.Interop.Excel.Range range1 = xlsWorkSheetAdd.get_Range("J1", "J65535");range1.NumberFormat = "@";//文本格式range1 = xlsWorkSheetAdd.get_Range("L1", "L65535");range1.NumberFormat = "0.00";//保留两位小数Excel.Range rng = xlsSheetShareMajor_Meisai.Columns["I", System.Type.Missing] as Excel.Range;rng.NumberFormatLocal =@"yyyy/m/d";//设置日期格式 //替换Range Drng = xlsWorkSheetTemplate.get_Range("D1", "D65535");Drng.Replace(" ", "", XlLookAt.xlPart, XlSearchOrder.xlByColumns, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);Drng.TextToColumns(Drng, Excel.XlTextParsingType.xlDelimited, Excel.XlTextQualifier.xlTextQualifierSingleQuote, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing); //分列处理 设置为文本Range TextToColumnRng = xlsWorkSheet.get_Range("E1", "E65535");xlsWorkSheet.get_Range("E1", "E65535").TextToColumns(TextToColumnRng, Excel.XlTextParsingType.xlDelimited, Excel.XlTextQualifier.xlTextQualifierSingleQuote, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing); //设置公式rng = xlMergeFileWorkSheet.get_Range("D2", "D" + rowcount);//设置列范围rng.Formula = @"=IF(RC[-3]=""H"",""Survivor"",""Donor"")";//设置公式 @的问题//rng.NumberFormat = "$0.00";//设置格式copyRng = xlsSheetTemplateMajor_Meisai.get_Range("N3", "N" + lastRowTemplate);copyRng.Formula = "=VLOOKUP(RC[-12],AR残!C[-13]:C[-11],2,0)"; //通过行、列的索引获取值string f = Convert.ToString(xlsSheetShareMajor_Meisai.get_Range(xlsSheetShareMajor_Meisai.Cells[2, 1], xlsSheetShareMajor_Meisai.Cells[2,1]).Value2); //筛选//确定筛选范围D1_rng = D1_TemSheet.Cells.get_Range(D1_TemSheet.Cells[1, 1], D1_TemSheet.Cells[1, 50]);//执行筛选动作rng.AutoFilter(5, "S", Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlFilterValues, Type.Missing, true);rng.AutoFilter(6, "H", Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlOr, "F", true);D1_rng.AutoFilter(D1_Column + 2, "#N/A", Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlFilterValues,"#N/A",false); //复制粘贴D2_rng.Copy(Type.Missing);D2_TemSheet.Cells.get_Range(D2_TemSheet.Cells[2, (D2_Column + 1)], D2_TemSheet.Cells[2, (D2_Column + 1)]).PasteSpecial(Excel.XlPasteType.xlPasteValues, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false); //Find查找rng = mySheet.get_Range("A1", "IV10").Find(arrLabel[j], Type.Missing, Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues, Microsoft.Office.Interop.Excel.XlLookAt.xlWhole, Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows, Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext, false, Type.Missing, Type.Missing); //文字占满单元格range.EntireColumn.AutoFit(); //另存xlsWorkBook.SaveAs(FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); ***关闭对象***System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWorkSheet);System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWorkBook);excelApp.Quit();Kill(excelApp);//调用方法关闭进程GC.Collect(); /// <summary>/// 关闭Excel进程/// </summary>public class KeyMyExcelProcess{ [DllImport("User32.dll", CharSet = CharSet.Auto)] public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID); public static void Kill(Microsoft.Office.Interop.Excel.Application excel) { try { IntPtr t = new IntPtr(excel.Hwnd); //得到这个句柄，具体作用是得到这块内存入口 int k = 0; GetWindowThreadProcessId(t, out k); //得到本进程唯一标志k System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); //得到对进程k的引用 p.Kill(); //关闭进程k } catch (System.Exception ex) { throw ex; } }} //关闭打开的Excel方法 public void CloseExcel(Microsoft.Office.Interop.Excel.Application ExcelApplication, Microsoft.Office.Interop.Excel.Workbook ExcelWorkbook){ ExcelWorkbook.Close(false, Type.Missing, Type.Missing); ExcelWorkbook = null; ExcelApplication.Quit(); GC.Collect(); KeyMyExcelProcess.Kill(ExcelApplication);} |
总结
以上就是本文关于C# 操作Excel代码总结的全部内容，希望对大家有所帮助。感兴趣的朋友可以继续参阅本站其他相关专题，如有不足之处，欢迎留言指出。感谢朋友们对本站的支持！
原文链接：http://www.cnblogs.com/qqnnhhbb/p/3601844.html
