ถามเลื่อง export excel หน่อยครับ ผมตอ้งกาน format colum date นะครับ ต้องทำไง
ผมต้องแก้สว่นไหนบ้าง
โคดผม
Code (VB.NET)
Private Sub releaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
GC.Collect()
End Try
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim rowsTotal, colsTotal As Short
Dim I, j, iC As Short
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
Dim xlApp As New Excel.Application
Try
Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
xlApp.Visible = True
rowsTotal = DataGridView1.RowCount - 1
colsTotal = DataGridView1.Columns.Count - 1
With excelWorksheet
.Cells.Select()
.Cells.Delete()
For iC = 0 To colsTotal
.Cells(1, iC + 1).Value = DataGridView1.Columns(iC).HeaderText
Next
For I = 0 To rowsTotal - 1
For j = 0 To colsTotal
.Cells(I + 2, j + 1).value = DataGridView1.Rows(I).Cells(j).Value
Next j
Next I
.Rows("1:1").Font.FontStyle = "Bold"
.Rows("1:1").Font.Size = 12
.Cells.Columns.AutoFit()
.Cells.Select()
.Cells.EntireColumn.AutoFit()
.Cells(1, 1).Select()
End With
Catch ex As Exception
MsgBox("Export Excel Error " & ex.Message)
Finally
'RELEASE ALLOACTED RESOURCES
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
xlApp = Nothing
End Try
End Sub
Tag : .NET, Ms Access, VB.NET
Date :
2012-11-27 15:45:17
By :
meksavanh
View :
1249
Reply :
3
เขียนแบบนี้ ประเทศเพื่อนบ้านชัวร์
เอา class นี้ไปใช้ แค่โยน datatable หรือ dataset ไปก็พอ
Code (C#)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data;
using System.IO;
namespace MsExcel
{
class InteropExcel : IDisposable
{
private object missing = Type.Missing;
private object readOnly = true;
private Excel.Application xlApp;
private Excel.Workbook xlBook;
private Excel.Range xlRange;
private string filePath;
private string message;
private int sheetCount;
private int rowCount;
public InteropExcel(string FilePath)
{
filePath = FilePath;
message = "Hello World!!!";
sheetCount = 0;
rowCount = 0;
xlApp = new Excel.Application();
xlApp.Visible = false;
xlApp.ScreenUpdating = false;
xlApp.DisplayAlerts = false;
}
public bool Export(DataSet DsData)
{
if(!Create())
return false;
foreach (DataTable DtData in DsData.Tables)
{
Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets.Add(missing, missing, missing, missing);
if(!FillData(xlSheet, DtData))
return false;
sheetCount++;
}
if(!Save())
return false;
message = string.Format("Export '{0}' Success. ({1} Sheets, {2} Rows)", Path.GetFileName(filePath), sheetCount.ToString("#,##0"), rowCount.ToString("#,##0"));
return true;
}
public bool Export(DataTable DtData)
{
if (!Create())
return false;
Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets.Add(missing, missing, missing, missing);
if (!FillData(xlSheet, DtData))
return false;
sheetCount++;
if (!Save())
return false;
message = string.Format("Export '{0}' Success. ({1} Sheets, {2} Rows)", Path.GetFileName(filePath), sheetCount.ToString("#,##0"), rowCount.ToString("#,##0"));
return true;
}
public bool Export(DataSet DsData, string TableName)
{
return Export(DsData.Tables[TableName]);
}
public bool Export(DataSet DsData, int TableIndex)
{
return Export(DsData.Tables[TableIndex]);
}
protected bool Create()
{
bool result = false;
if (!File.Exists(filePath))
File.Delete(filePath);
try
{
xlBook = xlApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
result = true;
}
catch (System.Runtime.InteropServices.COMException ex)
{
message = ex.Message;
Dispose();
}
return result;
}
protected bool Save()
{
bool result = false;
try
{
xlBook.SaveAs(filePath, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
result = true;
}
catch (System.Runtime.InteropServices.COMException ex)
{
message = ex.Message;
Dispose();
}
return result;
}
protected bool FillData(Excel.Worksheet xlSheet, DataTable DtData)
{
int row = 2;
int col = 1;
xlSheet.Name = DtData.TableName;
foreach (DataColumn Dc in DtData.Columns)
{
xlSheet.Cells[1, col] = string.Format("'{0}", Dc.ColumnName);
xlRange = (Excel.Range)xlSheet.Cells[1, col];
xlRange.Font.Name = "MS Sans Serif";
xlRange.Font.Size = 10;
xlRange.Interior.Color = System.Drawing.Color.LightYellow;
xlRange.Borders.Color = System.Drawing.Color.LightGray;
col++;
}
foreach (DataRow Dr in DtData.Rows)
{
col = 1;
foreach (DataColumn Dc in DtData.Columns)
{
xlRange = (Excel.Range)xlSheet.Cells[row, col];
xlRange.Font.Name = "MS Sans Serif";
xlRange.Font.Size = 10;
xlRange.Borders.Color = System.Drawing.Color.LightGray;
if (Dc.DataType == typeof(double) || Dc.DataType == typeof(float))
{
try
{
xlSheet.Cells[row, col] = Dr[col - 1];
xlRange.NumberFormat = "#,##0.00";
}
catch (System.Runtime.InteropServices.COMException ex)
{
message = ex.Message;
Dispose();
return false;
}
}
else if (Dc.DataType == typeof(int) || Dc.DataType == typeof(short) || Dc.DataType == typeof(ushort) ||
Dc.DataType == typeof(uint) || Dc.DataType == typeof(long) || Dc.DataType == typeof(ulong) ||
Dc.DataType == typeof(decimal))
{
try
{
xlSheet.Cells[row, col] = Dr[col - 1];
xlRange.NumberFormat = "#,##0";
}
catch (System.Runtime.InteropServices.COMException ex)
{
message = ex.Message;
Dispose();
return false;
}
}
else if (Dc.DataType == typeof(DateTime))
{
try
{
xlSheet.Cells[row, col] = Dr[col - 1];
}
catch (System.Runtime.InteropServices.COMException ex)
{
message = ex.Message;
Dispose();
return false;
}
}
else if (Dc.DataType == typeof(bool))
{
try
{
xlSheet.Cells[row, col] = Dr[col - 1];
}
catch (System.Runtime.InteropServices.COMException ex)
{
message = ex.Message;
Dispose();
return false;
}
}
else if (Dc.DataType == typeof(sbyte) || Dc.DataType == typeof(byte) || Dc.DataType == typeof(byte[]))
{
try
{
xlSheet.Cells[row, col] = null;
}
catch (System.Runtime.InteropServices.COMException ex)
{
message = ex.Message;
Dispose();
return false;
}
}
else
{
try
{
xlSheet.Cells[row, col] = (!string.IsNullOrEmpty(Dr[col - 1].ToString())) ? string.Format("'{0}", Dr[col - 1]) : null;
}
catch (System.Runtime.InteropServices.COMException ex)
{
message = ex.Message;
Dispose();
return false;
}
}
col++;
}
rowCount++;
row++;
}
return true;
}
public string Message
{
get { return message; }
}
public void Dispose()
{
if (xlApp != null)
{
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
GC.SuppressFinalize(xlRange);
GC.SuppressFinalize(xlBook);
GC.SuppressFinalize(xlApp);
GC.Collect();
xlRange = null;
xlBook = null;
xlApp = null;
}
}
}
}
Date :
2012-11-27 16:44:22
By :
ห้ามตอบเกินวันละ 2 กระทู้
ไป convert เอง
ปกติ c# ไปเป็น อิ จะไม่ค่อยมีปัญหาเท่าไหร่
เพราะระบุ type ชัดเจน
http://www.developerfusion.com/tools/convert/csharp-to-vb/
Date :
2012-11-28 08:00:24
By :
ห้ามตอบเกินวันละ 2 กระทู้
Load balance : Server 03