DataTable automatically exported to Microsoft Excel

Today I want to show an example that has served me for a long time and I'm still using.
We will make an example to export a DataTable to Excel full regardless of the number of columns or rows that you have.

There may be many ways to do this, but I find it really useful since this method works for any version of Excel. For reasons of Compatil with new versions of Excel only handle 256 columns.

The first thing we need is to add a reference to Microsoft.Office.Interop.Excel and do as follows:

Import reference to Excel

Import Excel reference

Then we need a form, a button, a DataGridView and a progress bar. We can have as seen in the picture:

Form application to export dataset to excel

Now I want to show all the code is really simple most important thing is that it adapts to any table. I will be able to be reused without problems.

 Imports Imports System.Data Imports System.Data.SqlClient Public Class Form1 Private Microsoft.Office.Interop ds As New DataSet Private Sub Form1_Load (ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load '/ / / /////////////////////////////////////////////// '/ / We create the connection and fill the DataGridView'////////////////////////////////////////// Dim cnn As New //////// SqlConnection ("Data Source = localhost \ sqlexpress; Initial Catalog = Northwind; Integrated Security = True") Dim da As New SqlDataAdapter ("Select * from customers", cnn) gives. Fill (ds) DataGridView1.DataSource = ds.Tables (0) End Sub Private Sub btnExportar_Click (ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportar.Click'/////////// ////////////////// '/ / Create the Excel object'//////////////////////// Dim Dim objLibroExcel ///// Dim objHojaExcel m_Excel m_Excel = CreateObject ("Excel.Application") m_Excel.Workbooks.Add objLibroExcel = () = objLibroExcel.Worksheets objHojaExcel (1) objHojaExcel.Name = "Customers" objHojaExcel.Visible = Excel . XlSheetVisibility.xlSheetVisible objHojaExcel.Activate ()'//////////////////////////////////////// ///////////////// '/ / define two variables to control'///////////////////// row and column //////////////////////////////////// Dim row As Integer = 1 Dim column As Integer = 1 '/ //////////////////////////////////////////////// '/ / We set the line with the column headings'//////////////////////////////////////// ///////// objHojaExcel.Range ("A1"). Select () For Each dc In ds.Tables (0). objHojaExcel.Range Columns (ColumnName (column) & 1). Value = dc.ColumnName column + = 1 Next row + = 1'//////////////////////////////////////// ///// '/ / We format the row titles'//////////////////////////////// As Excel.Range Dim objRango ///////////// = objHojaExcel.Range ("A1:" & ColumnName (ds.Tables (0). Columns.Count) & "1") objRango.Font . Bold = True objRango.Cells.Interior.ColorIndex = 35 objRango.Cells.Borders (Excel.XlBordersIndex.xlDiagonalDown). LineStyle = Excel.XlLineStyle.xlLineStyleNone objRango.Cells.Borders (Excel.XlBordersIndex.xlDiagonalUp). LineStyle = Excel. XlLineStyle.xlLineStyleNone objRango.Cells.Borders (Excel.XlBordersIndex.xlEdgeLeft). LineStyle = Excel.XlLineStyle.xlLineStyleNone objRango.Cells.Borders (Excel.XlBordersIndex.xlEdgeRight). LineStyle = Excel.XlLineStyle.xlContinuous objRango.Cells.Borders (Excel . XlBordersIndex.xlEdgeTop). LineStyle = Excel.XlLineStyle.xlContinuous objRango.Cells.Borders (Excel.XlBordersIndex.xlEdgeBottom). LineStyle = Excel.XlLineStyle.xlContinuous'//////////////// ////////////////////////// '/ / Load all rows in the datatable'////////////// //////////////////////////// ProgressBar1.Maximum = ds.Tables (0). Rows.Count ProgressBar1.Value column = 1 = 0 For Each dr In ds.Tables (0). Rows column = 1 For Each dc In ds.Tables (0). objHojaExcel.Range Columns (ColumnName (column) & row). Value = dr (dc.ColumnName) column + = 1 Next row + = 1 + = 1 Next ProgressBar1.Value'//////////////////////////////////// / / '/ / Set the width automatically' / / for all columns used'//////////////////////////////// ////// objHojaExcel.Range objRango = ("A1:" & ColumnName (ds.Tables (0). Columns.Count) & ds.Tables (0). Rows.Count.ToString) objRango.Select () objRango . Columns.AutoFit ()'///////////////////////////////////// '/ / tell '///////////////////////////////////// display Excel MsgBox ("Export to Excel Complete" , MsgBoxStyle.Information, ".:: solovb.net ::.") m_Excel.Visible = True End Sub Public Function ColumnName (ByVal number As Integer) As String Dim column (256) As String Column (1) =" A " Column (2) = "B" column (3) = "C" column (4) = "D" column (5) = "E" column (6) = "F" column (7) = "G" column ( 8) = "H" column (9) = "I" column (10) = "J" column (11) = "K" column (12) = "L" column (13) = "M" column (14) = "N" column (15) = "O" column (16) = "P" column (17) = "Q" column (18) = "R" column (19) = "S" column (20) = " T "column (21) =" U "column (22) =" V "column (23) =" W "column (24) =" X "column (25) =" Y "column (26) =" Z " column (27) = "AA" column (28) = "AB" column (29) = "AC" column (30) = "AD" column (31) = "AE" column (32) = "AF" column ( 33) = "AG" Column (34) = "AH" column (35) = "AI" column (36) = "AJ" column (37) = "AK" column (38) = "TO" column (39) = "AM" column (40) = "AN" column (41) = "AO" column (42) = "AP" column (43) = "AQ" column (44) = "AR" column (45) = " AS "column (46) =" AT "column (47) =" AU "column (48) =" AV "column (49) =" AW "column (50) =" AX "column (51) =" AY " column (52) = "AZ" column (53) = "BA" column (54) = "BB" column (55) = "BC" column (56) = "BD" column (57) = "BE" column ( 58) = "BF" column (59) = "BG" column (60) = "BH" column (61) = "BI" column (62) = "BJ" column (63) = "BK" column (64) = "BL" column (65) = "BM" column (66) = "BN" column (67) = "BO" column (68) = "BP" column (69) = "BQ" column (70) = " BR "column (71) =" BS "column (72) =" BT "column (73) =" BU "column (74) =" BV "column (75) =" BW "column (76) =" BX " column (77) = "BY" column (78) = "BZ" column (79) = "CA" column (80) = "CB" column (81) = "CC" column (82) = "CD" column ( 83) = "CE" column (84) = "CF" column (85) = "CG" column (86) = "CH" column (87) = "CI" column (88) = "CJ" column (89) = "CK" column (90) = "CL" column (91) = "CM" column (92) = "CN" column (93) = "CO" column (94) = "CP" column (95) = " CQ "column (96) =" CR "column (97) =" CS "column (98) =" CT "column (99) =" CU "column (100) =" CV "column (101) =" CW " column (102) = "CX" column (103) = "CY" column (104) = "CZ" column (105) = "DA" column (106) = "DB" column (107) = "DC" column ( 108) = "DD" column (109) = "DE" column (110) = "DF" column (111) = "DG" column (112) = "DH" column (113) = "ID" column (114) = "DJ" column (115) = "DK" column (116) = "DL" column (117) = "DM" column (118) = "DN" column (119) = "DO" column (120) = " DP "column (121) =" DQ "column (122) =" DR "column (123) =" DS "column (124) =" DT "column (125) =" DU "column (126) =" DV " column (127) = "DW" column (128) = "DX" column (129) = "DY" column (130) = "DZ" column (131) = "EA" column (132) = "EB" column ( 133) = "EC" column (134) = "ED" column (135) = "EE" column (136) = "EF" column (137) = "EG" column (138) = "EH" column (139) = "EI" column (140) = "EJ" column (141) = "EK" column (142) = "EL" column (143) = "MS" column (144) = "IN" column (145) = " EO "column (146) =" EP "column (147) =" EQ "column (148) =" ER "column (149) =" ES "column (150) =" ET "column (151) =" EU " column (152) = "EV" column (153) = "EW" column (154) = "EX" column (155) = "EY" column (156) = "EZ" column (157) = "FA" column ( 158) = "FB" column (159) = "FC" column (160) = "FD" column (161) = "FE" column (162) = "FF" column (163) = "FG" column (164) = "FH" column (165) = "FI" column (166) = "FJ" column (167) = "FK" column (168) = "FL" column (169) = "FM" column (170) = " FN "column (171) =" FOR "column (172) =" FP "column (173) =" CF "column (174) =" FR "column (175) =" FS "column (176) =" FT " column (177) = "FU" column (178) = "FV" column (179) = "FW" column (180) = "FX" column (181) = "FY" column (182) = "FZ" column ( 183) = "GA" column (184) = "GB" column (185) = "GC" column (186) = "GD" column (187) = "GE" column (188) = "GF" column (189) = "GG" column (190) = "GH" column (191) = "GI" column (192) = "GJ" column (193) = "GK" column (194) = "GL" column (195) = " GM "column (196) =" GN "column (197) =" GO "column (198) =" GP "column (199) =" GQ "column (200) =" GR "column (201) =" GS " column (202) = "GT" column (203) = "GU" column (204) = "GV" column (205) = "GW" column (206) = "GX" column (207) = "GY" column ( 208) = "GZ" column (209) = "HA" column (210) = "HB" column (211) = "HC" column (212) = "HD" column (213) = "HE" column (214) = "HF" column (215) = "HG" column (216) = "HH" column (217) = "HI" column (218) = "HJ" column (219) = "HK" column (220) = " HL "column (221) =" HM "column (222) =" HN "column (223) =" HO "column (224) =" HP "column (225) =" HQ "column (226) =" HR " column (227) = "HS" column (228) = "HT" column (229) = "HU" column (230) = "HV" column (231) = "HW" column (232) = "H" column ( 233) = "HY" column (234) = "HZ" column (235) = "IA" column (236) = "IB" column (237) = "IC" column (238) = "ID" column (239) = "IE" column (240) = "IF" column (241) = "IG" column (242) = "IH" column (243) = "II" column (244) = "IJ" column (245) = " IK "column (246) =" IL "column (247) =" IM "column (248) =" IN "column (249) =" IO "column (250) =" IP "column (251) =" IQ " column (252) = "IR" column (253) = "IS" column (254) = "IT" column (255) = "UI" column (256) = "IV" Return column (number) End Function End Class 

This method has served me well and I hope you too ... please leave your comments.