protected override void Action()
{
base.Action();
if (File.Exists(newWbPath))
{
throw new ActionException($"文件({newWbPath})已经存在");
}
Directory.CreateDirectory(Path.GetDirectoryName(newWbPath));
Wb.SaveAs(newWbPath, GetXlFileFormatByWbPath(newWbPath));
}
private bool SaveReport()
{
Console.WriteLine("Save Report...");
try
{
bool allPass = IsAllPass();
string resultStr = allPass ? "(Pass)" : "(Fail)";
string savePathFile = ExcelFileDirectory + @"\" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + resultStr + ".xlsx";
Wb.SaveAs(savePathFile);
Wb.Close(false, Type.Missing, Type.Missing);
Excel.Quit();
MoveReportToPreFolder(savePathFile);
}
catch (Exception ex)
{
Console.WriteLine("儲存檔案出錯，檔案可能正在使用" + Environment.NewLine + ex.Message);
return(false);
}
return(true);
}
private void buttonSaveExcel_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application App;
Microsoft.Office.Interop.Excel._Workbook Wb;
Microsoft.Office.Interop.Excel._Worksheet oS;
Microsoft.Office.Interop.Excel.Range oRan;
object misvalue = System.Reflection.Missing.Value;
int count;
string count_str;
try
{
count = save_time_str.Count();
count_str = count.ToString();
count_str = "A" + count_str;
List <string> output = Clear(save_time_str);
List <int> int_time_list = new List <int>();
List <string> output_temp = new List <string>();
int x = 0;
foreach (string el in output)
{
Int32.TryParse(el, out x);
int_time_list.Add(x);
}
int temp_val = int_time_list[5];
for (int j = 5; j < 17; j++)
{
if (int_time_list[j] > temp_val)
{
temp_val = int_time_list[j];
}
}
//eliminacja ujemnych i błędnych wartosci
for (int i = 1; i < int_time_list.Count; i++)
{
if (int_time_list[i] < 0)
{
int_time_list.RemoveAt(i);
int_time_list.RemoveAt(i - 1);
}
if (int_time_list[i] > 10 * temp_val)
{
int_time_list.RemoveAt(i);
int_time_list.RemoveAt(i - 1);
}
}
int_time_list.RemoveAt(int_time_list.Count - 1);
foreach (int el in int_time_list)
{
output_temp.Add(el.ToString());
}
string[,] output2 = new string[count, 1];
int it = 0;
foreach (string el in output_temp)
{
output2[it, 0] = el;
it++;
}
App = new Microsoft.Office.Interop.Excel.Application();
App.Visible = true;
Wb = (Microsoft.Office.Interop.Excel._Workbook)(App.Workbooks.Add(""));
oS = (Microsoft.Office.Interop.Excel._Worksheet)Wb.ActiveSheet;
oS.Cells[1, 1] = "times";
oS.get_Range("A1", "B1").Font.Bold = true;
oS.get_Range("A1", "B1").VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
oS.get_Range("A2", count_str).Value2 = output2;
oRan = oS.get_Range("A1", "B1");
oRan.EntireColumn.AutoFit();
App.Visible = false;
App.UserControl = false;
Wb.SaveAs("test505.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
textBox3.Visible = true;
textBox3.Text = "saved in documents folder";
Wb.Close();
}
catch (UnauthorizedAccessException)
{ }
}
public static void DataTableToExcel(System.Data.DataTable dt, string filename)
{
string ExcelPathx = AppDomain.CurrentDomain.BaseDirectory + "\\FILE TRIEN KHAI.xlsm";
if (dt.Rows.Count == 0)
{
return;
}
Excel.Application App = null;
Excel.Workbook Wb;
Excel.Worksheet Ws;
int isExcelOpen = 0;
try
{
App = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
}
catch (System.Runtime.InteropServices.COMException)
{
App = new Excel.Application();
isExcelOpen = 1;
}
//catch(Exception){}
//finally
//{
// App = new Excel.Application();
// isExcelOpen = 1;
//}
//oXL.Visible = true;
Wb = (Excel.Workbook)(App.Workbooks.Open(ExcelPathx, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
//Wb = (Excel.Workbook)(App.Workbooks.Add(Missing.Value));
Ws = (Excel.Worksheet)Wb.Worksheets["Sheet"];
//Ws = (Excel.Worksheet)Wb.ActiveSheet;
try
{
//Ws = (Excel.Worksheet)App.Worksheets.Add();
// Xử lý tiêu đề cột
int rowCount = dt.Rows.Count;
int colCount = dt.Columns.Count;
int c = 0;
//int r = 0;
//Excel.Range HeaderRow = Ws.get_Range("A1");
//foreach (System.Data.DataColumn dc in dt.Columns)
//{
// HeaderRow.get_Offset(0, r).Value2 = dc.ColumnName;
// r++;
//}
//HeaderRow.EntireRow.Font.Bold = true;
//HeaderRow.EntireRow.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
// Xử lý data-> mảng 2 chiều
object[,] rowData = new object[rowCount, colCount];
foreach (System.Data.DataRow row in dt.Rows)
{
for (int col = 0; col < colCount; col++)
{
if (IsNumeric(row[col].GetType().ToString()))
{
rowData[c, col] = System.Convert.ToDouble(row[col].ToString());
}
else
{
rowData[c, col] = row[col].ToString();
}
}
c++;
}
//xóa mảng trước khi pass
//Ws.get_Range("A2:AZ600").get_Resize(rowCount, colCount).Value2 = rowData; Xóa mảng trùng với mảng copy lên excel
Ws.get_Range("A2:AZ600").Cells.ClearContents();
// Paste mảng vào excel
Ws.get_Range("A2").get_Resize(rowCount, colCount).Value2 = rowData;
//Ws.get_Range("A1").get_Resize(1, colCount).EntireColumn.AutoFit();
// Giãn cột
// Lưu file
string ExcelPath = AppDomain.CurrentDomain.BaseDirectory + string.Format("{0}.xlsm", "FILE TRIEN KHAI");
if (System.IO.File.Exists(ExcelPath))
{
System.IO.File.Delete(ExcelPath);
}
Wb.SaveAs(ExcelPath, AccessMode: Excel.XlSaveAsAccessMode.xlShared);
Wb.Close();
//App.Quit();
if (isExcelOpen == 1)
{
App.Quit();
}
dt.Dispose();
}
catch
{
//throw ex;
}
// Dọn rác
System.Runtime.InteropServices.Marshal.ReleaseComObject(Ws);
System.Runtime.InteropServices.Marshal.ReleaseComObject(Wb);
System.Runtime.InteropServices.Marshal.ReleaseComObject(App);
System.Threading.Thread.Sleep(400);
}
private void Xuat_kehoachsx(System.Data.DataTable dt, string filename)//Xuất phiếu
{
if (dt.Rows.Count == 0)
{
return;
}
Excel.Application App = null;
Excel.Workbook Wb;
Excel.Worksheet Ws;
int isExcelOpen = 0;
try
{
App = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
}
catch (System.Runtime.InteropServices.COMException)
{
App = new Excel.Application();
isExcelOpen = 1;
}
//oXL.Visible = true;
Wb = (Excel.Workbook)(App.Workbooks.Add(Missing.Value));
Ws = (Excel.Worksheet)Wb.ActiveSheet;
try
{
Ws = (Excel.Worksheet)App.Worksheets.Add();
// Xử lý tiêu đề cột
int rowCount = dt.Rows.Count;
int colCount = dt.Columns.Count;
int c = 0;
int r = 0;
Excel.Range HeaderRow = Ws.get_Range("A1");
foreach (System.Data.DataColumn dc in dt.Columns)
{
HeaderRow.get_Offset(0, r).Value2 = dc.ColumnName;
r++;
}
HeaderRow.EntireRow.Font.Bold = true;
HeaderRow.EntireRow.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
// Xử lý data-> mảng 2 chiều
object[,] rowData = new object[rowCount, colCount];
foreach (System.Data.DataRow row in dt.Rows)
{
for (int col = 0; col < colCount; col++)
{
if (IsNumeric(row[col].GetType().ToString()))
{
rowData[c, col] = System.Convert.ToDouble(row[col].ToString());
}
else
{
rowData[c, col] = row[col].ToString();
}
}
c++;
}
// Paste mảng vào excel
Ws.get_Range("A2").get_Resize(c, colCount).Value2 = rowData;
Ws.get_Range("A1").get_Resize(1, colCount).EntireColumn.AutoFit();
// Lưu file
string ExcelPath = AppDomain.CurrentDomain.BaseDirectory + string.Format("{0}.xlsx", filename);
if (System.IO.File.Exists(ExcelPath))
{
System.IO.File.Delete(ExcelPath);
}
Wb.SaveAs(ExcelPath, AccessMode: Excel.XlSaveAsAccessMode.xlShared);
Wb.Close();
if (isExcelOpen == 1)
{
App.Quit();
}
dt.Dispose();
}
catch (Exception ex)
{
throw ex;
}
// Dọn rác
System.Runtime.InteropServices.Marshal.ReleaseComObject(Ws);
System.Runtime.InteropServices.Marshal.ReleaseComObject(Wb);
System.Runtime.InteropServices.Marshal.ReleaseComObject(App);
}