将数据从sql导出到Excel [英] Export datas from sql to Excel

问题描述

我想将sql表中的所有数据复制到Excel文件中.如何使用vb.net进行操作.

请帮助我

I want copy all the datas in my sql table to Excel file. How to do it using vb.net.

Please help me

推荐答案

此站点上有一些文章可以导出到Excel.可能会有一个将数据表转换为电子表格.我已经完成了一些导出到Excel的工作，并且该框架使操作变得非常容易.
您必须进行一些自定义才能使其正常运行，但这应该为您提供了一个起点:

There are some articles on this site for exporting to Excel. There might be one that will do a datatable to spreadsheet conversion. I have done some work on exporting to Excel, and the framework makes it quite easy.
You will have to do some customization to make it work, but this should give you a start:

private static void Excel(string fileName, List<IDirectoryInventoryDataCollector> list)
{
  try
  {
    var xlApp = new Excel.Application();
    var xlWorkBook = xlApp.Workbooks.Add();
    var xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
    ExcelTitleRow(list[0], 1, xlWorkSheet);

    int row = 2;
    foreach (var item in list)
    {
      ExcelFillRow(item, row++, xlWorkSheet);
    }

    for (int i = 1; i < list[0].MaxLevel - 1; i++)
    {
      ((Range)xlWorkSheet.Columns[i]).ColumnWidth = 2;
    }
    ((Range)xlWorkSheet.Columns[list[0].MaxLevel - 1]).ColumnWidth = 30;
    ((Range)xlWorkSheet.Rows[1]).WrapText = true;
    ((Range)xlWorkSheet.Rows[1]).HorizontalAlignment = HorizontalAlignment.Center;
    ((Range)xlWorkSheet.Cells[1, 1]).WrapText = false;

    xlWorkBook.SaveAs(fileName);
    xlWorkBook.Close();
    xlApp.Quit();
  }
  catch (AccessViolationException)
  {
    System.Windows.Forms.MessageBox.Show(
       "Have encountered access violation. This could be issue with Excel 2000 if that is only version installed on computer",
       "Access Violation");
  }
  catch (Exception)
  {
    System.Windows.Forms.MessageBox.Show("Unknown error",
       "Unknown error");
  }
}

private static void ExcelFillRow(IDirectoryInventoryDataCollector item, int row, Excel.Worksheet sheet)
{
  sheet.Cells[row, item.Level] = item.Name;
  int column = item.MaxLevel;
  foreach (var property in item.GetProperties())
  {
    sheet.Cells[row, column++] = property;
  }
}

private static void ExcelTitleRow(IDirectoryInventoryDataCollector item, int row, Excel.Worksheet sheet)
{
  sheet.Cells[row, 1] = "Name";
  int column = item.MaxLevel;
  foreach (var property in item.GetPropertyNames())
  {
    sheet.Cells[row, column++] = property;
  }
}

您将需要添加对Microsoft.Office.Interop.Excel

You will need to add a reference to Microsoft.Office.Interop.Excel

这篇关于将数据从sql导出到Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！