18-09-18, 04:44 AM
السلام عليكم
عندي class حصلت علية من المنتدى يقوم بنقل البيانات من ال datatable الى ملف excel
يعمل بشكل ممتاز لكن اريد ان اعدل علية ليقوم بجعل عرض الاعمدة في ملف ال excel بنفس عرض الاعمدة
في ال datagrid او ان تكون الاعمدة auto-fit حسب حجم البيانات في العمود
ارجو المساعدة
ارجو المساعدة
PHP كود :
Imports System.IO
Public Class ExportToExcelClr
Public Sub ExportToExcel(datatable As DataTable, GV As DataGridView, filename As String, Optional OpenDialog As Boolean = False, Optional SameColumnsWidth As Boolean = True)
Dim FlNm As String = ""
Dim sv As New SaveFileDialog
sv.FileName = filename
sv.Filter = "|*.xls"
If sv.ShowDialog <> DialogResult.OK Then Exit Sub
FlNm = sv.FileName
Dim fs As New StreamWriter(FlNm, False)
With fs
.WriteLine("<?xml version=""1.0""?>")
.WriteLine("<?mso-application progid=""Excel.Sheet""?>")
.WriteLine("<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet"">")
.WriteLine(" <Styles>")
.WriteLine(" <Style ss:ID=""hdr"">")
.WriteLine(" <Alignment ss:Horizontal=""Center""/>")
.WriteLine(" <Borders>")
.WriteLine(" <Border ss:Position=""Left"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>")
.WriteLine(" <Border ss:Position=""Right"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>")
.WriteLine(" <Border ss:Position=""Top"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>")
.WriteLine(" </Borders>")
.WriteLine(" <Font ss:FontName=""Droid Arabic Kufi"" ss:Size=""8"" ss:Bold=""1""/>") 'SET FONT
.WriteLine(" </Style>")
.WriteLine(" <Style ss:ID=""ksg"">")
.WriteLine(" <Alignment ss:Vertical=""Bottom""/>")
.WriteLine(" <Borders/>")
.WriteLine(" <Font ss:FontName=""Arial""/>") 'SET FONT
.WriteLine(" </Style>")
.WriteLine(" <Style ss:ID=""isi"">")
.WriteLine(" <Borders>")
.WriteLine(" <Border ss:Position=""Bottom"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>")
.WriteLine(" <Border ss:Position=""Left"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>")
.WriteLine(" <Border ss:Position=""Right"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>")
.WriteLine(" <Border ss:Position=""Top"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>")
.WriteLine(" </Borders>")
.WriteLine(" <Font ss:FontName=""Arial"" ss:Size=""11""/>")
.WriteLine(" </Style>")
.WriteLine(" </Styles>")
.WriteLine(" <Worksheet ss:Name=""sheet1"">")
.WriteLine(" <Table>")
For i As Integer = 0 To GV.ColumnCount - 1
.WriteLine(" <Column ss:AutoFitWidth=""0""/>")
Next
.WriteLine(" <Row ss:StyleID=""ksg"">")
For i As Integer = 0 To GV.ColumnCount - 1
Application.DoEvents()
.WriteLine(" <Cell ss:StyleID=""hdr"">")
.WriteLine(" <Data ss:Type=""String"">{0}</Data>", GV.Columns.Item(i).HeaderText)
.WriteLine(" </Cell>")
Next
.WriteLine(" </Row>")
For intRow As Integer = 0 To datatable.Rows.Count - 1
Application.DoEvents()
.WriteLine(" <Row ss:StyleID=""ksg"" ss:utoFitHeight =""0"">")
For intCol As Integer = 0 To datatable.Columns.Count - 1
Application.DoEvents()
.WriteLine(" <Cell ss:StyleID=""isi"">")
.WriteLine(" <Data ss:Type=""String"">{0}</Data>", GV.Item(intCol, intRow).Value.ToString)
.WriteLine(" </Cell>")
Next
.WriteLine(" </Row>")
Next
.WriteLine(" </Table>")
.WriteLine(" </Worksheet>")
.WriteLine("</Workbook>")
.Close()
If OpenDialog Then
Dim msg1
msg1 = MsgBox("تم الاستخراج ... هل تريد فتح الملف ؟", MsgBoxStyle.YesNo + MsgBoxStyle.Question, "Export")
If msg1 = vbNo Then Exit Sub
Process.Start(FlNm)
End If
End With
End Sub
Sub DataGridView_COLORE_FOR_ME(ByVal DataGridViewXT As Object)
Try
'-------------------------------------------------------------------------------------------
DataGridViewXT.DefaultCellStyle.BackColor = Color.FromArgb(255, 255, 255)
DataGridViewXT.AlternatingRowsDefaultCellStyle.BackColor = Color.FromArgb(255, 255, 192)
'-------------------------------------------------------------------------------------------
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical + MsgBoxStyle.MsgBoxRight + MsgBoxStyle.MsgBoxRtlReading, "Error")
End Try
End Sub
End Class
