C#Excel操作的用法
时间：2010-09-17 来源：狂风
这里是一些整理的Excel的一些简单的操作
ApplicationClass ExcelApp = New ApplicationClass();
2) 更改 Excel 标题栏：
ExcelApp.Caption := '应用程序调用 Microsoft Excel';
3) 添加新工作簿：
ExcelApp.WorkBooks.Add;
4) 打开已存在的工作簿：
ExcelApp.WorkBooks.Open( 'C:\Excel\Demo.xls' );
5) 设置第2个工作表为活动工作表：
ExcelApp.WorkSheets[2].Activate; 或
ExcelApp.WorksSheets[ 'Sheet2' ].Activate;
6) 给单元格赋值：
ExcelApp.Cells[1,4].Value := '第一行第四列';
7) 设置指定列的宽度（单位：字符个数），以第一列为例：
ExcelApp.ActiveSheet.Columns[1].ColumnsWidth := 5;
8) 设置指定行的高度（单位：磅）（1磅＝0.035厘米），以第二行为例：
ExcelApp.ActiveSheet.Rows[2].RowHeight := 1/0.035; // 1厘米
9) 在第8行之前插入分页符：
ExcelApp.WorkSheets[1].Rows[8].PageBreak := 1;
10) 在第8列之前删除分页符：
ExcelApp.ActiveSheet.Columns[4].PageBreak := 0;
11) 指定边框线宽度：
ExcelApp.ActiveSheet.Range[ 'B3:D4' ].Borders[2].Weight := 3;
1-左 2-右 3-顶 4-底 5-斜( \ ) 6-斜( / )
12) 清除第一行第四列单元格公式：
ExcelApp.ActiveSheet.Cells[1,4].ClearContents;
13) 设置第一行字体属性：
ExcelApp.ActiveSheet.Rows[1].Font.Name := '隶书';
ExcelApp.ActiveSheet.Rows[1].Font.Color := clBlue;
ExcelApp.ActiveSheet.Rows[1].Font.Bold := True;
ExcelApp.ActiveSheet.Rows[1].Font.UnderLine := True;
14) 进行页面设置：
a.页眉：
ExcelApp.ActiveSheet.PageSetup.CenterHeader := '报表演示';
b.页脚：
ExcelApp.ActiveSheet.PageSetup.CenterFooter := '第&P页';
c.页眉到顶端边距2cm：
ExcelApp.ActiveSheet.PageSetup.HeaderMargin := 2/0.035;
d.页脚到底端边距3cm：
ExcelApp.ActiveSheet.PageSetup.HeaderMargin := 3/0.035;
e.顶边距2cm：
ExcelApp.ActiveSheet.PageSetup.TopMargin := 2/0.035;
f.底边距2cm：
ExcelApp.ActiveSheet.PageSetup.BottomMargin := 2/0.035;
g.左边距2cm：
ExcelApp.ActiveSheet.PageSetup.LeftMargin := 2/0.035;
h.右边距2cm：
ExcelApp.ActiveSheet.PageSetup.RightMargin := 2/0.035;
i.页面水平居中：
ExcelApp.ActiveSheet.PageSetup.CenterHorizontally := 2/0.035;
j.页面垂直居中：
ExcelApp.ActiveSheet.PageSetup.CenterVertically := 2/0.035;
k.打印单元格网线：
ExcelApp.ActiveSheet.PageSetup.PrintGridLines := True;
15) 拷贝操作：
a.拷贝整个工作表：
ExcelApp.ActiveSheet.Used.Range.Copy;
b.拷贝指定区域：
ExcelApp.ActiveSheet.Range[ 'A1:E2' ].Copy;
c.从A1位置开始粘贴：
ExcelApp.ActiveSheet.Range.[ 'A1' ].PasteSpecial;
d.从文件尾部开始粘贴：
ExcelApp.ActiveSheet.Range.PasteSpecial;
16) 插入一行或一列：
a. ExcelApp.ActiveSheet.Rows[2].Insert;
b. ExcelApp.ActiveSheet.Columns[1].Insert;
17) 删除一行或一列：
a. ExcelApp.ActiveSheet.Rows[2].Delete;
b. ExcelApp.ActiveSheet.Columns[1].Delete;
18) 打印预览工作表：
ExcelApp.ActiveSheet.PrintPreview;
19) 打印输出工作表：
ExcelApp.ActiveSheet.PrintOut;
20) 工作表保存：
if not ExcelApp.ActiveWorkBook.Saved then
ExcelApp.ActiveSheet.PrintPreview;
21) 工作表另存为：
ExcelApp.SaveAs( 'C:\Excel\Demo1.xls' );
22) 放弃存盘：
ExcelApp.ActiveWorkBook.Saved := True;
23) 关闭工作簿：
ExcelApp.WorkBooks.Close;
24) 退出 Excel：
ExcelApp.Quit;
| 001 | /// <summary> |
| 002 | /// ExcelEdit 的摘要说明 |
| 003 | /// </summary> |
| 004 | public class ExcelEdit |
| 005 | { |
| 006 | public string mFilename; |
| 007 | public Microsoft.Office.Interop.Excel.Application app; |
| 008 | public Microsoft.Office.Interop.Excel.Workbooks wbs; |
| 009 | public Microsoft.Office.Interop.Excel.Workbook wb; |
| 010 | public Microsoft.Office.Interop.Excel.Worksheets wss; |
| 011 | public Microsoft.Office.Interop.Excel.Worksheet ws; |
| 012 | public ExcelEdit() |
| 013 | { |
| 014 | // |
| 015 | // TODO: 在此处添加构造函数逻辑 |
| 016 | // |
| 017 | } |
| 018 | public void Create()//创建一个Excel对象 |
| 019 | { |
| 020 | try |
| 021 | { |
| 022 | app = new Microsoft.Office.Interop.Excel.Application(); |
| 023 | wbs = app.Workbooks; |
| 024 | wb = wbs.Add(true); |
| 025 | } |
| 026 | catch (Exception ex) |
| 027 | { |
| 028 |
| 029 | throw new Exception(ex.Message); |
| 030 | } |
| 031 |
| 032 | } |
| 033 | /// <summary> |
| 034 | /// 读取Excel文档 |
| 035 | /// </summary> |
| 036 | /// <param name="Path">文件名称</param> |
| 037 | /// <returns>返回一个数据集</returns> |
| 038 | public DataTable ExcelToTable(string tablename) |
| 039 | { |
| 040 | string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + mFilename+ ";" + "Extended Properties=Excel 8.0;"; |
| 041 | OleDbConnection conn = new OleDbConnection(strConn); |
| 042 | conn.Open(); |
| 043 | string strExcel = ""; |
| 044 | OleDbDataAdapter myCommand = null; |
| 045 | DataTable dt = null; |
| 046 | strExcel = "select * from [" + tablename + "$]"; |
| 047 | myCommand = new OleDbDataAdapter(strExcel, strConn); |
| 048 | dt = new DataTable(); |
| 049 | myCommand.Fill(dt); |
| 050 | return dt; |
| 051 | } |
| 052 | public void Open(string FileName)//打开一个Excel文件 |
| 053 | { |
| 054 | try |
| 055 | { |
| 056 | app = new Microsoft.Office.Interop.Excel.Application(); |
| 057 | wbs = app.Workbooks; |
| 058 | wb = wbs.Add(FileName); |
| 059 | //wb = wbs.Open(FileName, 0, true, 5,"", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true,Type.Missing,Type.Missing); |
| 060 | //wb = wbs.Open(FileName,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Excel.XlPlatform.xlWindows,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing); |
| 061 | mFilename = FileName; |
| 062 | } |
| 063 | catch (Exception ex) |
| 064 | { |
| 065 |
| 066 | throw new Exception(ex.Message); |
| 067 | } |
| 068 |
| 069 | } |
| 070 | public Microsoft.Office.Interop.Excel.Worksheet GetSheet(string SheetName)//获取一个工作表 |
| 071 | { |
| 072 | Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[SheetName]; |
| 073 | return s; |
| 074 |
| 075 | } |
| 076 | public Microsoft.Office.Interop.Excel.Worksheet GetSheet(int index)//获取一个工作表 |
| 077 | { |
| 078 | Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[index]; |
| 079 | return s; |
| 080 | } |
| 081 | public Microsoft.Office.Interop.Excel.Worksheet AddSheet(string SheetName)//添加一个工作表 |
| 082 | { |
| 083 | Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); |
| 084 | s.Name = SheetName; |
| 085 | return s; |
| 086 | } |
| 087 |
| 088 | public void DelSheet(string SheetName)//删除一个工作表 |
| 089 | { |
| 090 | ((Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[SheetName]).Delete(); |
| 091 | } |
| 092 | public Microsoft.Office.Interop.Excel.Worksheet ReNameSheet(string OldSheetName, string NewSheetName)//重命名一个工作表一 |
| 093 | { |
| 094 | Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[OldSheetName]; |
| 095 | s.Name = NewSheetName; |
| 096 | return s; |
| 097 | } |
| 098 |
| 099 | public Microsoft.Office.Interop.Excel.Worksheet ReNameSheet(Microsoft.Office.Interop.Excel.Worksheet Sheet, string NewSheetName)//重命名一个工作表二 |
| 100 | { |
| 101 |
| 102 | Sheet.Name = NewSheetName; |
| 103 |
| 104 | return Sheet; |
| 105 | } |
| 106 |
| 107 | public void SetCellValue(Microsoft.Office.Interop.Excel.Worksheet ws, int x, int y, object value)//ws：要设值的工作表 X行Y列 value 值 |
| 108 | { |
| 109 | ws.Cells[x, y] = value; |
| 110 | } |
| 111 | public void SetCellValue(string ws, int x, int y, object value)//ws：要设值的工作表的名称 X行Y列 value 值 |
| 112 | { |
| 113 |
| 114 | GetSheet(ws).Cells[x, y] = value; |
| 115 |
| 116 | } |
| 117 |
| 118 | public void SetCellProperty(Microsoft.Office.Interop.Excel.Worksheet ws, int Startx, int Starty, int Endx, int Endy, int size, string name, Microsoft.Office.Interop.Excel.Constants color, Microsoft.Office.Interop.Excel.Constants HorizontalAlignment)//设置一个单元格的属性 字体， 大小，颜色 ，对齐方式 |
| 119 | { |
| 120 | name = "宋体"; |
| 121 | size = 12; |
| 122 | color = Microsoft.Office.Interop.Excel.Constants.xlAutomatic; |
| 123 | HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlRight; |
| 124 | ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Name = name; |
| 125 | ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Size = size; |
| 126 | ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Color = color; |
| 127 | ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).HorizontalAlignment = HorizontalAlignment; |
| 128 | } |
| 129 |
| 130 | public void SetCellProperty(string wsn, int Startx, int Starty, int Endx, int Endy, int size, string name, Microsoft.Office.Interop.Excel.Constants color, Microsoft.Office.Interop.Excel.Constants HorizontalAlignment) |
| 131 | { |
| 132 | //name = "宋体"; |
| 133 | //size = 12; |
| 134 | //color = Excel.Constants.xlAutomatic; |
| 135 | //HorizontalAlignment = Excel.Constants.xlRight; |
| 136 |
| 137 | Microsoft.Office.Interop.Excel.Worksheet ws = GetSheet(wsn); |
| 138 | ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Name = name; |
| 139 | ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Size = size; |
| 140 | ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Color = color; |
| 141 |
| 142 | ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).HorizontalAlignment = HorizontalAlignment; |
| 143 | } |
| 144 |
| 145 |
| 146 | public void UniteCells(Microsoft.Office.Interop.Excel.Worksheet ws, int x1, int y1, int x2, int y2)//合并单元格 |
| 147 | { |
| 148 | ws.get_Range(ws.Cells[x1, y1], ws.Cells[x2, y2]).Merge(Type.Missing); |
| 149 | } |
| 150 |
| 151 | public void UniteCells(string ws, int x1, int y1, int x2, int y2)//合并单元格 |
| 152 | { |
| 153 | GetSheet(ws).get_Range(GetSheet(ws).Cells[x1, y1], GetSheet(ws).Cells[x2, y2]).Merge(Type.Missing); |
| 154 |
| 155 | } |
| 156 |
| 157 |
| 158 | public void InsertTable(System.Data.DataTable dt, string ws, int startX, int startY)//将内存中数据表格插入到Excel指定工作表的指定位置 为在使用模板时控制格式时使用一 |
| 159 | { |
| 160 |
| 161 | for (int i = 0; i <= dt.Rows.Count - 1; i++) |
| 162 | { |
| 163 | for (int j = 0; j <= dt.Columns.Count - 1; j++) |
| 164 | { |
| 165 | GetSheet(ws).Cells[startX + i, j + startY] = dt.Rows[i][j].ToString(); |
| 166 |
| 167 | } |
| 168 |
| 169 | } |
| 170 |
| 171 | } |
| 172 | public void InsertTable(System.Data.DataTable dt, Microsoft.Office.Interop.Excel.Worksheet ws, int startX, int startY)//将内存中数据表格插入到Excel指定工作表的指定位置二 |
| 173 | { |
| 174 |
| 175 | for (int i = 0; i <= dt.Rows.Count - 1; i++) |
| 176 | { |
| 177 | for (int j = 0; j <= dt.Columns.Count - 1; j++) |
| 178 | { |
| 179 |
| 180 | ws.Cells[startX + i, j + startY] = dt.Rows[i][j]; |
| 181 |
| 182 | } |
| 183 |
| 184 | } |
| 185 |
| 186 | } |
| 187 |
| 188 |
| 189 | public void AddTable(System.Data.DataTable dt, string ws, int startX, int startY)//将内存中数据表格添加到Excel指定工作表的指定位置一 |
| 190 | { |
| 191 |
| 192 | for (int i = 0; i <= dt.Rows.Count - 1; i++) |
| 193 | { |
| 194 | for (int j = 0; j <= dt.Columns.Count - 1; j++) |
| 195 | { |
| 196 |
| 197 | GetSheet(ws).Cells[i + startX, j + startY] = dt.Rows[i][j]; |
| 198 |
| 199 | } |
| 200 |
| 201 | } |
| 202 |
| 203 | } |
| 204 | public void AddTable(System.Data.DataTable dt, Microsoft.Office.Interop.Excel.Worksheet ws, int startX, int startY)//将内存中数据表格添加到Excel指定工作表的指定位置二 |
| 205 | { |
| 206 |
| 207 |
| 208 | for (int i = 0; i <= dt.Rows.Count - 1; i++) |
| 209 | { |
| 210 | for (int j = 0; j <= dt.Columns.Count - 1; j++) |
| 211 | { |
| 212 |
| 213 | ws.Cells[i + startX, j + startY] = dt.Rows[i][j]; |
| 214 |
| 215 | } |
| 216 | } |
| 217 |
| 218 | } |
| 219 |
| 220 | public void InsertActiveChart(Microsoft.Office.Interop.Excel.XlChartType ChartType, string ws, int DataSourcesX1, int DataSourcesY1, int DataSourcesX2, int DataSourcesY2, Microsoft.Office.Interop.Excel.XlRowCol ChartDataType)//插入图表操作 |
| 221 | { |
| 222 | ChartDataType = Microsoft.Office.Interop.Excel.XlRowCol.xlColumns; |
| 223 | wb.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); |
| 224 | { |
| 225 | wb.ActiveChart.ChartType = ChartType; |
| 226 | wb.ActiveChart.SetSourceData(GetSheet(ws).get_Range(GetSheet(ws).Cells[DataSourcesX1, DataSourcesY1], GetSheet(ws).Cells[DataSourcesX2, DataSourcesY2]), ChartDataType); |
| 227 | wb.ActiveChart.Location(Microsoft.Office.Interop.Excel.XlChartLocation.xlLocationAsObject, ws); |
| 228 | } |
| 229 | } |
| 230 | public bool Save()//保存文档 |
| 231 | { |
| 232 | if (mFilename == "") |
| 233 | { |
| 234 | return false; |
| 235 | } |
| 236 | else |
| 237 | { |
| 238 | try |
| 239 | { |
| 240 | wb.Save(); |
| 241 | return true; |
| 242 | } |
| 243 |
| 244 | catch (Exception ex) |
| 245 | { |
| 246 | return false; |
| 247 | } |
| 248 | } |
| 249 | } |
| 250 | public bool SaveAs(object FileName)//文档另存为 |
| 251 | { |
| 252 | try |
| 253 | { |
| 254 | wb.SaveAs(FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); |
| 255 | return true; |
| 256 |
| 257 | } |
| 258 |
| 259 | catch (Exception ex) |
| 260 | { |
| 261 | return false; |
| 262 |
| 263 | } |
| 264 | } |
| 265 | public void Close()//关闭一个Excel对象，销毁对象 |
| 266 | { |
| 267 | //wb.Save(); |
| 268 | wb.Close(Type.Missing, Type.Missing, Type.Missing); |
| 269 | wbs.Close(); |
| 270 | app.Quit(); |
| 271 | wb = null; |
| 272 | wbs = null; |
| 273 | app = null; |
| 274 | GC.Collect(); |
| 275 | } |
| 276 | } |
相关阅读 更多 +
