C # Excel operation example

 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";//读取数据的excel12 var path2=@"C:\Users\Administrator\Desktop\JTWC.xlsx";//存储数据的excel13 var workbook=app.Workbooks.Open(path, Missing.Value, Missing.Value, Missing.Value, Missing.Value14 , Missing.Value, Missing.Value, Missing.Value, Missing.Value15 , Missing.Value, Missing.Value, Missing.Value, Missing.Value);//打开指定位置的excel文件16 Worksheet sheet=(Worksheet)workbook.Sheets[1];//获取第一个table17 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++)//循环一行数据的所有cell53 {54 resultSheet.Cells[row, j + 1] = myData.RowData[j];55 }56 row++;57 }58 59 app.AlertBeforeOverwriting = false; //屏蔽掉系统跳出的Alert60 //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

 

C # Excel operation example