private void WriteExcel(Dictionary <string, string> dict, string filename, int col = 2)
{
try
{
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = true;
oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(""));
oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
int row = 1;
foreach (var key in dict.Keys)
{
oSheet.Cells[row, 1] = key;
string keyVal;
if (dict.TryGetValue(key, out keyVal))
{
oSheet.Cells[row, col] = keyVal;
}
row++;
}
oWB.SaveAs("C:\\Users\\mahmad\\Desktop\\" + filename, 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);
oWB.Close();
oXL.Quit();
}
catch
{
MessageBox.Show("Something went wrong");
}
}
private void UpdateExcel(decimal row, string partno, string model, string pono, string year, string seqno, string sn)
{
Microsoft.Office.Interop.Excel.Application oXL = null;
Microsoft.Office.Interop.Excel._Workbook oWB = null;
Microsoft.Office.Interop.Excel._Worksheet oSheet = null;
try
{
oXL = new Microsoft.Office.Interop.Excel.Application();
oWB = oXL.Workbooks.Open(AppContext.BaseDirectory + @"\2021.xlsx");
oSheet = String.IsNullOrEmpty("Sheet1") ? (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet : (Microsoft.Office.Interop.Excel._Worksheet)oWB.Worksheets["Sheet1"];
oSheet.Cells[row, 1] = partno;
oSheet.Cells[row, 2] = partno;
oSheet.Cells[row, 3] = partno;
oSheet.Cells[row, 4] = partno;
oSheet.Cells[row, 5] = partno;
oSheet.Cells[row, 6] = partno;
oWB.Save();
MessageBox.Show("Done!");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
if (oWB != null)
{
oWB.Close();
}
}
}
public void createDDetailsReport(List <NodeRecord> nodeRecordsList)
{
try {
//Start Excel and get Application object.
oXL1 = new Microsoft.Office.Interop.Excel.Application();
//oXL1.Visible = true;
//Get a new workbook.
oWB1 = (Microsoft.Office.Interop.Excel._Workbook)(oXL1.Workbooks.Add(""));
oSheet1 = (Microsoft.Office.Interop.Excel._Worksheet)oWB1.ActiveSheet;
oSheet1.PageSetup.Orientation = Excel.XlPageOrientation.xlPortrait;
int rows = 1;
for (int i = 0; i < nodeRecordsList.Count(); i++)
{
oSheet1.get_Range("A" + rows, "B" + rows).Merge();
oSheet1.Cells[rows, 1].Value2 = nodeRecordsList[i].name;
oSheet1.Cells[rows, 1].Font.Bold = true;
oSheet1.Cells[rows++, 1].Font.Size = 15;
for (int j = 0; j < nodeRecordsList[i].record.Count(); j++)
{
oSheet1.Cells[rows, 1].Value2 = nodeRecordsList[i].record[j].status;
if (nodeRecordsList[i].record[j].status.Equals("Online"))
{
oSheet1.Cells[rows, 1].Font.Color = 0x22EE22;
}
else
{
oSheet1.Cells[rows, 1].Font.Color = 0x000000FF;
}
oSheet1.Cells[rows, 1].Font.Bold = true;
oSheet1.Cells[rows, 2].Font.Bold = true;
oSheet1.Cells[rows++, 2].Value2 = nodeRecordsList[i].record[j].timeDate;
}
}
//oRng1 = oSheet.get_Range("A1", "E1");
//oRng1.EntireColumn.AutoFit();
//oSheet.Columns["A:F"].ColumnWidth = 18;
// oXL.Visible = false;
oXL1.UserControl = false;
oXL1.DisplayAlerts = false;
oWB1.SaveAs(resPath + "Details" + ".xlsx", 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);
oWB1.Close();
reportLabel.Invoke((MethodInvoker) delegate {
reportLabel.Text = "Details Report is saved successfully";
});
} catch (Exception e) {
MessageBoxButtons buttons = MessageBoxButtons.OK;
DialogResult result;
// Displays the MessageBox.
result = MessageBox.Show(e.Message, "Details Report", buttons);
}
}
private void exportExl3(DataGridView dv)
{
try
{
object misValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel._Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = xlApp.Workbooks.Add(misValue);
Microsoft.Office.Interop.Excel._Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets.get_Item(1);
int num = 0;
int num2 = 0;
for (num = 0; num < 1; num++)
{
num2 = 0;
while (num2 <= (dv.ColumnCount - 1))
{
worksheet.Cells[num + 1, num2 + 1] = dv.Columns[num2].HeaderText;
num2++;
}
}
for (num = 0; num <= (dv.RowCount - 1); num++)
{
for (num2 = 0; num2 <= (dv.ColumnCount - 1); num2++)
{
worksheet.Cells[num + 2, num2 + 1] = dv.Rows[num].Cells[num2].Value;
}
}
string filename = @"W:\Software\AutoMailMachineBreakdown\Total Incidents Closed (" + TxtTo.Text + ")" + ".xls";
//string filename = string.Empty;
//SaveFileDialog dialog = new SaveFileDialog
//{
// Filter = "Excel files |*.xls|All files (*.*)|*.*",
// FilterIndex = 2,
// RestoreDirectory = true
//};
//if (dialog.ShowDialog() == DialogResult.OK)
//{
//filename = dialog.FileName;
xlApp.DisplayAlerts = false;
workbook.SaveAs(filename, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
workbook.Close(true, misValue, misValue);
xlApp.Quit();
//}
//else
//{
// return;
//}
this.releaseObject(worksheet);
this.releaseObject(workbook);
this.releaseObject(xlApp);
//MessageBox.Show("Exl exported successfully...");
}
catch (Exception exception)
{
MessageBox.Show(exception.ToString());
}
}
private void btnExcel_Click(object sender, EventArgs e)
{
try
{
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
app.Visible = true;
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
//worksheet.PageSetup.CenterHeader = lblTraining.Text;
//worksheet.PageSetup.LeftHeader = label1.Text + " " + lblS_date.Text;
//worksheet.PageSetup.RightHeader = label2.Text + " " + lblE_date.Text;
workbook.WebOptions.Encoding = Microsoft.Office.Core.MsoEncoding.msoEncodingUTF8;
app.StandardFont = "Myanmar3";
app.StandardFontSize = 10;
for (int i = 1; i < dgvOfficer.Columns.Count + 1; i++)
{
//worksheet.Range ["A1"].Style
worksheet.Cells[1, i] = dgvOfficer.Columns[i - 1].HeaderText.ToString();
}
for (int i = 0; i <= dgvOfficer.Rows.Count - 1; i++)
{
for (int j = 0; j < dgvOfficer.Columns.Count; j++)
{
if (dgvOfficer.Rows[i].Cells[j].Value != null)
{
worksheet.Cells[i + 2, j + 1] = dgvOfficer.Rows[i].Cells[j].Value.ToString();
}
else
{
worksheet.Cells[i + 2, j + 1] = "";
}
}
}
object misValue = System.Reflection.Missing.Value;
string caption = "";
if (cboRank_Search.Text.Trim() != "")
{
caption = "အရာရှိစာရင်း(" + cboRank_Search.Text.Trim() + ").xls";
}
else
{
caption = "အရာရှိစာရင်း.xls";
}
workbook.SaveAs(caption, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
workbook.Close(true, misValue, misValue);
app.Quit();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void exportExl(DataGridView dv)
{
try
{
object misValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel._Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = xlApp.Workbooks.Add(misValue);
Microsoft.Office.Interop.Excel._Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets.get_Item(1);
int num = 0;
int num2 = 0;
for (num = 0; num < 1; num++)
{
num2 = 0;
while (num2 <= (dv.ColumnCount - 1))
{
worksheet.Cells[num + 1, num2 + 1] = dv.Columns[num2].HeaderText;
num2++;
}
}
for (num = 0; num <= (dv.RowCount - 1); num++)
{
for (num2 = 0; num2 <= (dv.ColumnCount - 1); num2++)
{
worksheet.Cells[num + 2, num2 + 1] = dv.Rows[num].Cells[num2].Value;
}
}
string filename = string.Empty;
SaveFileDialog dialog = new SaveFileDialog
{
Filter = "Excel files |*.xls|All files (*.*)|*.*",
FilterIndex = 2,
RestoreDirectory = true
};
if (dialog.ShowDialog() == DialogResult.OK)
{
filename = dialog.FileName;
workbook.SaveAs(filename, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
workbook.Close(true, misValue, misValue);
xlApp.Quit();
}
else
{
return;
}
this.releaseObject(worksheet);
this.releaseObject(workbook);
this.releaseObject(xlApp);
MessageBox.Show("Exl exported successfully...");
}
catch (Exception exception)
{
MessageBox.Show(exception.ToString());
}
}
private void closeExcel()
{
oXL.DisplayAlerts = false;
oXL.Visible = false;
oXL.UserControl = true;
//oWB.Save();
oWB.SaveAs(file, 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);
oWB.Close();
oXL.Quit();
}
public void DataReport(string filename)
{
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workBook = app.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet workSheet = null;
workSheet = workBook.Sheets["Sheet1"];
workSheet = workBook.ActiveSheet;
workSheet.Name = "Sale Report";
app.Columns.ColumnWidth = 30;
app.DisplayAlerts = false;
System.Globalization.CultureInfo _cultureTHInfo = new System.Globalization.CultureInfo("th-TH");
DateTime dt;
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
workSheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
}
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
if (j == 0) // Datetime
{
dt = Convert.ToDateTime(dataGridView1.Rows[i].Cells[j].Value.ToString());
workSheet.Cells[i + 2, j + 1] = dt.ToString("dd MMM yyyy", _cultureTHInfo);
}
else
{
workSheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
}
}
string lastRow = "E" + Convert.ToString(dataGridView1.Rows.Count);
workSheet.get_Range("A1", lastRow).Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
workSheet.get_Range("A1", "E1").Cells.Font.Bold = true; // Header
workBook.SaveAs(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
workBook.Close();
app.Quit();
}
private void button1_Click(object sender, System.EventArgs e)
{
Microsoft.Office.Interop.Excel.Application xlexcel = null;
Microsoft.Office.Interop.Excel._Workbook xlWorkbook = null;
Microsoft.Office.Interop.Excel._Worksheet xlWorkSheet = null;
Excel.Range oRng;
try
{
//open existing workbook.
xlexcel.Workbooks.Add("C:\\vehicledet.xlsx");
xlWorkSheet = (Excel._Worksheet) xlWorkbook.ActiveSheet;
//Get a new workbook.
xlWorkbook = (Excel._Workbook)(oXL.Workbooks.Add( Missing.Value));
xlWorkSheet = (Excel._Worksheet) xlWorkbook.ActiveSheet;
//Add table headers going cell by cell.
xlWorkSheet.Cells[1, 1] = "Plate Number";
xlWorkSheet.Cells[1, 2] = "Car Model";
xlWorkSheet.Cells[1, 3] = "Car Brand";
xlWorkSheet.Cells[1, 4] = "Mileage";
//Format A1:D1 as bold, vertical alignment = center.
xlWorkSheet.get_Range("A1", "D1").Font.Bold = true;
xlWorkSheet.get_Range("A1", "D1").VerticalAlignment =
Excel.XlVAlign.xlVAlignCenter;
// insert text at every last row
int _lastRow = xlWorkSheet.Range["A" + xlWorkSheet.Rows.Count].End[Excel.XlDirection.xlUp].Row + 1;
xlWorkSheet.Cells[_lastRow, 1] = textBox1.Text;
xlWorkSheet.Cells[_lastRow, 2] = textBox2.Text;
xlWorkSheet.Cells[_lastRow, 3] = textBox3.Text;
xlWorkSheet.Cells[_lastRow, 4] = textBox4.Text;
//AutoFit columns A:D.
oRng = xlWorkSheet.get_Range("A1", "D1");
oRng.EntireColumn.AutoFit();
//Make sure Excel is visible and give the user control of Microsoft Excel's lifetime.
xlexcel.Visible = true;
xlexcel.UserControl = true;
xlWorkbook.Save();
xlWorkbook.Close();
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);
MessageBox.Show(errorMessage, "Error");
}
}
private string DownLoadExcel_Crosscheckfile(List <clsFAinfo> Result)
{
try
{
ProcessLogger.Fatal("8948 Down Initial" + DateTime.Now.ToString());
#region 获取模板路径
string fullPath = AppDomain.CurrentDomain.BaseDirectory + "System\\confing.xls";
SaveFileDialog sfdDownFile = new SaveFileDialog();
sfdDownFile.OverwritePrompt = false;
string DesktopPath = Convert.ToString(System.Environment.GetFolderPath(Environment.SpecialFolder.Desktop));
sfdDownFile.Filter = "Excel files (*.xls,*.xlsx)|*.xls;*.xlsx";
string fileinfo = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Resources\\");
sfdDownFile.FileName = Path.Combine(fileinfo, "print" + "_" + DateTime.Now.ToString("yyyyMMdd_mmss") + ".xls");
string strExcelFileName = string.Empty;
string ResaveName = AppDomain.CurrentDomain.BaseDirectory + "Resources\\" + "print" + "_" + DateTime.Now.ToString("yyyyMMdd_mmss") + ".xls";
#endregion
#region 导出前校验模板信息
if (string.IsNullOrEmpty(sfdDownFile.FileName))
{
MessageBox.Show("File name can't be empty, please Check, thanks!");
return("");
}
if (!File.Exists(fullPath))
{
MessageBox.Show("Template file does not exist, please Check, thanks!");
return("");
}
else
{
strExcelFileName = sfdDownFile.FileName;
}
#endregion
#region Excel 初始化
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Microsoft.Office.Interop.Excel.Range rng;
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
System.Reflection.Missing missingValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel._Workbook ExcelBook =
ExcelApp.Workbooks.Open(fullPath, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue);
#endregion
ProcessLogger.Fatal("8949 Input Initial" + DateTime.Now.ToString());
#region 导入
try
{
#region Sheet 初始化
Microsoft.Office.Interop.Excel._Worksheet ExcelSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelBook.Worksheets[1];
#region 填充数据
int RowIndex = 6;
string fapiaoleixingin = "";
string guidangren = "";
string danghao = "";
string guidangshijian = "";
foreach (clsFAinfo item in Result)
{
fapiaoleixingin = item.fapiaoleixing;
guidangren = item.guidangrenzhanghao;
danghao = item.danganhao;
if (item.Input_Date != null && item.Input_Date.Length > 8)
{
guidangshijian = item.Input_Date.Substring(0, 8);
}
RowIndex++;
ExcelSheet.Cells[RowIndex, 1] = "'" + item.fapiaohao;
ExcelSheet.Cells[RowIndex, 2] = "'" + item.bianhao;
//ExcelSheet.Cells[RowIndex, 3] = item.fapiaohao;
}
//ExcelApp.Visible = true;
//ExcelApp.ScreenUpdating = true;
ExcelSheet.Cells[1, 1] = "总件数：" + Result.Count.ToString();
ExcelSheet.Cells[2, 1] = "发票类型：" + fapiaoleixingin;
ExcelSheet.Cells[3, 1] = "归档人：" + guidangren;
ExcelSheet.Cells[4, 1] = "档号：" + danghao;
ExcelSheet.Cells[5, 1] = "归档时间：" + guidangshijian;
//直接打印
//ExcelApp.ActiveWindow.View = Excel.XlWindowView.xlPageBreakPreview;
//ExcelSheet.PageSetup.PaperSize =Excel.XlPaperSize.xlPaperA4;
//ExcelBook.PrintOut();
#region 写入文件
ProcessLogger.Fatal("8950 Output Initial" + DateTime.Now.ToString());
ExcelApp.DisplayAlerts = false;
ExcelApp.ScreenUpdating = true;
ExcelBook.SaveAs(ResaveName, missingValue, missingValue, missingValue, missingValue, missingValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missingValue, missingValue, missingValue, missingValue, missingValue);
ExcelApp.DisplayAlerts = false;
ExcelBook.Close(false, missingValue, missingValue);
ExcelBook = null;
#endregion
//转换成PDF 文件
//if (XLSConvertToPDF(ResaveName, ResaveName.Replace("xlsx", "pdf")))
//{
// // FilePath.Add(ResaveName.Replace("xlsx", "pdf"));
//}
return(ResaveName);
#endregion
#endregion
}
#endregion
#region 异常处理
catch (Exception ex)
{
ExcelApp.DisplayAlerts = false;
ExcelApp.Quit();
ExcelBook = null;
ExcelApp = null;
GC.Collect();
GC.WaitForPendingFinalizers();
throw ex;
}
#endregion
#region Finally垃圾回收
finally
{
//ExcelBook.Close(false, missingValue, missingValue);
//ExcelBook = null;
ExcelApp.DisplayAlerts = true;
ExcelApp.Quit();
clsKeyMyExcelProcess.Kill(ExcelApp);
GC.Collect();
GC.WaitForPendingFinalizers();
}
#endregion
}
catch (Exception ex)
{
throw ex;
}
}
public static bool crateFinalControl(string save, Clamp clamp)
{
bool open = false;
Microsoft.Office.Interop.Excel.Application oXL = null;
Microsoft.Office.Interop.Excel._Workbook oWB = null;
Microsoft.Office.Interop.Excel._Worksheet oSheet = null;
//Microsoft.Office.Interop.Excel.Range oRng;
object misvalue = System.Reflection.Missing.Value;
try
{
//Start Excel and get Application object.
oXL = new Microsoft.Office.Interop.Excel.Application();
//oXL.Visible = true;
// oWB = (Excel.Workbook)(oXL.Workbooks._Open(@"C:\Users\AleksandraTos\Documents\Visual Studio 2008\Projects\Control Generator\bin\Debug\final control.xlsx", Missing.Value,
//Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
//Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
oWB = (Excel.Workbook)(oXL.Workbooks._Open(save, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
//Get a new workbook.
//oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(""));
oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
if (!clamp.Clamptype.Contains("PC"))
{
RegularClamp(oSheet, clamp);
}
else
{
PinchClamp(oSheet, clamp);
}
oXL.Visible = false;
oXL.UserControl = false;
oWB.SaveAs(save, 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);
//oWB.Close(true, open, Missing.Value);
open = true;
oWB.Close(true, Missing.Value, Missing.Value);
oXL.Quit();
Marshal.ReleaseComObject(oSheet);
Marshal.ReleaseComObject(oWB);
Marshal.ReleaseComObject(oXL);
}
catch (Exception ex)
{
string newfolder = direktorijum + "\\Logs";
if (!Directory.Exists(newfolder))
{
Directory.CreateDirectory(newfolder);
}
newfolder += "\\log21" + DateTime.Now.ToString("dd_MM_yyyy hh_mm_ss") + ".txt";
StreamWriter sw = new StreamWriter(newfolder);
sw.Write(ex.ToString());
sw.Close();
//MessageBox.Show("Something went wrong during the creation of the control file", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
//if (!Directory.Exists(direktorijum + @"\Log Files")) Directory.CreateDirectory(direktorijum + @"\Log Files");
//StreamWriter sw = new StreamWriter(direktorijum + "\\Log Files\\Log" + DateTime.Now.ToString("dd-MM-yyyy -- HH-mm-ss") + ".txt");
//sw.Write(ex);
//sw.Close();
open = false;
}
finally
{
}
return(open);
}
private void btnPrint_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application oXL = null;
Microsoft.Office.Interop.Excel._Workbook oWB = null;
Microsoft.Office.Interop.Excel._Worksheet oSheet = null;
Excel.Range range;
oXL = new Microsoft.Office.Interop.Excel.Application();
oWB = oXL.Workbooks.Open(AppContext.BaseDirectory + @"\2021.xlsx");
oSheet = String.IsNullOrEmpty("Sheet1") ? (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet : (Microsoft.Office.Interop.Excel._Worksheet)oWB.Worksheets["Sheet1"];
int rw = 0;
int cl = 0;
range = oSheet.UsedRange;
if (txtSeqNo.Text == "0")
{
rw = range.Rows.Count + 1;
}
else
{
rw = range.Rows.Count + 2;
}
cl = range.Columns.Count;
try
{
//export UG to text file
using (TextWriter tw = new StreamWriter(AppContext.BaseDirectory + @"\SN.txt"))
{
for (int x = 0; x < DG.Rows.Count - 1; x++)
{
tw.Write($"{DG.Rows[x].Cells[0].Value.ToString()}");
tw.WriteLine();
//append text fields to excell
//UpdateExcel(decimal.Parse(txtSeqNo.Text), txtPartNo.Text, txtBrand.Text, txtPONo.Text, txtYear.Text, DG.Rows[x].Cells[0].Value.ToString().Substring(DG.Rows[x].Cells[0].Value.ToString().Length - 8, 8), DG.Rows[x].Cells[0].Value.ToString());
oSheet.Cells[rw + x, 1] = txtPartNo.Text;
oSheet.Cells[rw + x, 2] = txtBrand.Text;
oSheet.Cells[rw + x, 3] = txtPONo.Text;
oSheet.Cells[rw + x, 4] = txtYear.Text;
oSheet.Cells[rw + x, 5] = DG.Rows[x].Cells[0].Value.ToString().Substring(DG.Rows[x].Cells[0].Value.ToString().Length - 8, 8);
oSheet.Cells[rw + x, 6] = DG.Rows[x].Cells[0].Value.ToString();
}
}
oWB.Save();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
if (oWB != null)
{
oWB.Close();
}
oXL.Quit();
Marshal.ReleaseComObject(oSheet);
Marshal.ReleaseComObject(oWB);
Marshal.ReleaseComObject(oXL);
}
}
private bool XLSConvertToPDF(string sourcePath, string targetPath)
{
bool result = false;
Excel.XlFixedFormatType targetType = Excel.XlFixedFormatType.xlTypePDF;
object missing = Type.Missing;
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = null;
Microsoft.Office.Interop.Excel._Workbook ExcelBook = null;
try
{
object target = targetPath;
object type = targetType;
//workBook = application.Workbooks.Open(sourcePath, missing, missing, missing, missing, missing,
// missing, missing, missing, missing, missing, missing, missing, missing, missing);
// sourcePath = "C:\\Users\\IBM_ADMIN\\Desktop\\newadd.xlsx";
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
System.Reflection.Missing missingValue = System.Reflection.Missing.Value;
ExcelBook = ExcelApp.Workbooks.Open(sourcePath, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue);
//ExcelApp.Visible = true;
//ExcelApp.ScreenUpdating = true;
////ActiveWindow.SmallScroll Down:=6;
//ExcelApp.ActiveWindow.View = Excel.XlWindowView.xlPageBreakPreview;
////ExcelApp.ActiveWindow.SmallScroll = Excel.;
//ExcelApp.ActiveWindow.Zoom = 80;
////ActiveWindow.SmallScroll Down:=-3
//// excelRange.WrapText = true;
////ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
//ActiveWindow.SmallScroll Down:=30
//Set ActiveSheet.HPageBreaks(1).Location = Range("A67")
//ActiveWindow.SmallScroll Down:=-75
//Microsoft.Office.Interop.Excel.Worksheet WS2 = (Microsoft.Office.Interop.Excel.Worksheet)ExcelBook.Worksheets[2];
////上边距
//double top = 0;
////左边距
//double left = 0;
////右边距
//double right = 0;
////下边距
//double footer = 0;
//WS2.DisplayAutomaticPageBreaks = false;//显示分页线
//WS2.PageSetup.CenterFooter = "第 &P 页，共 &N 页";
//WS2.PageSetup.TopMargin = ExcelApp.InchesToPoints(top / 2.54);//上
//WS2.PageSetup.BottomMargin = ExcelApp.InchesToPoints(footer / 15.54);//下
//WS2.PageSetup.LeftMargin = ExcelApp.InchesToPoints(left / 2.54);//左
//WS2.PageSetup.RightMargin = ExcelApp.InchesToPoints(right / 2.54);//右
//WS2.PageSetup.CenterHorizontally = true;//水平居中 xlSheet.PageSetup.PrintTitleRows = "$1:$3";//顶端标题行
//WS2.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA3;//A3纸张大小 xlSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;//纸张方向.横向
//Excel.Range excelRange = WS2.get_Range(WS2.Cells[1, 1], WS2.Cells[64, 24]);
//自动调整列宽
//// excelRange.EntireColumn.AutoFit();
//// excelRange.WrapText = false; //文本自动换行
//excelRange.ShrinkToFit = false;
////设置字体在单元格内的对其方式
//excelRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
//// 文本水平居中方式
//excelRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//////设置为横向打印
////WS2.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
////ExcelApp.ActiveWindow.FreezePanes = true;
////excelRange.EntireColumn.AutoFit();
////WS2.PageSetup.Orientation = 2;
//WS2.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4;
//WS2.PageSetup.LeftMargin = ExcelApp.InchesToPoints(0.0);
//WS2.PageSetup.RightMargin = ExcelApp.InchesToPoints(0.0);
//WS2.PageSetup.TopMargin = ExcelApp.InchesToPoints(0.0);
//WS2.PageSetup.BottomMargin = ExcelApp.InchesToPoints(0.0);
//WS2.PageSetup.HeaderMargin = ExcelApp.InchesToPoints(0.0);
//WS2.PageSetup.FooterMargin = ExcelApp.InchesToPoints(0.0);
//WS2.PageSetup.CenterHorizontally = true;
//// WS2.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4;
//WS2.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
//excelRange = WS2.get_Range(WS2.Cells[1, 1], WS2.Cells[2, 20]);
//WS2.PageSetup.PrintTitleRows = excelRange.get_Address(excelRange.Row, excelRange.Column, Excel.XlReferenceStyle.xlA1, 1, 1);
ExcelBook.ExportAsFixedFormat(targetType, target, Excel.XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing);
result = true;
}
catch
{
result = false;
}
finally
{
if (ExcelBook != null)
{
ExcelBook.Close(true, missing, missing);
ExcelBook = null;
}
if (ExcelApp != null)
{
ExcelApp.Quit();
ExcelApp = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
return(result);
}
public static void SaveFile(DataGridView dataGridView, string filePathAndName)
{
// creating Excel Application
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
// creating new WorkBook within Excel application
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
// creating new Excelsheet in workbook
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
//Do not show alerts
app.DisplayAlerts = false;
// see the excel sheet behind the program
app.Visible = false;
// get the reference of first sheet. By default its name is Sheet1.
// store its reference to worksheet
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
// changing the name of active sheet
worksheet.Name = "Exported from gridview";
//// storing header part in Excel
//int excelColNo = 1;
//for (int i = 0; i < dataGridView.Columns.Count; i++)
//{
// if (dataGridView.Columns[i].Visible == true)
// {
// worksheet.Cells[1, excelColNo] = dataGridView.Columns[i].HeaderText;
// excelColNo++;
// }
//}
// storing Each row and column value to excel sheet
int excelColNo = 1;
for (int i = 0; i < dataGridView.Rows.Count; i++)
{
excelColNo = 1;
for (int j = 0; j < dataGridView.Columns.Count; j++)
{
if (dataGridView.Columns[j].Visible == true)
{
worksheet.Cells[i + 2, excelColNo] = "'" + dataGridView.Rows[i].Cells[j].Value.ToString();
excelColNo++;
}
}
}
// save the application
string timeStamp = DateTime.Now.ToString("ddMMyy");
NewFileName = "DK12_Cold_TM_" + timeStamp + "_UTF8_TM-DK.txt";
try
{
workbook.SaveAs(FileHelper.FilePath + "\\" + FileHelper.NewFileName, Excel.XlFileFormat.xlCurrentPlatformText, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
MessageBox.Show("File saved succesfully!");
}
catch (Exception ex)
{
MessageBox.Show("Error during save: " + ex.Message);
workbook.Close();
app.Quit();
}
workbook.Close();
// Exit from the application
app.Quit();
}
private void ExportToExcel()
{
bool IsExport = false;
// Creating a Excel object.
Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
//DataGridView에 불러온 Data가 아무것도 없을 경우
if (studentScoreTable.Rows.Count == 0)
{
MessageBox.Show("데이터가 없습니다.", "Inform", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
try
{
worksheet = workbook.ActiveSheet;
int cellRowIndex = 1;
int cellColumnIndex = 1;
for (int col = 0; col < studentScoreTable.Columns.Count; col++)
{
if (cellRowIndex == 1)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = studentScoreTable.Columns[col].HeaderText;
}
cellColumnIndex++;
}
cellColumnIndex = 1;
cellRowIndex++;
for (int row = 0; row < studentScoreTable.Rows.Count - 1; row++)
{
for (int col = 0; col < studentScoreTable.Columns.Count; col++)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = studentScoreTable.Rows[row].Cells[col].Value.ToString();
cellColumnIndex++;
}
cellColumnIndex = 1;
cellRowIndex++;
}
SaveFileDialog saveFileDialog = GetExcelSave();
if (saveFileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
workbook.SaveAs(saveFileDialog.FileName);
MessageBox.Show("저장되었습니다.");
IsExport = true;
}
//Export 성공 했으면 객체들 해제
if (IsExport)
{
workbook.Close();
excel.Quit();
workbook = null;
excel = null;
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public ActionResult DownLoadFile()
{
try
{
#region Exal
byte[] bytes;
string fileName = "", contentType = "", PaperPath = "";
DataSet dswordchargeinrow = new DataSet();
KeystoneProject.Buisness_Logic.Master.Bl_Services Bl_obj = new Buisness_Logic.Master.Bl_Services();
Services AddServiceMod = new Services();
AddServiceMod.StoreAllServices = Bl_obj.GetAllServicesExcelData();
AddServiceMod.dsServicesCharges = Bl_obj.GetAllWardName();
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
Excel.Range chartRange;
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets["Sheet1"];
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet;
app.get_Range("A2", "S2").Locked = true;
string WardNAme;
//worksheet.Name = "Exported from Keystone";
int count = 0;
for (int i = 1; i < AddServiceMod.StoreAllServices.Tables[0].Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = AddServiceMod.StoreAllServices.Tables[0].Columns[i - 1].ColumnName;
count = i;
}
int m = 0;
for (int word = 0; word < AddServiceMod.dsServicesCharges.Tables[0].Rows.Count; word++)
{
worksheet.Cells[1, count + word] = AddServiceMod.dsServicesCharges.Tables[0].Rows[m]["WardName"];
// worksheet.get_Range(worksheet.Cells[1, word], worksheet.Cells[4, 3]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Orange);
WardNAme = AddServiceMod.dsServicesCharges.Tables[0].Rows[m]["WardName"].ToString() + ",";
m++;
}
for (int i = 0; i < AddServiceMod.StoreAllServices.Tables[0].Rows.Count; i++)
{
for (int j = 0; j < AddServiceMod.StoreAllServices.Tables[0].Columns.Count; j++)
{
// if (j < 2)
// {
worksheet.Cells[i + 2, j + 1] = AddServiceMod.StoreAllServices.Tables[0].Rows[i][j].ToString();
// }
if (AddServiceMod.StoreAllServices.Tables[0].Rows[i]["ServiceID"].ToString() != "")
{
dswordchargeinrow = Bl_obj.SelectServiceChargesbyID(Convert.ToInt16(AddServiceMod.StoreAllServices.Tables[0].Rows[i]["ServiceID"]));
if (dswordchargeinrow.Tables[0].Rows.Count > 0)
{
foreach (DataRow drcharge in AddServiceMod.dsServicesCharges.Tables[0].Rows)
{
int charge = 0;
foreach (DataRow dr in dswordchargeinrow.Tables[0].Rows)
{
string WardName = drcharge["WardName"].ToString();
string WardName1 = dr["WardName"].ToString();
if (drcharge["WardName"].ToString() == dr["WardName"].ToString())
{
worksheet.Cells[i + 2, charge + 7] = dr["GeneralCharges"];
}
charge++;
}
}
}
}
}
}
#endregion
Random obj = new Random();
int a = obj.Next(1, 300);
string DateCode = "Service_Excel_Data" + Bl_obj.ReffrenceExcel();
fileName = Server.MapPath("~/") + "Service_Excel/" + DateCode;
workbook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
workbook.Close(true, Type.Missing, Type.Missing);
string Path = fileName + ".xlsx";
string filename = System.IO.Path.GetFileName(DateCode + ".xlsx");
// string fullpath = System.IO.Path.Combine(Path, fileName);
contentType = MimeMapping.GetMimeMapping(Path);
File(Path, contentType, filename);
// File(Path, contentType, filename);
return(new JsonResult {
Data = "Done", JsonRequestBehavior = JsonRequestBehavior.AllowGet
});
//fileName = "Service_Excel_Data" + Bl_obj.ReffrenceCode()+".xlsx";
}
catch (Exception ex)
{
throw;
}
// return File(FileById.FileContent, "/MRDFiles/pdf", FileById.FileName);
}
//public static void addPictureExcel(string value, int cellname, int cellnum)
//{
// foreach (Excel.Shape sh in myExcelWorkSheet.Shapes)
// {
// if (sh.Name != "Picture 1")
// sh.Delete(]=1;
// }
// myExcelWorkSheet.Shapes.AddPicture(value, Microsoft.Office.Core.MsoTriState.msoTrue, Microsoft.Office.Core.MsoTriState.msoTrue, cellname, cellnum, 131, 40]=1;
//}
public static bool createFinalControlReport(string save, List <Clamp> clamps, string date, string RN)
{
bool result = false;
int counter = 5;
object misValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application oXL = null;
Microsoft.Office.Interop.Excel._Workbook oWB = null;
Microsoft.Office.Interop.Excel._Worksheet oSheet = null;
try
{
oXL = new Microsoft.Office.Interop.Excel.Application();
//oXL.Visible = true;
// oWB = (Excel.Workbook)(oXL.Workbooks._Open(@"C:\Users\AleksandraTos\Documents\Visual Studio 2008\Projects\Control Generator\bin\Debug\final control.xlsx", Missing.Value,
//Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
//Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
oWB = (Excel.Workbook)(oXL.Workbooks._Open(save, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
//Get a new workbook.
//oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(""));
oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
if (oXL == null)
{
//MessageBox.Show("Excel is not properly installed!!");
//return;
}
else
{
oSheet.Cells[5, 1] = date;
oSheet.Cells[15, 1] = RN;
foreach (Clamp c in clamps)
{
oSheet.Cells[counter, 2] = c.Serialnum;
oSheet.Cells[counter, 3] = c.Rado;
oSheet.Cells[12, 1] = c.Clamptype;
if (c.Clamptype.Contains("WH1 - 15V") || c.Clamptype.Contains("WH2 - 15V"))
{
oSheet.Cells[9, 1] = "15V";
oSheet.Cells[counter, 8] = c.Smallresistance;
}
else
{
oSheet.Cells[9, 1] = "24V";
oSheet.Cells[counter, 8] = c.Bigresistance;
}
oSheet.Cells[counter, 6] = "OK";
oSheet.Cells[counter, 5] = "MM, NS, MM";
oSheet.Cells[counter, 4] = "1";
counter++;
}
oXL.Visible = false;
oXL.UserControl = false;
oWB.SaveAs(save, 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);
//oWB.Close(true, open, Missing.Value);
result = true;
oWB.Close(true, Missing.Value, Missing.Value);
oXL.Quit();
Marshal.ReleaseComObject(oSheet);
Marshal.ReleaseComObject(oWB);
Marshal.ReleaseComObject(oXL);
}
}
catch (Exception ex)
{
string newfolder = direktorijum + "\\Logs";
if (!Directory.Exists(newfolder))
{
Directory.CreateDirectory(newfolder);
}
newfolder += "\\log22" + DateTime.Now.ToString("dd_MM_yyyy hh_mm_ss") + ".txt";
StreamWriter sw = new StreamWriter(newfolder);
sw.Write(ex.ToString());
sw.Close();
//MessageBox.Show("Something went wrong during the creation of the control file", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
//if (!Directory.Exists(direktorijum + @"\Log Files")) Directory.CreateDirectory(direktorijum + @"\Log Files");
//StreamWriter sw = new StreamWriter(direktorijum + "\\Log Files\\Log" + DateTime.Now.ToString("dd-MM-yyyy -- HH-mm-ss") + ".txt");
//sw.Write(ex);
//sw.Close();
result = false;
}
return(result);
}
public void createColorScaleExcel()
{
try {
//Start Excel and get Application object.
oXL = new Microsoft.Office.Interop.Excel.Application();
//oXL.Visible = true;
//Get a new workbook.
oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(""));
oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
oSheet.Columns["A:F"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
oSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
oSheet.get_Range("A1:A2", "B1:B2").Merge();
oSheet.Cells[1, 1].Value2 = "From: " + reportDate[0] + " at " + reportTimePeriod[0] + "\nTo: " + reportDate[1] + " at " + reportTimePeriod[1];
int m = 3;
for (int i = 0; i < nodePathInfoList.Count(); i++)
{
oSheet.Cells[m, 1].Value2 = nodePathInfoList[i].portName + ":";
oSheet.Cells[m, 1].Font.Bold = true;
oSheet.Cells[m, 1].Font.Size = 20;
oSheet.Cells[m, 1].HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
oSheet.get_Range("A" + m.ToString(), "B" + m.ToString()).Merge();
// Down to top counter
int z = nodePathInfoList[i].nodePathData.Count() - 1;
for (int j = 0; j < nodePathInfoList[i].nodePathData.Count(); j++)
{
if (j != 0 && j % 6 == 0)
{
m += 4;
}
oSheet.Cells[m + 1, (j % 6) + 1].Value2 = nodePathInfoList[i].nodePathData[z].ElementAt(1);
oSheet.Cells[m + 2, (j % 6) + 1].Value2 = nodePathInfoList[i].nodePathData[z].ElementAt(2);
oSheet.Cells[m + 3, (j % 6) + 1].Value2 = nodePathInfoList[i].nodePathData[z].ElementAt(3);
z--;
// Set all borders for table
oSheet.Cells[m + 1, (j % 6) + 1].Borders.Color = System.Drawing.Color.Black.ToArgb();
oSheet.Cells[m + 2, (j % 6) + 1].Borders.Color = System.Drawing.Color.Black.ToArgb();
oSheet.Cells[m + 3, (j % 6) + 1].Borders.Color = System.Drawing.Color.Black.ToArgb();
// Create a color scale for third row
cfColorScale = (ColorScale)(oSheet.get_Range("A" + (m + 3).ToString(), "F" + (m + 3).ToString()).FormatConditions.AddColorScale(2));
// Min and Max color
cfColorScale.ColorScaleCriteria[1].FormatColor.Color = 0x000000FF; // Red
cfColorScale.ColorScaleCriteria[2].FormatColor.Color = 0x0000FF00; // Green
// Set table font size and bold
oSheet.get_Range("A" + (m + 1).ToString(), "F" + (m + 1).ToString()).Font.Size = 11;
oSheet.get_Range("A" + (m + 2).ToString(), "F" + (m + 2).ToString()).Font.Size = 11;
oSheet.get_Range("A" + (m + 3).ToString(), "F" + (m + 3).ToString()).Font.Size = 11;
oSheet.get_Range("A" + (m + 1).ToString(), "F" + (m + 1).ToString()).Font.Bold = true;
oSheet.get_Range("A" + (m + 2).ToString(), "F" + (m + 2).ToString()).Font.Bold = true;
oSheet.get_Range("A" + (m + 3).ToString(), "F" + (m + 3).ToString()).Font.Bold = true;
}
m += 6;
}
oRng = oSheet.get_Range("A1", "E1");
oRng.EntireColumn.AutoFit();
oSheet.Columns["A:F"].ColumnWidth = 18;
// oXL.Visible = false;
oXL.UserControl = false;
oXL.DisplayAlerts = false;
oWB.SaveAs(resPath + ".xlsx", 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);
oWB.Close();
reportLabel.Invoke((MethodInvoker) delegate {
reportLabel.Text = "Report is saved successfully";
});
} catch (Exception e) {
MessageBoxButtons buttons = MessageBoxButtons.OK;
DialogResult result;
// Displays the MessageBox.
result = MessageBox.Show(e.Message, "Reports", buttons);
}
}
public static bool createPackingList(string save, List <Clamp> clamps, List <string> onetimeparams, string RN)
{
bool open = false;
int counter = 16;
object misValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application oXL = null;
Microsoft.Office.Interop.Excel._Workbook oWB = null;
Microsoft.Office.Interop.Excel._Worksheet oSheet = null;
//Microsoft.Office.Interop.Excel.Range oRng;
try
{
//if (xlApp == null)
//{
// //MessageBox.Show("Excel is not properly installed!!");
// //return;
//}
//else
//{
oXL = new Microsoft.Office.Interop.Excel.Application();
//oXL.Visible = true;
// oWB = (Excel.Workbook)(oXL.Workbooks._Open(@"C:\Users\AleksandraTos\Documents\Visual Studio 2008\Projects\Control Generator\bin\Debug\final control.xlsx", Missing.Value,
//Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
//Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
oWB = (Excel.Workbook)(oXL.Workbooks._Open(save, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
//Get a new workbook.
//oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(""));
oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
oSheet.Cells[4, 8] = onetimeparams[0];
oSheet.Cells[13, 1] = onetimeparams[1];
oSheet.Cells[10, 1] = RN;
oSheet.Cells[13, 4] = onetimeparams[2];
foreach (Clamp c in clamps)
{
oSheet.Cells[counter, 7] = c.Serialnum;
oSheet.Cells[counter, 8] = c.Rado;
oSheet.Cells[counter, 9] = "1";
oSheet.Cells[16, 6] = c.Clamptype;
counter++;
}
oXL.Visible = false;
oXL.UserControl = false;
oWB.SaveAs(save, 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);
//oWB.Close(true, open, Missing.Value);
open = true;
oWB.Close(true, Missing.Value, Missing.Value);
oXL.Quit();
Marshal.ReleaseComObject(oSheet);
Marshal.ReleaseComObject(oWB);
Marshal.ReleaseComObject(oXL);
//}
}
catch (Exception ex)
{
string newfolder = direktorijum + "\\Logs";
if (!Directory.Exists(newfolder))
{
Directory.CreateDirectory(newfolder);
}
newfolder += "\\log23" + DateTime.Now.ToString("dd_MM_yyyy hh_mm_ss") + ".txt";
StreamWriter sw = new StreamWriter(newfolder);
sw.Write(ex.ToString());
sw.Close();
//MessageBox.Show("Something went wrong during the creation of the control file", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
//if (!Directory.Exists(direktorijum + @"\Log Files")) Directory.CreateDirectory(direktorijum + @"\Log Files");
//StreamWriter sw = new StreamWriter(direktorijum + "\\Log Files\\Log" + DateTime.Now.ToString("dd-MM-yyyy -- HH-mm-ss") + ".txt");
//sw.Write(ex);
//sw.Close();
open = false;
}
return(open);
}
public int print(bool isShow)
{
// 打开一个Excel进程
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
// 利用这个进程打开一个Excel文件
Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + @"\..\..\xls\cleancut\5 SOP-MFG-302-R05A 清洁分切工序清场记录.xlsx");
// 选择一个Sheet，注意Sheet的序号是从1开始的
Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[1];
// 修改Sheet中某行某列的值
fill_excel(my);
//"生产指令-步骤序号- 表序号 /&P"
int sheetnum;
OleDbDataAdapter da = new OleDbDataAdapter("select ID from 清场记录" + " where 生产指令ID=" + instrid.ToString(), mySystem.Parameter.connOle);
DataTable dt = new DataTable("temp");
da.Fill(dt);
List <Int32> sheetList = new List <Int32>();
for (int i = 0; i < dt.Rows.Count; i++)
{
sheetList.Add(Convert.ToInt32(dt.Rows[i]["ID"].ToString()));
}
sheetnum = sheetList.IndexOf(Convert.ToInt32(dt_prodinstr.Rows[0]["ID"])) + 1;
my.PageSetup.RightFooter = instrcode + "-05-" + sheetnum.ToString("D3") + " &P/" + wb.ActiveSheet.PageSetup.Pages.Count; // &P 是页码
if (isShow)
{
//true->预览
// 设置该进程是否可见
oXL.Visible = true;
// 让这个Sheet为被选中状态
my.Select(); // oXL.Visible=true 加上这一行 就相当于预览功能
return(0);
}
else
{
int pageCount = wb.ActiveSheet.PageSetup.Pages.Count;
bool isPrint = true;
//false->打印
try
{
// 设置该进程是否可见
//oXL.Visible = false; // oXL.Visible=false 就会直接打印该Sheet
// 直接用默认打印机打印该Sheet
my.PrintOut();
}
catch
{ isPrint = false; }
finally
{
if (isPrint)
{
//写日志
string log = "=====================================\n";
log += DateTime.Now.ToString("yyyy年MM月dd日 hh时mm分ss秒") + "\n" + label角色.Text + "：" + mySystem.Parameter.userName + " 打印文档\n";
dt_prodinstr.Rows[0]["日志"] = dt_prodinstr.Rows[0]["日志"].ToString() + log;
bs_prodinstr.EndEdit();
if (!mySystem.Parameter.isSqlOk)
{
da_prodinstr.Update((DataTable)bs_prodinstr.DataSource);
}
else
{
da_prodinstrsql.Update((DataTable)bs_prodinstr.DataSource);
}
}
// 关闭文件，false表示不保存
wb.Close(false);
// 关闭Excel进程
oXL.Quit();
// 释放COM资源
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(oXL);
wb = null;
oXL = null;
my = null;
}
return(pageCount);
}
}
public int print(bool b)
{
int label_打印成功 = 1;
// 打开一个Excel进程
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
// 利用这个进程打开一个Excel文件
string dir = System.IO.Directory.GetCurrentDirectory();
dir += "./../../xls/cleancut/SOP-MFG-302-R01A 清洁分切生产指令.xlsx";
Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(dir);
// 选择一个Sheet，注意Sheet的序号是从1开始的
Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[2];
// 修改Sheet中某行某列的值
fill_excel(my);
//"生产指令-步骤序号- 表序号 /&P"
my.PageSetup.RightFooter = instrcode + "-01-" + find_indexofprint().ToString("D3") + " &P/" + wb.ActiveSheet.PageSetup.Pages.Count; // &P 是页码
if (b)
{
// 设置该进程是否可见
oXL.Visible = true;
// 让这个Sheet为被选中状态
my.Select(); // oXL.Visible=true 加上这一行 就相当于预览功能
return(0);
}
else
{
int pageCount = wb.ActiveSheet.PageSetup.Pages.Count;
// 直接用默认打印机打印该Sheet
try
{
my.PrintOut(); // oXL.Visible=false 就会直接打印该Sheet
}
catch
{
label_打印成功 = 0;
}
finally
{
if (1 == label_打印成功)
{
string log = "\n=====================================\n";
log += DateTime.Now.ToString("yyyy年MM月dd日 hh时mm分ss秒") + "\n" + label角色.Text + ":" + mySystem.Parameter.userName + " 完成打印\n";
dt_prodinstr.Rows[0]["日志"] = dt_prodinstr.Rows[0]["日志"].ToString() + log;
bs_prodinstr.EndEdit();
if (!mySystem.Parameter.isSqlOk)
{
da_prodinstr.Update((DataTable)bs_prodinstr.DataSource);
}
else
{
da_prodinstrsql.Update((DataTable)bs_prodinstr.DataSource);
}
}
// 关闭文件，false表示不保存
wb.Close(false);
// 关闭Excel进程
oXL.Quit();
// 释放COM资源
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(oXL);
wb = null;
oXL = null;
my = null;
}
return(pageCount);
}
}
private void printToolStripMenuItem_Click(object sender, EventArgs e)
{
try
{
//string file = "";
//if (folderBrowserDialog1.ShowDialog() == DialogResult.OK)
//{
// file = folderBrowserDialog1.SelectedPath + @"\MES.xlsx";
//}
//else
//{
// return;
//}
//FileStream outStream = new FileStream(file, FileMode.Create, FileAccess.Write, FileShare.Read);
//outStream.Dispose();
//string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;" +
// "Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=0'", file);
////FileStream inStream = new FileStream(Application.StartupPath + @"\temp.xlsx", FileMode.Open, FileAccess.Read, FileShare.None);
//using (OleDbConnection cn = new OleDbConnection(connectionString))
//{
// cn.Open();
// for (int i = 0; i <= dgvTrainner .Rows .Count ; i++)
// {
// OleDbCommand cmd1 = new OleDbCommand("INSERT INTO [Sheet1$] " +
// "([Column1],[Column2],[Column3],[Column4],[Column5],[Column6]) " +
// "VALUES(@value1, @value2, @value3, @value4, @value5, @value6)", cn);
// cmd1.Parameters.AddWithValue("@value1", dgvTrainner.Rows[i].Cells[0].Value.ToString () );
// cmd1.Parameters.AddWithValue("@value2", dgvTrainner.Rows[i].Cells[1].Value.ToString());
// cmd1.Parameters.AddWithValue("@value3", dgvTrainner.Rows[i].Cells[2].Value.ToString());
// cmd1.Parameters.AddWithValue("@value4", dgvTrainner.Rows[i].Cells[3].Value.ToString());
// cmd1.Parameters.AddWithValue("@value5", dgvTrainner.Rows[i].Cells[4].Value.ToString());
// cmd1.Parameters.AddWithValue("@value6", dgvTrainner.Rows[i].Cells[5].Value.ToString());
// cmd1.ExecuteNonQuery();
// }
//}
//using (inStream)
//{
// while (true)
// {
// int b = inStream.ReadByte();
// if (b == -1)
// break;
// outStream.WriteByte((byte)b);
// }
// MessageBox.Show("Your output file is ready in " + file + " as Marker_M_Data.xlsx!", "Creating output file Completed!", MessageBoxButtons.OK, MessageBoxIcon.Information);
//}
//inStream.Dispose();
//outStream.Dispose();
/*
* Function -2
*/
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
app.Visible = true;
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
worksheet.PageSetup.CenterHeader = lblTraining.Text;
worksheet.PageSetup.LeftHeader = label1.Text + " " + lblS_date.Text;
worksheet.PageSetup.RightHeader = label2.Text + " " + lblE_date.Text;
workbook.WebOptions.Encoding = Microsoft.Office.Core.MsoEncoding.msoEncodingUTF8;
app.StandardFont = "Myanmar3";
app.StandardFontSize = 10;
for (int i = 1; i < dgvTrainner.Columns.Count + 1; i++)
{
//worksheet.Range ["A1"].Style
worksheet.Cells[1, i] = dgvTrainner.Columns[i - 1].HeaderText.ToString();
}
for (int i = 0; i <= dgvTrainner.Rows.Count - 1; i++)
{
for (int j = 0; j < dgvTrainner.Columns.Count; j++)
{
if (dgvTrainner.Rows[i].Cells[j].Value != null)
{
worksheet.Cells[i + 2, j + 1] = dgvTrainner.Rows[i].Cells[j].Value.ToString();
}
else
{
worksheet.Cells[i + 2, j + 1] = "";
}
}
}
object misValue = System.Reflection.Missing.Value;
workbook.SaveAs(lblTraining.Text + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
workbook.Close(true, misValue, misValue);
app.Quit();
/*
* Function -1
*/
//Excel.Application xlApp;
//Excel.Workbook xlWorkBook;
//Excel.Worksheet xlWorkSheet;
//object misValue = System.Reflection.Missing.Value;
//xlApp = new Excel.Application();
//xlWorkBook = xlApp.Workbooks.Add(misValue);
//xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
////int i = 0;
////int j = 0;
//for (int i = 0; i < dgvTrainner .RowCount ; i++)
//{
// for (int j = 0; j < dgvTrainner .ColumnCount; j++)
// {
// DataGridViewCell cell = dgvTrainner[j, i];
// xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
// }
//}
//xlWorkBook.SaveAs("MES.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
//xlWorkBook.Close(true, misValue, misValue);
//xlApp.Quit();
//release_Object(xlWorkSheet);
//release_Object(xlWorkBook);
//release_Object(xlApp);
//MessageBox.Show("Excel file တည်ဆောက်ပြီးပါပြီ။","သင်တန်းဆိုင်ရာ သတင်းပေးချက်", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
