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

Question

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.

Excel does not have the concept of datatypes other than formatting a cell/column in a specific way. You could try to create a script that tries to match input with the format and if it doesn't match makes the cell red. Or delete the content or whatever seems right — Sascha
– Sascha, Commented Apr 11, 2012 at 3:28

Greg · Accepted Answer · 2012-04-11 03:38:28Z

1

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.

answered Apr 11, 2012 at 3:38

Greg

8,87421 gold badges72 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

LightBulb · Accepted Answer · 2014-11-05 10:10:21Z

//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;
    }                                   
}

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related