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
{
///
/// 将数据保存到Excel中
///
/// 待保存数据
/// 保存文件名(含路径)
public void FileSave(List
{
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
}
///
/// C#关闭指定进程
///
///
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
}
}
}
}
}
}