最简单DataSet导出EXCEL并进行格式控制等操作-优快云博客

本文链接：https://blog.youkuaiyun.com/cai_zhi_qiang/article/details/2234210

本文介绍了一种使用DataSet将数据导出到Excel的方法，包括检查Excel安装、打开模板文件、填充数据集中的数据到Excel工作表，并进行格式设置等步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

<public void ExportExcel(DataSet ds)
{

if (ds == null) return;

Excel.Application xlApp = new Excel.Application();

            if (xlApp == null)
            {
                MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
                return;
            }

            Excel.Workbooks workbooks = xlApp.Workbooks;
           // Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            //以下为读模版
            Excel.Workbook workbook = ((Excel.Workbook)workbooks.Open(@"c:/human.xlt", Type.Missing, Type.Missing,
                              Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                              Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing));

            Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
            Excel.Range range;

            string Caption = this.textBox1.Text;
            long totalCount = ds.Tables[0].Rows.Count;
            long rowRead = 0;

            xlApp.Application.DisplayAlerts = false;
            Excel.Range ran= worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 11]);
            ran.Merge(true);
            ran.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            ran.Font.Size = 18;
            worksheet.Cells[1, 1] = Caption;
            //写入字段
            for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
            {

                worksheet.Cells[2, i + 1] = ds.Tables[0].Columns[i].ColumnName;
                range = (Excel.Range)worksheet.Cells[2, i + 1];
                range.Interior.ColorIndex = 20;
                range.Font.Color = 200;
                //range.get_Value(SystemColors.GrayText);
                range.Font.Bold = true;

}
//写入数值

            for (int r = 0; r < ds.Tables[0].Rows.Count; r++)
            {
                for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                {
                    worksheet.Cells[r + 3, i + 1] = ds.Tables[0].Rows[r][i];
                }
                rowRead++;

            }
            range = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[ds.Tables[0].Rows.Count + 2, ds.Tables[0].Columns.Count]);


            range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
            range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
            range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
            range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;

            if (ds.Tables[0].Columns.Count > 1)
            {
                range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
                range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
                range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
            }

            range.EntireColumn.AutoFit();
            range.EntireRow.AutoFit();
            range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
            xlApp.Visible = true;
        }

}
}