bhz8181
چهارشنبه 24 شهریور 1395, 11:50 صبح
سلام
من دنبال کدی جهت خروجی اکسل هستم که سرعتر از درج سلول به سلول باشه. دیتاست من حدود 21 دیتاتیبل داره که هر تیبل باید در یک شیت فایل اکسل قرار بگیره و حجم ردیف هاش هم زیاده چه روش یا کدی رو پیشنهادی میکنید که سریعتر از روش گفته شده باشه با روش گفته شده من خروجی رو دریافت نکردم.
نمونه کد فعلی:
Dim ds_temp As DataTable Dim exc As New Excel.Application
If exc Is Nothing Then
MsgBox("ERROR: EXCEL couldn't be started!", MsgBoxStyle.Critical, "توجه")
Else
exc.UserControl = True
Dim oldci As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCultu re
System.Threading.Thread.CurrentThread.CurrentCultu re = New System.Globalization.CultureInfo("en-US")
''
'Dim obook As Excel.Workbook = exc.Workbooks.Add 'Add Workbook to excel
'Dim oSheet As Excel._Worksheet = obook.Worksheets(1)
exc.Workbooks.Add()
Dim obook As Excel.Sheets = exc.Workbooks(1).Worksheets
Dim oSheet As Excel._Worksheet = CType(obook(1), Excel._Worksheet)
''
'Dim style As Microsoft.Office.Interop.Excel.Style
'style = obook.w.Styles.Add("Style1")
'style.Font.Name = "Arial"
'style.Font.Bold = True
'style.Font.Size = 12
'style.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPattern Solid
'style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLe ft
'style.NumberFormat = "@"
''
If oSheet Is Nothing Then
MsgBox("ERROR: worksheet == null", MsgBoxStyle.Critical, "0")
Else
Dim s = 1
Dim l = 0
Dim w = 0
For Each ds_temp In ds.Tables
If Not ds_temp Is Nothing Then
oSheet = CType(obook(s), Excel._Worksheet)
oSheet.DisplayRightToLeft = True
oSheet.Columns.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
oSheet.Columns.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
oSheet.Name = ds_temp.TableName.ToString
oSheet.Select()
' oSheet.Columns.AutoFit()
'oSheet.Range("A1:X1").EntireColumn.AutoFit()
Dim k = 0
'oSheet = .Worksheets(l + 1)
'oSheet.Name = dg_temp.Name.Remove(0, 3)
'oSheet.DisplayRightToLeft = True
'oSheet.Activate()
'oSheet.Cells.Style = style
'obook.Worksheets(l + 1).Select()
For Each Dc As DataColumn In ds_temp.Columns
k += 1
oSheet.Cells(1, k).Value = Dc.ColumnName.ToString.Trim
oSheet.Cells(1, k).Font.Bold = True
Next
Try
For i As Integer = 0 To ds_temp.Rows.Count - 1
For j As Integer = 0 To ds_temp.Columns.Count - 1
Try
oSheet.Cells(i + 2, j + 1) = ds_temp.Rows(i).Item(j).ToString.Trim
Catch ex As Exception
oSheet.Cells(i + 2, j + 1) = String.Empty
End Try
Next
Next
Catch ex As Exception
MsgBox(ex.ToString, MsgBoxStyle.Critical, "2-Office ")
Return False
End Try
End If
oSheet.Columns.ColumnWidth = 30
s = s + 1
Next
' oSheet.Range("A1").ColumnWidth = 30
exc.Visible = True
exc.Workbooks(1).SaveAs(PathForSavaFile & "" & FileName)
'exc.SaveWorkspace(PathForSavaFile & "" & FileName)
'.SaveAs(PathForSavaFile & "" & FileName)
System.Threading.Thread.CurrentThread.CurrentCultu re = oldci
oSheet = CType(obook(1), Excel._Worksheet)
oSheet.Select()
System.Runtime.InteropServices.Marshal.ReleaseComO bject(exc)
frm.Cursor = Cursors.Default
من دنبال کدی جهت خروجی اکسل هستم که سرعتر از درج سلول به سلول باشه. دیتاست من حدود 21 دیتاتیبل داره که هر تیبل باید در یک شیت فایل اکسل قرار بگیره و حجم ردیف هاش هم زیاده چه روش یا کدی رو پیشنهادی میکنید که سریعتر از روش گفته شده باشه با روش گفته شده من خروجی رو دریافت نکردم.
نمونه کد فعلی:
Dim ds_temp As DataTable Dim exc As New Excel.Application
If exc Is Nothing Then
MsgBox("ERROR: EXCEL couldn't be started!", MsgBoxStyle.Critical, "توجه")
Else
exc.UserControl = True
Dim oldci As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCultu re
System.Threading.Thread.CurrentThread.CurrentCultu re = New System.Globalization.CultureInfo("en-US")
''
'Dim obook As Excel.Workbook = exc.Workbooks.Add 'Add Workbook to excel
'Dim oSheet As Excel._Worksheet = obook.Worksheets(1)
exc.Workbooks.Add()
Dim obook As Excel.Sheets = exc.Workbooks(1).Worksheets
Dim oSheet As Excel._Worksheet = CType(obook(1), Excel._Worksheet)
''
'Dim style As Microsoft.Office.Interop.Excel.Style
'style = obook.w.Styles.Add("Style1")
'style.Font.Name = "Arial"
'style.Font.Bold = True
'style.Font.Size = 12
'style.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPattern Solid
'style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLe ft
'style.NumberFormat = "@"
''
If oSheet Is Nothing Then
MsgBox("ERROR: worksheet == null", MsgBoxStyle.Critical, "0")
Else
Dim s = 1
Dim l = 0
Dim w = 0
For Each ds_temp In ds.Tables
If Not ds_temp Is Nothing Then
oSheet = CType(obook(s), Excel._Worksheet)
oSheet.DisplayRightToLeft = True
oSheet.Columns.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
oSheet.Columns.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
oSheet.Name = ds_temp.TableName.ToString
oSheet.Select()
' oSheet.Columns.AutoFit()
'oSheet.Range("A1:X1").EntireColumn.AutoFit()
Dim k = 0
'oSheet = .Worksheets(l + 1)
'oSheet.Name = dg_temp.Name.Remove(0, 3)
'oSheet.DisplayRightToLeft = True
'oSheet.Activate()
'oSheet.Cells.Style = style
'obook.Worksheets(l + 1).Select()
For Each Dc As DataColumn In ds_temp.Columns
k += 1
oSheet.Cells(1, k).Value = Dc.ColumnName.ToString.Trim
oSheet.Cells(1, k).Font.Bold = True
Next
Try
For i As Integer = 0 To ds_temp.Rows.Count - 1
For j As Integer = 0 To ds_temp.Columns.Count - 1
Try
oSheet.Cells(i + 2, j + 1) = ds_temp.Rows(i).Item(j).ToString.Trim
Catch ex As Exception
oSheet.Cells(i + 2, j + 1) = String.Empty
End Try
Next
Next
Catch ex As Exception
MsgBox(ex.ToString, MsgBoxStyle.Critical, "2-Office ")
Return False
End Try
End If
oSheet.Columns.ColumnWidth = 30
s = s + 1
Next
' oSheet.Range("A1").ColumnWidth = 30
exc.Visible = True
exc.Workbooks(1).SaveAs(PathForSavaFile & "" & FileName)
'exc.SaveWorkspace(PathForSavaFile & "" & FileName)
'.SaveAs(PathForSavaFile & "" & FileName)
System.Threading.Thread.CurrentThread.CurrentCultu re = oldci
oSheet = CType(obook(1), Excel._Worksheet)
oSheet.Select()
System.Runtime.InteropServices.Marshal.ReleaseComO bject(exc)
frm.Cursor = Cursors.Default