62,041
社区成员
发帖
与我相关
我的任务
分享
protected void ExportExcel(DataTable dt)
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
try
{
if (dt == null || dt.Rows.Count == 0) return;
if (xlApp == null)
{
MessageBox.Show("Unable to create Excel object, may your machine son not to install Excel", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
Microsoft.Office.Interop.Excel.Range range;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
range.Interior.ColorIndex = 15;
range.Font.Bold = true;
}
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString();
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
}
string filePath = "";
filePath = Application.ExecutablePath;
int FindIndex = filePath.IndexOf("\\");
filePath = filePath.Substring(0, FindIndex + 1) + "TEI_Export_AAR";
//xlApp.Visible = true;
DirectoryInfo TheFolder = new DirectoryInfo(filePath);
if (!TheFolder.Exists)
{
System.IO.Directory.CreateDirectory(filePath);
}
string temp = string.Format(@"\Mileage{0}.xls", Util.GetNowDate().ToString("MMddyyyymmss"));
FileInfo TheFile = new FileInfo(filePath + temp);
if (TheFile.Exists)
{
DateTime time = Util.GetNowDate();
temp = string.Format(@"\Mileage{0}.xls", time.ToString("MMddyyyymmss"));
}
//Save the result to a temp path
workbook.SaveAs(filePath + temp, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, null, null, false, false,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//workbook.SaveAs(filePath + "picture");
MessageBox.Show(filePath + temp, "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception)
{
throw;
}
finally
{
xlApp.Quit();
GC.Collect();//
}
}