c# datatable导入execl

来源：互联网发布：php 获取主域名编辑：程序博客网时间：2024/05/21 23:34

//弹出窗体

private System.Windows.Forms.SaveFileDialog m_objSave = new SaveFileDialog();
//导出数据方法
        public void m_mthExportToExecl()
        {
            BLL.JC_Form.JC_CommZoneBasicInfo zb = new BLL.JC_Form.JC_CommZoneBasicInfo();
            DataSet ds = zb.GetAll("");

//创建datatable
            DataTable dt = new DataTable();
            dt.Columns.Add("A", typeof(string));
            dt.Columns.Add("B", typeof(string));
            dt.Columns.Add("C", typeof(string));
            dt.Columns.Add("D", typeof(string));
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                DataRow datarow = dt.NewRow();
                if (i % 2 == 0)
                {
                    datarow["A"] = "队名";
                    datarow["B"] = ds.Tables[0].Rows[i]["ZoneCode"].ToString();
                    datarow["C"] = "含水量";
                    datarow["D"] = ds.Tables[0].Rows[i]["ZoneCode"].ToString();
                }
                else
                {
                    datarow["A"] = "井号";
                    datarow["B"] = ds.Tables[0].Rows[i]["Disable"].ToString();
                    datarow["C"] = "日期";
                    datarow["D"] = ds.Tables[0].Rows[i]["ToExamine"].ToString();
                }
                dt.Rows.Add(datarow);
            }
            this.m_objSave.DefaultExt = "xls";
            this.m_objSave.Filter = "Excel文件(*.xls)|*.xls";

            if (this.m_objSave.ShowDialog() == DialogResult.OK)
            {
                m_mthDoExport(dt, m_objSave.FileName);
            }

}

//具体导出的方法
        private void m_mthDoExport(DataTable dtSource, string strFileName)
        {
            int rowNum = dtSource.Rows.Count;
            int columnNum = dtSource.Columns.Count;
            int rowIndex = 1;
            int columnIndex = 0;

            if (dtSource == null || string.IsNullOrEmpty(strFileName))
            {
                return;
            }
            if (rowNum > 0)
            {
                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
                xlApp.DefaultFilePath = "";
                xlApp.DisplayAlerts = true;
                xlApp.SheetsInNewWorkbook = 1;
                Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
                //将DataTable的列名导入Excel表第一行
                foreach (DataColumn dc in dtSource.Columns)
                {
                    columnIndex++;
                    xlApp.Cells[rowIndex, columnIndex] = dc.ColumnName;
                }
                //将DataTable中的数据导入Excel中
                for (int i = 0; i < rowNum; i++)
                {
                    rowIndex++;
                    columnIndex = 0;
                    for (int j = 0; j < columnNum; j++)
                    {
                        columnIndex++;
                        xlApp.Cells[rowIndex, columnIndex] = dtSource.Rows[i][j].ToString();
                    }
                }
                xlBook.SaveCopyAs(strFileName);
                xlApp = null;
                xlBook = null;
            }
        }