CODEWITHSUNDEEP
c#asp.netsql-serverexport-to-excel
Ankur Agarwal
Ankur Agarwal

Reputation: 21

Export a table from SQL Server 2008 Database to excel using c#

I want to export a table from SQL Server 2008 Database to Excel sheet with the same constraints that were in database schema. For example, if in database a column has a datatype of integer or varchar then in the excel sheet it should have same datatype and there should be an error message that should be displayed in excel sheet itself if someone tries to enter invalid data into the columns.

I found a code script here but it is not implementing the constraints.

Upvotes: 2

Views: 1433

Answers (2)

Yogesh Jadhav
Yogesh Jadhav

Reputation: 11

//This is my Code that I have used for sql server to Excel database Dump //Try It

private void button1_Click_2(object sender, EventArgs e)
{
    DataSet DtSetmatch = new DataSet();
    SQL.DataTable dttt = new SQL.DataTable();
    string selectedTable = cmbImportItemList.Text;
    using (SqlDataAdapter adater = new SqlDataAdapter("Select * from "+selectedTable, new SqlConnection(Properties.Settings.Default.connectionstring2)))
    {
        adater.Fill(dttt);
    }          
    Excel.Application oXL;
    Excel._Workbook oWB;
    Excel._Worksheet oSheet;
    oXL = new Excel.Application();
    oXL.Visible = true;            
    oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
    oSheet = (Excel._Worksheet)oWB.ActiveSheet;
    SQL.DataTable dtCategories = dttt.DefaultView.ToTable(true,dttt.Columns[0].ToString());

    foreach (SQL.DataRow category in dtCategories.Rows)
    {
        oSheet = (Excel._Worksheet)oXL.Worksheets.Add();
        oSheet.Name = category[0].ToString()
            .Replace(" ", "")
            .Replace("  ", "")
            .Replace("/", "")
            .Replace("\\", "")
            .Replace("*", "");                    
        string[] colNames = new string[dttt.Columns.Count];
        int col = 0;
        try
        {
            foreach (SQL.DataColumn dc in dttt.Columns) colNames[col++] = dc.ColumnName;////dc.ColumnName;
            char lastColumn = (char)(65 + dttt.Columns.Count - 1);
            oSheet.get_Range("A1", lastColumn + "1").Value2 = colNames;
            oSheet.get_Range("A1", lastColumn + "1").Font.Bold = true;
            oSheet.get_Range("A1", lastColumn + "1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;                            
            SQL.DataRow[] dr = dttt.Select(string.Format(""));
            string[,] rowData = new string[dr.Count<SQL.DataRow>(), dttt.Columns.Count];                            
            int rowCnt = 0;
            int redRows = 2;
            foreach (SQL.DataRow row in dr)
            {                                
                for (col = 0; col < dttt.Columns.Count; col++)
                {
                    rowData[rowCnt, col] = row[col].ToString();                                   
                }
                redRows++;
                rowCnt++;                             
            }                            
            oSheet.get_Range("A2", lastColumn + rowCnt.ToString()).Value2 = rowData;
            oXL.Visible = true;
            oXL.UserControl = true;
            try
            {
                oWB.SaveAs("C://Products.xlsx", AccessMode: Excel.XlSaveAsAccessMode.xlShared);
            }
            catch
            {
            }
            break;
        }
        catch (Exception aee)
        {
        }
        break;
    }                                   
}

Upvotes: 0

Greg
Greg

Reputation: 8784

Check out EPPlus it is able to convert a datatable to XLSX and you can apply data validation to columns to mimic a "datatype" in Excel.

Upvotes: 1

Related Questions