Acey.ExcelX实例演练(2)
-- 从ListView中导出Excel报表
关键词:ListView,数据绑定,导入Excel,导出Excel
WinForm中用来显示数据时我们常用ListView,有时我们需要将数据从Excel表格导入到系统中,有时我们也会将ListView中的数据导出至xcel,今天我们用一个小程序来练习一下这两个功能。
首先建一个WinForm的项目,在窗口中添加一个toolStrip,在其中添加两个button,一个用于导入Excel,一个用于导出Excel。再添加一个Listview。如图所示。
public partial class AceyOffice : Form
{
public AceyOffice()
{
InitializeComponent();
InitLvPart("StudentInfo");
}
//初始化Listview的显示列
private void InitLvPart(string customflg)
{
if (customflg == "StudentInfo")
{
this.listView1.Clear();
this.listView1.Columns.Add("姓名");
this.listView1.Columns.Add("学号");
this.listView1.Columns.Add("数学");
this.listView1.Columns.Add("语文");
this.listView1.Columns.Add("英语");
this.listView1.Columns.Add("总分");
}
}
//导入Excel
private void toolStripButton1_Click(object sender, EventArgs e)
{
OpenFileDialog ofdlg = new OpenFileDialog();
ofdlg.Filter = "(*.xls)|*.xls";
ofdlg.Title = "请选择需导入Excel文件";
if (ofdlg.ShowDialog() == DialogResult.OK)
{
string filepath = ofdlg.FileName;
IWorkbook workbook = ExcelxApplication.Open(filepath);
IWorksheet worksheet = workbook.Worksheets[0];
List<List<string>> studentsinfo = new List<List<string>>();
//获取Excel表括格中的所有信息
for (int i = 1; i < worksheet.Rows.Count;i++ )
{
if (worksheet.Cells[i, 0].Value != null)
{
List<string> student = new List<string>();
string studentname = worksheet.Cells[i, 0].Value.ToString();
student.Add(studentname);
string studentnumber = worksheet.Cells[i, 1].Value.ToString();
student.Add(studentnumber);
string math = worksheet.Cells[i, 2].Value.ToString();
student.Add(math);
string chinese = worksheet.Cells[i, 3].Value.ToString();
student.Add(chinese);
string english = worksheet.Cells[i, 4].Value.ToString();
student.Add(english);
string total = worksheet.Cells[i, 5].Value.ToString();
student.Add(total);
studentsinfo.Add(student);
}
else
break;
}
//绑定ListView
for(int j=0;j<studentsinfo.Count;j++)
{
ListViewItem item = new ListViewItem();
item.Text = studentsinfo[j][0];
item.SubItems.Add(studentsinfo[j][1]);
item.SubItems.Add(studentsinfo[j][2]);
item.SubItems.Add(studentsinfo[j][3]);
item.SubItems.Add(studentsinfo[j][4]);
item.SubItems.Add(studentsinfo[j][5]);
this.listView1.Items.Add(item);
}
}
}
//导出Excel
private void toolStripButton2_Click(object sender, EventArgs e)
{
IWorkbook workbook = ExcelxApplication.CreateWorkbook();
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Cells[0, 0].Value = "姓名";
worksheet.Cells[0, 1].Value = "学号";
worksheet.Cells[0, 2].Value = "语文";
worksheet.Cells[0, 3].Value = "数学";
worksheet.Cells[0, 4].Value = "英语";
worksheet.Cells[0, 5].Value = "总分";
for (int i = 0; i < this.listView1.Items.Count; i++)
{
worksheet.Cells[i + 1, 0].Value = this.listView1.Items[i].SubItems[0].Text;
worksheet.Cells[i + 1, 1].Value = this.listView1.Items[i].SubItems[1].Text;
worksheet.Cells[i + 1, 2].Value = this.listView1.Items[i].SubItems[2].Text;
worksheet.Cells[i + 1, 3].Value = this.listView1.Items[i].SubItems[3].Text;
worksheet.Cells[i + 1, 4].Value = this.listView1.Items[i].SubItems[4].Text;
worksheet.Cells[i + 1, 5].Value = this.listView1.Items[i].SubItems[5].Text;
}
workbook.SaveAs("C:\\StudentInfo.xls", FileFormat.Excel97To2003);
}
}