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 }
Do something useful!