c#excel的操作例子

 1 class MyData//存储行数据
 2 {
 3 public List<string> RowData { get; set; } 
 4 }
 5 
 6 static void Main(string[] args)
 7 {
 8 
 9 Application app=new ApplicationClass();
10 app.DisplayAlerts = false;
11 var path = @"C:\Users\Administrator\Desktop\JTWC台风季1979-2010.xls";//读取数据的excel
12 var path2=@"C:\Users\Administrator\Desktop\JTWC.xlsx";//存储数据的excel
13 var workbook=app.Workbooks.Open(path, Missing.Value, Missing.Value, Missing.Value, Missing.Value
14 , Missing.Value, Missing.Value, Missing.Value, Missing.Value
15 , Missing.Value, Missing.Value, Missing.Value, Missing.Value);//打开指定位置的excel文件
16 Worksheet sheet=(Worksheet)workbook.Sheets[1];//获取第一个table
17 var resultWorkBook= app.Workbooks.Open(path2);
18 var resultSheet =(Worksheet) resultWorkBook.Sheets[1];
19 
20 //
21 var resultData=new List<MyData>();
22 
23 string numberAndNameStr = string.Empty;
24 for (int i = 1; i < 31039; i++)
25 {
26 var number = ((Range) sheet.Cells[i, 1]).Text.ToString();
27 var name = ((Range) sheet.Cells[i, 2]).Text.ToString();
28 var tempStr = number + name;
29 if (tempStr != numberAndNameStr)//根据每一行的前两个cell的组合,判断这一行数据是否属于同一个Typhoon(这是一个具体问题)
30 {
31 var str = new List<string>
32 {
33 ((Range) sheet.Cells[i, 1]).Text.ToString(),
34 ((Range) sheet.Cells[i, 2]).Text.ToString(),
35 ((Range) sheet.Cells[i, 3]).Text.ToString(),
36 ((Range) sheet.Cells[i, 4]).Text.ToString(),
37 ((Range) sheet.Cells[i, 5]).Text.ToString(),
38 ((Range) sheet.Cells[i, 6]).Text.ToString(),
39 ((Range) sheet.Cells[i, 7]).Text.ToString(),
40 ((Range) sheet.Cells[i, 8]).Text.ToString()
41 
42 };
43 var data = new MyData() {RowData = str};
44 resultData.Add(data);
45 //写入excel数据
46 numberAndNameStr = tempStr;
47 }
48 }
49 int row = 1;
50 foreach (MyData myData in resultData)//循环每一行数据
51 {
52 for (int j = 0; j < myData.RowData.Count; j++)//循环一行数据的所有cell
53 {
54 resultSheet.Cells[row, j + 1] = myData.RowData[j];
55 }
56 row++;
57 }
58 
59 app.AlertBeforeOverwriting = false; //屏蔽掉系统跳出的Alert
60 //workbook.Save();
61 resultWorkBook.SaveAs(path2, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);//用save方法会保存失败
62 
63 app.Quit();
64 System.Runtime.InteropServices.Marshal.ReleaseComObject(app);//释放资源
65 Console.WriteLine("Over");
66 }
View Code

 

posted @ 2014-10-09 20:38  李辉健  阅读(439)  评论(0编辑  收藏  举报