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 bool DeleteExcelSheet(string pExcelPath, string pSheetName, out string pDeleteSheet)
{
try
{
object objOpt = Missing.Value;
//打开一个Excel应用
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
if (app == null)
{
throw new Exception("打开Excel应用时发生错误！");
}
app.Visible = false;
Microsoft.Office.Interop.Excel.Workbooks wbs = app.Workbooks;
Microsoft.Office.Interop.Excel._Workbook wb = wbs.Open(pExcelPath, 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);
wb.EnableAutoRecover = false;
Microsoft.Office.Interop.Excel.Sheets shs = wb.Sheets;
Microsoft.Office.Interop.Excel._Worksheet sh = (Microsoft.Office.Interop.Excel._Worksheet)shs.get_Item(pSheetName);
app.DisplayAlerts = false;
sh.Delete();
wb.Save();
pDeleteSheet = string.Empty;
return(true);
}
catch (Exception vErr)
{
pDeleteSheet = vErr.Message;
return(false);
}
finally
{
KillProcess();
}
}
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 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);
}
}
public void exportDataExcel(DataSet dsTemp, string reportName)
{
ArrayList paramArraylist = new ArrayList();
string Excelpath = ConfigurationSettings.AppSettings["ExcelFile"] + "CarrierReports.xlsx";
string dateTime = "CarrierReports " + System.DateTime.Now.Year.ToString() + System.DateTime.Now.Month.ToString() + System.DateTime.Now.Day.ToString() + System.DateTime.Now.Hour.ToString() + System.DateTime.Now.Minute.ToString() + System.DateTime.Now.Second.ToString() + ".xlsx";
string Destpath = ConfigurationSettings.AppSettings["ExcelFile"] + dateTime;
DataSet DsExportedData = (DataSet)dsTemp;
File.Copy(Excelpath, Destpath);
Microsoft.Office.Interop.Excel.ApplicationClass XcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel._Workbook workbook = XcelApp.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Workbook workbookDummy = XcelApp.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
Microsoft.Office.Interop.Excel.Range chartrange;
#region Excel
//workbook = XcelApp.Workbooks.Open(Destpath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true); SVN
try
{
if (DsExportedData != null)
{
if (DsExportedData.Tables[0] != null)
{
#region TemplateData
worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.Sheets["TemplateData"];
//worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.ActiveSheet;
DataTable dtTemplateData = (DataTable)DsExportedData.Tables[0];
for (int j = 0; j < dtTemplateData.Rows.Count; j++)
{
for (int k = 0; k < dtTemplateData.Columns.Count; k++)
{
worksheet.Cells[j + 2, k + 1] = dtTemplateData.Rows[j].ItemArray[k].ToString();
}
}
chartrange = worksheet.get_Range("A1", "D1");
chartrange.Font.Bold = true;
chartrange.Cells.Interior.Color = System.Drawing.Color.Gainsboro.ToArgb();
chartrange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
chartrange.Borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Black);
XcelApp.Columns.AutoFit();
workbook.Save();
#endregion
}
if (DsExportedData.Tables[1] != null)
{
#region TemplateBillingData
worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.Sheets["TemplateBillingData"];
//worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.ActiveSheet;
DataTable dtTemplateBillingData = (DataTable)DsExportedData.Tables[1];
for (int j = 0; j < dtTemplateBillingData.Rows.Count; j++)
{
for (int k = 0; k < dtTemplateBillingData.Columns.Count; k++)
{
worksheet.Cells[j + 2, k + 1] = dtTemplateBillingData.Rows[j].ItemArray[k].ToString();
}
}
chartrange = worksheet.get_Range("A1", "E1");
chartrange.Font.Bold = true;
chartrange.Cells.Interior.Color = System.Drawing.Color.Gainsboro.ToArgb();
chartrange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
chartrange.Borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Black);
XcelApp.Columns.AutoFit();
workbook.RefreshAll();
workbook.Save();
#endregion
}
else
{
}
}
else
{
}
// workbook.Close(); SVN
}
catch (Exception ex)
{
// workbook.Close(); SVN
ErrorHandler.WriteError(ex.Message);
}
#endregion
}
