摘要:Excel 轉出的sample (用 Microsoft Excel 14.0 Object Library 做的)
符合 Microsoft Excel 14.0 Object Library , 低的版本不一定可以用, 有些mothed 好像在不同版本的時候名稱不同, 這個適合在不是很大的匯出的時候很方便, 如果要一次轉出好幾萬筆資料的話, 寫資料的部分寫法需要調整 , 要用大Ranges 批量轉出才會快
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
namespace DSMAGIWH.AC003
{
public partial class MonthlyReport : Form
{
public MonthlyReport()
{
InitializeComponent();
dateTimePicker1.Value = DateTime.Now.AddDays(-DateTime.Now.Day + 1);
dateTimePicker2.Value = DateTime.Now.AddMonths(1).AddDays(-DateTime.Now.AddMonths(1).Day);
}
private void button1_Click(object sender, EventArgs e)
{
SaveFileDialog dlg = new SaveFileDialog();
dlg.FileName = "~Monthly Report "+dateTimePicker2.Value.ToString("yyyy-MM")+".xlsx";
dlg.Filter = "Excel files (*.xls;*.xlsx)|*.xls;*.xlsx|All files (*.*)|*.* ";
if (dlg.ShowDialog(null) == System.Windows.Forms.DialogResult.OK)
{
System.IO.File.Delete(dlg.FileName);
string template = System.IO.File.Exists(dlg.FileName) ? dlg.FileName : "";
ExportExcel(dlg.FileName);
}
}
private void ExportExcel(string sFileName) {
Excel.Application ExcelApp = new Excel.Application();
ExcelApp.Visible = false;
ExcelApp.DisplayAlerts = false;
Excel.Workbook wb = ExcelApp.Workbooks.Add();
Excel.Worksheet ws = wb.Sheets[1];
if (Convert.ToDouble(ExcelApp.Version) < 12) sFileName = sFileName.Replace(".xlsx", ".xls");
// 設整頁字型
Excel.Range range = ws.Cells;
range.Font.Name = "Calibri";
range.Font.Size = 11;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
range = null;
try
{
// 匯出資料===================
if( WriteExcelData(ws))
wb.SaveAs(sFileName);
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
wb.Close();
ExcelApp.Quit();
//不知為何只要加多幾個設定 Excel總是清不乾淨, 最終手段幹掉他
try
{
if (ExcelApp != null)
{
int lpdwProcessId;
GetWindowThreadProcessId(new IntPtr(ExcelApp.Hwnd), out lpdwProcessId);
System.Diagnostics.Process.GetProcessById(lpdwProcessId).Kill();
}
}
catch (Exception ex)
{
Console.WriteLine("Delete Excel Process Error:" + ex.Message);
}
ws = null;
wb = null;
ExcelApp = null;
GC.Collect();
//外部獨立啟動方式
System.Diagnostics.Process ps = new System.Diagnostics.Process();
ps.StartInfo.FileName = sFileName;
ps.Start();
Close();
}
private bool WriteExcelData(Excel.Worksheet ws)
{
//取資料
WebServiceEncodeLib wsb = new WebServiceEncodeLib();
string sql = "SELECT * FROM WH_AC003_OUT_VIEW3 WHERE EMAIL_DATE between '"
+ dateTimePicker1.Value.ToString("yyyyMMdd") + "' AND '" + dateTimePicker2.Value.ToString("yyyyMMdd") + "' ORDER BY EMAIL ";
DataSet ds = wsb.getDataSetXml(sql);
if (ds.Tables[0] == null)
{
MessageBox.Show("no data between " + dateTimePicker1.Value.ToString("MM/dd/yyyy") + " to " + dateTimePicker2.Value.ToString("MM/dd/yyyy"));
return false;
}
DataTable dt = ds.Tables[0];
//產生表頭
string[] sa = new string[] {"NO", "W/H OUT#","CNEE","ITEM#" ,"IN WH","LFD","OUT WH","QTY","PACKING","STORAGE FEE"
,"OTHER CHARGE","TOTAL FEE(USD)","(NTD)","EMAIL DAYS","REMARK#"};
SetExcelColumnsName(ws, sa, new int[] { 6, 10, 10, 15,8,8,8, 6, 8, 8, 8, 8, 9, 14, 10 });
int iColumnCount = sa.Length;
Excel.Range range = ws.get_Range("A2", getExcelColString(iColumnCount) + "2");
range.Interior.Color = Color.FromArgb(255, 192, 128);
range.Font.Bold = true;
range.WrapText = true;
Color c1 = GlobalParam.ColorDiff;
Color c2 = Color.LightGoldenrodYellow;
Color oldColor = c1;
//寫入資料
int irow = 3;
int CNEE_PO_COUNT = 1;
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow item = dt.Rows[i];
DataRow itemPrior = (i == 0) ? dt.Rows[0] : dt.Rows[i - 1];
if ((irow > 3) && ((item["CNEE_PO"].ToString() != itemPrior["CNEE_PO"].ToString())))
{
oldColor = (oldColor == c1) ? c2 : c1;
CNEE_PO_COUNT++;
}
range = ws.get_Range("A" + irow.ToString(), getExcelColString(iColumnCount) + irow.ToString());
range.Interior.Color = oldColor;
int iSMAT = 0;
try
{
iSMAT = Convert.ToInt32(item["SAMT"]) + Convert.ToInt32(item["OTHER_CHARGE"]);
}
catch (Exception)
{
MessageBox.Show("單號 " + item["CNEE_PO"].ToString() + " 輸入的金額格式有錯誤, 無法轉換成數字");
}
SetExcelRowValue(ws, irow, new string[] {
CNEE_PO_COUNT.ToString(),
item["CNEE_PO"].ToString(),
item["CNEE_OUT"].ToString(),
item["PO_NO"].ToString(),
"'"+item["TO_WH"].ToString().Substring(4,2) + "/" + item["TO_WH"].ToString().Substring(6,2),
"'"+item["LFD"].ToString().Substring(4,2) + "/" + item["LFD"].ToString().Substring(6,2),
"'"+item["OUT_DATE"].ToString().Substring(4,2) + "/" + item["OUT_DATE"].ToString().Substring(6,2),
item["TUB_OUT"].ToString(),
item["QTY_DESCP"].ToString().Split(new char[] { 'x', 'X' })[0],
item["USD_AMT"].ToString(),
item["OTHER_CHARGE"].ToString(),
iSMAT.ToString(),
item["NTD_AMT"].ToString(),
item["EMAIL"].ToString(),
item["M_REMARK"].ToString()
});
if ((irow > 3) && (item["CNEE_PO"].ToString() == itemPrior["CNEE_PO"].ToString()))
{
ExcelCellMarge(ws, irow, 1);
ExcelCellMarge(ws, irow, 2);
ExcelCellMarge(ws, irow, 3);
ExcelCellMarge(ws, irow, 11);
ExcelCellMarge(ws, irow, 12);
ExcelCellMarge(ws, irow, 13);
ExcelCellMarge(ws, irow, 14);
ExcelCellMarge(ws, irow, 15);
}
irow++;
}
ws.get_Range("A2:" + getExcelColString(iColumnCount) + (irow - 1).ToString()).Borders.Weight = Excel.XlBorderWeight.xlThin;
ws.get_Range("A2:" + getExcelColString(iColumnCount) + (irow - 1).ToString()).AutoFilter(1, Type.Missing,
Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);
return true;
}
#region Kill Special Excel Process
[DllImport("user32.dll", SetLastError = true)]
static extern int GetWindowThreadProcessId(IntPtr hWnd, out int lpdwProcessId);
#endregion
//判斷且合併儲存格
private void ExcelCellMarge(Excel.Worksheet ws, int irow, int iCol)
{
string sCol = getExcelColString(iCol);
ws.Range[sCol + (irow - 1).ToString() + ":" + sCol + irow.ToString()].Merge();
}
private string getExcelColString(int Col)
{
string colFixString = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
if (Col <= 26)
{
return colFixString[Col - 1].ToString();
}
else
{
return colFixString[(Col / 26) - 1].ToString() + colFixString[((Col - 1) % 26)].ToString();
}
}
private void SetExcelColumnsName(Excel.Worksheet ws, string[] names, int[] widths)
{
for (int i = 0; i < names.Count(); i++)
{
ws.Cells[2, i + 1] = names[i];
ws.Cells[2, i + 1].ColumnWidth = widths[i];
}
}
private void SetExcelRowValue(Excel.Worksheet ws, int iRow, string[] sa)
{
for (int i = 0; i < sa.Count(); i++)
{
ws.Cells[iRow, i + 1] = sa[i];
}
}
}
}