C# 将list集合中的信息保存到Excel中

程序中下载数据时，一般会将数据库中的信息保存到excel中。本例中，将list集合Student对象中的属性保存到excel中。展示如下：

需要添加Com组件，并引入excel命名空间。using Microsoft.Office.Interop.Excel;

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Drawing;//Image
using System.IO;//File
using Models.Ext;
using Microsoft.Office.Interop.Excel;
using System.Diagnostics;//Process
using System.ComponentModel;//Win32Exception

namespace Common
{
public class ExcelPrint
{
/// <summary>
/// 将数据保存到Excel中
/// </summary>
/// <param name="objStudent">待保存数据</param>
/// <param name="fileName">保存文件名(含路径)</param>
public void FileSave(List<StudentExt> list, string fileName)
{
KillProcess("EXCEL");

//创建Excel工作簿对象
Microsoft.Office.Interop.Excel.Application excelApp = new Application();

// 设置禁止弹出保存和覆盖的询问提示框
excelApp.DisplayAlerts = false;
excelApp.AlertBeforeOverwriting = false;

//创建工作簿集workbooks
Workbooks objBooks = excelApp.Workbooks;
//创建一个工作簿workbook
//Workbook objBook = objBooks.Add();添加一个新的工作簿
Workbook objBook = objBooks.Open(fileName);//打开原来存在的Excel工作簿

//保留第一个sheet
Worksheet objSheet = null;
int sheetCount = objBook.Sheets.Count;
for (int i = sheetCount; i >1; --i)
{
objSheet = objBook.Sheets.Item[i];
objSheet.Delete();
}
//复制第一个表的格式
Worksheet originSheet = objBook.Sheets.Item[1];//定位第一个sheet
Worksheet currentSheet = null;
//根据传入的list参数集合，新增sheet
for (int i = 1; i <= list.Count; ++i)
{
originSheet.Copy(After: objBook.Sheets.Item[objBook.Sheets.Count]);//将第一个sheet复制到最后面
currentSheet = objBook.Sheets.Item[objBook.Sheets.Count];//获得最新生成的sheet
currentSheet.Name = list[i - 1].ObjStudent.StudentName;//重新命名
//写入学员信息
currentSheet.Cells[2, 2] = list[i - 1].ObjStudent.StudentName;
currentSheet.Cells[2, 4] = list[i - 1].ObjStudent.Gender;
currentSheet.Cells[3, 2] = list[i - 1].ObjStudent.StudentId;
currentSheet.Cells[3, 4] = list[i - 1].ClassName;
currentSheet.Cells[4, 2] = list[i - 1].ObjStudent.Age;
currentSheet.Cells[4, 4] = list[i - 1].ObjStudent.CardNo;
currentSheet.Cells[5, 2] = list[i - 1].ObjStudent.Birthday;
currentSheet.Cells[5, 4] = list[i - 1].ObjStudent.PhoneNumber;
currentSheet.Cells[6, 2] = "'" + list[i - 1].ObjStudent.StudentIdNo;
currentSheet.Cells[7, 2] = list[i - 1].ObjStudent.StudentAddress;
//保存图片
if (list[i - 1].ObjStudent.StuImage != null && list[i - 1].ObjStudent.StuImage.Length != 0)
{
Image objImage = (Image)new Common.SerializeObjectToString().DeserializeObject(list[i - 1].ObjStudent.StuImage);
string path = fileName.Substring(0, fileName.LastIndexOf("\\"));
if (File.Exists(path + "\\Student.jpg"))
File.Delete(path + "\\Student.jpg");
else
{
objImage.Save(path + "\\Student.jpg");
currentSheet.Shapes.AddPicture(path + "\\Student.jpg", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, 285, 47, 70, 80);
File.Delete(path + "\\Student.jpg");
}
}
}

//另存为+关闭
objBook.SaveAs(fileName);
objBook.Close();

//关闭Excel进程
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);

//关闭Excel任务管理器中的Excel进程，（有时候任务管理器中依旧存在Excel进程）不知道有没有用
KillProcess("EXCEL");

#region excel其他操作
//增加工作表worksheet
//Worksheet objSheet = (Worksheet)excelApp.Worksheets.Add(Type.Missing,Type.Missing, 1,XlSheetType.xlWorksheet);

//另存为+关闭
//string excelPath = Environment.CurrentDirectory;
//string file = fileName.Substring(fileName.LastIndexOf("\\") + 1);文件名

//打开Excel
//excelApp.Visible = true;
#endregion
}
/// <summary>
/// C#关闭指定进程
/// </summary>
/// <param name="strProcessesByName"></param>
public void KillProcess(string strProcessesByName)
{
foreach (Process p in Process.GetProcesses())
{
if (p.ProcessName.ToUpper().Contains(strProcessesByName))
{
try
{
p.Kill();
p.WaitForExit();//possibly with a timeout
}
catch (Win32Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);//process was terminatine or can't be terminated -deal with it
}
catch(InvalidOperationException ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);//process has already exited-might be able to let this one go
}
}
}
}
}
}