本文共 3885 字,大约阅读时间需要 12 分钟。
相关网址:http://www.cnblogs.com/luxiaoxun/p/3374992.html
方法1:http://www.cnblogs.com/luxiaoxun/p/3374992.html
封装重写; list 导入数据方法:
/// <summary>
/// 将List数据导入到excel中 /// </summary> /// <param name="data">要导入的数据</param> /// <param name="isColumnWritten">DataTable的列名是否要导入</param> /// <param name="sheetName">要导入的excel的sheet的名称</param> /// <param name="cols">列名集合</param> /// <returns>导入数据行数(包含列名那一行)</returns> public int ListToExcel<T>(List<T> data, string sheetName, string[] cols, bool isColumnWritten) { int i = 0; int j = 0; int count = 0; ISheet sheet = null; fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite); if (fileName.IndexOf(".xlsx") > 0) // 2007版本 workbook = new XSSFWorkbook(); else if (fileName.IndexOf(".xls") > 0) // 2003版本 workbook = new HSSFWorkbook(); try { if (workbook != null) { sheetName = string.IsNullOrEmpty(sheetName) ? System.IO.Path.GetFileNameWithoutExtension(fileName) : sheetName; sheet = workbook.CreateSheet(sheetName); } else { return -1; } if (isColumnWritten == true) //写入DataTable的列名 { IRow row = sheet.CreateRow(0); for (j = 0; j < cols.Length; ++j) { string[] str = cols[j].Split(','); row.CreateCell(j).SetCellValue(str[1]); } count = 1; } else { count = 0; } Type entityType = data[0].GetType(); System.Reflection.PropertyInfo[] entityProperties = entityType.GetProperties(); //将所有entity添加到导出的row中 foreach (object entity in data) { IRow row = sheet.CreateRow(count); //检查所有的的实体都为同一类型 if (entity.GetType() != entityType) { throw new Exception("要转换的集合元素类型不一致"); } object[] entityValues = new object[entityProperties.Length]; for (int m = 0; m < entityProperties.Length; m++) { for (j = 0; j < cols.Length; ++j) { string[] str = cols[j].Split(','); if (entityProperties[m].Name == str[0]) { object valuetemp = entityProperties[m].GetValue(entity, null); if (valuetemp != null) { row.CreateCell(j).SetCellValue(valuetemp.ToString()); } } } } ++count; } workbook.Write(fs); //写入到excel Dispose(); return count; } catch (Exception ex) { Console.WriteLine("Exception: " + ex.Message); return -1; } }方法2: Microsoft.Office.Interop.Excel dll
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
//Microsoft.Office.Interop.Excel.Workbooks workbooks = ExcelApp.Workbooks; //Microsoft.Office.Interop.Excel.Workbook workBook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); //Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[1];//取得sheet1 //打开一个WorkBook Microsoft.Office.Interop.Excel.Workbooks workbooks = ExcelApp.Workbooks; Microsoft.Office.Interop.Excel.Workbook workBook = ExcelApp.Workbooks.Open(tf, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //得到WorkSheet对象 Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets.get_Item(1); for (int i = 0; i < workSheet.Rows.Count; i++) { // Response.Write("row{0}:" + workSheet.Rows[i].ToString()); for (int j = 0; j < workSheet.Cells.Count; j++) { Response.Write("cell[i,j]:" + workSheet.Cells[i, j].ToString()); } } return; // ExcelApp.Caption ="test"; //ExcelApp.Workbooks.Open(tf, // Type.Missing, Type.Missing, Type.Missing, Type.Missing, // Type.Missing, Type.Missing, Type.Missing, Type.Missing, // Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); 得到WorkSheet对象 //Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets.get_Item(1); //DataTable dt = (DataTable)ExcelApp.Worksheets["Sheet1"]; //foreach (DataRow item in dt.Rows) //{ //} ExcelApp.Workbooks.Close(); ExcelApp.Quit(); //ExcelApp.WorkBooks.Open( 'C:\Excel\Demo.xls' );