最近在做C#导出excel,虽然以也做过,但是数据量没有现在这么多的。
第一种方法就是以后组件的方式来做:
//public static void OutExcel(List<SubStationRealtimedata> listData, string str) //{ // Microsoft.Office.Interop.Excel.Application excelApp = new Application(); // Microsoft.Office.Interop.Excel.Workbook wbook = null; // Microsoft.Office.Interop.Excel.Sheets sheets =null; // Microsoft.Office.Interop.Excel.Worksheet wkSheet = null; // Microsoft.Office.Interop.Excel.Range range = null; // Microsoft.Office.Interop.Excel.Range rangeWire = null; // wbook = excelApp.Workbooks.Add(true); // sheets = wbook.Worksheets; // wkSheet = (Worksheet)sheets.get_Item(1); // System.Windows.Forms.Application.DoEvents(); // range = wkSheet.get_Range("A1", Missing.Value); // rangeWire = wkSheet.get_Range("B1", Missing.Value); // range.Value2 = "浙江"; // rangeWire.Value2 = "某某线"; // wkSheet.Cells[4, 1] = "时间"; // for (int i = 0; i < arrayList.Count; i++) // { // wkSheet.Cells[4, i + 2] = arrayList[i]; // int j=5; // foreach(SubStationRealtimedata subdata in listData) // { // wkSheet.Cells[j, 1] = subdata.SendTime; // wkSheet.Cells[j, i + 2] = subdata.CurrentPhaseA; // j++; // } // } // wbook.Close(); // excelApp.Quit(); //}
由于以上种方法速度太慢,后来经过园子的网友推荐用npoi,这种流的方式效率非常高,网上先下载npoi组件。
SaveFileDialog dialog = new SaveFileDialog(); dialog.Filter = "Excel文件|*.xls"; if (dialog.ShowDialog() == DialogResult.No) { } string filename = dialog.FileName; HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("数据"); //列表头 IRow rowheader = sheet.CreateRow(4); //这里必须固定 rowheader.CreateCell(0, CellType.String).SetCellValue("时间"); for (int i = 1; i <= arrayList.Count; i++) { rowheader.CreateCell(i, CellType.String).SetCellValue(arrayList[i-1].ToString()); } ArrayList arrayListData = null; //内容 int j = 5; foreach (SubStationRealtimedata subdata in listData) { #region 先放入一个arraylist arrayListData = new ArrayList(); if (pexcel != null) { if (pexcel.PCurrentA) { arrayListData.Add(subdata.CurrentPhaseA); } if (pexcel.PCurrentB) { arrayListData.Add(subdata.CurrentPhaseB); } if (pexcel.PCurrentC) { arrayListData.Add(subdata.CurrentPhaseB); } if (pexcel.PVoltageA) { arrayListData.Add(subdata.CurrentPhaseB); } if (pexcel.PVoltageB) { arrayListData.Add(subdata.CurrentPhaseB); } if (pexcel.PVoltageC) { arrayListData.Add(subdata.CurrentPhaseB); } if (pexcel.PharmonicPrecent3) { arrayListData.Add(subdata.harmonicPrecent3); } if (pexcel.PharmonicPrecent5) { arrayListData.Add(subdata.harmonicPrecent5); } if (pexcel.PharmonicPrecent7) { arrayListData.Add(subdata.harmonicPrecent7); } if (pexcel.PharmonicPrecent9) { arrayListData.Add(subdata.harmonicPrecent9); } if (pexcel.PharmonicPrecent11) { arrayListData.Add(subdata.harmonicPrecent11); } if (pexcel.PharmonicPrecent13) { arrayListData.Add(subdata.harmonicPrecent13); } if (pexcel.PActive) { arrayListData.Add(subdata.Active); } if (pexcel.PReactive) { arrayListData.Add(subdata.Reactive); } } #endregion IRow rowcontent = sheet.CreateRow(j); rowcontent.CreateCell(0, CellType.String).SetCellValue(subdata.SendTime); for (int i = 1; i <= arrayListData.Count; i++) { rowcontent.CreateCell(i, CellType.String).SetCellValue(arrayListData[i-1].ToString()); } j++; } //最后输出流就可以了。呵呵!非常方便。 using (Stream stream = File.OpenWrite(filename)) { workbook.Write(stream); }