public Export(string embedded)
{
string tmp = String.Empty;
try
{
//получаем шаблон из прикладных ресурсов
Stream template = GetResourceFileStream(embedded);
//создаем временный файл
tmp = System.IO.Path.GetTempFileName().Replace(".tmp", ".xlsx");
//сохраняем шаблон во временный файл
using (var fileStream = File.Create(tmp))
{
template.CopyTo(fileStream);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
// Создаем приложение и открываем в нём временный файл
objApp = new Excel.Application();
objBook = objApp.Workbooks.Open(tmp);
objBook.Activate();
objSheets = objBook.Worksheets;
objSheet = (Excel._Worksheet)objSheets.get_Item(1);
}
private void InitializeExcel()
{
// 開啟一個新的應用程式
excelApp = new Excel.Application();
// 讓Excel文件可見
excelApp.Visible = true;
// 停用警告訊息
excelApp.DisplayAlerts = false;
// 加入新的活頁簿
excelApp.Workbooks.Add(Type.Missing);
// 引用第一個活頁簿
wBook = excelApp.Workbooks[1];
// 設定活頁簿焦點
wBook.Activate();
// 引用第一個工作表
wSheet = (Excel._Worksheet)wBook.Worksheets[1];
// 命名工作表的名稱
wSheet.Name = "角度精度驗證";
// 設定工作表焦點
wSheet.Activate();
// 設定第1列資料
excelApp.Cells[1, 1] = "第一邊";
excelApp.Cells[1, 2] = "第二邊";
excelApp.Cells[1, 3] = "第三邊";
}
//Excel.Range wRange;
public DataExcel()
{
// 開啟一個新的應用程式
excelApp = new Excel.Application
{
// 讓Excel文件可見
//Visible = true,
// 停用警告訊息
DisplayAlerts = false
};
// 加入新的活頁簿
excelApp.Workbooks.Add(Type.Missing);
// 引用第一個活頁簿
wBook = excelApp.Workbooks[1];
// 設定活頁簿焦點
wBook.Activate();
// 引用第一個工作表
wSheet = (Excel._Worksheet)wBook.Worksheets[1];
// 命名工作表的名稱
wSheet.Name = "result";
// 設定工作表焦點
wSheet.Activate();
}
/// <summary>
/// Funzione che lancia l'applicazione o la sua installazione nel caso in cui non sia ancora installata.
/// </summary>
/// <param name="xlApp">Applicazione Excel su cui lanciare il programma.</param>
/// <param name="idApplicazione">Id dell'applicazione da avviare.</param>
///
public static void AvviaApplicazione(Excel.Application xlApp, int idApplicazione)
{
string file = Simboli.FileApplicazione[idApplicazione];
//controllo se è già aperta
bool opened = false;
Excel._Workbook workbook = xlApp.Workbooks.OfType <Excel.Workbook>().Where(wb => wb.Name == file + ".xlsm").FirstOrDefault();
if (workbook != null)
{
xlApp.Visible = true;
xlApp.WindowState = Excel.XlWindowState.xlMaximized;
workbook.Activate();
opened = true;
}
if (!opened)
{
#if DEBUG
string path = @"D:\Repository\Iren\PSO\Applicazioni\" + file + @"\bin\Debug\" + file + ".xlsm";
#else
string path = Path.Combine(Environment.ExpandEnvironmentVariables(Simboli.LocalBasePath), file + ".xlsm");
#endif
if (!File.Exists(path))
{
//Installazione da remoto
string installPath = Path.Combine(Simboli.RemoteBasePath, file, file + ".vsto");
var process = new System.Diagnostics.Process
{
StartInfo = new System.Diagnostics.ProcessStartInfo
{
FileName = installPath
//Arguments = "dfshim.dll,ShOpenVerbApplication " + installPath
},
EnableRaisingEvents = true,
};
process.Start();
}
else
{
xlApp.Visible = true;
xlApp.WindowState = Excel.XlWindowState.xlMaximized;
xlApp.Workbooks.Open(path);
}
}
}
public Form1()
{
InitializeComponent();
foreach (string com in System.IO.Ports.SerialPort.GetPortNames())
{
comboBox1.Items.Add(com);
}
excelApp = new Excel.Application(); // 開啟一個新的應用程式
//excelApp.Visible = true; // 讓Excel文件可見
excelApp.DisplayAlerts = false; // 停用警告訊息
excelApp.Workbooks.Add(Type.Missing); // 加入新的活頁簿
wBook = excelApp.Workbooks[1]; // 引用第一個活頁簿
wBook.Activate(); // 設定活頁簿焦點
}
public static void Export(DataTable dt, string filepath)
{
ExcelApp.Application myExcel = new ExcelApp.Application();
//Create a New file
ExcelApp._Workbook mybook = myExcel.Workbooks.Add();
//Open the exist file
//ExcelApp._Workbook mybook = myExcel.Workbooks.Open(filepath,
// Type.Missing, Type.Missing, Type.Missing,
// Type.Missing,Type.Missing, Type.Missing, Type.Missing,
// Type.Missing, Type.Missing, Type.Missing,
// Type.Missing, Type.Missing,Type.Missing, Type.Missing);
//ExcelApp._Workbook mybook = myExcel.Workbooks.Open(Filename: filepath);
myExcel.Visible = true;
try
{
mybook.Activate();
ExcelApp._Worksheet mysheet = mybook.Worksheets.Add();
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
ExcelApp.Range cell =
mysheet.get_Range(((char)(65 + j)).ToString() + (i + 1).ToString());
cell.Select();
cell.Cells.FormulaR1C1 = dt.Rows[i][j] ?? "";
}
}
//mybook.SaveAs(Filename: filepath);
mybook.Save();
}
catch (Exception ex)
{
}
finally
{
mybook.Close();
myExcel.Quit();
GC.Collect();
}
}
/// <summary>
/// Opens the original word file and initializes the Word.Document object.
/// The original file may be HTML, DOC, or RTF
/// </summary>
private void OpenDocument()
{
object missing = System.Reflection.Missing.Value;
// doesn't update any reference links
object updateLinks = 0;
object readOnly = true;
// not display the read-only recommended message
object ignoreReadOnlyRecommended = true;
//We could pass in a real password for these two fields, but currently
//there is no way to pass in a document specific password in System4
object password = missing;
//for write-reserved workbook
object writeResPassword = password;
m_workbook = m_excelApp.Workbooks.Open(
m_originalFileName, updateLinks, readOnly, missing,
password, writeResPassword, ignoreReadOnlyRecommended,
missing, missing, missing, missing, missing, missing,
missing, missing);
m_workbook.Activate();
m_workbook.WebOptions.Encoding = Office.MsoEncoding.msoEncodingUTF8;
}
public static void Export1(DataTable dt, string filepath)
{
String strFileName = "";
strFileName = filepath;
// Server File Path Where you want to save excel file.
ExcelApp.Application myExcel = new ExcelApp.Application();
//Create a New file
ExcelApp._Workbook mybook = myExcel.Workbooks.Add(System.Reflection.Missing.Value);
//Open the exist file
//ExcelApp._Workbook mybook = myExcel.Workbooks.Open(filepath,
// Type.Missing, Type.Missing, Type.Missing,
// Type.Missing,Type.Missing, Type.Missing, Type.Missing,
// Type.Missing, Type.Missing, Type.Missing,
// Type.Missing, Type.Missing,Type.Missing, Type.Missing);
//ExcelApp._Workbook mybook = myExcel.Workbooks.Open(Filename: filepath);
myExcel.Visible = false;
try
{
mybook.Activate();
ExcelApp._Worksheet mysheet = (ExcelApp._Worksheet)mybook.ActiveSheet;
int colIndex = 0;///********////
int rowIndex = 0;
//foreach (DataColumn dcol in dt.Columns)
//{
// colIndex = colIndex + 1;
// myExcel.Cells[1, colIndex] = dcol.ColumnName;
//}
foreach (DataColumn dcol in dt.Columns)
{
colIndex = colIndex + 1;
myExcel.Cells[rowIndex + 1, colIndex] = dcol.ColumnName;
mysheet.get_Range(myExcel.Cells[rowIndex + 1, colIndex], myExcel.Cells[rowIndex + 1, colIndex]).WrapText = true;
mysheet.get_Range(myExcel.Cells[rowIndex + 1, colIndex], myExcel.Cells[rowIndex + 1, colIndex]).Font.Bold = true;
mysheet.get_Range(myExcel.Cells[rowIndex + 1, colIndex], myExcel.Cells[rowIndex + 1, colIndex]).Font.Size = 10;
mysheet.get_Range(myExcel.Cells[rowIndex + 1, colIndex], myExcel.Cells[rowIndex + 1, colIndex]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
ExcelApp.Borders borders = mysheet.get_Range(myExcel.Cells[rowIndex + 1, colIndex], myExcel.Cells[rowIndex + 1, colIndex]).Borders;
borders[ExcelApp.XlBordersIndex.xlEdgeLeft].LineStyle = ExcelApp.XlLineStyle.xlContinuous;
borders[ExcelApp.XlBordersIndex.xlEdgeTop].LineStyle = ExcelApp.XlLineStyle.xlContinuous;
borders[ExcelApp.XlBordersIndex.xlEdgeBottom].LineStyle = ExcelApp.XlLineStyle.xlContinuous;
borders[ExcelApp.XlBordersIndex.xlEdgeRight].LineStyle = ExcelApp.XlLineStyle.xlContinuous;
borders.Color = 0;
borders[ExcelApp.XlBordersIndex.xlInsideVertical].LineStyle = ExcelApp.XlLineStyle.xlLineStyleNone;
borders[ExcelApp.XlBordersIndex.xlInsideHorizontal].LineStyle = ExcelApp.XlLineStyle.xlLineStyleNone;
borders[ExcelApp.XlBordersIndex.xlDiagonalUp].LineStyle = ExcelApp.XlLineStyle.xlLineStyleNone;
borders[ExcelApp.XlBordersIndex.xlDiagonalDown].LineStyle = ExcelApp.XlLineStyle.xlLineStyleNone;
borders = null;
// mysheet.Columns.WrapText = mysheet.get_Range(myExcel.Cells[rowIndex + 1, colIndex], myExcel.Cells[rowIndex + 1, colIndex]);
//mysheet.get_Range(myExcel.Cells[1, colIndex], myExcel.Cells[1, colIndex]).
}
ExcelApp.Style style1 = myExcel.ActiveWorkbook.Styles.Add("Content", Type.Missing);
//style1.Borders.Color = Color.Black;
style1.Font.Name = "Verdana";
// style1.WrapText = true;
style1.Font.Size = 10;
style1.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
style1.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Pink);
foreach (DataRow drow in dt.Rows)
{
rowIndex = rowIndex + 1;
colIndex = 0;
//string str = drow[3].ToString();
//if ((!string.IsNullOrEmpty(drow[3].ToString())))
//{
// decimal sum = Convert.ToDecimal(str);
// if (sum <= 0)
// {
// break;
// }
//}
foreach (DataColumn dcol in dt.Columns)
{
colIndex = colIndex + 1;
myExcel.Cells[rowIndex + 1, colIndex] = drow[dcol.ColumnName];
}
// //if (sum != 0)
// //{
// int col = colIndex;
// for (int i = 0; i <= col; i++)
// {
// ExcelApp.Range rng = (ExcelApp.Range)mysheet.Cells[rowIndex + 1, col];
// // mysheet.Columns.WrapText = rng;
// // rng.WrapText = true;
// rng.Style = style1;
// ExcelApp.Borders borders = mysheet.get_Range(myExcel.Cells[rowIndex + 1, col], myExcel.Cells[rowIndex + 1, col]).Borders;
// borders[ExcelApp.XlBordersIndex.xlEdgeBottom].LineStyle = ExcelApp.XlLineStyle.xlContinuous;
// }
// //}
//}
}
mysheet.Columns.AutoFit();
//For Saving excel file on Server
mybook.SaveCopyAs(strFileName);
}
catch (Exception wzx)
{
MessageBox.Show(wzx.Message);
}
finally
{
mybook.Close(false, false, System.Reflection.Missing.Value);
myExcel.Quit();
GC.Collect();
}
}
public string CreateTotal(List <LabelData> labelList, string fileName = "")
{
string currentData = DateTime.Now.ToString("yyyyMMdd");
string currentDateTime = DateTime.Now.ToString("HHmm");
Excel.Application excelApp = new Excel.Application();
excelApp.Visible = false;
excelApp.DisplayAlerts = false;
Excel._Workbook wBook = excelApp.Workbooks.Add(Type.Missing);
wBook.Activate();
Excel._Worksheet wSheet = excelApp.ActiveSheet;
wSheet.Name = "總表";
Excel.Range wRange;
var printPageSetup = wSheet.PageSetup;
printPageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4;
printPageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
printPageSetup.FitToPagesWide = 1;
excelApp.Cells[1, 2] = "分檢交貨與裝置庫存比較表";
excelApp.Cells[2, 2] = "日期: " + DateTime.Now.ToString("yyyy-MM-dd HH:mm");
if (labelList.Count > 0)
{
var nameGroup = from s in labelList
group s by new { s.Drawer, s.MedID, s.MedName, s.Min, s.Max, s.Current, s.Amount } into g
select new
{
Drawer = g.Key.Drawer,
MedID = g.Key.MedID,
MedName = g.Key.MedName,
Min = g.Key.Min,
Max = g.Key.Max,
Current = g.Key.Current,
Amount = g.Key.Amount
};
excelApp.Cells[2, 4] = "使用單位: " + fileName;
//wSheet.get_Range("C2", "E2").Merge(wSheet.get_Range("C2", "E2").MergeCells);
excelApp.Cells[3, 1] = "No.";
excelApp.Cells[3, 2] = "藥品名稱";
excelApp.Cells[3, 3] = "藥品八碼";
excelApp.Cells[3, 4] = "藥格位置";
excelApp.Cells[3, 5] = "最小值";
excelApp.Cells[3, 6] = "最大值";
excelApp.Cells[3, 7] = "庫存量";
excelApp.Cells[3, 8] = "補藥量";
int lineCount = 4;
foreach (var ng in nameGroup)
{
var groupData = labelList.Select(x => x.MedID == ng.MedID).ToArray();
excelApp.Cells[lineCount, 1] = (lineCount - 3);
excelApp.Cells[lineCount, 2] = ng.MedName;
excelApp.Cells[lineCount, 3] = ng.MedID;
excelApp.Cells[lineCount, 4] = ng.Drawer;
excelApp.Cells[lineCount, 5] = ng.Min;
excelApp.Cells[lineCount, 6] = ng.Max;
excelApp.Cells[lineCount, 7] = ng.Current;
excelApp.Cells[lineCount, 8] = ng.Amount;
lineCount++;
}
}
else
{
excelApp.Cells[3, 1] = "無異常";
}
string areaName = "";
if (!string.IsNullOrEmpty(fileName) && !string.IsNullOrEmpty(fileName.Split(' ')[0]))
{
areaName = fileName;
//areaName = fileName.Split(' ')[0].Substring(16);
}
string mailName = currentData + "-" + currentDateTime + "-" + areaName + "-比對總表";
string pathFile = $"{Properties.Settings.Default.FilePath}\\{currentData}\\{currentData}-{currentDateTime}-{areaName}-比對總表";
wBook.SaveAs(pathFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wBook.Close(false, Type.Missing, Type.Missing);
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(wBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
wBook = null;
wRange = null;
excelApp = null;
GC.Collect();
System.Threading.Thread.Sleep(5000);
if (labelList.Count > 0)
{
SendEmail.SendMail(pathFile, mailName);
}
return("已建立 " + pathFile + "\r\n");
}
private void saveTOcsv(object sender, EventArgs e)
{
button1_Click(sender, e);
// 開啟一個新的應用程式
Excel.Application app = new Excel.Application();
// 讓Excel文件可見
app.Visible = true;
// 停用警告訊息
app.DisplayAlerts = false;
// 加入新的活頁簿
app.Workbooks.Add(Type.Missing);
// 引用第一個活頁簿
wbk = app.Workbooks[1];
// 設定活頁簿焦點
wbk.Activate();
try
{
// 引用第一個工作表
wst = (Excel._Worksheet)wbk.Worksheets[1];
// 命名工作表的名稱
wst.Name = db.Station_0001.mday;
// 設定工作表焦點
wst.Activate();
app.Cells[1, 1] = "ubike retrieve data";
// 設定metainfo
app.Cells[1, 1] = "站點";
app.Cells[2, 1] = "資料更新時間";
app.Cells[3, 1] = "目前車輛數";
app.Cells[4, 1] = "空位數量";
app.Cells[5, 1] = "總停車格";
// 設定第1列顏色
wrange = wst.Range[wst.Cells[1, 1], wst.Cells[5, 1]];
wrange.Select();
wrange.Font.Color = ColorTranslator.ToOle(Color.Blue); //word color
wrange.Interior.Color = ColorTranslator.ToOle(Color.White); //back color
// 設定第2列資料
for (int c = 2; c < db.Stations.Count; c++)
{
app.Cells[1, c] = db.Stations[c - 2].sno; //站點
app.Cells[2, c] = db.Stations[c - 2].mday; //資料更新時間
app.Cells[3, c] = db.Stations[c - 2].sbi; //目前車數
app.Cells[4, c] = db.Stations[c - 2].bemp; //空格數
app.Cells[5, c] = db.Stations[c - 2].tot; //總停車數
}
try
{
//另存活頁簿
wbk.SaveAs(savePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wbk.SaveCopyAs(db.Station_0001.mday);
Console.WriteLine("儲存文件於 " + Environment.NewLine + savePath);
}
catch (Exception ex)
{
Console.WriteLine("儲存檔案出錯，檔案可能正在使用" + Environment.NewLine + ex.Message);
}
}
catch (Exception ex)
{
Console.WriteLine("產生報表時出錯！" + Environment.NewLine + ex.Message);
}
//關閉活頁簿
wbk.Close(false, Type.Missing, Type.Missing);
//關閉Excel
app.Quit();
//釋放Excel資源
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
wbk = null;
wst = null;
wrange = null;
app = null;
GC.Collect();
}
private void XMLsave2excel(object sender, EventArgs e)
{
// 開啟一個新的應用程式
Excel.Application app = new Excel.Application();
// 讓Excel文件可見
app.Visible = true;
// 停用警告訊息
app.DisplayAlerts = false;
// 加入新的活頁簿
app.Workbooks.Add(Type.Missing);
// 引用第一個活頁簿
wbk = app.Workbooks[1];
// 設定活頁簿焦點
wbk.Activate();
try
{
// 引用第一個工作表
wst = (Excel._Worksheet)wbk.Worksheets[1];
// 命名工作表的名稱
wst.Name = "kk citybike";
// 設定工作表焦點
wst.Activate();
app.Cells[1, 1] = "city bike retrieve data";
// 設定metainfo
app.Cells[1, 1] = "站點";
app.Cells[2, 1] = "目前車輛數";
app.Cells[3, 1] = "空位數量";
app.Cells[4, 1] = "總停車格";
app.Cells[5, 1] = "lat,lon";
// 設定第1列顏色
wrange = wst.Range[wst.Cells[1, 1], wst.Cells[5, 1]];
wrange.Select();
wrange.Font.Color = ColorTranslator.ToOle(Color.Blue); //word color
wrange.Interior.Color = ColorTranslator.ToOle(Color.White); //back color
// 設定第2列資料
for (int c = 2; c < bs.cbs.Count + 2; c++)
{
app.Cells[1, c] = c - 1; //站點
app.Cells[2, c] = bs.cbs[c - 2].StationNums1; //資料更新時間
app.Cells[3, c] = Convert.ToInt32(bs.cbs[c - 2].StationNums2); //目前車數
app.Cells[4, c] = Convert.ToInt32(bs.cbs[c - 2].StationNums1) + Convert.ToInt32(bs.cbs[c - 2].StationNums2); //空格數
app.Cells[5, c] = bs.cbs[c - 2].StationLat + "," + bs.cbs[c - 2].StationLon; //總停車數
}
DateTime thisDay = DateTime.Today;
savePath = "C:\\Users\\user\\Desktop\\rawdata\\" + "kk";
try
{
//另存活頁簿
wbk.SaveAs(savePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wbk.SaveCopyAs("kk");
Console.WriteLine("儲存文件於 " + Environment.NewLine + savePath);
}
catch (Exception ex)
{
Console.WriteLine("儲存檔案出錯，檔案可能正在使用" + Environment.NewLine + ex.Message);
}
}
catch (Exception ex)
{
Console.WriteLine("產生報表時出錯！" + Environment.NewLine + ex.Message);
}
//關閉活頁簿
wbk.Close(false, Type.Missing, Type.Missing);
//關閉Excel
app.Quit();
//釋放Excel資源
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
wbk = null;
wst = null;
wrange = null;
app = null;
GC.Collect();
}
private void ExportLogToExcel(Boolean IsWoner)
{
// 設定儲存檔名，不用設定副檔名，系統自動判斷 excel 版本，產生 .xls 或 .xlsx 副檔名
//Log File Path
string pathFile = @"D:\SVS_FAR_FRR_Log";
int currentNumofSheet = 0;
// 開啟一個新的應用程式
excelApp = new Excel.Application();
if (excelApp == null)
{
MessageBox.Show("Excel APP NEW Error !!!");
System.Windows.Forms.Application.Exit();
}
// 讓Excel文件可見
//excelApp.Visible = true;
excelApp.Visible = false;
// 停用警告訊息
excelApp.DisplayAlerts = false;
excelApp.AlertBeforeOverwriting = false;
//Create Log File and set title
if (!(System.IO.File.Exists(pathFile + ".xls")) && !(System.IO.File.Exists(pathFile + ".xlsx")))
{
//// 開啟一個新的應用程式
//excelApp = new Excel.Application();
//// 讓Excel文件可見
//excelApp.Visible = true;
//// 停用警告訊息
//excelApp.DisplayAlerts = false;
//// 加入新的活頁簿
excelApp.Workbooks.Add(Type.Missing);
// 引用第一個活頁簿
wBook = excelApp.Workbooks[1];
currentNumofSheet = excelApp.Worksheets.Count;
for (int i = currentNumofSheet; i < 2; i++)
{
excelApp.Worksheets.Add(Type.Missing);
}
currentNumofSheet = excelApp.Worksheets.Count;
// 設定活頁簿焦點
wBook.Activate();
try
{
for (int i = 0; i <= 1; i++)
{
// 引用第1個工作表
wSheet = (Excel._Worksheet)wBook.Worksheets[i + 1];
// 命名工作表的名稱
if (i == 0)
{
wSheet.Name = "FRR簽名測試";
}
else
{
wSheet.Name = "FAR簽名測試";
}
// 設定工作表焦點
wSheet.Activate();
excelApp.Cells[1, 1] = "Excel測試";
// 設定第1列資料
excelApp.Cells[1, 1] = "NAME";
excelApp.Cells[1, 2] = "SCORE";
excelApp.Cells[1, 3] = "RESULT";
excelApp.Cells[1, 4] = "SECURITY";
excelApp.Cells[1, 5] = "OWNER";
excelApp.Cells[1, 6] = "COST TIME";
excelApp.Cells[1, 7] = "FILE DATE";
}
try
{
//另存活頁簿
wBook.SaveAs(pathFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Console.WriteLine("儲存文件於 " + Environment.NewLine + pathFile);
}
catch (Exception ex)
{
Console.WriteLine("儲存檔案出錯，檔案可能正在使用" + Environment.NewLine + ex.Message);
}
}
catch (Exception ex)
{
Console.WriteLine("產生報表時出錯！" + Environment.NewLine + ex.Message);
}
////關閉活頁簿
wBook.Close(false, Type.Missing, Type.Missing);
}
//============================================
excelApp.UserControl = true;
wBook = excelApp.Workbooks.Open(pathFile);
if (IsWoner)
{
wSheet = (Excel._Worksheet)wBook.Worksheets[1];
}
else
{
wSheet = (Excel._Worksheet)wBook.Worksheets[2];
}
wSheet.Activate();
int x = wSheet.UsedRange.Cells.Rows.Count;
int y = wSheet.UsedRange.Cells.Columns.Count;
//write verify data into log
//// 設定第1列資料
//excelApp.Cells[1, 1] = "NAME";
//excelApp.Cells[1, 2] = "SCORE";
//excelApp.Cells[1, 3] = "RESULT";
//excelApp.Cells[1, 4] = "SECURITY";
//excelApp.Cells[1, 5] = "WONER";
//excelApp.Cells[1, 6] = "CostTime";
//excelApp.Cells[1, 7] = "DATE";
//============================================
x++; //new data line
excelApp.Cells[x, 1] = Template.FileName;
excelApp.Cells[x, 2] = logInfoData.Score;
excelApp.Cells[x, 3] = logInfoData.Result;
excelApp.Cells[x, 4] = logInfoData.Serurity;
excelApp.Cells[x, 5] = logInfoData.Woner;
excelApp.Cells[x, 6] = logInfoData.vcostTime;
excelApp.Cells[x, 7] = logInfoData.date;
//TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT
//// 設定第1列顏色
//wRange = wSheet.Range[wSheet.Cells[1, 1], wSheet.Cells[1, 5]];
//wRange.Select();
//wRange.Font.Color = ColorTranslator.ToOle(Color.White);
//wRange.Interior.Color = ColorTranslator.ToOle(Color.LightSeaGreen);
//// 設定第2列資料
//excelApp.Cells[2, 1] = "AA";
//excelApp.Cells[2, 2] = "10";
//// 設定第3列資料
//excelApp.Cells[3, 1] = "BB";
//excelApp.Cells[3, 2] = "20";
//// 設定第4列資料
//excelApp.Cells[4, 1] = "CC";
//excelApp.Cells[4, 2] = "30";
//VVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVV
//// 設定第5列資料
//excelApp.Cells[5, 1] = "總計";
//// 設定總和公式 =SUM(B2:B4)
//excelApp.Cells[5, 2].Formula = string.Format("=SUM(B{0}:B{1})", 2, 4);
//// 設定第5列顏色
//wRange = wSheet.Range[wSheet.Cells[5, 1], wSheet.Cells[5, 2]];
//wRange.Select();
//wRange.Font.Color = ColorTranslator.ToOle(Color.Red);
//wRange.Interior.Color = ColorTranslator.ToOle(Color.Yellow);
// 自動調整欄寬
wRange = wSheet.Range[wSheet.Cells[1, 1], wSheet.Cells[x, 7]];
wRange.Select();
wRange.Columns.AutoFit();
try
{
//另存活頁簿
wBook.SaveAs(pathFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Console.WriteLine("儲存文件於 " + Environment.NewLine + pathFile);
}
catch (Exception ex)
{
Console.WriteLine("儲存檔案出錯，檔案可能正在使用" + Environment.NewLine + ex.Message);
}
//關閉活頁簿
wBook.Close(false, Type.Missing, Type.Missing);
//關閉Excel
excelApp.Quit();
//釋放Excel資源
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
wBook = null;
wSheet = null;
wRange = null;
excelApp = null;
GC.Collect();
Console.Read();
}
