winform导出excel

winform导出excel

1. 拖控件-button

2. 准备datatable数据

DataTable dt = new DataTable();
            dt.Columns.Add("name");
            dt.Columns.Add("age");

            DataRow dr = null;
            dr = dt.NewRow();
            dr["name"] = "zhangsan拉拉";
            dr["age"] = "18";
            dt.Rows.Add(dr);

            dr = dt.NewRow();
            dr["name"] = "lisi拉拉";
            dr["age"] = "19";
            dt.Rows.Add(dr);

3. button点击事件

private void button1_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("name");
            dt.Columns.Add("age");

            DataRow dr = null;
            dr = dt.NewRow();
            dr["name"] = "zhangsan拉拉";
            dr["age"] = "18";
            dt.Rows.Add(dr);

            dr = dt.NewRow();
            dr["name"] = "lisi拉拉";
            dr["age"] = "19";
            dt.Rows.Add(dr);

            DataTableToExcel(dt, AppDomain.CurrentDomain.SetupInformation.ApplicationBase+"/test3.xls");
        }

        public static void DataTableToExcel(DataTable dt, string path)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            //创建工作表 
            ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);
            #region 标题
            //在工作表中添加一行
            IRow row = sheet.CreateRow(0);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                //在行中添加一列
                ICell cell = row.CreateCell(i);
                //设置列的内容	 
                cell.SetCellValue(dt.Columns[i].ColumnName);
            }
            #endregion
            #region 填充数据
            for (int i = 1; i <= dt.Rows.Count; i++)//遍历DataTable行
            {
                DataRow dataRow = dt.Rows[i - 1];
                row = sheet.CreateRow(i);//在工作表中添加一行

                for (int j = 0; j < dt.Columns.Count; j++)//遍历DataTable列
                {
                    ICell cell = row.CreateCell(j);//在行中添加一列
                    cell.SetCellValue(dataRow[j].ToString());//设置列的内容	 
                }
            }
            #endregion
            #region 输出Excel
            MemoryStream ms = new MemoryStream();
            workbook.Write(ms);
            using (FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write))
            {
                byte[] bArr = ms.ToArray();
                fs.Write(bArr, 0, bArr.Length);
                fs.Flush();
            }
            #endregion
        }