private void button1_Click(object sender, EventArgs e) { Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) { MessageBox.Show("Excel is not properly installed!!"); return; } Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.Cells[1, 1] = "Sheet 1 content"; xlWorkBook.SaveAs("d:\\csharp-Excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); MessageBox.Show("Excel file created , you can find the file d:\\csharp-Excel.xls"); }
public void Dispose() { try { if (mBook != null) { mBook.Close(false, MISSING, MISSING); mBook = null; } } catch {} try { if (mSheet != null) { mSheet = null; } } catch {} try { if (mExcelApp != null) { mExcelApp.Quit(); mExcelApp = null; } } catch {} }
private static object[,] GetExcelData(string tempateID) { OpenFileDialog openFileDialog = new OpenFileDialog { Filter = "Excel file(*.xlsx)|*.xlsx", Multiselect = false }; string path; if (openFileDialog.ShowDialog() == DialogResult.OK) { path = openFileDialog.FileName; } else { return(null); } Microsoft.Office.Interop.Excel.Application excel = null; Microsoft.Office.Interop.Excel.Workbook wkb = null; object[,] result = null; try { excel = new Microsoft.Office.Interop.Excel.Application(); wkb = OpenBook(excel, path); excel.DisplayAlerts = false; Excel.Range xlRange = null; Excel.Worksheet sheet = wkb.Sheets[ExcelTemplate.GetTemplate(tempateID)]; if (sheet != null) { xlRange = sheet.UsedRange; } if (xlRange != null) { result = xlRange.Value2; } excel.Quit(); return(result); } catch (Exception ex) { MessageBox.Show(ex.ToString()); if (excel != null) { excel.Quit(); } return(result); } }
private void button3_Click(object sender, EventArgs e) { dataGridView1.DataSource = null; for (int j = 0; j < dataGridView1.Rows.Count - 1; j++) { dataGridView1.Rows.RemoveAt(j); j--; while (dataGridView1.Rows.Count == 0) { continue; } } Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); Excel.Workbooks workbooks = app.Workbooks; Excel.Workbook workbook = workbooks.Open(textBox1.Text); Excel.Worksheet worksheet = workbook.ActiveSheet; try { int rcount = worksheet.UsedRange.Rows.Count; int i = 0; //Initializing Columns dataGridView1.ColumnCount = worksheet.UsedRange.Columns.Count; for (i = 1; i < rcount; i++) { //dataGridView1.Rows.Add(worksheet.Cells[i + 1, 1].Value, worksheet.Cells[i + 1, 2].Value, worksheet.Cells[i + 1, 3].Value, worksheet.Cells[i + 1, 4].Value, worksheet.Cells[i + 1, 5].Value, worksheet.Cells[i + 1, 6].Value, worksheet.Cells[i + 1, 7].Value, worksheet.Cells[i + 1, 8].Value, worksheet.Cells[i + 1, 9].Value); dataGridView1.Rows.Add(worksheet.Cells[i + 1, 1].Value, worksheet.Cells[i + 1, 2].Value, worksheet.Cells[i + 1, 3].Value, worksheet.Cells[i + 1, 4].Value, worksheet.Cells[i + 1, 5].Value); } workbook.Close(); app.Quit(); Marshal.ReleaseComObject(workbook); Marshal.ReleaseComObject(workbooks); Marshal.ReleaseComObject(worksheet); } catch (Exception ex) { MessageBox.Show(ex.Message); workbook.Close(); app.Quit(); Marshal.ReleaseComObject(workbook); Marshal.ReleaseComObject(workbooks); Marshal.ReleaseComObject(worksheet); } }
public static Excel_Table LoadFromFile(string FileName) { //Открываем файл Экселя //Создаём приложение. Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application(); //Открываем книгу. Excel.Workbook ObjWorkBook = ObjExcel.Workbooks.Open(FileName, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); //Выбираем таблицу(лист). Excel.Worksheet ObjWorkSheet; ObjWorkSheet = (Excel.Worksheet)ObjWorkBook.Sheets[1]; // Создаём новый Excel_Table объект Excel_Table ET = new Excel_Table(); var lastCell = ObjWorkSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell);// Находим последнюю ячейку. ET.Table_Width = lastCell.Column; ET.Table_Height = lastCell.Row; // Настройка прогрессбара Progress.Maximum = ET.Table_Width * ET.Table_Height + 2 * ET.Table_Height; Progress.Process = "Считывание данных из Excel"; ET.list = new string[ET.Table_Width, ET.Table_Height]; // массив значений с листа равен по размеру листу for (int i = 0; i < ET.Table_Width; i++) //по всем колонкам { for (int j = 1; j < ET.Table_Height; j++) // по всем строкам { ET.list[i, j] = ObjWorkSheet.Cells[j + 1, i + 1].Text.ToString(); //считываем текст в строку Progress.Position++; Application.DoEvents(); if (Progress.Abort) { ObjWorkBook.Close(false, Type.Missing, Type.Missing); //закрыть не сохраняя //Удаляем приложение (выходим из экселя) - а то будет висеть в процессах! ObjExcel.Quit(); return(ET); } } } ObjWorkBook.Close(false, Type.Missing, Type.Missing); //закрыть не сохраняя //Удаляем приложение (выходим из экселя) - а то будет висеть в процессах! ObjExcel.Quit(); Progress.Done = ET.Table_Width * ET.Table_Height; return(ET); }
/// <summary> /// Close all Excel workbooks without saving, removing any references to the /// excel app and perform garbage collection /// </summary> public static void TryQuitAndCleanupWithoutSaving() { if (_excelApp != null) { _excelApp.Workbooks.Cast <Workbook>().ToList().ForEach((wb) => wb.Close(false)); _excelApp.Quit(); while (Marshal.ReleaseComObject(_excelApp) > 0) { } GC.Collect(); GC.WaitForPendingFinalizers(); _excelApp = null; } }
private bool ValidarExcel() { try { Microsoft.Office.Interop.Excel.Application xlsApp = new Microsoft.Office.Interop.Excel.Application(); Workbook xlsBook; Sheets xlHojas; xlsApp.DisplayAlerts = false; xlsApp.Visible = false; string PathAr = Path.Combine(System.Windows.Forms.Application.StartupPath, @"Resources\Excel\StepthManager.xlsx"); xlsBook = xlsApp.Workbooks.Open(PathAr); xlHojas = xlsBook.Sheets; xlsBook.Close(true); xlsApp.Quit(); releaseObject(xlHojas); releaseObject(xlsBook); releaseObject(xlsApp); return(true); } catch (Exception ex) { LogError.AddExcFileTxt(ex, "frmInventario ~ ValidarExcel"); return(false); } }
public void cargaBeneficiario(string Path, List <Educafin.Models.Alumno> lis) { System.IO.FileInfo fi = new System.IO.FileInfo(Path); if (fi.Exists) { fi.Delete(); } Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook xlWorkBook; Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); int i = 2; xlWorkSheet.Cells[1, 1] = "Curp"; xlWorkSheet.Cells[1, 2] = "Matricula"; xlWorkSheet.Cells[1, 3] = "Apellido Paterno"; xlWorkSheet.Cells[1, 4] = "Apellido Materno"; xlWorkSheet.Cells[1, 5] = "Nombre(s)"; foreach (var item in lis) { xlWorkSheet.Cells[i, 1] = item.curp; xlWorkSheet.Cells[i, 2] = item.matricula; xlWorkSheet.Cells[i, 3] = item.apePat; xlWorkSheet.Cells[i, 4] = item.apeMat; xlWorkSheet.Cells[i, 5] = item.nombreTemp; i++; } xlWorkBook.SaveAs(Path); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); }
public void ExportDTToExcel(System.Data.DataTable dt) { Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); app.Visible = false; Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet; // Headers. for (int i = 0; i < dt.Columns.Count; i++) { ws.Cells[1, i + 1] = dt.Columns[i].ColumnName; } // Content. for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { ws.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString(); } } // Lots of options here. See the documentation. wb.SaveAs("d:\\tsst.xlsx"); wb.Close(); app.Quit(); }
private void button3_Click(object sender, EventArgs e) { SaveFileDialog fichero = new SaveFileDialog(); fichero.Filter = "Excel (*.xls)|*.xls"; if (fichero.ShowDialog() == DialogResult.OK) { Microsoft.Office.Interop.Excel.Application aplicacion; Microsoft.Office.Interop.Excel.Workbook libros_trabajo; Microsoft.Office.Interop.Excel.Worksheet hoja_trabajo; aplicacion = new Microsoft.Office.Interop.Excel.Application(); libros_trabajo = aplicacion.Workbooks.Add(); hoja_trabajo = (Microsoft.Office.Interop.Excel.Worksheet)libros_trabajo.Worksheets.get_Item(1); //Recorremos el DataGridView rellenando la hoja de trabajo for (int i = 0; i < dtReporteGeneral.Rows.Count - 1; i++) { for (int j = 0; j < dtReporteGeneral.Columns.Count; j++) { hoja_trabajo.Cells[i + 1, j + 1] = dtReporteGeneral.Rows[i].Cells[j].Value.ToString(); } } libros_trabajo.SaveAs(fichero.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal); libros_trabajo.Close(true); aplicacion.Quit(); } //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 (i = 0; i <= dtReporteGeneral.RowCount - 1; i++) //{ // for (j = 0; j <= dtReporteGeneral.ColumnCount - 1; j++) // { // DataGridViewCell cell = dtReporteGeneral[j, i]; // xlWorkSheet.Cells[i + 1, j + 1] = cell.Value; // } //} //xlWorkBook.SaveAs("Reporte_Historico.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); //xlWorkBook.Close(true, misValue, misValue); //xlApp.Quit(); //releaseObject(xlWorkSheet); //releaseObject(xlWorkBook); //releaseObject(xlApp); //MessageBox.Show("Excel creado en = c:\\Reporte_Historico.xls"); }
private void Generate_PDFs() { Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); object misValue = System.Reflection.Missing.Value; Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(misValue); Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.Cells[1, 1] = "AppNo"; xlWorkSheet.Cells[1, 2] = "Name"; xlWorkSheet.Cells[1, 3] = "Department"; xlWorkSheet.Cells[1, 4] = "Category"; xlWorkSheet.Cells[1, 5] = "Aggregate"; studentsSelectedList = studentsSelectedList.OrderByDescending(o => o.aggregate).ToList(); for (int i = 0; i < studentsSelectedList.Count(); i++) { //Setting width of the Excel Coloumns. var studentinfo = studentsSelectedList[i]; xlWorkSheet.Columns[2].ColumnWidth = 18; xlWorkSheet.Columns[3].ColumnWidth = 21; xlWorkSheet.Columns[4].ColumnWidth = 18; xlWorkSheet.Columns[5].ColumnWidth = 18; //Setting information in Excel Workbook xlWorkSheet.Cells[i + 2, 1] = studentinfo.appRefNum; xlWorkSheet.Cells[i + 2, 2] = studentinfo.name; xlWorkSheet.Cells[i + 2, 3] = studentinfo.preferenceList[0]; xlWorkSheet.Cells[i + 2, 4] = studentinfo.category; xlWorkSheet.Cells[i + 2, 5] = studentinfo.aggregate; } //Saving as PDF xlWorkBook.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, "C:\\Users\\hassa\\Desktop\\MeritListGenerator\\MeritListGenerator\\MeritList.pdf"); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); }
private void reSaveFile(string filePath) { object paramMissing = Type.Missing; var app = new Microsoft.Office.Interop.Excel.Application(); app.Caption = System.Guid.NewGuid().ToString().ToUpper(); string ver = app.Version; var wb = app.Workbooks.Open(filePath); app.DisplayAlerts = false; wb.SaveAs(filePath, FileFormat: Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook); wb.Close(false, paramMissing, paramMissing); app.Quit(); Config.EnsureProcessKilled(IntPtr.Zero, app.Caption); Marshal.ReleaseComObject(wb); Marshal.FinalReleaseComObject(wb); wb = null; Marshal.ReleaseComObject(app); Marshal.FinalReleaseComObject(app); app = null; GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); }
public void cargaFormatoEntregasAnteriores(string Path) { System.IO.FileInfo fi = new System.IO.FileInfo(Path); if (fi.Exists) { fi.Delete(); } Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook xlWorkBook; Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.Cells[1, 1] = "CURP"; xlWorkSheet.Cells[1, 2] = "CCT"; xlWorkSheet.Cells[1, 3] = "Matricula"; xlWorkSheet.Cells[1, 4] = "No. Serie"; xlWorkBook.SaveAs(Path); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); }
//导出Execl明细按钮点击事件 private void button1_Click(object sender, EventArgs e) { if (tower_list.Rows.Count <= 0) { MessageBox.Show("该表中没有需要导出的数据!", "提示"); return; } string fileName = "楼盘信息表"; string saveFileName = ""; SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.DefaultExt = "xlsx"; saveDialog.Filter = "Excel文件|*.xlsx"; saveDialog.FileName = fileName; saveDialog.ShowDialog(); saveFileName = saveDialog.FileName; if (saveFileName.IndexOf(":") < 0) { return; //被点了取消 } Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) { MessageBox.Show("无法创建Excel对象,您的电脑可能未安装Excel"); return; } Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks; Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 //写入标题 for (int i = 0; i < tower_list.ColumnCount; i++) { worksheet.Cells[1, i + 1] = tower_list.Columns[i].HeaderText; } //写入数值 for (int r = 0; r < tower_list.Rows.Count; r++) { for (int i = 0; i < tower_list.ColumnCount; i++) { worksheet.Cells[r + 2, i + 1] = tower_list.Rows[r].Cells[i].Value; } System.Windows.Forms.Application.DoEvents(); } worksheet.Columns.EntireColumn.AutoFit();//列宽自适应 MessageBox.Show(fileName + "资料保存成功", "提示", MessageBoxButtons.OK); if (saveFileName != "") { try { workbook.Saved = true; workbook.SaveCopyAs(saveFileName); } catch (Exception ex)//异常处理 { MessageBox.Show("导出文件时出错,文件可能正被打开!\n" + ex.Message); } } xlApp.Quit(); GC.Collect();//强行销毁 }
public void WriteResultToExcelAttendance() { this.xlApp = new Microsoft.Office.Interop.Excel.Application(); if (this.xlApp == null) { MessageBox.Show("Excel is not properly installed!!"); } this.xlApp.Visible = false; Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(1); Excel.Sheets xlWorksheet = xlWorkBook.Worksheets; var xlNewSheet = (Excel.Worksheet)xlWorksheet.Add(xlWorksheet[1]); int row = 1; foreach (Attendance student in attendanceList) { xlNewSheet.Cells[row, 1] = student.StudentNo; xlNewSheet.Cells[row, 2] = student.Name; xlNewSheet.Cells[row, 3] = student.Visa; xlNewSheet.Cells[row, 4] = student.NewWarning; xlNewSheet.Cells[row, 5] = student.CourseCode; xlNewSheet.Cells[row, 6] = student.StartDate; xlNewSheet.Cells[row, 7] = student.EndDate; xlNewSheet.Cells[row, 8] = student.CurrentAttendace; xlNewSheet.Cells[row, 9] = student.OverallAttendace; //foreach (Payment payment in payments) //{ // xlNewSheet.Cells[row, 1] = student.StNo; // if (payment.DueDate < System.DateTime.Today) // { // Excel.Range cell = xlNewSheet.Cells[row, 7]; // cell.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); // } // xlNewSheet.Cells[row, 7] = payment.DueDate; // xlNewSheet.Cells[row, 8] = payment.PaymentAmount; // xlNewSheet.Cells[row, 9] = payment.Note; row++; //} } //Excel.Range range = xlNewSheet.UsedRange; //range.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic); String filename = xlApp.GetSaveAsFilename("result.xls", "Excel files (*.xls), *.xls"); if (string.IsNullOrEmpty(filename)) { filename = "result.xls"; } xlWorkBook.SaveAs(filename, Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); xlWorkBook.Close(true, Type.Missing, Type.Missing); xlApp.Quit(); MessageBox.Show("Done!!!!"); }
private void button2_Click(object sender, EventArgs e) { Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) { MessageBox.Show("Excel is not properly installed!!"); return; } Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.Cells[1, 3] = "Weather forecast for the week "; xlWorkSheet.Cells[2, 1] = listBox1.Items[0].ToString(); xlWorkSheet.Cells[2, 7] = listBox1.Items[6].ToString(); xlWorkSheet.Cells[3, 3] = label1.Text; xlWorkBook.SaveAs("d:\\csharp-Excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); MessageBox.Show("Excel file created , you can find the file d:\\csharp-Excel.xls"); }
private void button1_Click(object sender, EventArgs e) { try { saveFileDialog1.Title = "Save as Excel File"; saveFileDialog1.FileName = ""; saveFileDialog1.Filter = "Excel Files(2003)|*.xls|Excel Files(2007)|*.xls"; if (saveFileDialog1.ShowDialog() != DialogResult.Cancel) { Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); ExcelApp.Application.Workbooks.Add(Type.Missing); ExcelApp.Columns.ColumnWidth = 20; for (int i = 1; i < dataGridView1.Columns.Count + 1; i++) { ExcelApp.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++) { ExcelApp.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString(); } } ExcelApp.ActiveWorkbook.SaveCopyAs(saveFileDialog1.FileName.ToString()); ExcelApp.ActiveWorkbook.Saved = true; ExcelApp.Quit(); } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
static void Main(string[] args) { System.IO.StreamReader txtfile = new System.IO.StreamReader("D:\\Learning\\TestDLL\\Dllpath.txt"); int counter = txtfile.ReadLine().Count(); Console.WriteLine(counter); Console.ReadLine(); Excel.Application xlapp = new Microsoft.Office.Interop.Excel.Application(); if (xlapp== null) { Console.WriteLine("Excel is not installed"); return; } Excel.Workbook xlworkbook; Excel.Worksheet xlworksheet; object misvalue = System.Reflection.Missing.Value; xlworkbook = xlapp.Workbooks.Add(misvalue); String filepath = @"D:\Learning\TestDLL\pdfshell.dll"; FileVersionInfo myFileVersionInfo = FileVersionInfo.GetVersionInfo(filepath); xlworksheet=xlworkbook.Worksheets.get_Item(1); xlworksheet.Name = "DLLProperties"; xlworksheet.Cells[1, 1] ="NameofFile" ; xlworksheet.Cells[1, 2] = "ProductVersion"; xlworksheet.Cells.Font.Size = 25; xlworkbook.SaveAs("D:\\Learning\\demoexcel3.xls", Excel.XlFileFormat.xlWorkbookNormal, misvalue, misvalue, misvalue, misvalue, Excel.XlSaveAsAccessMode.xlExclusive, misvalue, misvalue, misvalue, misvalue, misvalue); xlworkbook.Close(true,misvalue,misvalue); xlapp.Quit(); }
private void ExportarExcel(DataGridView grd) { SaveFileDialog fichero = new SaveFileDialog(); fichero.Filter = "Excel (*.xls)|*.xls"; if (fichero.ShowDialog() == DialogResult.OK) { Microsoft.Office.Interop.Excel.Application aplicacion; Microsoft.Office.Interop.Excel.Workbook libros_trabajo; Microsoft.Office.Interop.Excel.Worksheet hoja_trabajo; aplicacion = new Microsoft.Office.Interop.Excel.Application(); libros_trabajo = aplicacion.Workbooks.Add(); hoja_trabajo = (Microsoft.Office.Interop.Excel.Worksheet)libros_trabajo.Worksheets.get_Item(1); //Recorremos el DataGridView rellenando la hoja de trabajo for (int i = 0; i < grd.Rows.Count - 1; i++) { for (int j = 0; j < grd.Columns.Count; j++) { hoja_trabajo.Cells[i + 1, j + 1] = grd.Rows[i].Cells[j].Value.ToString(); } } libros_trabajo.SaveAs(fichero.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal); libros_trabajo.Close(true); aplicacion.Quit(); } }
public static void Create(UserPasswordInfo userinfo) { //TODO exceli burada oluştur Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.Cells[1, 1] = "Application Name"; xlWorkSheet.Cells[1, 2] = "Email"; xlWorkSheet.Cells[1, 3] = "Username"; xlWorkSheet.Cells[2, 1] = userinfo.Application; xlWorkSheet.Cells[2, 3] = userinfo.UserName; xlWorkSheet.Cells[2, 2] = userinfo.Email; xlApp.DisplayAlerts = false; xlWorkBook.SaveAs("C:\\Users\\BaranOzsarac\\Documents\\EmailConfirmed_20191226_1340.xlsx", Excel.XlFileFormat.xlOpenXMLWorkbook, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); Marshal.ReleaseComObject(xlWorkSheet); Marshal.ReleaseComObject(xlWorkBook); Marshal.ReleaseComObject(xlApp); }
public void ExcelWrite(string path, string worksheet, int Row, int Column, List <object> Values) { try { List <string> output = new List <string>(); var excelApp = new Microsoft.Office.Interop.Excel.Application(); //Dont show Excel when open excelApp.Visible = false; Microsoft.Office.Interop.Excel.Workbooks books = excelApp.Workbooks; //Open Excel file by Path provided by the user Microsoft.Office.Interop.Excel.Workbook sheet = books.Open(path); //Select worksheet by the name provided by the user Microsoft.Office.Interop.Excel.Worksheet indsheet = sheet.Sheets[worksheet]; Microsoft.Office.Interop.Excel.Range activecell = indsheet.Cells[Row, Column]; for (int i = 0; i < Values.Count; i += 1) { indsheet.Cells[Row + i, Column] = Values[i].ToString(); } sheet.Close(true); excelApp.Quit(); } catch { } }
private void BtnToExcel_Click(object sender, RoutedEventArgs e) { Microsoft.Office.Interop.Excel.Application application = new Microsoft.Office.Interop.Excel.Application(); application.Workbooks.Add(); Microsoft.Office.Interop.Excel.Worksheet worksheet = application.ActiveSheet; worksheet.Cells[1, 1] = "Id_Worker"; worksheet.Cells[1, 2] = "FIO"; worksheet.Cells[1, 3] = "DateOfBirth"; worksheet.Cells[1, 4] = "Salary"; worksheet.Cells[1, 5] = "Position"; var cellRange = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, 5]]; var allWorkers = AppData.Context.Workers.ToList(); for (int i = 0; i < allWorkers.Count(); i++) { var currentWorkers = allWorkers[i]; worksheet.Cells[i + 2, 1] = currentWorkers.Id_Worker; worksheet.Cells[i + 2, 2] = currentWorkers.FIO; worksheet.Cells[i + 2, 3] = currentWorkers.DateOfBirth.ToString("dd:MM:yyyy HH:mm"); worksheet.Cells[i + 2, 4] = currentWorkers.Salary; worksheet.Cells[i + 2, 5] = currentWorkers.WorkersPosition.PositionName; } checkExportDirectory(); worksheet.SaveAs($"{AppDomain.CurrentDomain.BaseDirectory}Export" + $"\\Export_Workers_XLSX_{DateTime.Now.ToString("dd_MM_yyyy_HH_mm_ss")}.xlsx"); application.Quit(); MessageBox.Show("Данные выгружены"); }
private void Button_Export_Click(object sender, EventArgs e) { try { Microsoft.Office.Interop.Excel.Application Exl = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook wb; XlReferenceStyle RefStyle = Exl.ReferenceStyle; Exl.Visible = false; wb = Exl.Workbooks.Add(Type.Missing); Worksheet ws = wb.Worksheets.get_Item(1) as Worksheet; for (int j = 0; j < dataGrid_Glossary.Columns.Count; ++j) { for (int i = 0; i < dataGrid_Glossary.Rows.Count; ++i) { object Val = dataGrid_Glossary.Rows[i].Cells[j].Value; if (Val != null) { (ws.Cells[i + 2, j + 1] as Range).Value2 = Val.ToString(); } } } ws.Columns.EntireColumn.AutoFit(); Exl.ReferenceStyle = RefStyle; Exl.Application.ActiveWorkbook.SaveAs("glossary.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); Exl.Quit(); } catch (ExternalException a) { Debug.WriteLine(a.Message); } }
public void Write(string path, object data) { Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) { throw new Exception("PC chưa cài excel!"); } Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.Cells[1, 1] = "ID"; xlWorkSheet.Cells[1, 2] = "Name"; xlWorkSheet.Cells[2, 1] = "1"; xlWorkSheet.Cells[2, 2] = "One"; xlWorkSheet.Cells[3, 1] = "2"; xlWorkSheet.Cells[3, 2] = "Two"; xlWorkBook.SaveAs(path, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); Marshal.ReleaseComObject(xlWorkSheet); Marshal.ReleaseComObject(xlWorkBook); Marshal.ReleaseComObject(xlApp); }
public CreateExcel(DataGridView dgv, string excelPath) { // 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; // see the excel sheet behind the program app.Visible = true; // 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 for (int i = 1; i < dgv.Columns.Count + 1; i++) { worksheet.Cells[1, i] = dgv.Columns[i - 1].HeaderText; } // storing Each row and column value to excel sheet for (int i = 0; i < dgv.Rows.Count - 1; i++) { for (int j = 0; j < dgv.Columns.Count; j++) { worksheet.Cells[i + 2, j + 1] = dgv.Rows[i].Cells[j].Value.ToString(); } } // save the application workbook.SaveAs(excelPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing); // Exit from the application app.Quit(); }
private void Button2_Click(object sender, EventArgs e) { Int64 todoist_project_id = 2215010764; Int64 wunderlist_project_id = 401500612; Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); Excel.Workbook xlWorkbook = app.Workbooks.Open(@"C:\freigabe\aufgabenliste.xlsx"); Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1]; Excel.Range xlRange = xlWorksheet.UsedRange; for (int i = 2; i <= 4; i++) { String titel = xlRange.Cells[i, 1].Value2.ToString() + " | " + DateTime.Now.ToString("MM-dd-yyyy-HH-mm-ss"); double datum = double.Parse(xlRange.Cells[i, 2].Value2.ToString()); var dateTimeWunderlist = DateTime.FromOADate(datum).ToString("yyyy-MM-dd:hh:mm:ss"); var dateTimeTodoist = DateTime.FromOADate(datum).ToString("yyyy-MM-dd"); // MessageBox.Show(titel + " " + dateTime); if (checkBoxWunderlist.Checked) { String resultWunderlistRequest = Wunderlist.CreateTask(wunderlist_project_id, titel, dateTimeWunderlist); } if (checkBoxTodoist.Checked) { String resultTodoistRequest = Todoist.CreateTask(todoist_project_id, titel, dateTimeTodoist); } } GC.Collect(); GC.WaitForPendingFinalizers(); Marshal.ReleaseComObject(xlRange); Marshal.ReleaseComObject(xlWorksheet); xlWorkbook.Close(); Marshal.ReleaseComObject(xlWorkbook); app.Quit(); Marshal.ReleaseComObject(app); }
public List <string> ExcelInfo(string path) { List <string> output = new List <string>(); try { var excelApp = new Microsoft.Office.Interop.Excel.Application(); //Dont show Excel when open excelApp.Visible = false; Microsoft.Office.Interop.Excel.Workbooks books = excelApp.Workbooks; //Open Excel file by Path provided by the user Microsoft.Office.Interop.Excel.Workbook sheet = books.Open(path); foreach (Microsoft.Office.Interop.Excel.Worksheet worksheet in sheet.Worksheets) { output.Add(worksheet.Name.ToString()); } // accessing the desired worksheet in the dictionary sheet.Close(true); excelApp.Quit(); } catch { //To be implemented } return(output); }
private void 导出到ExcelToolStripMenuItem_Click(object sender, EventArgs e) { string fileName; saveFileDialog1.Filter = "Excel 2003 *.xls|*.xls|Excel 2007 *.xlsx|*.xlsx"; if (saveFileDialog1.ShowDialog() == DialogResult.OK) { fileName = saveFileDialog1.FileName; Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); app.Visible = false; Workbook wBook = app.Workbooks.Add(true); Worksheet wSheet = wBook.Worksheets[1] as Worksheet; DsToWsheet(ds, wSheet); //设置禁止弹出保存和覆盖的询问提示框 app.DisplayAlerts = false; app.AlertBeforeOverwriting = false; wBook.SaveAs(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); app.Quit(); app = null; statusBarShow("已成功导出到" + fileName); } }
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"); } }
/// <summary> /// Кнопка создания XLS и сохранения результатов /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void Button3_Click(object sender, EventArgs e) { Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) { MessageBox.Show("Excel не установлен!"); return; } Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.Cells[1, 1] = "Кол-во студентов"; xlWorkSheet.Cells[1, 2] = "Кол-во отличников"; xlWorkSheet.Cells[1, 3] = "Процент отличников"; xlWorkSheet.Cells[2, 1] = stCap.ToString(); xlWorkSheet.Cells[2, 2] = otlCap.ToString(); xlWorkSheet.Cells[2, 3] = perc.ToString(); xlWorkBook.SaveAs("marks.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); Marshal.ReleaseComObject(xlWorkSheet); Marshal.ReleaseComObject(xlWorkBook); MessageBox.Show("Файл marks.xls в папке проекта успешно создан!"); }
private static void ExportToExcel(DataGridView dtgvExport, string modelNumber) { // 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; try { worksheet = workbook.ActiveSheet; worksheet.Name = modelNumber + "_Result"; int cellRowIndex = 1; int cellColumnIndex = 1; //Loop through each row and read value from each column. for (int i = 0; i < dtgvExport.Rows.Count - 1; i++) { for (int j = 0; j < dtgvExport.Columns.Count; j++) { // Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check. if (cellRowIndex == 1) { worksheet.Cells[cellRowIndex, cellColumnIndex] = dtgvExport.Columns[j].HeaderText; } else { worksheet.Cells[cellRowIndex, cellColumnIndex] = dtgvExport.Rows[i].Cells[j].Value.ToString(); } cellColumnIndex++; } cellColumnIndex = 1; cellRowIndex++; } //Getting the location and file name of the excel to save from user. SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*"; saveDialog.FilterIndex = 2; if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK) { workbook.SaveAs(saveDialog.FileName); MessageBox.Show("Export Successful"); } } catch (System.Exception ex) { MessageBox.Show(ex.Message); } finally { excel.Quit(); workbook = null; excel = null; } }
private void readExcel_Click(object sender, EventArgs e) { RESET_Click(sender, e); readExcel.BackColor = System.Drawing.Color.Chartreuse; Microsoft.Office.Interop.Excel.Application xlApp; Microsoft.Office.Interop.Excel.Workbook xlWorkBook; Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Microsoft.Office.Interop.Excel.Application(); xlWorkBook = xlApp.Workbooks.Open("d:\\csharp-Excel.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); Range aRange = xlWorkSheet.get_Range("A1", "A2"); if (aRange == null) { MessageBox.Show("Could not get a range. Check to be sure you have the correct versions of the office DLLs."); } else { MessageBox.Show(xlWorkSheet.get_Range("A1", "A1").Value2.ToString()); MessageBox.Show(xlWorkSheet.get_Range("A2", "A2").Value2.ToString()); } xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); }
/// <summary> /// 表导出Excel /// </summary> /// <param name="modeFilePath">模板路径</param> /// <param name="saveFilePath">保存文件路径</param> /// <param name="tableName">导出数据名称</param> public void ListToExcel(string modeFilePath, string saveFilePath, string tableName) { //需要添加 Microsoft.Office.Interop.Excel引用 Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); if (app == null) { Console.Write("服务器上缺少Excel组件,需要安装Office软件。"); } app.Visible = false; app.UserControl = true; Excel.Workbooks workbooks = app.Workbooks; //加载模板 Excel._Workbook workbook = workbooks.Add(modeFilePath); Excel.Sheets sheets = workbook.Sheets; try { switch (tableName) { case "SingleInspection": //SettlementObservationWriteData(workbook, sheets); SingleInspectionWriteData(workbook, sheets,saveFilePath); break; case "LoftingScore": LoftingScoreWriteData(workbook, sheets); break; //......... } //保存生成的Excel文件。 //Missing 在System.Reflection命名空间下。 workbook.SaveAs(saveFilePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); //按顺序释放资源。 eb.NAR(sheets); eb.NAR(workbook); eb.NAR(workbooks); app.Quit(); eb.NAR(app); eb.KillProcess("EXCEL");//杀死excel进程 switch(tableName) { case "SingleInspection": ef.Save_Excel(saveFilePath, "施工测量放样报验单"); break; } ef.Get_Excel(14, @"D:\123.xls"); } catch (Exception ex) { MessageBox.Show("导出异常" + ex.Message, "导出异常", MessageBoxButtons.OK, MessageBoxIcon.Warning); } finally { eb.KillProcess("EXCEL");//杀死excel进程 } }
public void Read(string filename) { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook wb = excel.Workbooks.Open(filename); // Get worksheet names foreach (Microsoft.Office.Interop.Excel.Worksheet sh in wb.Worksheets) Debug.WriteLine(sh.Name); // Get values from sheets SH1 and SH3 (in my file) //object val1 = wb.Sheets["SH1"].Cells[1, "A"].Value2; //object val3 = wb.Sheets["SH3"].Cells[1, "A"].Value2; //Debug.WriteLine("{0} / {1}", val1, val3); wb.Close(); excel.Quit(); }
/// <summary> /// "Export to Excel" button, uses textbox to left to determine fileName /// </summary> private void btnCreateExcelFile_Click(object sender, EventArgs e) { try { Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); //Content Type ExcelApp.Application.Workbooks.Add(Type.Missing); ExcelApp.Columns.ColumnWidth = 30; Microsoft.Office.Interop.Excel.Workbook wb = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Worksheet wsContentType = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet; // storing header part in Excel for (int i = 1; i < grdReport.Columns.Count + 1; i++) { wsContentType.Cells[1, i] = grdReport.Columns[i - 1].HeaderText; } // storing Each row and column value to excel sheet for (int i = 0; i < grdReport.Rows.Count - 1; i++) { for (int j = 0; j < grdReport.Columns.Count; j++) { wsContentType.Cells[i + 2, j + 1] = grdReport.Rows[i].Cells[j].Value.ToString(); } } if (saveContentType.ShowDialog() == System.Windows.Forms.DialogResult.OK) { ExcelApp.ActiveWorkbook.SaveCopyAs(saveContentType.FileName); ExcelApp.ActiveWorkbook.Saved = true; } ExcelApp.Quit(); } catch (Exception exp) { LogError(exp); MessageBox.Show("Failed to create Excel file"); } }
public void Convert(String metricsFolder) { metrics_files = Directory.GetFiles(metricsFolder); foreach (String file in metrics_files) { string[] nameArray = file.Split('\\'); //get an array of all elements split at \\ name = nameArray[nameArray.Length - 1]; //return the last element of the array which shoudl always be the file name string path = metricsFolder + "\\backups\\"; //check to make sure backup directory exists, otherwise create it if (!Directory.Exists(path)) Directory.CreateDirectory(path); var app = new Microsoft.Office.Interop.Excel.Application(); var wb = app.Workbooks.Open(file); wb.SaveAs(Filename: file + "x", FileFormat: Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook); wb.Close(); app.Quit(); path += name;//add the file name to the backup path so the move can be completed File.Move(file, path); } }
public void createPortfolioXlFile(string folderPath) { if (!System.IO.Directory.Exists(folderPath)) { throw new DirectoryNotFoundException(); } Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) { MessageBox.Show("Excel is not properly installed!!"); return; } object misValue = System.Reflection.Missing.Value; Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(misValue); Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); int count = 1; foreach (string colName in columnNames) { xlWorkSheet.Cells[1, count++] = colName; } Excel.Range row1 = xlWorkSheet.UsedRange; row1.Columns.AutoFit(); try { xlWorkBook.SaveAs(path); xlWorkBook.Close(false, misValue, misValue); xlApp.Quit(); } finally { Utility.releaseObject(xlWorkSheet); Utility.releaseObject(xlWorkBook); Utility.releaseObject(xlApp); } }
private void drawGraph() { Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); try { xlWorkBook = xlApp.Workbooks.Open("C:\\Users\\DELL\\Desktop\\ReadFromSerial_DrawGraphic\\csharp-Excel.xls", 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); //Get all the sheets in the workbook while (thread1.IsAlive) { //son satır bulunuyor excel dosyasındaki Excel.Range last = xlWorkSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing); Excel.Range range = xlWorkSheet.get_Range("A1", last); int lastUsedRow = last.Row; int lastUsedColumn = last.Column; string ReceiveData = myport.ReadLine(); // comdan degeri okuyuruz // alınan degerdeki stringleri temizleyerek sadece double değeri yakalıyor string[] HeatingData = ReceiveData.Split(':'); string[] HeatingData2 = HeatingData[1].Split('D'); var result = HeatingData2[0]; double heating = Convert.ToDouble(result); theTime = DateTime.Now; // anlik olarak zamani ogreniyoruz! string zaman = theTime.ToString("yyyy/MM/dd HH:mm:ss"); Thread.Sleep(1000); // ilk threadi anlik olarak durduruyor ve Invoke ile GUI threadini ulasip cizdiriyor! this.Invoke((MethodInvoker)delegate { chart1.Series["Series1"].Points.AddY(result); // excel dosyasındaki son yazılan satırdan bir sonraki satıra sıcaklığı yazdırıyor xlWorkSheet.Cells[lastUsedRow+1, 2] = (heating / 100); xlWorkSheet.Cells[lastUsedRow + 1, 1] = zaman; }); } } catch { // MessageBox.Show("Dosya bulunamadı"); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.Cells[1, 1] = "Zaman"; xlWorkSheet.Cells[1, 2] = "Sıcaklık Celcius"; xlWorkBook.SaveAs("C:\\Users\\DELL\\Desktop\\ReadFromSerial_DrawGraphic\\csharp-Excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); MessageBox.Show("Dosya oluşturuldu , proje klasörünüzde bulunmaktadır"); } }
/// <summary> /// Exporta las tablas a un documento de Excel /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void Exportar_Excel(object sender, RoutedEventArgs e) { this.Cursor = Cursors.Wait; //Fechas en el rango string fechaInicialRango = dateInicio.Text.Replace("/", "-"); string fechaFinalRango = dateFinal.Text.Replace("/", "-"); //Documento de Excel Excel.Application xlApp; Excel.Workbook xlWorkBook; object misValue = System.Reflection.Missing.Value; xlApp = new Microsoft.Office.Interop.Excel.Application(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkBook.Worksheets.Add(); xlWorkBook.Worksheets.Add(); //TABLA DE ENTREGAS Excel.Worksheet xlWorkSheet; xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.Name = "Entregas"; for (int i = 0; i <= dataGridEntregas.Items.Count; i++) { for (int j = 0; j <= dataGridEntregas.Columns.Count - 1; j++) { //Se ponen los encabezados if (i == 0) { switch (j + 1) { case 1: xlWorkSheet.Cells[i + 1, j + 1] = "FOLIO"; break; case 2: xlWorkSheet.Cells[i + 1, j + 1] = "COSTO"; break; case 3: xlWorkSheet.Cells[i + 1, j + 1] = "ABONO"; break; case 4: xlWorkSheet.Cells[i + 1, j + 1] = "REALIZÓ"; break; } } //Se pone la información else { Entrada miEntrada = (Entrada)dataGridEntregas.Items[i - 1]; switch (j + 1) { case 1: xlWorkSheet.Cells[i + 1, j + 1] = miEntrada.folioNota.ToString(); break; case 2: xlWorkSheet.Cells[i + 1, j + 1] = miEntrada.total.ToString(CultureInfo.InvariantCulture); break; case 3: xlWorkSheet.Cells[i + 1, j + 1] = miEntrada.anticipo.ToString(CultureInfo.InvariantCulture); break; case 4: xlWorkSheet.Cells[i + 1, j + 1] = miEntrada.hechaPor; break; } } } } xlWorkSheet.Cells[dataGridEntregas.Items.Count + 2, 3] = txtSubtotalEntregas.Text.Substring(11); //TABLA DE SALIDAS Excel.Worksheet xlWorkSheet2; xlWorkSheet2 = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(2); xlWorkSheet2.Name = "Salidas"; for (int i = 0; i <= dataGridSalidas.Items.Count; i++) { for (int j = 0; j <= dataGridSalidas.Columns.Count - 1; j++) { //Se ponen los encabezados if (i == 0) { switch (j + 1) { case 1: xlWorkSheet2.Cells[i + 1, j + 1] = "DESCRIPCIÓN"; break; case 2: xlWorkSheet2.Cells[i + 1, j + 1] = "COSTO"; break; } } //Se pone la información else { Salida miSalida = (Salida)dataGridSalidas.Items[i - 1]; switch (j + 1) { case 1: xlWorkSheet2.Cells[i + 1, j + 1] = miSalida.descripcion; break; case 2: xlWorkSheet2.Cells[i + 1, j + 1] = miSalida.costo.ToString(CultureInfo.InvariantCulture); break; } } } } xlWorkSheet2.Cells[dataGridSalidas.Items.Count + 2, 2] = txtSubtotalSalidas.Text.Substring(11); //TOTAL Excel.Worksheet xlWorkSheet4; xlWorkSheet4 = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(3); xlWorkSheet4.Name = "BALANCE"; xlWorkSheet4.Cells[1, 1] = "BALANCE"; xlWorkSheet4.Cells[1, 2] = txtBalance.Text; xlWorkSheet4.Cells[3, 1] = "Consultado el en rango"; xlWorkSheet4.Cells[3, 2] = dateInicio.Text; xlWorkSheet4.Cells[3, 3] = dateFinal.Text; this.Cursor = Cursors.Arrow; System.Windows.Forms.SaveFileDialog saveDlg = new System.Windows.Forms.SaveFileDialog(); saveDlg.FileName = "Balance "+fechaInicialRango+" "+fechaFinalRango+".xlsx"; saveDlg.InitialDirectory = @"C:\"; saveDlg.Filter = "Libros de Excel (*.xlsx)|*.xlsx"; saveDlg.FilterIndex = 0; saveDlg.RestoreDirectory = true; saveDlg.Title = "Indique dónde se guardará su libro de Excel | La Modistería"; if (saveDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) { string path = saveDlg.FileName; xlWorkBook.SaveCopyAs(path); xlWorkBook.Saved = true; xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); } MessageBox.Show("¡Su archivo de Excel se ha exportado exitosamente!"); }
public void Template(DataTable dt) { string serverpath = this.MapPath("."); string date = System.DateTime.Now.ToString("ddMMyyyy_hhmmssm"); string newfile = @"" + serverpath + "\\ExcelReports\\BreakEven_Report_" + date + ".xls"; string filename = "BreakEven_Report_" + date + ".xls"; String ExcelPath = System.Configuration.ConfigurationManager.AppSettings["ExcelPath"].ToString(); string newpath = ExcelPath + "BreakEven_Report_" + date + ".xls"; //string newpath = "\\Reports\\ExcelReports\\BreakEven_Report_" + date + ".xls"; lnkexcel.HRef = newpath; if (File.Exists(newfile) == false) { File.Copy(@"" + serverpath + "\\ExcelTemplates\\BreakEven_Report_Template.xls", newfile); Microsoft.Office.Interop.Excel.Application xlApp = null; Excel.Workbook xlWorkbook = null; Excel.Sheets xlSheets = null; Excel.Worksheet xlNewSheet = null; try { xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) return; xlWorkbook = xlApp.Workbooks.Open(newfile, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); xlSheets = xlWorkbook.Sheets as Excel.Sheets; xlNewSheet = (Excel.Worksheet)xlSheets[2]; int rowCount = 1; foreach (DataRow dr in dt.Rows) { rowCount += 1; for (int i = 1; i < dt.Columns.Count + 1; i++) { xlNewSheet.Cells[rowCount, i] = dr[i - 1].ToString(); } } xlNewSheet = (Excel.Worksheet)xlSheets[1]; hfBevnval.Value = string.IsNullOrEmpty(hfBevnval.Value) ? "0.0" : hfBevnval.Value; xlNewSheet.Cells[14, 13] = hfBevnval.Value + "%"; //Excel.Worksheet excelc = null; //excelc=(Excel.Worksheet)xlSheets[2]; //excelc.Cells.Value2("M14", "M14"); //xcel.Range oRange; // Resize the columns //oRange = xlNewSheet.get_Range(xlNewSheet.Cells[1, 1], // xlNewSheet.Cells[rowCount, dt.Columns.Count]); //oRange.EntireColumn.AutoFit(); objcf.HideDBData_XLSheets(xlSheets, "2"); xlWorkbook.Save(); xlWorkbook.Close(Type.Missing, Type.Missing, Type.Missing); xlApp.Quit(); foreach (System.Diagnostics.Process proc in System.Diagnostics.Process.GetProcessesByName("EXCEL")) { if (proc.MainWindowTitle.ToString() == "") { //proc.Kill(); } } //Response.Redirect(newfilepath); string fName = newfile; //DownLoadFileFromServer(newpath); } finally { xlApp = null; } System.Web.UI.ScriptManager.RegisterStartupScript(this, this.GetType(), "", "anc();", true); } }
private void btnSendToExcel_Click(object sender, EventArgs e) { Excel.Application excel = null; try { //DataGridView沒有資料就不執行 if (this.dgvReferOrderAll.Rows.Count <= 1) { MessageBox.Show("沒有可滙出的資料!", "訊息", MessageBoxButtons.OK, MessageBoxIcon.Asterisk); return; } string Date = DateTime.Now.ToString("yyyy-MM-dd"); //設定滙出後的存檔路徑(儲存在桌面) string SaveFilePath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + @"\BentoReport_" + Date + ".xls"; //new 出一個Excel excel = new Microsoft.Office.Interop.Excel.Application(); //看的到Excel在工作 excel.Visible = false; //新增加一工作簿 excel.Application.Workbooks.Add(true); PGB pgb = new PGB(); pgb.progressBar1.Minimum = 0; pgb.progressBar1.Maximum = dgvReferOrderAll.Rows.Count - 1; pgb.progressBar1.Step = 1; pgb.progressBar1.Value = 0; pgb.progressBar1.ForeColor = Color.Pink; pgb.progressBar1.Style = ProgressBarStyle.Continuous; pgb.Show(); //寫入欄位名稱 for (int i = 0; i < dgvReferOrderAll.Columns.Count; i++) { excel.Cells[1, i + 1] = dgvReferOrderAll.Columns[i].HeaderText; } //把DataGridView資料寫到Excel for (int i = 0; i < dgvReferOrderAll.Rows.Count - 1; i++) { pgb.progressBar1.Value++; for (int j = 0; j < dgvReferOrderAll.Columns.Count; j++) { if (dgvReferOrderAll[j, i].ValueType == typeof(string)) { excel.Cells[i + 2, j + 1] = "'" + dgvReferOrderAll[j, i].Value.ToString(); } else { excel.Cells[i + 2, j + 1] = dgvReferOrderAll[j, i].Value.ToString(); } } //設定欄位靠右 excel.get_Range("A" + (i + 2)).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign. xlHAlignRight; excel.get_Range("C" + (i + 2)).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign. xlHAlignRight; //設定欄位顏色 excel.get_Range("A" + (i + 2)).Interior.Color = Color.Pink; excel.get_Range("B" + (i + 2)).Interior.Color = Color.Pink; excel.get_Range("C" + (i + 2)).Interior.Color = Color.Pink; excel.get_Range("D" + (i + 2)).Interior.Color = Color.Pink; //設定欄位框線 excel.get_Range("A" + (i + 2)).Borders.LineStyle = 1; excel.get_Range("B" + (i + 2)).Borders.LineStyle = 1; excel.get_Range("C" + (i + 2)).Borders.LineStyle = 1; excel.get_Range("D" + (i + 2)).Borders.LineStyle = 1; } /* 先將DataGridView的Rows總數給變數aa,以利後續透過aa+1的方式來新增要加入的資料 (將DataGridView資料轉至Excel後,在Rows下方插入TextBox.Text) */ int aa = dgvReferOrderAll.Rows.Count + 1; excel.Cells[aa, 1] = "[葷]數量:"; excel.Cells[aa, 2] = txtTotalOrder0.Text; excel.Cells[aa, 3] = "[素]數量:"; excel.Cells[aa, 4] = txtTotalOrder1.Text; //設定欄位靠右 excel.get_Range("A" + aa).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; excel.get_Range("C" + aa).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; //設定欄位顏色 excel.get_Range("A" + aa).Interior.Color = Color.MediumOrchid; excel.get_Range("B" + aa).Interior.Color = Color.MediumPurple; excel.get_Range("C" + aa).Interior.Color = Color.MediumOrchid; excel.get_Range("D" + aa).Interior.Color = Color.MediumPurple; //設定欄位字體顏色 excel.get_Range("A" + aa).Font.Color = Color.Snow; excel.get_Range("B" + aa).Font.Color = Color.Snow; excel.get_Range("C" + aa).Font.Color = Color.Snow; excel.get_Range("D" + aa).Font.Color = Color.Snow; //設定欄位字體為粗體 excel.get_Range("A" + aa).Font.Bold = true; excel.get_Range("B" + aa).Font.Bold = true; excel.get_Range("C" + aa).Font.Bold = true; excel.get_Range("D" + aa).Font.Bold = true; aa = aa + 1; excel.Cells[aa, 1] = "[葷]金額:"; excel.Cells[aa, 2] = txtTotalPrice0.Text; excel.Cells[aa, 3] = "[素]金額:"; excel.Cells[aa, 4] = txtTotalPrice1.Text; excel.get_Range("A" + aa).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; excel.get_Range("C" + aa).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; excel.get_Range("A" + aa).Interior.Color = Color.MediumOrchid; excel.get_Range("B" + aa).Interior.Color = Color.MediumPurple; excel.get_Range("C" + aa).Interior.Color = Color.MediumOrchid; excel.get_Range("D" + aa).Interior.Color = Color.MediumPurple; excel.get_Range("A" + aa).Font.Color = Color.Snow; excel.get_Range("B" + aa).Font.Color = Color.Snow; excel.get_Range("C" + aa).Font.Color = Color.Snow; excel.get_Range("D" + aa).Font.Color = Color.Snow; excel.get_Range("A" + aa).Font.Bold = true; excel.get_Range("B" + aa).Font.Bold = true; excel.get_Range("C" + aa).Font.Bold = true; excel.get_Range("D" + aa).Font.Bold = true; aa = aa + 1; excel.Cells[aa, 3] = "總金額:"; excel.Cells[aa, 4] = txtTotalPriceAll.Text; excel.get_Range("C" + aa).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; excel.get_Range("C" + aa).Interior.Color = Color.MediumOrchid; excel.get_Range("D" + aa).Interior.Color = Color.MediumPurple; excel.get_Range("C" + aa).Font.Color = Color.Snow; excel.get_Range("D" + aa).Font.Color = Color.Snow; excel.get_Range("C" + aa).Font.Bold = true; excel.get_Range("D" + aa).Font.Bold = true; //設定滙出後,欄位寛度自動配合資料調整 excel.Cells.EntireRow.AutoFit(); //自動調整列高 excel.Cells.EntireColumn.AutoFit(); //將所有欄位做垂直置中 excel.Cells.VerticalAlignment = Excel.XlVAlign.xlVAlignTop; //將A1~D1的欄位做水平置中 excel.get_Range("A1").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; excel.get_Range("B1").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; excel.get_Range("C1").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; excel.get_Range("D1").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //設定A1~D1欄位框線 excel.get_Range("A1").Borders.LineStyle = 1; excel.get_Range("B1").Borders.LineStyle = 1; excel.get_Range("C1").Borders.LineStyle = 1; excel.get_Range("D1").Borders.LineStyle = 1; excel.get_Range("A1").Font.Color = Color.White; excel.get_Range("A1").Interior.Color = Color.DodgerBlue; excel.get_Range("B1").Font.Color = Color.White; excel.get_Range("B1").Interior.Color = Color.DodgerBlue; excel.get_Range("C1").Font.Color = Color.White; excel.get_Range("C1").Interior.Color = Color.DodgerBlue; excel.get_Range("D1").Font.Color = Color.White; excel.get_Range("D1").Interior.Color = Color.DodgerBlue; /* excel.get_Range("E1").Font.Color = Color.White; excel.get_Range("E1").Interior.Color = Color.DodgerBlue; excel.get_Range("F1").Font.Color = Color.White; excel.get_Range("F1").Interior.Color = Color.DodgerBlue; excel.get_Range("G1").Font.Color = Color.White; excel.get_Range("G1").Interior.Color = Color.DodgerBlue; excel.get_Range("H1").Font.Color = Color.White; excel.get_Range("H1").Interior.Color = Color.DodgerBlue; excel.get_Range("I1").Font.Color = Color.White; excel.get_Range("I1").Interior.Color = Color.DodgerBlue; excel.get_Range("J1").Font.Color = Color.White; excel.get_Range("J1").Interior.Color = Color.DodgerBlue; excel.get_Range("K1").Font.Color = Color.White; excel.get_Range("K1").Interior.Color = Color.DodgerBlue; excel.get_Range("L1").Font.Color = Color.White; excel.get_Range("L1").Interior.Color = Color.DodgerBlue; excel.get_Range("M1").Font.Color = Color.White; excel.get_Range("M1").Interior.Color = Color.DodgerBlue; excel.get_Range("N1").Font.Color = Color.White; excel.get_Range("N1").Interior.Color = Color.DodgerBlue; excel.get_Range("O1").Font.Color = Color.White; excel.get_Range("O1").Interior.Color = Color.DodgerBlue; */ //設置禁止彈出覆蓋或儲存的彈跳視窗 excel.DisplayAlerts = false; excel.AlertBeforeOverwriting = false; //將檔案儲存到SaveFile指定的位置 excel.ActiveWorkbook.SaveCopyAs(SaveFilePath); pgb.Close(); MessageBox.Show("已成功滙出Excel檔!" + Environment.NewLine + "檔案儲存在您電腦的桌面,檔名:BentoReport_" + Date + ".xls", "完成", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show(ex.Message); } //關閉工作簿和結束Excel程式 excel.Workbooks.Close(); excel.Quit(); //釋放資源 System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); excel = null; GC.Collect(); }
public void Excelvalidation(string path, int sno, Excel.Sheets val_sheetlist) { #region Initial Excel.Worksheet valsheet = (Excel.Worksheet)val_sheetlist[1]; FileInfo finfo = new FileInfo(path); Microsoft.Office.Interop.Excel.Application xlApp = null; Excel.Workbook xlWorkbook = null; Excel.Sheets xlSheets = null; Excel.Worksheet xlNewSheet = null; Excel.Worksheet xlsummarysheet = null; Excel.Worksheet xlscvsheet = null; string ErrMsg = "", hidelist = ""; int i = 1; try { valsheet.Cells[sno, 1] = finfo.Name; xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) return; xlWorkbook = xlApp.Workbooks.Open(path, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); int boindex = 0, sumindex = 0, cvrindex = 0; for (int l = 1; l <= xlWorkbook.Sheets.Count; l++) { if (xlWorkbook.Sheets[l].Name.ToString().Trim().Replace(" ", "").ToLower() == "boxoffice") { boindex = l; } if (xlWorkbook.Sheets[l].Name.ToString().Trim().Replace(" ", "").ToLower() == "coversheet") { cvrindex = l; } if (xlWorkbook.Sheets[l].Name.ToString().Trim().Replace(" ", "").ToLower() == "summary") { sumindex = l; } } xlSheets = xlWorkbook.Sheets as Excel.Sheets; if (boindex != 0) { xlNewSheet = (Excel.Worksheet)xlSheets[boindex]; xlNewSheet.Unprotect("7135"); } if (boindex != 0) { xlsummarysheet = (Excel.Worksheet)xlSheets[sumindex]; xlsummarysheet.Unprotect("7135"); } #endregion if (1 == valstatus) { hidelist = "2,3"; lnkvalexcel.Visible = true; #region Validate process if (xlNewSheet.ProtectContents) { ErrMsg = "File is protcted"; } else if (boindex == 0 && sumindex == 0 && cvrindex == 0) { ErrMsg = "Sheet name not found!"; } //if (!xlNewSheet.ProtectContents && (boindex != 0 && sumindex != 0 && cvrindex != 0)) if ((boindex != 0 && sumindex != 0 && cvrindex != 0)) { string sch_day = "", sch_date = "", sch_time = "", tot_att = "", gross_sales = "", tot_exp = "", gross_rec = "", Discount = "", SummaryMsg = ""; string royalty = "", gurantee = "", var_exp_act = "", share1 = "", share2 = "", share3 = "", iferr = ""; if (WriteError == 0) { #region Write Error royalty = xlsummarysheet.Cells[31, 2].Text.Trim(); gurantee = xlsummarysheet.Cells[34, 2].Text.Trim(); var_exp_act = xlsummarysheet.Cells[54, 2].Text.Trim(); share1 = xlsummarysheet.Cells[90, 2].Text.Trim(); share2 = xlsummarysheet.Cells[93, 2].Text.Trim(); share3 = xlsummarysheet.Cells[96, 2].Text.Trim(); if (royalty.AutoformatDecimal() == 0) SummaryMsg += "Royalty Zero,"; if (gurantee.AutoformatDecimal() == 0) SummaryMsg += "Guarantee Zero,"; if (var_exp_act.AutoformatDecimal() == 0) SummaryMsg += "Expenses Zero,"; //if (share1.AutoformatDecimal() + share2.AutoformatDecimal() + share3.AutoformatDecimal() != 100) // SummaryMsg += "Total share Not equal to 100,"; iferr = ""; for (i = 3; i < 13; i++) { ErrMsg = ""; sch_day = ""; sch_date = ""; sch_time = ""; tot_att = ""; gross_sales = ""; tot_exp = "0"; gross_rec = ""; sch_date = (xlNewSheet.Cells[1, i].Value != null) ? Convert.ToString(xlNewSheet.Cells[1, i].Value).Trim() : sch_date; sch_day = Convert.ToString(xlNewSheet.Cells[2, i].Text).ToString().Trim(); sch_time = Convert.ToString(xlNewSheet.Cells[3, i].Text).ToString().Trim(); tot_att = Convert.ToString(xlNewSheet.Cells[9, i].Value).ToString().Trim(); gross_sales = Convert.ToString(xlNewSheet.Cells[13, i].Value).ToString().Trim(); //tot_exp = Convert.ToString(xlNewSheet.Cells[26, i].Value).ToString().Trim(); gross_rec = (xlNewSheet.Cells[31, i].Value != null) ? Convert.ToString(xlNewSheet.Cells[31, i].Value.ToString()).Trim() : gross_rec; sch_date = (sch_date.ToLower().Contains("perf") == true) ? "0" : "1"; sch_day = (sch_day.Contains("Day") == true) ? "0" : "1"; sch_time = (sch_time.Contains("time") == true) ? "0" : "1"; tot_att = (tot_att.AutoformatInt() == 0) ? "0" : "1"; gross_sales = (gross_sales.AutoformatDecimal() == 0) ? "0" : "1"; //tot_exp = (tot_exp.AutoformatDecimal() == 0) ? "0" : "1"; gross_rec = (gross_rec.AutoformatDecimal() == 0) ? "0" : "1"; string ticket = "", price = "", ps_tkt_price = ""; for (int j = 100; j <= 110; j++) { ticket = xlNewSheet.Cells[j, 1].Text.Trim(); price = xlNewSheet.Cells[j, i].Text.Trim(); price = (price.AutoformatDecimal() == null) ? "0" : price.AutoformatDecimal().ToString(); if ((ticket.Contains("#") == true && price.AutoformatDecimal() > 0) || (ticket != "" && ticket.Contains("#") == false && price.AutoformatDecimal() == 0)) { ps_tkt_price = "0"; } } int totcnt = Convert.ToInt32(sch_date.AutoformatInt() + sch_day.AutoformatInt() + sch_time.AutoformatInt() + tot_att.AutoformatInt() + gross_sales.AutoformatInt() + tot_exp.AutoformatInt() + gross_rec.AutoformatInt()); if (totcnt < 6 && totcnt != 0) { if (sch_date.AutoformatInt() == 0) { //ErrMsg += "Schedule date error,"; } if (sch_day.AutoformatInt() == 0) { ErrMsg += "Schedule day error,"; } if (tot_att.AutoformatInt() == 0) { ErrMsg += "Total attendance error,"; } if (gross_sales.AutoformatInt() == 0) { ErrMsg += "Gross sales error,"; } //if (tot_exp.AutoformatInt() == 0) //{ // ErrMsg += "Total expenses error,"; //} if (gross_rec.AutoformatInt() == 0) { ErrMsg += "Gross receipts error,"; } } if (Discount.AutoformatInt() == 0 && i == 3) { ErrMsg += "Discount extra columns,"; } if (ps_tkt_price.AutoformatInt() == 0 && totcnt == 7) { ErrMsg += "Ticket price mismatching,"; } valsheet.Cells[sno, i] = ErrMsg; if (string.IsNullOrEmpty(ErrMsg) == false && string.IsNullOrEmpty(iferr) == true) iferr = ErrMsg; } valsheet.Cells[sno, 13] = SummaryMsg; #endregion } xlscvsheet = (Excel.Worksheet)xlSheets[cvrindex]; string recid = ""; #region GetMySQLRecID string shname = "", city = "", venue = "", presenter = ""; DateTime opdate, endate; shname = xlscvsheet.Cells[1, 1].Text; city = xlscvsheet.Cells[3, 2].Text; venue = xlscvsheet.Cells[4, 2].Text; presenter = xlscvsheet.Cells[5, 2].Text; if (!string.IsNullOrEmpty(xlscvsheet.Cells[8, 1].Text)) { opdate = Convert.ToDateTime(xlscvsheet.Cells[8, 1].Text); endate = Convert.ToDateTime(xlscvsheet.Cells[8, 2].Text); valsheet.Cells[sno, 14] = shname; valsheet.Cells[sno, 15] = city; valsheet.Cells[sno, 16] = opdate; valsheet.Cells[sno, 17] = endate; if (AllNew != 0) { MasterDataLayer.MasterData objmst = new MasterDataLayer.MasterData(); DataTable dt = new DataTable(); dt = objmst.GetMysql_Recordid(shname, city, opdate, endate); if (dt.Rows.Count > 0) { recid = dt.Rows[0]["RecordID"].ToString(); } } } else { recid = "Opening date not found!"; } valsheet.Cells[sno, 2] = recid; #endregion if (!string.IsNullOrEmpty(ErrMsg) || !string.IsNullOrEmpty(SummaryMsg)) { hdnvalidation_status.Value = "1"; } else if (recid.ToLower().Contains("new") == false && string.IsNullOrEmpty(iferr) == true) { //xlWorkbook.SaveAs(Saveaspath + finfo.Name); valsheet.Cells[sno, 25] = "Pass"; } else { valsheet.Cells[sno, 25] = "Fail"; } } else { valsheet.Cells[sno, i + 1] = ErrMsg; } #endregion } else { lnkexcelclms.Visible = true; hidelist = "1"; write_excel_columns(finfo, xlNewSheet, xlsummarysheet, sno, val_sheetlist); } // objcf.HideDBData_XLSheets(val_sheetlist, "1"); // xlWorkbook.Save(); xlWorkbook.Close(false, Type.Missing, Type.Missing); xlApp.Quit(); GC.Collect(); } catch (Exception ex) { valsheet.Cells[sno, i + 1] = ex.Message + " File Name: " + finfo.Name; xlWorkbook.Save(); xlWorkbook.Close(Type.Missing, Type.Missing, Type.Missing); xlApp.Quit(); GC.Collect(); // throw new Exception(ex.Message + " File Name: " + finfo.Name); } finally { // objcf.HideDBData_XLSheets(val_sheetlist, hidelist); xlApp = null; } }
public void write_to_db() { int sno = 2; Kill_Excel_Process(); string valexcelpath = ValidationInputPath; Microsoft.Office.Interop.Excel.Application xlApp1 = null; Excel.Workbook xlWorkbook1 = null; Excel.Sheets val_sheets = null; Excel.Worksheet valsheet = null; Excel.Worksheet xlClmWritingsheet = null; xlApp1 = new Microsoft.Office.Interop.Excel.Application(); xlWorkbook1 = xlApp1.Workbooks.Open(valexcelpath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); val_sheets = xlWorkbook1.Sheets as Excel.Sheets; valsheet = (Excel.Worksheet)val_sheets[1]; xlClmWritingsheet = (Excel.Worksheet)val_sheets[2]; string file, _recoid, _psWeek, _cityid = "0"; try { for (int e = 2; e < 5000; e++) { try { file = valsheet.Cells[e, 1].Text; _recoid = valsheet.Cells[e, 2].Text; _psWeek = valsheet.Cells[e, 23].Text; if (_recoid.ToLower() == "new") { _recoid = createnewengt(e, valsheet); _cityid = _recoid.ToString().Split(',').GetValue(1).ToString(); _recoid = _recoid.ToString().Split(',').GetValue(0).ToString(); valsheet.Cells[e, 2] = _recoid; valsheet.Cells[e, 20] = _cityid; } else { _cityid = valsheet.Cells[e, 20].Text; } if (string.IsNullOrEmpty(file) == true) break; file = ExcelFolderPath + file; if (_cityid != "0" && _recoid != "0") { Read(file, sno, _recoid, _psWeek); valsheet.Cells[e, 25] = "Pass"; } } catch (Exception ex1) { valsheet.Cells[e, 25] = "Fail: " + ex1.Message; } sno++; } } finally { xlWorkbook1.Save(); xlWorkbook1.Close(Type.Missing, Type.Missing, Type.Missing); xlApp1.Quit(); GC.Collect(); Kill_Excel_Process(); } }
private void 导出ExcelToolStripMenuItem1_Click(object sender, EventArgs e) { string fileName; saveFileDialog1.Filter = "Excel 2003 *.xls|*.xls|Excel 2007 *.xlsx|*.xlsx"; if (saveFileDialog1.ShowDialog() == DialogResult.OK) { fileName = saveFileDialog1.FileName; Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); app.Visible = false; Workbook wBook = app.Workbooks.Add(true); Worksheet wSheet = wBook.Worksheets[1] as Worksheet; DsToWsheet(ds, wSheet); //设置禁止弹出保存和覆盖的询问提示框 app.DisplayAlerts = false; app.AlertBeforeOverwriting = false; wBook.SaveAs(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); app.Quit(); app = null; } }
//生成Excel private bool ExportToExcel(DataTable dt) { bool result = false; this.UseWaitCursor = true; if (dt.Rows.Count > 65536) { MessageBox.Show("數據記錄太多(最多不能超過65536條),不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); return result; } Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); try { labMsg.Text = "Excel正在匯出中…………"; if (xlApp == null) { throw new Exception("无法创建Excel对象,可能您的机器未安装Excel"); } Excel.Workbooks workbooks = xlApp.Workbooks; Excel.Workbook workbook = workbooks.Add(true); Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1]; worksheet.Name = "過膠機生產日報表"; //worksheet.QueryTables.Add #region worksheet的格式 worksheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4; //設置頁面A4打印 worksheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape; //設置橫向打印 worksheet.PageSetup.Zoom = false; //設置頁面縮放比例 Zoom必須設為False FitToPagesWide才有效 worksheet.PageSetup.FitToPagesWide = 1;//設置葉寬為一頁 worksheet.PageSetup.CenterHorizontally = true;//頁面水平居中 worksheet.PageSetup.TopMargin = xlApp.InchesToPoints(0.275590551181102); worksheet.PageSetup.BottomMargin = xlApp.InchesToPoints(0.196850393700787); worksheet.PageSetup.LeftMargin = xlApp.InchesToPoints(0.196850393700787); worksheet.PageSetup.RightMargin = xlApp.InchesToPoints(0.196850393700787); worksheet.PageSetup.FooterMargin = xlApp.InchesToPoints(0.31496062992126); worksheet.PageSetup.HeaderMargin = xlApp.InchesToPoints(0.31496062992126); xlApp.Visible = false; #endregion #region 列名 #region 循環填充列名,很慢,已經REMARK //for (i = 1; i <= dt.Columns.Count - 1; i++) //{ // if (i == 11 || i == 12 || i == 13) // { // MergeColumn(xlApp, xlApp.Cells[RowIndex + 1, i], xlApp.Cells[RowIndex + 1, i], "", 12, 65535); // xlApp.Cells[RowIndex + 1, i] = dt.Columns[i].ColumnName.Substring(8, 3); // } // else if (i == 14 || i == 15 || i == 16 || i == 17) // { // MergeColumn(xlApp, xlApp.Cells[RowIndex + 1, i], xlApp.Cells[RowIndex + 1, i], "", 12, 65535); // xlApp.Cells[RowIndex + 1, i] = dt.Columns[i].ColumnName.Trim().Substring(8); // } // else if (i >= 23 && i <= 31) // { // MergeColumn(xlApp, xlApp.Cells[RowIndex + 1, i], xlApp.Cells[RowIndex + 1, i], "", 12, 65535); // xlApp.Cells[RowIndex + 1, i] = dt.Columns[i].ColumnName.Trim(); // } // else // { // MergeColumn(xlApp, xlApp.Cells[RowIndex, i], xlApp.Cells[RowIndex + 1, i], "", 12, 65535); // xlApp.Cells[RowIndex, i] = dt.Columns[i].ColumnName; // } //} #endregion int RowIndex = 2; MergeColumn(xlApp, xlApp.Cells[1, 1], xlApp.Cells[1, 30], "過膠機生產日報表", 16, 65535, 0); //大標題 MergeColumn(xlApp, xlApp.Cells[RowIndex, 11], xlApp.Cells[RowIndex, 13], "當更生產數量", 12, 65535, 0); MergeColumn(xlApp, xlApp.Cells[RowIndex, 14], xlApp.Cells[RowIndex, 17], "過膠行車長度", 12, 65535, 0); MergeColumn(xlApp, xlApp.Cells[RowIndex, 23], xlApp.Cells[RowIndex, 30], "常用物料使用情況", 12, 65535, 0); RowIndex = RowIndex + 1; MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 1], xlApp.Cells[RowIndex, 1], "生產日期", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 2], xlApp.Cells[RowIndex, 2], "班次", 12, 65535, 5); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 3], xlApp.Cells[RowIndex, 3], "機台編號", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 4], xlApp.Cells[RowIndex, 4], "機組人員", 12, 65535, 21.5); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 5], xlApp.Cells[RowIndex, 5], "工程單號", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 6], xlApp.Cells[RowIndex, 6], "產品名稱", 12, 65535, 15); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 7], xlApp.Cells[RowIndex, 7], "印張編號", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 8], xlApp.Cells[RowIndex, 8], "生產內容", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 9], xlApp.Cells[RowIndex, 9], "版本", 12, 65535, 7.5); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 10], xlApp.Cells[RowIndex, 10], "訂單數量", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex, 11], xlApp.Cells[RowIndex, 11], "正品數", 12, 65535, 7); MergeColumn(xlApp, xlApp.Cells[RowIndex, 12], xlApp.Cells[RowIndex, 12], "次品數", 12, 65535, 7); MergeColumn(xlApp, xlApp.Cells[RowIndex, 13], xlApp.Cells[RowIndex, 13], "廢品數", 12, 65535, 7); MergeColumn(xlApp, xlApp.Cells[RowIndex, 14], xlApp.Cells[RowIndex, 14], "單 張(cm)", 12, 65535, 10); MergeColumn(xlApp, xlApp.Cells[RowIndex, 15], xlApp.Cells[RowIndex, 15], "正品數(m)", 12, 65535, 10); MergeColumn(xlApp, xlApp.Cells[RowIndex, 16], xlApp.Cells[RowIndex, 16], "次品數(m)", 12, 65535, 10); MergeColumn(xlApp, xlApp.Cells[RowIndex, 17], xlApp.Cells[RowIndex, 17], "廢品數(m)", 12, 65535, 10); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 18], xlApp.Cells[RowIndex, 18], "上工序次品數", 12, 65535, 7); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 19], xlApp.Cells[RowIndex, 19], "上工序廢品數", 12, 65535, 7); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 20], xlApp.Cells[RowIndex, 20], "累計數量", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 21], xlApp.Cells[RowIndex, 21], "級別", 12, 65535, 5); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 22], xlApp.Cells[RowIndex, 22], "工程狀態", 12, 65535, 9.5); MergeColumn(xlApp, xlApp.Cells[RowIndex, 23], xlApp.Cells[RowIndex, 23], "物料編號", 12, 65535, 12); MergeColumn(xlApp, xlApp.Cells[RowIndex, 24], xlApp.Cells[RowIndex, 24], "物料名稱", 12, 65535, 35); MergeColumn(xlApp, xlApp.Cells[RowIndex, 25], xlApp.Cells[RowIndex, 25], "採購單號", 12, 65535, 12); MergeColumn(xlApp, xlApp.Cells[RowIndex, 26], xlApp.Cells[RowIndex, 26], "生產批次", 12, 65535, 12); MergeColumn(xlApp, xlApp.Cells[RowIndex, 27], xlApp.Cells[RowIndex, 27], "實際用量", 12, 65535, 8.5); MergeColumn(xlApp, xlApp.Cells[RowIndex, 28], xlApp.Cells[RowIndex, 28], "單位", 12, 65535, 5); MergeColumn(xlApp, xlApp.Cells[RowIndex, 29], xlApp.Cells[RowIndex, 29], "寬度(mm)", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex, 30], xlApp.Cells[RowIndex, 30], "長度(m)", 12, 65535, 9); #endregion #region 填充數據 向Excel中逐行逐列写入表格中的数据 RowIndex = RowIndex + 1; //數據開始的行號 int ExcelCurrectRow = RowIndex; //當前行 int dtRowNO; int realRowNO = 0;//dt實際行 int dtKeyRowNO = RowIndex;//NO:4 int megreCount = 0; int dtMetrialRowNO = 23;//物料編號所在列 int tempRow; int tempCol; #region 較慢的填充數據方法 已經REMARK //for (int row = 0; row < dt.Rows.Count; row++) //{ // //DataRow[] rows = dt.Select(dt.Rows[row][0].ToString()); // for (int col = 1; col < dt.Columns.Count; col++) // { // if (row > 0) //班次內工程ID相等 col22之後不合併 // { // if (dt.Rows[row - 1][0].ToString() == dt.Rows[row][0].ToString() && col < 22) // { // CellStyle(xlApp, xlApp.Cells[currectRow, col], xlApp.Cells[RowIndex, col], 10, -4142, true); // } // else // { // CellStyle(xlApp, xlApp.Cells[RowIndex, col], xlApp.Cells[RowIndex, col], 10, -4142, false); // xlApp.Cells[RowIndex, col] = dt.Rows[row][col].ToString(); // if (col < 22) currectRow = RowIndex; // } // } // else // { // CellStyle(xlApp, xlApp.Cells[RowIndex, col], xlApp.Cells[RowIndex, col], 10, -4142, false); // xlApp.Cells[RowIndex, col] = dt.Rows[row][col].ToString(); // } // } // RowIndex++; // //if (RowIndex == 15) goto range; // for 測試 //} #endregion DataTable tbPJSFID = dt.DefaultView.ToTable(true, new string[] { "GGuID" }); for (dtRowNO = 0; dtRowNO < tbPJSFID.Rows.Count; dtRowNO++) { DataRow[] currectKeyRow = dt.Select(" GGuID = '" + dt.Rows[realRowNO][0].ToString() + "' "); megreCount = currectKeyRow.Length; for (tempRow = 0; tempRow < megreCount; tempRow++) { if (tempRow == 0) { for (tempCol = 1; tempCol < dtMetrialRowNO; tempCol++) //先合併物料編號之前的單元格 { CellStyle(xlApp, xlApp.Cells[dtKeyRowNO, tempCol], xlApp.Cells[dtKeyRowNO + megreCount - 1, tempCol], currectKeyRow[tempRow][tempCol].ToString(), true); } realRowNO += megreCount; } for (tempCol = dtMetrialRowNO; tempCol < dt.Columns.Count; tempCol++) //然後再填充物料 { CellStyle(xlApp, xlApp.Cells[dtKeyRowNO, tempCol], xlApp.Cells[dtKeyRowNO, tempCol], currectKeyRow[tempRow][tempCol].ToString(), false); } dtKeyRowNO++; } //dtKeyRowNO++; //if (realRowNO > dt.Rows.Count) break; /* * DataTable pjsf_iPJSFID = dt.DefaultView.ToTable(true, new string[] { "pjsf_iPJSFID" }); List<Thread> threads = new List<Thread>(); for (dtRowNO = 0; dtRowNO < pjsf_iPJSFID.Rows.Count; dtRowNO++) { */ } #endregion #region 設置Sheet邊框 //range: Excel.Range SheetRangeMargin = xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[dt.Rows.Count + 3, dt.Columns.Count - 1]); SheetRangeMargin.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; SheetRangeMargin.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; SheetRangeMargin.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; #endregion Excel.Range DataFormat = xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[dt.Rows.Count + 3, dt.Columns.Count]); DataFormat.Font.Name = "楷体"; DataFormat.Font.Size = 10; Excel.Range IntFormat = xlApp.get_Range(xlApp.Cells[3, 15], xlApp.Cells[dt.Rows.Count + 3, 17]); IntFormat.NumberFormatLocal = "0.00"; #region savecode Object missing = Missing.Value; SaveFileDialog saveForm = new SaveFileDialog(); saveForm.Filter = "Execl files (*.xls)|*.xls"; saveForm.FilterIndex = 0; saveForm.FileName = "過膠機生產日報表"; if (saveForm.ShowDialog() == DialogResult.OK) { string fileName = saveForm.FileName; workbook.SaveAs(fileName, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing); } #endregion result = true; } catch (Exception ex) { throw ex; } finally { labMsg.Text = ""; this.UseWaitCursor = false; xlApp.Quit(); IntPtr t = new IntPtr(xlApp.Hwnd); //杀死进程的好方法 int k = 0; GetWindowThreadProcessId(t, out k); System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); p.Kill(); System.GC.Collect(); } labMsg.Text = ""; return result; }
private void MainMenuSaveAs_Click(object sender, EventArgs e) { if (flag_open) { // MainFormSaveFileDialog.FileName = _dataFile; string filter = "Книга Microsoft Office Excel (*.xls)|*.xls"; // string filter = "CSV(разделители запятые) (*.csv)|*.csv"; MainFormSaveFileDialog.Filter = filter; //MainFormSaveFileDialog.DefaultExt = "csv"; MainFormSaveFileDialog.DefaultExt = "xls"; if (MainFormSaveFileDialog.ShowDialog() == DialogResult.OK) { int i; //System.IO.File.WriteAllLines(MainFormSaveFileDialog.FileName, list_out); //bool _result = System.IO.File.Exists(MainFormSaveFileDialog.FileName); if (System.IO.File.Exists(MainFormSaveFileDialog.FileName)) System.IO.File.Delete(MainFormSaveFileDialog.FileName); if (MainFormCheckBoxResults.Checked == true) { flag_error = false; CheckOutData(MainFormSaveFileDialog.FileName); } System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); object opt = Type.Missing; Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); ExcelApp.SheetsInNewWorkbook = 1; Microsoft.Office.Interop.Excel.Workbook ExcelAppWorkbook = (Microsoft.Office.Interop.Excel.Workbook)ExcelApp.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel.Worksheet ExcelWorksheet = ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveSheet); info.NumberDecimalSeparator = "."; if (MainFormFactoryUnitIDTextBox.Text != "") for (i = 0; i < list_out_height.Count; i++) ExcelWorksheet.Cells[i + 1, 1] = MainFormFactoryUnitIDTextBox.Text; if (MainFormCalibTypeIDTextBox.Text != "") for (i = 0; i < list_out_height.Count; i++) ExcelWorksheet.Cells[i + 1, 2] = MainFormCalibTypeIDTextBox.Text; /* if (list_out_kvant.Count == list_out_height.Count) for (i = 0; i < list_out_kvant.Count; i++) ExcelWorksheet.Cells[i + 1, 5] = list_out_kvant[i]; */ /* if (Convert.ToDouble(list_out_kvant.Count/list_out_height.Count,info) < Convert.ToDouble(0.11,info)) for (i = 0; i < list_out_kvant.Count*10; i+=10) ExcelWorksheet.Cells[i + 1, 5] = list_out_kvant[i/10]; else for (i = 0; i < list_out_kvant.Count; i++) ExcelWorksheet.Cells[i + 1, 5] = list_out_kvant[i]; */ for (i = 0; i < list_out_height.Count; i++) { ExcelWorksheet.Cells[i + 1, 3] = Convert.ToDouble(list_out_height[i],info); ExcelWorksheet.Cells[i + 1, 4] = Convert.ToDouble(list_out_volume[i],info); } ExcelAppWorkbook.SaveAs(MainFormSaveFileDialog.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7); // ExcelApp.Visible = true; System.Threading.Thread.CurrentThread.CurrentCulture = oldCI; ExcelAppWorkbook.Close(null, null, null); ExcelApp.Quit(); Marshal.ReleaseComObject(ExcelAppWorkbook); Marshal.ReleaseComObject(ExcelApp); ExcelApp = null; GC.Collect(); MainFormLabelStatusBar.Text = "Файл сохранен"; if(!flag_error) MainFormLabelResult.Text = "Забирай свои калибровки..."; else MainFormLabelResult.Text = "Забирай свои калибровки...и проверь файл с ошибками:" + _errorFileName; this.AutoSize = true; this.StartPosition = FormStartPosition.CenterScreen; } } else MessageBox.Show("Чтобы что-то сохранить, нужно сначала что-то открыть...", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); }
public void Template(DataSet ds) { DataTable dt = new DataTable(); dt = ds.Tables[0]; string serverpath = this.MapPath("."); //string filename = "Settlement_Reports_" + System.DateTime.Now.ToString("ddmmyyyy_hh_mm_ss") + ".xls"; string filename = "Settlement_Reports_" + System.DateTime.Now.ToString("ddMMyyyy_hhmmss") + ".xlsx"; //DeleteCreatedFiles(filename, @"" + serverpath + "\\ExcelReports\\"); string newfile = @"" + serverpath + "\\Reports\\ExcelReports\\" + filename; String ExcelPath = System.Configuration.ConfigurationManager.AppSettings["ExcelPath"].ToString(); string newpath = ExcelPath + filename; //string newpath = "\\Reports\\ExcelReports\\" + filename; if (File.Exists(newfile) == false) { File.Copy(@"" + serverpath + "\\Reports\\ExcelTemplates\\Settlement_Reports_Template.xlsx", newfile); Microsoft.Office.Interop.Excel.Application xlApp = null; Microsoft.Office.Interop.Excel.Workbook xlWorkbook = null; Microsoft.Office.Interop.Excel.Sheets xlSheets = null; Microsoft.Office.Interop.Excel.Worksheet xlNewSheet = null; Excel.Worksheet xlCoverSheet = null; try { xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) return; xlWorkbook = xlApp.Workbooks.Open(newfile, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); xlSheets = xlWorkbook.Sheets as Excel.Sheets; xlNewSheet = (Excel.Worksheet)xlSheets[5]; xlCoverSheet = (Excel.Worksheet)xlSheets[1]; int rowCount = 2; int colstart = dt.Columns["Schedule_Type"].Ordinal; int i; foreach (DataRow dr in dt.Rows) { rowCount += 1; for (i = 1; i < dt.Columns.Count + 1; i++) { xlNewSheet.Cells[rowCount, (i + 1)] = dr[i - 1].ToString(); } break; } int k = 0; foreach (DataRow dr in dt.Rows) { if (k == 0) { k++; continue; } rowCount += 1; for (i = colstart; i <= (dt.Columns.Count); i++) { xlNewSheet.Cells[rowCount, (i + 1)] = dr[i - 1].ToString(); } } rowCount = 17; if (ds.Tables[1].Rows.Count > 0) { xlNewSheet.Cells[rowCount, (2)] = ds.Tables[1].Rows[0]["TOTALGROSSPERWK"].ToString(); xlNewSheet.Cells[rowCount, (3)] = ds.Tables[1].Rows[0]["SUM_PER_PS_SCALE"].ToString(); } //objcf.HideDBData_XLSheets(xlSheets, "5"); if (ds.Tables[2].Rows.Count > 0) { write_coversheet_data(xlCoverSheet, ds.Tables[2]); } xlWorkbook.Save(); xlWorkbook.Close(Type.Missing, Type.Missing, Type.Missing); xlApp.Quit(); GC.Collect(); foreach (System.Diagnostics.Process proc in System.Diagnostics.Process.GetProcessesByName("EXCEL")) { if (proc.MainWindowTitle.ToString() == "") { // proc.Kill(); } } //Response.Redirect(newfilepath); string fName = newfile; hdnfilepath.Value = newpath; lnkexcel.HRef = newpath; Image2.Visible = true; } finally { xlApp = null; } //ScriptManager.RegisterStartupScript(this, this.GetType(), "", "anc();", true); } }
public static void createReport() { Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); Excel.Range chartRange; if (xlApp == null) { MessageBox.Show("Excel is not properly installed!!"); return; } Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.Columns.AutoFit(); xlWorkSheet.get_Range("A1", "C1").Merge(false); xlWorkSheet.get_Range("A1", "C1").Cells.Font.Size = 16; xlWorkSheet.get_Range("A1").Cells.Font.Name = "Times New Roman"; xlWorkSheet.get_Range("A1").Cells.FormulaR1C1 = "TRƯỜNG ĐH CÔNG NGHỆ THÔNG TIN"; xlWorkSheet.Cells[1, 2].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.get_Range("A2", "C2").Merge(false); xlWorkSheet.get_Range("A2", "C2").Cells.Font.Size = 16; xlWorkSheet.get_Range("A2").Cells.Font.Name = "Times New Roman"; xlWorkSheet.get_Range("A2").Cells.Font.Bold = true; xlWorkSheet.get_Range("A2").Cells.FormulaR1C1 = "PHÒNG ĐÀO TẠO ĐẠI HỌC"; xlWorkSheet.Cells[2, 2].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.get_Range("A4", "E4").Merge(false); xlWorkSheet.get_Range("A4", "E4").Cells.Font.Size = 20; xlWorkSheet.get_Range("A4").Cells.Font.Name = "Times New Roman"; xlWorkSheet.get_Range("A4").Cells.Font.Bold = true; xlWorkSheet.get_Range("A4").Cells.FormulaR1C1 = "BẢNG ĐIỂM CUỐI KỲ"; xlWorkSheet.Cells[4, 2].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.get_Range("A5", "C5").Merge(false); xlWorkSheet.get_Range("A5", "C5").Cells.Font.Size = 16; xlWorkSheet.get_Range("A5").Cells.Font.Name = "Times New Roman"; xlWorkSheet.get_Range("A5").Cells.Font.Bold = true; xlWorkSheet.get_Range("A5").Cells.FormulaR1C1 = "HỌC KỲ: "; xlWorkSheet.get_Range("A5").Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; xlWorkSheet.get_Range("D5", "E5").Merge(false); xlWorkSheet.get_Range("D5", "E5").Cells.Font.Size = 16; xlWorkSheet.get_Range("D5").Cells.Font.Name = "Times New Roman"; xlWorkSheet.get_Range("D5").Cells.Font.Bold = true; xlWorkSheet.get_Range("D5").Cells.FormulaR1C1 = "NĂM HỌC: "; xlWorkSheet.get_Range("F5", "G5").Merge(false); xlWorkSheet.get_Range("F5", "G5").Cells.Font.Size = 16; xlWorkSheet.get_Range("F5").Cells.Font.Name = "Times New Roman"; xlWorkSheet.get_Range("F5").Cells.Font.Bold = true; xlWorkSheet.get_Range("F5").Cells.FormulaR1C1 = "Trọng số: "; xlWorkSheet.get_Range("A6", "C6").Merge(false); xlWorkSheet.get_Range("A6", "C6").Cells.Font.Size = 16; xlWorkSheet.get_Range("A6").Cells.Font.Name = "Times New Roman"; xlWorkSheet.get_Range("A6").Cells.Font.Bold = true; xlWorkSheet.get_Range("A6").Cells.FormulaR1C1 = "Môn học: "; xlWorkSheet.get_Range("D6", "E6").Merge(false); xlWorkSheet.get_Range("D6", "E6").Cells.Font.Size = 16; xlWorkSheet.get_Range("D6").Cells.Font.Name = "Times New Roman"; xlWorkSheet.get_Range("D6").Cells.Font.Bold = true; xlWorkSheet.get_Range("D6").Cells.FormulaR1C1 = "Lớp: "; xlWorkSheet.get_Range("F6", "G6").Merge(false); xlWorkSheet.get_Range("F6", "G6").Cells.Font.Size = 16; xlWorkSheet.get_Range("F6").Cells.Font.Name = "Times New Roman"; xlWorkSheet.get_Range("F6").Cells.Font.Bold = true; xlWorkSheet.get_Range("F6").Cells.FormulaR1C1 = "Ngày thi: "; xlWorkSheet.get_Range("A7", "C7").Merge(false); xlWorkSheet.get_Range("A7", "C7").Cells.Font.Size = 16; xlWorkSheet.get_Range("A7").Cells.Font.Name = "Times New Roman"; xlWorkSheet.get_Range("A7").Cells.Font.Bold = true; xlWorkSheet.get_Range("A7").Cells.FormulaR1C1 = "Giảng viên: "; xlWorkSheet.get_Range("D7", "E7").Merge(false); xlWorkSheet.get_Range("D7", "E7").Cells.Font.Size = 16; xlWorkSheet.get_Range("D7").Cells.Font.Name = "Times New Roman"; xlWorkSheet.get_Range("D7").Cells.Font.Bold = true; xlWorkSheet.get_Range("D7").Cells.FormulaR1C1 = "Mã giảng viên: "; xlWorkSheet.get_Range("F7", "G7").Merge(false); xlWorkSheet.get_Range("F7", "G7").Cells.Font.Size = 16; xlWorkSheet.get_Range("F7").Cells.Font.Name = "Times New Roman"; xlWorkSheet.get_Range("F7").Cells.Font.Bold = true; xlWorkSheet.get_Range("F7").Cells.FormulaR1C1 = "Phòng thi: "; //Mark table xlWorkSheet.get_Range("A10", "F10").Cells.Font.Size = 16; xlWorkSheet.get_Range("A10", "F10").Cells.Font.Name = "Times New Roman"; xlWorkSheet.get_Range("A10", "F10").Cells.Font.Bold = true; xlWorkSheet.get_Range("A10").Cells.EntireColumn.ColumnWidth = 8; xlWorkSheet.get_Range("B10").Cells.EntireColumn.ColumnWidth = 30; xlWorkSheet.get_Range("C10").Cells.EntireColumn.ColumnWidth = 45; xlWorkSheet.get_Range("D10").Cells.EntireColumn.ColumnWidth = 15; xlWorkSheet.get_Range("E10").Cells.EntireColumn.ColumnWidth = 25; xlWorkSheet.get_Range("F10").Cells.EntireColumn.ColumnWidth = 20; xlWorkSheet.get_Range("A10", "F10").Cells.EntireColumn.AutoFit(); xlWorkSheet.get_Range("A10", "F10").Cells.EntireRow.AutoFit(); xlWorkSheet.Cells[10, 1] = "STT"; xlWorkSheet.Cells[10, 2] = "MSSV"; xlWorkSheet.Cells[10, 3] = "Họ tên sinh viên"; xlWorkSheet.Cells[10, 4] = "Điểm số"; xlWorkSheet.Cells[10, 5] = "Điểm chữ"; xlWorkSheet.Cells[10, 6] = "Ghi chú"; xlWorkSheet.get_Range("A11", "F11").Cells.Font.Size = 14; xlWorkSheet.get_Range("A11", "F11").Cells.Font.Name = "Times New Roman"; //Read data file here xlWorkSheet.Cells[11, 1] = "1"; xlWorkSheet.Cells[11, 2] = "12520001"; xlWorkSheet.Cells[11, 3] = "Nguyễn Tuấn Anh"; xlWorkSheet.Cells[11, 4] = "9.0"; xlWorkSheet.Cells[11, 5] = "Chín"; xlWorkSheet.Cells[11, 6] = ""; try { SaveFileDialog sfd = new SaveFileDialog(); sfd.Filter = "Excel file(*.xls)|*.xls"; sfd.FilterIndex = 1; if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.Cancel) { return; } xlWorkBook.SaveAs(sfd.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); MessageBox.Show("Excel file created success"); } catch (Exception ex) { MessageBox.Show("Excel file created fail"); } xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); }
public void validate(string from) { if (File.Exists(ValidationExcelPath) == true) { File.Delete(ValidationExcelPath); } File.Copy(ValidationExcelTemplate, ValidationExcelPath); int sno = 2; Kill_Excel_Process(); string valexcelpath = ValidationExcelPath; Microsoft.Office.Interop.Excel.Application xlApp1 = null; Excel.Workbook xlWorkbook1 = null; Excel.Sheets val_sheets = null; Excel.Worksheet valsheet = null; Excel.Worksheet xlClmWritingsheet = null; xlApp1 = new Microsoft.Office.Interop.Excel.Application(); xlWorkbook1 = xlApp1.Workbooks.Open(valexcelpath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); val_sheets = xlWorkbook1.Sheets as Excel.Sheets; valsheet = (Excel.Worksheet)val_sheets[1]; xlClmWritingsheet = (Excel.Worksheet)val_sheets[2]; try { if (from == "folder") { foreach (string f in Directory.GetFiles(ExcelFolderPath)) { if (f.ToLower().Contains("xls")) { Excelvalidation(f, sno, val_sheets); // Read(f, sno); sno++; } } } if (from == "excel") { string file, _recoid; for (int e = 2; e < 5000; e++) { file = ExcelFolderPath + valsheet.Cells[e, 1].Text; _recoid = valsheet.Cells[e, 2].Text; if (_recoid.ToLower() == "new") { createnewengt(e, valsheet); } if (string.IsNullOrEmpty(file) == true) break; Excelvalidation(file, sno, val_sheets); sno++; } } } catch (Exception ex) { throw new Exception(ex.Message + " S.No." + sno); } finally { xlWorkbook1.Save(); xlWorkbook1.Close(Type.Missing, Type.Missing, Type.Missing); xlApp1.Quit(); GC.Collect(); Kill_Excel_Process(); } }
private void ExportDataSetToExcel(DataTable dt,DataTable dt2) { // Load Excel application Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); // Create empty workbook excel.Workbooks.Add(); // Create Worksheet from active sheet Microsoft.Office.Interop.Excel._Worksheet workSheet = excel.ActiveSheet; Microsoft.Office.Interop.Excel.Worksheet workSheet2 = (Microsoft.Office.Interop.Excel.Worksheet)excel.Sheets.Add(); // I created Application and Worksheet objects before try/catch, // so that i can close them in finnaly block. // It's IMPORTANT to release these COM objects!! try { // ------------------------------------------------ // Creation of header cells // ------------------------------------------------ workSheet.Cells[1, "A"]="origen"; workSheet.Cells[1, "B"]="column1"; workSheet.Cells[1, "C"]="referencia1"; workSheet.Cells[1, "D"]="referencia2"; workSheet.Cells[1, "E"]="referencia3"; workSheet.Cells[1, "F"]="presupuesto"; workSheet.Cells[1, "G"]="codcapi"; workSheet.Cells[1, "H"]="capitulo"; workSheet.Cells[1, "I"]="codunit"; workSheet.Cells[1, "J"]="unitario"; workSheet.Cells[1, "K"]="undunita"; workSheet.Cells[1, "L"]="cantxppto"; workSheet.Cells[1, "M"]="codinsu"; workSheet.Cells[1, "N"]="insutipo"; workSheet.Cells[1, "O"]="insumo"; workSheet.Cells[1, "P"]="unidinsu"; workSheet.Cells[1, "Q"]="ctrlinven"; workSheet.Cells[1, "R"]="validación"; workSheet.Cells[1, "S"]="precioppto"; workSheet.Cells[1, "T"]="consumounitario"; workSheet.Cells[1, "U"]="consumototal"; workSheet.Cells[1, "V"]="adic"; workSheet.Cells[1, "W"]="precioaut"; workSheet.Cells[1, "X"]="PrecioCompra"; workSheet.Cells[1, "Y"]="PrecioEntrado"; workSheet.Cells[1, "Z"]="Ped"; workSheet.Cells[1, "AA"]="aprob"; workSheet.Cells[1, "AB"]="comp"; workSheet.Cells[1, "AC"]="ent"; workSheet.Cells[1, "AD"]="sali"; workSheet.Cells[1, "AE"]="traslado"; workSheet.Cells[1, "AF"]="xcomp"; workSheet.Cells[1, "AG"]="xent"; workSheet.Cells[1, "AH"]="saldoxunit"; workSheet.Cells[1, "AI"]="saldoxppto"; workSheet.Cells[1, "AJ"]="vlrEnt"; workSheet.Cells[1, "AK"]="vlrEnttraslado"; workSheet.Cells[1, "AL"]="VlrCompradoxent"; workSheet.Cells[1, "AM"]="vlrxcomp"; workSheet.Cells[1, "AN"]="VlrTraslado"; workSheet.Cells[1, "AO"]="vlrproy"; workSheet.Cells[1, "AP"]="Vlrini"; workSheet.Cells[1, "AQ"]="vlrejec"; workSheet2.Cells[1, "A"] = "referencia1"; // ------------------------------------------------ // Populate sheet with some real data from "cars" list // ------------------------------------------------ int row = 2; // start row (in row 1 are header cells) for (int i = 0; i <= dt.Rows.Count - 1; i++) { workSheet.Cells[row, "A"] = dt.Rows[i].Field<string>("origen"); workSheet.Cells[row, "B"] = dt.Rows[i].Field<DateTime>("column1"); workSheet.Cells[row, "C"] = dt.Rows[i].Field<string>("referencia1"); workSheet.Cells[row, "D"] = dt.Rows[i].Field<string>("referencia2"); workSheet.Cells[row, "E"] = dt.Rows[i].Field<string>("referencia3"); /* workSheet.Cells[row, "F"] = dt.Rows[i].Field<string>("presupuesto"); workSheet.Cells[row, "G"] = dt.Rows[i].Field<string>("codcapi"); workSheet.Cells[row, "H"] = dt.Rows[i].Field<string>("capitulo"); workSheet.Cells[row, "I"] = dt.Rows[i].Field<string>("codunit"); workSheet.Cells[row, "J"] = dt.Rows[i].Field<string>("unitario"); workSheet.Cells[row, "K"] = dt.Rows[i].Field<string>("undunita"); workSheet.Cells[row, "L"] = dt.Rows[i].Field<decimal>("cantxppto"); workSheet.Cells[row, "M"] = dt.Rows[i].Field<string>("codinsu"); workSheet.Cells[row, "N"] = dt.Rows[i].Field<string>("insutipo"); // workSheet.Cells[row, "O"] = dt.Rows[i].Field<string>("Proyecto"); workSheet.Cells[row, "P"] = dt.Rows[i].Field<string>("insumo"); workSheet.Cells[row, "Q"] = dt.Rows[i].Field<string>("unidinsu"); workSheet.Cells[row, "R"] = dt.Rows[i].Field<string>("ctrlinven"); workSheet.Cells[row, "S"] = dt.Rows[i].Field<string>("validación"); workSheet.Cells[row, "T"] = dt.Rows[i].Field<decimal>("precioppto"); workSheet.Cells[row, "U"] = dt.Rows[i].Field<decimal>("consumounitario"); workSheet.Cells[row, "V"] = dt.Rows[i].Field<decimal>("consumototal"); workSheet.Cells[row, "W"] = dt.Rows[i].Field<decimal>("precioaut"); workSheet.Cells[row, "X"] = dt.Rows[i].Field<decimal>("PrecioCompra"); workSheet.Cells[row, "Y"] = dt.Rows[i].Field<decimal>("PrecioEntrado"); workSheet.Cells[row, "Z"] = dt.Rows[i].Field<decimal>("Ped"); workSheet.Cells[row, "AA"] = dt.Rows[i].Field<decimal>("aprob"); workSheet.Cells[row, "AB"] = dt.Rows[i].Field<decimal>("comp"); workSheet.Cells[row, "AC"] = dt.Rows[i].Field<decimal>("traslado"); workSheet.Cells[row, "AD"] = dt.Rows[i].Field<decimal>("sali"); workSheet.Cells[row, "AE"] = dt.Rows[i].Field<decimal>("xcomp"); workSheet.Cells[row, "AF"] = dt.Rows[i].Field<decimal>("xent"); workSheet.Cells[row, "AG"] = dt.Rows[i].Field<decimal>("saldoxunit"); workSheet.Cells[row, "AH"] = dt.Rows[i].Field<decimal>("saldoxppto"); workSheet.Cells[row, "AI"] = dt.Rows[i].Field<decimal>("vlrEnt"); workSheet.Cells[row, "AJ"] = dt.Rows[i].Field<decimal>("vlrEnttraslado"); workSheet.Cells[row, "AK"] = dt.Rows[i].Field<decimal>("VlrCompradoxent"); workSheet.Cells[row, "AL"] = dt.Rows[i].Field<decimal>("vlrxcomp"); workSheet.Cells[row, "AM"] = dt.Rows[i].Field<decimal>("VlrTraslado"); workSheet.Cells[row, "AN"] = dt.Rows[i].Field<decimal>("vlrproy"); workSheet.Cells[row, "AO"] = dt.Rows[i].Field<decimal>("Vlrini"); //workSheet.Cells[row, "AP"] = dt.Rows[i].Field<decimal>("Fecha"); workSheet.Cells[row, "AQ"] = Convert.ToString(dt.Rows[i].Field<decimal>("vlrejec")); workSheet.Cells[row].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray);*/ //57 //124 row++; } int row2=2; for (int i = 0; i <= dt2.Rows.Count - 1; i++) { workSheet2.Cells[row2, "A"] = dt2.Rows[i].Field<string>("referencia1"); workSheet2.Cells[row2, "D"] = dt2.Rows[i].Field<string>("referencia2"); workSheet2.Cells[row2, "E"] = dt2.Rows[i].Field<string>("referencia3"); workSheet2.Cells[row2, "F"] = dt2.Rows[i].Field<string>("presupuesto"); workSheet2.Cells[row2, "G"] = dt2.Rows[i].Field<string>("codcapi"); /* workSheet2.Cells[row2, "H"] = dt2.Rows[i].Field<string>("capitulo"); workSheet2.Cells[row2, "I"] = dt2.Rows[i].Field<string>("codunit"); workSheet2.Cells[row2, "K"] = dt2.Rows[i].Field<string>("undunita"); workSheet2.Cells[row2, "L"] = dt2.Rows[i].Field<decimal>("cantxppto"); workSheet2.Cells[row2, "M"] = dt2.Rows[i].Field<string>("codinsu"); workSheet2.Cells[row2, "N"] = dt2.Rows[i].Field<string>("insutipo"); // workSheet.Cells[row, "O"] = dt.Rows[i].Field<string>("Proyecto"); workSheet2.Cells[row2, "P"] = dt2.Rows[i].Field<string>("insumo"); workSheet2.Cells[row2, "Q"] = dt2.Rows[i].Field<string>("unidinsu"); workSheet2.Cells[row2, "R"] = dt2.Rows[i].Field<string>("ctrlinven"); workSheet2.Cells[row2, "S"] = dt2.Rows[i].Field<string>("validación"); workSheet2.Cells[row2, "T"] = dt2.Rows[i].Field<decimal>("precioppto"); workSheet2.Cells[row2, "U"] = dt2.Rows[i].Field<decimal>("consumounitario"); workSheet2.Cells[row2, "V"] = dt2.Rows[i].Field<decimal>("consumototal"); workSheet2.Cells[row2, "W"] = dt2.Rows[i].Field<decimal>("precioaut"); workSheet2.Cells[row2, "X"] = dt2.Rows[i].Field<decimal>("PrecioCompra"); workSheet2.Cells[row2, "Y"] = dt2.Rows[i].Field<decimal>("PrecioEntrado"); workSheet2.Cells[row2, "Z"] = dt2.Rows[i].Field<decimal>("Ped"); workSheet2.Cells[row2, "AA"] = dt2.Rows[i].Field<decimal>("aprob"); workSheet2.Cells[row2, "AB"] = dt2.Rows[i].Field<decimal>("comp"); workSheet2.Cells[row2, "AC"] = dt2.Rows[i].Field<decimal>("traslado"); workSheet2.Cells[row2, "AD"] = dt2.Rows[i].Field<decimal>("sali"); workSheet2.Cells[row2, "AE"] = dt2.Rows[i].Field<decimal>("xcomp"); workSheet2.Cells[row2, "AF"] = dt2.Rows[i].Field<decimal>("xent"); workSheet2.Cells[row2, "AG"] = dt2.Rows[i].Field<decimal>("saldoxunit"); workSheet2.Cells[row2, "AH"] = dt2.Rows[i].Field<decimal>("saldoxppto"); workSheet2.Cells[row2, "AI"] = dt2.Rows[i].Field<decimal>("vlrEnt"); workSheet2.Cells[row2, "AJ"] = dt2.Rows[i].Field<decimal>("vlrEnttraslado"); workSheet2.Cells[row2, "AK"] = dt2.Rows[i].Field<decimal>("VlrCompradoxent"); workSheet2.Cells[row2, "AL"] = dt2.Rows[i].Field<decimal>("vlrxcomp"); workSheet2.Cells[row2, "AM"] = dt2.Rows[i].Field<decimal>("VlrTraslado"); workSheet2.Cells[row2, "AN"] = dt2.Rows[i].Field<decimal>("vlrproy"); workSheet2.Cells[row2, "AO"] = dt2.Rows[i].Field<decimal>("Vlrini"); workSheet2.Cells[row2, "AP"] = dt2.Rows[i].Field<int>("IdFecha"); workSheet2.Cells[row2, "AQ"] = Convert.ToString(dt2.Rows[i].Field<decimal>("vlrejec"));*/ //57 //124 row2++; } // Apply some predefined styles for data to look nicely :) workSheet.Range["A1"].AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic1); workSheet2.Range["A1"].AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic1); // Define filename string fileName = string.Format(@"{0}\ExcelData.xls", Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory)); // Save this data as a file workSheet.SaveAs(fileName); workSheet2.SaveAs(fileName); // Display SUCCESS message } catch (Exception ex) { throw ex; } finally { // Quit Excel application excel.Quit(); // Release COM objects (very important!) if (excel != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); if (workSheet != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet); // Empty variables excel = null; workSheet = null; // Force garbage collector cleaning GC.Collect(); } }
public static void Saveas(DataGridView dgv, string path) { Microsoft.Office.Interop.Excel.Application APP = new Microsoft.Office.Interop.Excel.Application(); APP.Application.Workbooks.Add(Type.Missing); APP.Columns.ColumnWidth = 20; for (int i = 1; i < dgv.Columns.Count + 1; i++) { APP.Cells[1, i] = dgv.Columns[i - 1].HeaderText; } for (int i = 0; i < dgv.Rows.Count; i++) { for (int j = 0; j < dgv.Columns.Count; j++) { APP.Cells[i + 2, j + 1] = dgv.Rows[i].Cells[j].Value; } } SaveFileDialog saveFileDialog1 = new SaveFileDialog(); saveFileDialog1.Filter = "Excel File (*.xlsx)|*.xlsx"; saveFileDialog1.InitialDirectory = path; saveFileDialog1.FilterIndex = 1; saveFileDialog1.OverwritePrompt = false; if (saveFileDialog1.ShowDialog() == DialogResult.OK) { FileInfo file = new FileInfo(saveFileDialog1.FileName); if (file.Exists) { file.Delete(); } APP.ActiveWorkbook.SaveAs(saveFileDialog1.FileName.ToString()); APP.ActiveWorkbook.Saved = true; APP.Quit(); OpenV = true; } }
public void WriteConsolidateReportToResponse(DataSet consolidatedReportDS) { if (null != consolidatedReportDS) { //Response.Write("Beginning report generation .."); if (consolidatedReportDS.Tables.Count > 0) { string fileName = String.Format("{0}.xls", DateTime.Now.ToString("yyyyMMddhhmmss")); string reportFileName = Server.MapPath(String.Format(@"/{0}", fileName)); Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); xlApp.Visible = false; xlApp.DisplayAlerts = false; Excel.Workbook xlWB = xlApp.Workbooks.Add(Type.Missing); foreach (DataTable dt in consolidatedReportDS.Tables) { WriteDataTableToWorkbook(xlWB, dt); } xlWB.SaveAs(reportFileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); xlWB.Close(true, Type.Missing, Type.Missing); xlApp.Quit(); // Clear the content of the response Response.ClearContent(); // Add the file name and attachment, which will force the open/cancel/save dialog box to show, to the header Response.AddHeader("Content-Disposition", "inline; filename=" + reportFileName); // Add the file size into the response header Response.AddHeader("Content-Length", new System.IO.FileInfo(reportFileName).Length.ToString()); // Set the ContentType Response.ContentType = "application/x-excel"; //// Write the file into the response (TransmitFile is for ASP.NET 2.0. In ASP.NET 1.1 you have to use WriteFile instead) Response.TransmitFile(reportFileName); // End the response Response.Flush(); System.IO.File.Delete(reportFileName); } } }
public static void Save(DataGridView dgv, string Savepath) { Microsoft.Office.Interop.Excel.Application APP = new Microsoft.Office.Interop.Excel.Application(); APP.Application.Workbooks.Add(Type.Missing); APP.Columns.ColumnWidth = 20; for (int i = 1; i < dgv.Columns.Count + 1; i++) { APP.Cells[1, i] = dgv.Columns[i - 1].HeaderText; } for (int i = 0; i < dgv.Rows.Count; i++) { for (int j = 0; j < dgv.Columns.Count; j++) { APP.Cells[i + 2, j + 1] = dgv.Rows[i].Cells[j].Value; } } FileInfo file = new FileInfo(Savepath); if (file.Exists) { file.Delete(); } OpenV = true; APP.ActiveWorkbook.SaveAs(Savepath.ToString()); APP.ActiveWorkbook.Saved = true; APP.Quit(); }
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e) { bool detailed = radioButtonDetailledMode.Checked; Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) { MessageBox.Show("Excel is not properly installed!!"); return; } Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.get_Range("a1", "f1").Merge(false); Excel.Range chartRange = xlWorkSheet.get_Range("a1", "f1"); chartRange.FormulaR1C1 = string.Format("{0} assets information, media account '{1}'", radioButtonAllAssets.Checked ? "All" : "Selected", _context.Credentials.ClientId); chartRange.VerticalAlignment = 3; chartRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DarkBlue); chartRange.Font.Size = 20; xlWorkSheet.get_Range("a2", "f2").Merge(false); Excel.Range chartRange2 = xlWorkSheet.get_Range("a2", "f2"); chartRange2.FormulaR1C1 = string.Format("Exported with Azure Media Services Explorer v{0} on {1}. Dates are {2}.", Assembly.GetExecutingAssembly().GetName().Version.ToString(), checkBoxLocalTime.Checked ? DateTime.Now.ToString() : DateTime.UtcNow.ToString(), checkBoxLocalTime.Checked ? "local" : "UTC based" ); chartRange2.VerticalAlignment = 3; chartRange2.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DarkBlue); chartRange2.Font.Size = 12; int row = 4; int index = 1; xlWorkSheet.Cells[row, index++] = "Asset Name"; xlWorkSheet.Cells[row, index++] = "Id"; xlWorkSheet.Cells[row, index++] = "Last Modified"; xlWorkSheet.Cells[row, index++] = "Type"; xlWorkSheet.Cells[row, index++] = "Size"; int backindex = index; _context.StreamingEndpoints.ToList().ForEach(se => xlWorkSheet.Cells[row, index++] = "Streaming URL" ); index = backindex + _context.StreamingEndpoints.Count(); xlWorkSheet.Cells[row, index++] = "Expiration time"; if (detailed) { xlWorkSheet.Cells[row, index++] = "Alternate Id"; xlWorkSheet.Cells[row, index++] = "Storage Account"; xlWorkSheet.Cells[row, index++] = "Streaming Locators Count"; xlWorkSheet.Cells[row, index++] = "Streaming Min Expiration time"; xlWorkSheet.Cells[row, index++] = "Streaming Max Expiration time"; xlWorkSheet.Cells[row, index++] = "SAS Locators Count"; xlWorkSheet.Cells[row, index++] = "SAS Min Expiration time"; xlWorkSheet.Cells[row, index++] = "SAS Max Expiration time"; xlWorkSheet.Cells[row, index++] = "Dynamic encryption"; xlWorkSheet.Cells[row, index++] = "Asset filters count"; } Excel.Range formatRange; formatRange = xlWorkSheet.get_Range("a4"); formatRange.EntireRow.Font.Bold = true; formatRange.EntireRow.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightBlue); if (radioButtonAllAssets.Checked) { int skipSize = 0; int batchSize = 1000; int currentBatch = 0; int total = _context.Assets.Count(); int index2 = 1; while (true) { IQueryable _assetsCollectionQuery = _context.Assets.Skip(skipSize).Take(batchSize); foreach (IAsset asset in _assetsCollectionQuery) { row++; currentBatch++; ExportAssetExcel(asset, xlWorkSheet, row, detailed, checkBoxLocalTime.Checked); backgroundWorker1.ReportProgress(100 * index2 / total, DateTime.Now); //notify progress to main thread. We also pass time information in UserState to cover this property in the example. //if cancellation is pending, cancel work. if (backgroundWorker1.CancellationPending) { xlApp.DisplayAlerts = false; xlWorkBook.Close(); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); e.Cancel = true; return; } index2++; } if (currentBatch == batchSize) { skipSize += batchSize; currentBatch = 0; } else { break; } } } else // Selected or visible asets { IEnumerable<IAsset> myassets; if (radioButtonSelectedAssets.Checked) { myassets = _selassets; } else { myassets = _visibleassets; } int total = myassets.Count(); int index3 = 1; foreach (IAsset asset in myassets) { row++; ExportAssetExcel(asset, xlWorkSheet, row, detailed, checkBoxLocalTime.Checked); backgroundWorker1.ReportProgress(100 * index3 / total, DateTime.Now); //notify progress to main thread. We also pass time information in UserState to cover this property in the example. //if cancellation is pending, cancel work. if (backgroundWorker1.CancellationPending) { xlApp.DisplayAlerts = false; xlWorkBook.Close(); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); e.Cancel = true; return; } index3++; } } // Set the range to fill. var aRange = xlWorkSheet.get_Range("A4", "Z100"); aRange.EntireColumn.AutoFit(); try { xlWorkBook.SaveAs(filename, Excel.XlFileFormat.xlWorkbookDefault, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); } catch { MessageBox.Show("Error when saving the Excel file.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } if (checkBoxOpenFileAfterExport.Checked) System.Diagnostics.Process.Start(filename); }
private void btnToExcel_Click(object sender, EventArgs e) { Excel.Application excel = null; try { //DataGridView沒有資料就不執行 if (dgvDataShow.Rows.Count <= 1) { MessageBox.Show("沒有可滙出的資料!", "訊息", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } //設定滙出後的存檔路徑(儲存在桌面) string SaveFilePath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + @"\即將到期用戶名單" + ".xls"; //new 出一個Excel excel = new Microsoft.Office.Interop.Excel.Application(); //看的到Excel在工作 excel.Visible = false; //新增加一工作簿 excel.Application.Workbooks.Add(true); PGB pgb = new PGB(); pgb.progressBar1.Value = 0; pgb.progressBar1.Minimum = 0; pgb.progressBar1.Maximum = dgvDataShow.Rows.Count - 1; pgb.progressBar1.Step = 1; pgb.Show(); //寫入欄位名稱 for (int i = 0; i < dgvDataShow.Columns.Count; i++) { excel.Cells[1, i + 1] = dgvDataShow.Columns[i].HeaderText; } //把DataGridView資料寫到Excel for (int i = 0; i < dgvDataShow.Rows.Count - 1; i++) { pgb.progressBar1.Value++; Application.DoEvents(); for (int j = 0; j < dgvDataShow.Columns.Count; j++) { if (dgvDataShow[j, i].ValueType == typeof(string)) { excel.Cells[i + 2, j + 1] = "'" + dgvDataShow[j, i].Value.ToString(); } else { excel.Cells[i + 2, j + 1] = dgvDataShow[j, i].Value.ToString(); } } } //設定滙出後,欄位寛度自動配合資料調整 excel.Cells.EntireRow.AutoFit(); //自動調整列高 excel.Cells.EntireColumn.AutoFit(); //設置禁止彈出覆蓋或儲存的彈跳視窗 excel.DisplayAlerts = false; excel.AlertBeforeOverwriting = false; //將檔案儲存到SaveFile指定的位置 excel.ActiveWorkbook.SaveCopyAs(SaveFilePath); if (excel.Application.Version == "11.0")//Office 2007 up { excel.ActiveWorkbook.SaveAs(SaveFilePath); } else { //Office 2003 Up,FileFormat: Excel.XlFileFormat.xlExcel8=>指定Excel 2003 xls格式 excel.ActiveWorkbook.SaveAs(SaveFilePath, FileFormat: Excel.XlFileFormat.xlExcel8); } pgb.Dispose(); MessageBox.Show("已成功滙出Excel檔!" + Environment.NewLine + "檔案儲存在您電腦的桌面,檔名:即將到期用戶名單" + ".xls", "完成", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show(ex.Message); } //關閉工作簿和結束Excel程式 excel.Workbooks.Close(); excel.Quit(); //釋放資源 System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); excel = null; GC.Collect(); }
private void button1_Click(object sender, EventArgs e) { string strPath = System.Windows.Forms.Application.StartupPath + "\\Exported File\\List of ID.xlsx"; Excel._Application xl = new Microsoft.Office.Interop.Excel.Application(); Excel._Workbook xb = xl.Workbooks.Open(strPath); Excel._Worksheet xs; xs = xb.Worksheets["Sheet1"]; int irows = 3; for (int r = 0; r <= (gvReport.Rows.Count - 1); r++) { System.Windows.Forms.Application.DoEvents(); if (irows > 3) { //xlApp.Rows.get_Range("2:2").Select(); xl.Rows["3:3"].Select(); xl.Application.CutCopyMode = Excel.XlCutCopyMode.xlCopy; xl.Selection.Copy(); xl.Rows[irows + ":" + irows].Select(); xl.Selection.Insert(Shift: -4121); } xl.Cells[irows, 1] = gvReport.Rows[r].Cells["Nickname"].Value; xl.Cells[irows, 2] = gvReport.Rows[r].Cells["LastName"].Value; xl.Cells[irows, 3] = gvReport.Rows[r].Cells["FirstName"].Value; xl.Cells[irows, 4] = gvReport.Rows[r].Cells["StudentID"].Value; xl.Cells[irows, 5] = gvReport.Rows[r].Cells["Birthday"].Value; xl.Cells[irows, 6] = gvReport.Rows[r].Cells["Age"].Value; xl.Cells[irows, 7] = gvReport.Rows[r].Cells["Group"].Value; irows += 1; //while (pbExport.Value != pbExport.Maximum) //{ // pbExport.Value += 1; //} } //xl.Visible = true; //xl.ActiveWindow.SelectedSheets.PrintPreview(); string path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop); string filename = path + "\\" + "ID - " + DateTime.Now.ToString("(MM.dd.yyyy.hh_mm_ss)") + ".xlsx"; xl.ActiveWorkbook.SaveAs(filename); xl.DisplayAlerts = false; xb.Close(); xl.Quit(); foreach (Process proc in Process.GetProcessesByName("EXCEL")) { proc.Kill(); } MessageBox.Show("The File Has Been Saved : \n" + filename, "File Export", MessageBoxButtons.OK, MessageBoxIcon.Information); //pbExport.Value = 0; //gbExport.Visible = false; }
private void PerformBuildRequirements() { _excel = new Excel.Application(); _word = new Word.Application(); _word.Visible = true; _excel.Visible = true; this.BeginInvoke( new Action<Form1>(s => { btnBuild.Enabled = false; btnBuild.Text = "Working"; }), new object[] { this }); var spreadsheetPath = tbSpreadsheetPath.Text; if (File.Exists(spreadsheetPath)) { var workbook = _excel.Workbooks.Open(spreadsheetPath); var requirements = new List<Requirement>(); Word.Document doc = BuildRequirementsDoc(workbook, out requirements); if (cbBuildTrace.Enabled) { BuildTraceMatrix(workbook, requirements); try { workbook.Save(); } catch (Exception) { } } try { // This call throws when the user elects to 'cancel' the save operation. doc.Save(); } catch (Exception) { } } else { MessageBox.Show("No spreadsheet selected."); } foreach (Excel.Workbook wb in _excel.Workbooks) { wb.Close(); } _excel.Quit(); _word.Quit(ref Missing, ref Missing, ref Missing); this.BeginInvoke( new Action<Form1>(s => { btnBuild.Enabled = true; btnBuild.Text = "Build"; }), new object[] { this }); }
/// <summary> /// 64位系统,把Excel里的数据转换为DataTable,应用引用的com组件:Microsoft.Office.Interop.Excel.dll 读取EXCEL文件 /// </summary> /// <param name="filenameurl">文件路径</param> /// <param name="sheetIndex">sheet名称的索引</param> /// <param name="splitstr"></param> /// <returns></returns> public static DataTable ExecleToDataSet(string filenameurl, string sheetName, string splitstr) { Microsoft.Office.Interop.Excel.Workbook wb = null; Microsoft.Office.Interop.Excel.Worksheet ws = null; DataTable xlsTable = new DataTable(); object missing = System.Reflection.Missing.Value; //lauch excel application XLog.Write("lauch excel application"); Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); try { if (excel != null) { excel.Visible = false; excel.UserControl = true; // 以只读的形式打开EXCEL文件 wb = excel.Workbooks.Open(filenameurl, missing, true, missing, missing, missing, missing, missing, missing, true, missing, missing, missing, missing, missing); //取得第一个工作薄 ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[sheetName];//.get_Item(sheetIndex); //取得总记录行数(包括标题列) int rowsint = ws.UsedRange.Cells.Rows.Count; //得到行数 int columnsint = ws.UsedRange.Cells.Columns.Count;//得到列数 for (int row = 1; row <= rowsint; row++) { if (row == 1) { //添加列字段 for (int n = 1; n <= columnsint; n++) { string column = (((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, n]).Text.ToString()); xlsTable.Columns.Add(column, typeof(string)); } } else { //添加行数据 DataRow dr = xlsTable.NewRow(); for (int col = 1; col <= columnsint; col++) { dr[col - 1] = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[row, col]).Text.ToString(); } xlsTable.Rows.Add(dr); } } } return xlsTable; } catch (Exception e) { XLog.Write(e.Message); return xlsTable; } finally { wb.Close(false, missing, missing); System.Runtime.InteropServices.Marshal.ReleaseComObject(wb); wb = null; excel.Workbooks.Close(); excel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); excel = null; GC.Collect(); GC.WaitForPendingFinalizers(); } }
private void Read(string path, int sno, string engt_id, string ps_wkno) { Microsoft.Office.Interop.Excel.Application xlApp = null; Excel.Workbook xlWorkbook = null; Excel.Sheets xlSheets = null; Excel.Worksheet xlNewSheet = null; Excel.Worksheet xlscvsheet = null; try { #region initial xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) return; xlWorkbook = xlApp.Workbooks.Open(path, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); int boindex = 0, sumindex = 0, cvrindex = 0; for (int l = 1; l <= xlWorkbook.Sheets.Count; l++) { if (xlWorkbook.Sheets[l].Name.ToString().Trim().Replace(" ", "").ToLower() == "boxoffice") { boindex = l; } if (xlWorkbook.Sheets[l].Name.ToString().Trim().Replace(" ", "").ToLower() == "coversheet") { cvrindex = l; } if (xlWorkbook.Sheets[l].Name.ToString().Trim().Replace(" ", "").ToLower() == "summary") { sumindex = l; } } xlSheets = xlWorkbook.Sheets as Excel.Sheets; xlNewSheet = (Excel.Worksheet)xlSheets[boindex]; xlNewSheet.Unprotect("7135"); Excel.Worksheet excelsheet3 = null; excelsheet3 = (Excel.Worksheet)xlSheets[sumindex]; DataTable dt = new DataTable("table1"); DataTable dt1 = new DataTable("table2"); DataTable dtcvr = new DataTable("tablecvr"); DataTable dtchr = new DataTable("tablecvrchg"); string filename = xlNewSheet.Name; #endregion #region getrecordid xlscvsheet = (Excel.Worksheet)xlSheets[cvrindex]; string shname = "", city = "", recid = engt_id; DateTime opdate, endate; shname = xlscvsheet.Cells[1, 1].Text; city = xlscvsheet.Cells[3, 2].Text; opdate = Convert.ToDateTime(xlscvsheet.Cells[8, 1].Text); endate = Convert.ToDateTime(xlscvsheet.Cells[8, 2].Text); xlNewSheet.Cells[sno, 14] = shname; xlNewSheet.Cells[sno, 15] = city; xlNewSheet.Cells[sno, 16] = opdate; xlNewSheet.Cells[sno, 17] = endate; MasterDataLayer.MasterData objmst = new MasterDataLayer.MasterData(); //DataTable dt_id = new DataTable(); //dt_id = objmst.GetMysql_Recordid(shname, city, opdate, endate); //if (dt_id.Rows.Count > 0) //{ // recid = dt_id.Rows[0]["RecordID"].ToString(); //} //else { recid = "7604"; } #endregion #region engt dt creation dt.Columns.Add("Sno"); dt.Columns.Add("Recordid"); dt.Columns.Add("Show Name"); dt.Columns.Add("City Name"); dt.Columns.Add("Engt_Date"); dt.Columns.Add("Deal_Tax_Ptg"); dt.Columns.Add("Deal_Tax2_Ptg"); dt.Columns.Add("deal_sub_sales_comm"); dt.Columns.Add("deal_ph_sales_comm"); dt.Columns.Add("deal_web_sales_comm"); dt.Columns.Add("deal_cc_sales_comm"); dt.Columns.Add("deal_remote_sales_comm"); dt.Columns.Add("deal_single_tix_comm"); dt.Columns.Add("deal_grp_sales_comm1"); dt.Columns.Add("deal_grp_sales_comm2"); dt.Columns.Add("deal_misc_othr_amt_1"); dt.Columns.Add("deal_misc_othr_amt_2"); dt.Columns.Add("deal_royalty_income"); dt.Columns.Add("deal_incm_wthd_tax_act_amt"); dt.Columns.Add("deal_guarantee_income"); dt.Columns.Add("deal_cmpny_mid_monies_ptg"); dt.Columns.Add("deal_producer_share_split_ptg"); dt.Columns.Add("deal_star_royalty_ptg"); dt.Columns.Add("deal_presenter_share_split_Ptg"); dt.Columns.Add("exp_d_ad_gross_bgt"); dt.Columns.Add("exp_d_ad_gross_act"); dt.Columns.Add("exp_d_stghand_loadin_bgt"); dt.Columns.Add("exp_d_stghand_loadin_act"); dt.Columns.Add("exp_d_stghand_loadout_bgt"); dt.Columns.Add("exp_d_stghand_loadout_act"); dt.Columns.Add("exp_d_stghand_running_bgt"); dt.Columns.Add("exp_d_stghand_running_act"); dt.Columns.Add("exp_d_wardrobe_loadin_bgt"); dt.Columns.Add("exp_d_wardrobe_loadin_act"); dt.Columns.Add("exp_d_wardrobe_loadout_bgt"); dt.Columns.Add("exp_d_wardrobe_loadout_act"); dt.Columns.Add("exp_d_wardrobe_running_bgt"); dt.Columns.Add("exp_d_wardrobe_running_act"); dt.Columns.Add("exp_d_labor_catering_bgt"); dt.Columns.Add("exp_d_labor_catering_act"); dt.Columns.Add("exp_d_musician_bgt"); dt.Columns.Add("exp_d_musician_act"); dt.Columns.Add("exp_d_insurance_per_unit"); dt.Columns.Add("exp_d_insurance_bgt"); dt.Columns.Add("exp_d_insurance_act"); dt.Columns.Add("exp_d_ticket_print_per_unit"); dt.Columns.Add("exp_d_ticket_print_bgt"); dt.Columns.Add("exp_d_ticket_print_act"); dt.Columns.Add("exp_d_other_1_desc"); dt.Columns.Add("exp_d_other_1_bgt"); dt.Columns.Add("exp_d_other_1_act"); dt.Columns.Add("exp_l_ada_expense_bgt"); dt.Columns.Add("exp_l_ada_expense_act"); dt.Columns.Add("exp_l_bo_bgt"); dt.Columns.Add("exp_l_bo_act"); dt.Columns.Add("exp_l_catering_bgt"); dt.Columns.Add("exp_l_catering_act"); dt.Columns.Add("exp_l_equip_rental_bgt"); dt.Columns.Add("exp_l_equip_rental_act"); dt.Columns.Add("exp_l_grp_sales_bgt"); dt.Columns.Add("exp_l_grp_sales_act"); dt.Columns.Add("exp_l_house_staff_bgt"); dt.Columns.Add("exp_l_house_staff_act"); dt.Columns.Add("exp_l_league_fee_bgt"); dt.Columns.Add("exp_l_league_fee_act"); dt.Columns.Add("exp_l_license_bgt"); dt.Columns.Add("exp_l_license_act"); dt.Columns.Add("exp_l_limo_bgt"); dt.Columns.Add("exp_l_limo_act"); dt.Columns.Add("exp_l_orchestra_sh_remove_bgt"); dt.Columns.Add("exp_l_orchestra_sh_remove_act"); dt.Columns.Add("exp_l_presenter_profit_bgt"); dt.Columns.Add("exp_l_presenter_profit_act"); dt.Columns.Add("exp_l_police_bgt"); dt.Columns.Add("exp_l_police_act"); dt.Columns.Add("exp_l_program_bgt"); dt.Columns.Add("exp_l_program_act"); dt.Columns.Add("exp_l_rent_btg"); dt.Columns.Add("exp_l_rent_act"); dt.Columns.Add("exp_l_sound_bgt"); dt.Columns.Add("exp_l_sound_act"); dt.Columns.Add("exp_l_ticket_print_bgt"); dt.Columns.Add("exp_l_ticket_print_act"); dt.Columns.Add("exp_l_phone_bgt"); dt.Columns.Add("exp_l_phone_act"); dt.Columns.Add("exp_l_dryice_bgt"); dt.Columns.Add("exp_l_dryice_act"); dt.Columns.Add("MISCELLANEOUS_bgt"); dt.Columns.Add("MISCELLANEOUS_act"); dt.Columns.Add("exp_l_other1_desc"); dt.Columns.Add("exp_l_other1_bgt"); dt.Columns.Add("exp_l_other1_act"); dt.Columns.Add("exp_l_local_fixed_bgt"); dt.Columns.Add("exp_l_local_fixed_act"); dt.Columns.Add("deal_facility_fee_amt"); dt.Columns.Add("engt_exchange_rate"); dt.Columns.Add("engt_subscription_amt"); dt.Columns.Add("deal_incm_wthd_tax_act_unit"); dt.Columns.Add("ps_schedule_weeks"); dt.Columns.Add("deal_misc_othr_unit_1"); dt.Columns.Add("deal_misc_othr_unit_2"); dt.Columns.Add("deal_presenter_mid_monies_ptg"); dt.Columns.Add("mny_remaining_mid_mny"); #endregion #region engt val assign string Show_Name = xlNewSheet.Cells[1, 1].text; string City_Name = xlNewSheet.Cells[2, 1].text; string Engt_Date = xlNewSheet.Cells[4, 1].text; string deal_tax_ptg = xlNewSheet.Cells[14, 2].text; string deal_tax2_ptg = xlNewSheet.Cells[15, 2].text; string deal_sub_sales_comm = xlNewSheet.Cells[16, 2].text; string deal_ph_sales_comm = xlNewSheet.Cells[17, 2].text; string deal_web_sales_comm = xlNewSheet.Cells[18, 2].text; string deal_cc_sales_comm = xlNewSheet.Cells[19, 2].text; string deal_remote_sales_comm = xlNewSheet.Cells[20, 2].text; string deal_single_tix_comm = xlNewSheet.Cells[21, 2].text; string deal_grp_sales_comm1 = xlNewSheet.Cells[22, 2].text; string deal_grp_sales_comm2 = xlNewSheet.Cells[23, 2].text; string deal_misc_othr_amt_1 = xlNewSheet.Cells[24, 2].text; string deal_misc_othr_amt_2 = "", deal_facility_fee_amt = ""; string fforother = xlNewSheet.Cells[25, 2].text; //if (fforother.ToLower() == "facility" == true) deal_misc_othr_amt_2 = xlNewSheet.Cells[25, 2].text; //else deal_facility_fee_amt = "0";// xlNewSheet.Cells[25, 2].text; string deal_misc_othr_unit_1 = (deal_misc_othr_amt_1.Contains("%") == true) ? "%" : "$"; string deal_misc_othr_unit_2 = (fforother.Contains("%") == true) ? "%" : "$"; string deal_royalty_income = excelsheet3.Cells[31, 2].text; string deal_incm_wthd_tax_act_amt = excelsheet3.Cells[32, 2].text; string deal_incm_wthd_tax_act_unit = (deal_incm_wthd_tax_act_amt.Contains("%") == true) ? "%" : "$"; string deal_guarantee_income = excelsheet3.Cells[34, 2].text; string deal_cmpny_mid_monies_ptg = excelsheet3.Cells[83, 2].text; string deal_producer_share_split_ptg = excelsheet3.Cells[90, 2].text; string deal_star_royalty_ptg = excelsheet3.Cells[93, 2].text; string deal_presenter_share_split_Ptg = excelsheet3.Cells[96, 2].text; string exp_d_ad_gross_bgt = excelsheet3.Cells[42, 3].text; string exp_d_ad_gross_act = excelsheet3.Cells[42, 4].text; string exp_d_stghand_loadin_bgt = excelsheet3.Cells[43, 3].text; string exp_d_stghand_loadin_act = excelsheet3.Cells[43, 4].text; string exp_d_stghand_loadout_bgt = excelsheet3.Cells[44, 3].text; string exp_d_stghand_loadout_act = excelsheet3.Cells[44, 4].text; string exp_d_stghand_running_bgt = excelsheet3.Cells[45, 3].text; string exp_d_stghand_running_act = excelsheet3.Cells[45, 4].text; string exp_d_wardrobe_loadin_bgt = excelsheet3.Cells[46, 3].text; string exp_d_wardrobe_loadin_act = excelsheet3.Cells[46, 4].text; string exp_d_wardrobe_loadout_bgt = excelsheet3.Cells[47, 3].text; string exp_d_wardrobe_loadout_act = excelsheet3.Cells[47, 4].text; string exp_d_wardrobe_running_bgt = excelsheet3.Cells[48, 3].text; string exp_d_wardrobe_running_act = excelsheet3.Cells[48, 4].text; string exp_d_labor_catering_bgt = excelsheet3.Cells[49, 3].text; string exp_d_labor_catering_act = excelsheet3.Cells[49, 4].text; string exp_d_musician_bgt = excelsheet3.Cells[50, 3].text; string exp_d_musician_act = excelsheet3.Cells[50, 4].text; string exp_d_insurance_per_unit = excelsheet3.Cells[51, 2].text; string exp_d_insurance_bgt = excelsheet3.Cells[51, 3].text; string exp_d_insurance_act = excelsheet3.Cells[51, 4].text; string exp_d_ticket_print_per_unit = excelsheet3.Cells[52, 2].text; string exp_d_ticket_print_bgt = excelsheet3.Cells[52, 3].text; string exp_d_ticket_print_act = excelsheet3.Cells[52, 4].text; string exp_d_other_1_desc = excelsheet3.Cells[53, 2].text; string exp_d_other_1_bgt = excelsheet3.Cells[53, 3].text; string exp_d_other_1_act = excelsheet3.Cells[53, 4].text; string exp_l_ada_expense_bgt = excelsheet3.Cells[56, 3].text; string exp_l_ada_expense_act = excelsheet3.Cells[56, 4].text; string exp_l_bo_bgt = excelsheet3.Cells[57, 3].text; string exp_l_bo_act = excelsheet3.Cells[57, 4].text; string exp_l_catering_bgt = excelsheet3.Cells[58, 3].text; string exp_l_catering_act = excelsheet3.Cells[58, 4].text; string exp_l_equip_rental_bgt = excelsheet3.Cells[59, 3].text; string exp_l_equip_rental_act = excelsheet3.Cells[59, 4].text; string exp_l_grp_sales_bgt = excelsheet3.Cells[60, 3].text; string exp_l_grp_sales_act = excelsheet3.Cells[60, 4].text; string exp_l_house_staff_bgt = excelsheet3.Cells[61, 3].text; string exp_l_house_staff_act = excelsheet3.Cells[61, 4].text; string exp_l_league_fee_bgt = excelsheet3.Cells[62, 3].text; string exp_l_league_fee_act = excelsheet3.Cells[62, 4].text; string exp_l_license_bgt = excelsheet3.Cells[63, 3].text; string exp_l_license_act = excelsheet3.Cells[63, 4].text; string exp_l_limo_bgt = excelsheet3.Cells[64, 3].text; string exp_l_limo_act = excelsheet3.Cells[64, 4].text; string exp_l_orchestra_sh_remove_bgt = excelsheet3.Cells[65, 3].text; string exp_l_orchestra_sh_remove_act = excelsheet3.Cells[65, 4].text; string exp_l_presenter_profit_bgt = excelsheet3.Cells[66, 3].text; string exp_l_presenter_profit_act = excelsheet3.Cells[66, 4].text; string exp_l_police_bgt = excelsheet3.Cells[67, 3].text; string exp_l_police_act = excelsheet3.Cells[67, 4].text; string exp_l_program_bgt = excelsheet3.Cells[68, 3].text; string exp_l_program_act = excelsheet3.Cells[68, 4].text; string exp_l_rent_btg = excelsheet3.Cells[69, 3].text; string exp_l_rent_act = excelsheet3.Cells[69, 4].text; string exp_l_sound_bgt = excelsheet3.Cells[70, 3].text; string exp_l_sound_act = excelsheet3.Cells[70, 4].text; string exp_l_ticket_print_bgt = excelsheet3.Cells[71, 3].text; string exp_l_ticket_print_act = excelsheet3.Cells[71, 4].text; string exp_l_phone_bgt = excelsheet3.Cells[72, 3].text; string exp_l_phone_act = excelsheet3.Cells[72, 4].text; string exp_l_dryice_bgt = excelsheet3.Cells[73, 3].text; string exp_l_dryice_act = excelsheet3.Cells[73, 4].text; string misc1 = excelsheet3.Cells[74, 3].text; string misc2 = excelsheet3.Cells[74, 4].text; string exp_l_other1_desc = excelsheet3.Cells[75, 2].text; string exp_l_other1_bgt = excelsheet3.Cells[75, 3].text; string exp_l_other1_act = excelsheet3.Cells[75, 4].text; string exp_l_local_fixed_bgt = excelsheet3.Cells[76, 3].text; string exp_l_local_fixed_act = excelsheet3.Cells[76, 4].text; // string deal_facility_fee_amt = xlNewSheet.Cells[25, 2].text; string engt_exchange_rate = excelsheet3.Cells[3, 7].text; string engt_subscription_amt = (string.IsNullOrEmpty(xlNewSheet.Cells[32, 15].text) == true) ? xlNewSheet.Cells[32, 14].text : xlNewSheet.Cells[32, 15].text; string deal_presenter_mid_monies_ptg = excelsheet3.Cells[83, 4].text; string mny_remaining_mid_mny = excelsheet3.Cells[81, 5].text; #endregion #region engt row add dt.Rows.Add(sno, recid, Show_Name, City_Name, Engt_Date, deal_tax_ptg.AutoformatDecimal(), deal_tax2_ptg.AutoformatDecimal(), deal_sub_sales_comm.AutoformatDecimal(), deal_ph_sales_comm.AutoformatDecimal(), deal_web_sales_comm.AutoformatDecimal(), deal_cc_sales_comm.AutoformatDecimal(), deal_remote_sales_comm.AutoformatDecimal(), deal_single_tix_comm.AutoformatDecimal(), deal_grp_sales_comm1.AutoformatDecimal(), deal_grp_sales_comm2.AutoformatDecimal(), deal_misc_othr_amt_1.AutoformatDecimal(), deal_misc_othr_amt_2.AutoformatDecimal(), deal_royalty_income.AutoformatDecimal(), deal_incm_wthd_tax_act_amt.AutoformatDecimal(), deal_guarantee_income.AutoformatDecimal(), deal_cmpny_mid_monies_ptg.AutoformatDecimal(), deal_producer_share_split_ptg.AutoformatDecimal(), deal_star_royalty_ptg.AutoformatDecimal(), deal_presenter_share_split_Ptg.AutoformatDecimal(), exp_d_ad_gross_bgt.AutoformatDecimal(), exp_d_ad_gross_act.AutoformatDecimal(), exp_d_stghand_loadin_bgt.AutoformatDecimal(), exp_d_stghand_loadin_act.AutoformatDecimal(), exp_d_stghand_loadout_bgt.AutoformatDecimal(), exp_d_stghand_loadout_act.AutoformatDecimal(), exp_d_stghand_running_bgt.AutoformatDecimal(), exp_d_stghand_running_act.AutoformatDecimal(), exp_d_wardrobe_loadin_bgt.AutoformatDecimal(), exp_d_wardrobe_loadin_act.AutoformatDecimal(), exp_d_wardrobe_loadout_bgt.AutoformatDecimal(), exp_d_wardrobe_loadout_act.AutoformatDecimal(), exp_d_wardrobe_running_bgt.AutoformatDecimal(), exp_d_wardrobe_running_act.AutoformatDecimal(), exp_d_labor_catering_bgt.AutoformatDecimal(), exp_d_labor_catering_act.AutoformatDecimal(), exp_d_musician_bgt.AutoformatDecimal(), exp_d_musician_act.AutoformatDecimal(), exp_d_insurance_per_unit.AutoformatDecimal(), exp_d_insurance_bgt.AutoformatDecimal(), exp_d_insurance_act.AutoformatDecimal(), exp_d_ticket_print_per_unit.AutoformatDecimal(), exp_d_ticket_print_bgt.AutoformatDecimal(), exp_d_ticket_print_act.AutoformatDecimal(), exp_d_other_1_desc.AutoformatDecimal(), exp_d_other_1_bgt.AutoformatDecimal(), exp_d_other_1_act.AutoformatDecimal(), exp_l_ada_expense_bgt.AutoformatDecimal(), exp_l_ada_expense_act.AutoformatDecimal(), exp_l_bo_bgt.AutoformatDecimal(), exp_l_bo_act.AutoformatDecimal(), exp_l_catering_bgt.AutoformatDecimal(), exp_l_catering_act.AutoformatDecimal(), exp_l_equip_rental_bgt.AutoformatDecimal(), exp_l_equip_rental_act.AutoformatDecimal(), exp_l_grp_sales_bgt.AutoformatDecimal(), exp_l_grp_sales_act.AutoformatDecimal(), exp_l_house_staff_bgt.AutoformatDecimal(), exp_l_house_staff_act.AutoformatDecimal(), exp_l_league_fee_bgt.AutoformatDecimal(), exp_l_league_fee_act.AutoformatDecimal(), exp_l_license_bgt.AutoformatDecimal(), exp_l_license_act.AutoformatDecimal(), exp_l_limo_bgt.AutoformatDecimal(), exp_l_limo_act.AutoformatDecimal(), exp_l_orchestra_sh_remove_bgt.AutoformatDecimal(), exp_l_orchestra_sh_remove_act.AutoformatDecimal(), exp_l_presenter_profit_bgt.AutoformatDecimal(), exp_l_presenter_profit_act.AutoformatDecimal(), exp_l_police_bgt.AutoformatDecimal(), exp_l_police_act.AutoformatDecimal(), exp_l_program_bgt.AutoformatDecimal(), exp_l_program_act.AutoformatDecimal(), exp_l_rent_btg.AutoformatDecimal(), exp_l_rent_act.AutoformatDecimal(), exp_l_sound_bgt.AutoformatDecimal(), exp_l_sound_act.AutoformatDecimal(), exp_l_ticket_print_bgt.AutoformatDecimal(), exp_l_ticket_print_act.AutoformatDecimal(), exp_l_phone_bgt.AutoformatDecimal(), exp_l_phone_act.AutoformatDecimal(), exp_l_dryice_bgt.AutoformatDecimal(), exp_l_dryice_act.AutoformatDecimal(), misc1.AutoformatDecimal(), misc2.AutoformatDecimal(), exp_l_other1_desc.AutoformatDecimal(), exp_l_other1_bgt.AutoformatDecimal(), exp_l_other1_act.AutoformatDecimal(), exp_l_local_fixed_bgt.AutoformatDecimal(), exp_l_local_fixed_act.AutoformatDecimal(), deal_facility_fee_amt.AutoformatDecimal(), engt_exchange_rate.AutoformatDecimal(), engt_subscription_amt.AutoformatDecimal(), deal_incm_wthd_tax_act_unit, ps_wkno, deal_misc_othr_unit_1, deal_misc_othr_unit_2, deal_presenter_mid_monies_ptg.AutoformatDecimal(), mny_remaining_mid_mny.AutoformatDecimal() ); #endregion #region schedule dt creation dt1.Columns.Add("Sno"); dt1.Columns.Add("Recordid"); dt1.Columns.Add("Schedule_Type"); dt1.Columns.Add("schedule_date"); dt1.Columns.Add("schedule_st_time"); dt1.Columns.Add("bo_drop_count"); dt1.Columns.Add("bo_paid_attendance"); dt1.Columns.Add("bo_comps"); dt1.Columns.Add("bo_gross_sales"); dt1.Columns.Add("bo_sub_gross_rcpt"); dt1.Columns.Add("bo_ph_gross_rcpt"); dt1.Columns.Add("bo_web_gross_rcpt"); dt1.Columns.Add("bo_cc_gross_rcpt"); dt1.Columns.Add("bo_outlet_gross_rcpt"); dt1.Columns.Add("bo_single_tix_gross_rcpt"); dt1.Columns.Add("bo_small_group_gross_rcpt"); dt1.Columns.Add("bo_large_group_gross_rcpt"); dt1.Columns.Add("bo_other_per_gross_rcpt"); dt1.Columns.Add("bo_other_usd_gross_rcpt"); dt1.Columns.Add("bo_sub_t_sold"); dt1.Columns.Add("bo_ph_t_sold"); dt1.Columns.Add("bo_web_t_sold"); dt1.Columns.Add("bo_cc_t_sold"); dt1.Columns.Add("bo_outlet_t_sold"); dt1.Columns.Add("bo_single_tix_t_sold"); dt1.Columns.Add("bo_small_group_t_sold"); dt1.Columns.Add("dsct_sub1_per"); dt1.Columns.Add("dsct_sub1_tickets"); dt1.Columns.Add("dsct_sub2_per"); dt1.Columns.Add("dsct_sub2_tickets"); dt1.Columns.Add("dsct_sub3_per"); dt1.Columns.Add("dsct_sub3_tickets"); dt1.Columns.Add("dsct_sub4_per"); dt1.Columns.Add("dsct_sub4_tickets"); dt1.Columns.Add("dsct_sub5_per"); dt1.Columns.Add("dsct_sub5_tickets"); dt1.Columns.Add("dsct_sub6_per"); dt1.Columns.Add("dsct_sub6_tickets"); dt1.Columns.Add("dsct_sml_grp_per"); dt1.Columns.Add("dsct_sml_grp_tickets"); dt1.Columns.Add("dsct_lrg_grp_per"); dt1.Columns.Add("dsct_lrg_grp_tickets"); dt1.Columns.Add("dsct_misc1_per"); dt1.Columns.Add("dsct_misc1_tickets"); dt1.Columns.Add("dsct_misc2_per"); dt1.Columns.Add("dsct_misc2_tickets"); dt1.Columns.Add("dsct_misc3_per"); dt1.Columns.Add("dsct_misc3_tickets"); dt1.Columns.Add("dsct_misc4_per"); dt1.Columns.Add("dsct_misc4_tickets"); dt1.Columns.Add("dsct_demand_price"); dt1.Columns.Add("Scale1 ticket"); dt1.Columns.Add("Scale2 ticket"); dt1.Columns.Add("Scale3 ticket"); dt1.Columns.Add("Scale4 ticket"); dt1.Columns.Add("Scale5 ticket"); dt1.Columns.Add("Scale6 ticket"); dt1.Columns.Add("Scale7 ticket"); dt1.Columns.Add("Scale8 ticket"); dt1.Columns.Add("Scale9 ticket"); dt1.Columns.Add("Scale10 ticket"); dt1.Columns.Add("Scale11 ticket"); dt1.Columns.Add("Scale1 price"); dt1.Columns.Add("Scale2 price"); dt1.Columns.Add("Scale3 price"); dt1.Columns.Add("Scale4 price"); dt1.Columns.Add("Scale5 price"); dt1.Columns.Add("Scale6 price"); dt1.Columns.Add("Scale7 price"); dt1.Columns.Add("Scale8 price"); dt1.Columns.Add("Scale9 price"); dt1.Columns.Add("Scale10 price"); dt1.Columns.Add("Scale11 price"); dt1.Columns.Add("dsct_misc5_per"); dt1.Columns.Add("dsct_misc5_tickets"); #endregion #region insert schedule using for loop string stype = "", perday = "", perday_p = ""; bool perflag; DateTime opdateinc = opdate, Tdate = opdate; for (int i = 3; i <= 12; i++) { stype = Convert.ToString(xlNewSheet.Cells[1, i].value); perflag = stype.ToLower().Contains("perf"); perday = Convert.ToString(xlNewSheet.Cells[2, i].value).ToLower(); perday = (perday.Length > 3) ? perday.Substring(0, 3) : perday; opdateinc = (perday_p == perday || i == 3) ? Tdate : opdateinc.AddDays(1); perday_p = perday; stype = (perday != "day" && perflag == true) ? Convert.ToString(opdateinc) : stype; perflag = stype.ToLower().Contains("perf"); if (perflag == false) { //dt1.Rows.Add("Performance " + (i - 2).ToString(), // stype, // xlNewSheet.Cells[3, i].text, Tdate = Convert.ToDateTime(stype); string bo_drop_count = xlNewSheet.Cells[6, i].text; string bo_paid_attendance = xlNewSheet.Cells[7, i].text; string bo_comps = xlNewSheet.Cells[8, i].text; string bo_gross_sales = xlNewSheet.Cells[13, i].text; string bo_sub_gross_rcpt = xlNewSheet.Cells[32, i].text; string bo_ph_gross_rcpt = xlNewSheet.Cells[33, i].text; string bo_web_gross_rcpt = xlNewSheet.Cells[34, i].text; string bo_cc_gross_rcpt = xlNewSheet.Cells[35, i].text; string bo_outlet_gross_rcpt = xlNewSheet.Cells[36, i].text; string bo_single_tix_gross_rcpt = xlNewSheet.Cells[37, i].text; string bo_small_group_gross_rcpt = xlNewSheet.Cells[38, i].text; string bo_large_group_gross_rcpt = xlNewSheet.Cells[39, i].text; string bo_other_per_gross_rcpt = xlNewSheet.Cells[40, i].text; string bo_other_usd_gross_rcpt = xlNewSheet.Cells[41, i].text; string bo_sub_t_sold = xlNewSheet.Cells[45, i].text; string bo_ph_t_sold = xlNewSheet.Cells[46, i].text; string bo_web_t_sold = xlNewSheet.Cells[47, i].text; string bo_cc_t_sold = xlNewSheet.Cells[48, i].text; string bo_outlet_t_sold = xlNewSheet.Cells[49, i].text; string bo_single_tix_t_sold = xlNewSheet.Cells[50, i].text; string bo_small_group_t_sold = xlNewSheet.Cells[51, i].text; string dsct_sub1_per = xlNewSheet.Cells[58, i].text; string dsct_sub1_tickets = xlNewSheet.Cells[59, i].text; string dsct_sub2_per = xlNewSheet.Cells[60, i].text; string dsct_sub2_tickets = xlNewSheet.Cells[61, i].text; string dsct_sub3_per = xlNewSheet.Cells[62, i].text; string dsct_sub3_tickets = xlNewSheet.Cells[63, i].text; string dsct_sub4_per = xlNewSheet.Cells[64, i].text; string dsct_sub4_tickets = xlNewSheet.Cells[65, i].text; string dsct_sub5_per = xlNewSheet.Cells[66, i].text; string dsct_sub5_tickets = xlNewSheet.Cells[67, i].text; string dsct_sub6_per = xlNewSheet.Cells[68, i].text; string dsct_sub6_tickets = xlNewSheet.Cells[69, i].text; string dsct_sml_grp_per = xlNewSheet.Cells[72, i].text; string dsct_sml_grp_tickets = xlNewSheet.Cells[73, i].text; string dsct_lrg_grp_per = xlNewSheet.Cells[76, i].text; string dsct_lrg_grp_tickets = xlNewSheet.Cells[77, i].text; string dsct_misc1_per = xlNewSheet.Cells[80, i].text; string dsct_misc1_tickets = xlNewSheet.Cells[81, i].text; string dsct_misc2_per = xlNewSheet.Cells[82, i].text; string dsct_misc2_tickets = xlNewSheet.Cells[83, i].text; string dsct_misc3_per = xlNewSheet.Cells[84, i].text; string dsct_misc3_tickets = xlNewSheet.Cells[85, i].text; string dsct_misc4_per = xlNewSheet.Cells[86, i].text; string dsct_misc4_tickets = xlNewSheet.Cells[87, i].text; string dsct_misc5_per = xlNewSheet.Cells[88, i].text; string dsct_misc5_tickets = xlNewSheet.Cells[89, i].text; string dsct_demand_price = xlNewSheet.Cells[97, i].text; string Scale1ticket = xlNewSheet.Cells[100, 1].text; string Scale2ticket = xlNewSheet.Cells[101, 1].text; string Scale3ticket = xlNewSheet.Cells[102, 1].text; string Scale4ticket = xlNewSheet.Cells[103, 1].text; string Scale5ticket = xlNewSheet.Cells[104, 1].text; string Scale6ticket = xlNewSheet.Cells[105, 1].text; string Scale7ticket = xlNewSheet.Cells[106, 1].text; string Scale8ticket = xlNewSheet.Cells[107, 1].text; string Scale9ticket = xlNewSheet.Cells[108, 1].text; string Scale10ticket = xlNewSheet.Cells[109, 1].text; string Scale11ticket = xlNewSheet.Cells[110, 1].text; string Scale1price = xlNewSheet.Cells[100, i].text; string Scale2price = xlNewSheet.Cells[101, i].text; string Scale3price = xlNewSheet.Cells[102, i].text; string Scale4price = xlNewSheet.Cells[103, i].text; string Scale5price = xlNewSheet.Cells[104, i].text; string Scale6price = xlNewSheet.Cells[105, i].text; string Scale7price = xlNewSheet.Cells[106, i].text; string Scale8price = xlNewSheet.Cells[107, i].text; string Scale9price = xlNewSheet.Cells[108, i].text; string Scale10price = xlNewSheet.Cells[109, i].text; string Scale11price = xlNewSheet.Cells[110, i].text; #region test Nullable<decimal> d; Nullable<Int32> dd; dd = bo_drop_count.AutoformatInt(); dd = bo_paid_attendance.AutoformatInt(); d = bo_comps.AutoformatInt(); d = bo_gross_sales.AutoformatDecimal(); d = bo_sub_gross_rcpt.AutoformatDecimal(); d = bo_ph_gross_rcpt.AutoformatDecimal(); d = bo_web_gross_rcpt.AutoformatDecimal(); d = bo_cc_gross_rcpt.AutoformatDecimal(); d = bo_outlet_gross_rcpt.AutoformatDecimal(); d = bo_single_tix_gross_rcpt.AutoformatDecimal(); d = bo_small_group_gross_rcpt.AutoformatDecimal(); d = bo_large_group_gross_rcpt.AutoformatDecimal(); d = bo_other_per_gross_rcpt.AutoformatDecimal(); d = bo_other_usd_gross_rcpt.AutoformatDecimal(); d = bo_sub_t_sold.AutoformatInt(); d = bo_ph_t_sold.AutoformatInt(); d = bo_web_t_sold.AutoformatInt(); d = bo_cc_t_sold.AutoformatInt(); d = bo_outlet_t_sold.AutoformatInt(); d = bo_single_tix_t_sold.AutoformatInt(); d = bo_small_group_t_sold.AutoformatInt(); d = dsct_sub1_per.AutoformatDecimal(); d = dsct_sub1_tickets.AutoformatInt(); d = dsct_sub2_per.AutoformatDecimal(); d = dsct_sub2_tickets.AutoformatInt(); d = dsct_sub3_per.AutoformatDecimal(); d = dsct_sub3_tickets.AutoformatInt(); d = dsct_sub4_per.AutoformatDecimal(); d = dsct_sub4_tickets.AutoformatInt(); d = dsct_sub5_per.AutoformatDecimal(); d = dsct_sub5_tickets.AutoformatInt(); d = dsct_sub6_per.AutoformatDecimal(); d = dsct_sub6_tickets.AutoformatInt(); d = dsct_sml_grp_per.AutoformatDecimal(); d = dsct_sml_grp_tickets.AutoformatInt(); d = dsct_lrg_grp_per.AutoformatDecimal(); d = dsct_lrg_grp_tickets.AutoformatInt(); d = dsct_misc1_per.AutoformatDecimal(); d = dsct_misc1_tickets.AutoformatInt(); d = dsct_misc2_per.AutoformatDecimal(); d = dsct_misc2_tickets.AutoformatInt(); d = dsct_misc3_per.AutoformatDecimal(); d = dsct_misc3_tickets.AutoformatInt(); d = dsct_misc4_per.AutoformatDecimal(); d = dsct_misc4_tickets.AutoformatInt(); d = dsct_demand_price.AutoformatDecimal(); d = Scale1price.AutoformatDecimal(); d = Scale2price.AutoformatDecimal(); d = Scale3price.AutoformatDecimal(); d = Scale4price.AutoformatDecimal(); d = Scale5price.AutoformatDecimal(); d = Scale6price.AutoformatDecimal(); d = Scale7price.AutoformatDecimal(); d = Scale8price.AutoformatDecimal(); d = Scale9price.AutoformatDecimal(); d = Scale10price.AutoformatDecimal(); d = Scale11price.AutoformatDecimal(); d = dsct_misc5_per.AutoformatDecimal(); d = dsct_misc5_tickets.AutoformatDecimal(); #endregion dt1.Rows.Add(sno, recid, "Performance " + (i - 2).ToString(), stype, xlNewSheet.Cells[3, i].text, bo_drop_count.AutoformatInt(), bo_paid_attendance.AutoformatInt(), bo_comps.AutoformatInt(), bo_gross_sales.AutoformatDecimal(), bo_sub_gross_rcpt.AutoformatDecimal(), bo_ph_gross_rcpt.AutoformatDecimal(), bo_web_gross_rcpt.AutoformatDecimal(), bo_cc_gross_rcpt.AutoformatDecimal(), bo_outlet_gross_rcpt.AutoformatDecimal(), bo_single_tix_gross_rcpt.AutoformatDecimal(), bo_small_group_gross_rcpt.AutoformatDecimal(), bo_large_group_gross_rcpt.AutoformatDecimal(), bo_other_per_gross_rcpt.AutoformatDecimal(), bo_other_usd_gross_rcpt.AutoformatDecimal(), bo_sub_t_sold.AutoformatInt(), bo_ph_t_sold.AutoformatInt(), bo_web_t_sold.AutoformatInt(), bo_cc_t_sold.AutoformatInt(), bo_outlet_t_sold.AutoformatInt(), bo_single_tix_t_sold.AutoformatInt(), bo_small_group_t_sold.AutoformatInt(), dsct_sub1_per.AutoformatDecimal(), dsct_sub1_tickets.AutoformatInt(), dsct_sub2_per.AutoformatDecimal(), dsct_sub2_tickets.AutoformatInt(), dsct_sub3_per.AutoformatDecimal(), dsct_sub3_tickets.AutoformatInt(), dsct_sub4_per.AutoformatDecimal(), dsct_sub4_tickets.AutoformatInt(), dsct_sub5_per.AutoformatDecimal(), dsct_sub5_tickets.AutoformatInt(), dsct_sub6_per.AutoformatDecimal(), dsct_sub6_tickets.AutoformatInt(), dsct_sml_grp_per.AutoformatDecimal(), dsct_sml_grp_tickets.AutoformatInt(), dsct_lrg_grp_per.AutoformatDecimal(), dsct_lrg_grp_tickets.AutoformatInt(), dsct_misc1_per.AutoformatDecimal(), dsct_misc1_tickets.AutoformatInt(), dsct_misc2_per.AutoformatDecimal(), dsct_misc2_tickets.AutoformatInt(), dsct_misc3_per.AutoformatDecimal(), dsct_misc3_tickets.AutoformatInt(), dsct_misc4_per.AutoformatDecimal(), dsct_misc4_tickets.AutoformatInt(), dsct_demand_price.AutoformatDecimal(), ticketseats(Scale1ticket), ticketseats(Scale2ticket), ticketseats(Scale3ticket), ticketseats(Scale4ticket), ticketseats(Scale5ticket), ticketseats(Scale6ticket), ticketseats(Scale7ticket), ticketseats(Scale8ticket), ticketseats(Scale9ticket), ticketseats(Scale10ticket), ticketseats(Scale11ticket), Scale1price.AutoformatDecimal(), Scale2price.AutoformatDecimal(), Scale3price.AutoformatDecimal(), Scale4price.AutoformatDecimal(), Scale5price.AutoformatDecimal(), Scale6price.AutoformatDecimal(), Scale7price.AutoformatDecimal(), Scale8price.AutoformatDecimal(), Scale9price.AutoformatDecimal(), Scale10price.AutoformatDecimal(), Scale11price.AutoformatDecimal(), dsct_misc5_per.AutoformatDecimal(), dsct_misc5_tickets.AutoformatDecimal() ); } } #endregion #region Coversheet dt creation #region cvrdoc dtcvr.Columns.Add("cvr_docs_id"); dtcvr.Columns.Add("cvr_engt_id"); dtcvr.Columns.Add("cvr_s_cover_flag"); dtcvr.Columns.Add("cvr_grnty_flag"); dtcvr.Columns.Add("cvr_royalty_flag"); dtcvr.Columns.Add("cvr_ovrg_flag"); dtcvr.Columns.Add("cvr_s_summary_flag"); dtcvr.Columns.Add("cvr_venue_sett_flag"); dtcvr.Columns.Add("cvr_bo_sheet_flag"); dtcvr.Columns.Add("cvr_bo_statements_flag"); dtcvr.Columns.Add("cvr_lbr_bills_flag"); dtcvr.Columns.Add("cvr_musician_bills_flag"); dtcvr.Columns.Add("cvr_local_exp_invoice_flag"); dtcvr.Columns.Add("cvr_ad_flag"); dtcvr.Columns.Add("cvr_contact_flag"); dtcvr.Columns.Add("cvr_s_cover_notes"); dtcvr.Columns.Add("cvr_grnty_notes"); dtcvr.Columns.Add("cvr_royalty_notes"); dtcvr.Columns.Add("cvr_ovrg_notes"); dtcvr.Columns.Add("cvr_s_summary_notes"); dtcvr.Columns.Add("cvr_venue_sett_notes"); dtcvr.Columns.Add("cvr_bo_sheet_notes"); dtcvr.Columns.Add("cvr_bo_statements_notes"); dtcvr.Columns.Add("cvr_lbr_bills_notes"); dtcvr.Columns.Add("cvr_musician_bills_notes"); dtcvr.Columns.Add("cvr_local_exp_invoice_notes"); dtcvr.Columns.Add("cvr_ad_notes"); dtcvr.Columns.Add("cvr_contact_notes"); dtcvr.Columns.Add("email_list"); #endregion #region cvrchages and receivables dtchr.Columns.Add("cvr_chgs_id"); dtchr.Columns.Add("cvr_engt_id"); dtchr.Columns.Add("cvr_chgs_desc"); dtchr.Columns.Add("cvr_chgs_amt"); dtchr.Columns.Add("cvr_chgs_check"); dtchr.Columns.Add("cvr_chgs_notes"); dtchr.Columns.Add("cvr_type"); #endregion #endregion #region Assign value for coversheet #region cvrdoc_assign string cvr_s_cover_flag = "", cvr_grnty_flag = "", cvr_royalty_flag = "", cvr_ovrg_flag = "", cvr_s_summary_flag = "", cvr_venue_sett_flag = "", cvr_bo_sheet_flag = "", cvr_bo_statements_flag = "", cvr_lbr_bills_flag = "", cvr_musician_bills_flag = "", cvr_local_exp_invoice_flag = "", cvr_ad_flag = "", cvr_contact_flag = "", cvr_s_cover_notes = "", cvr_grnty_notes = "", cvr_royalty_notes = "", cvr_ovrg_notes = "", cvr_s_summary_notes = "", cvr_venue_sett_notes = "", cvr_bo_sheet_notes = "", cvr_bo_statements_notes = "", cvr_lbr_bills_notes = "", cvr_musician_bills_notes = "", cvr_local_exp_invoice_notes = "", cvr_ad_notes = "", cvr_contact_notes = "", email_list = ""; cvr_s_cover_flag = get_cvr_doc_flg(xlscvsheet.Cells[29, 4].text); cvr_grnty_flag = get_cvr_doc_flg(xlscvsheet.Cells[30, 4].text); cvr_royalty_flag = get_cvr_doc_flg(xlscvsheet.Cells[31, 4].text); cvr_ovrg_flag = get_cvr_doc_flg(xlscvsheet.Cells[32, 4].text); cvr_s_summary_flag = get_cvr_doc_flg(xlscvsheet.Cells[33, 4].text); cvr_venue_sett_flag = get_cvr_doc_flg(xlscvsheet.Cells[34, 4].text); cvr_bo_sheet_flag = get_cvr_doc_flg(xlscvsheet.Cells[35, 4].text); cvr_bo_statements_flag = get_cvr_doc_flg(xlscvsheet.Cells[36, 4].text); cvr_lbr_bills_flag = get_cvr_doc_flg(xlscvsheet.Cells[37, 4].text); cvr_musician_bills_flag = get_cvr_doc_flg(xlscvsheet.Cells[38, 4].text); cvr_local_exp_invoice_flag = get_cvr_doc_flg(xlscvsheet.Cells[39, 4].text); cvr_ad_flag = get_cvr_doc_flg(xlscvsheet.Cells[40, 4].text); cvr_contact_flag = get_cvr_doc_flg(xlscvsheet.Cells[41, 4].text); cvr_s_cover_notes = xlscvsheet.Cells[29, 5].text; cvr_grnty_notes = xlscvsheet.Cells[30, 5].text; cvr_royalty_notes = xlscvsheet.Cells[31, 5].text; cvr_ovrg_notes = xlscvsheet.Cells[32, 5].text; cvr_s_summary_notes = xlscvsheet.Cells[33, 5].text; cvr_venue_sett_notes = xlscvsheet.Cells[34, 5].text; cvr_bo_sheet_notes = xlscvsheet.Cells[35, 5].text; cvr_bo_statements_notes = xlscvsheet.Cells[36, 5].text; cvr_lbr_bills_notes = xlscvsheet.Cells[37, 5].text; cvr_musician_bills_notes = xlscvsheet.Cells[38, 5].text; cvr_local_exp_invoice_notes = xlscvsheet.Cells[39, 5].text; cvr_ad_notes = xlscvsheet.Cells[40, 5].text; cvr_contact_notes = xlscvsheet.Cells[41, 5].text; email_list = xlscvsheet.Cells[66, 1].text; dtcvr.Rows.Add(sno, recid, cvr_s_cover_flag, cvr_grnty_flag, cvr_royalty_flag, cvr_ovrg_flag, cvr_s_summary_flag, cvr_venue_sett_flag, cvr_bo_sheet_flag, cvr_bo_statements_flag, cvr_lbr_bills_flag, cvr_musician_bills_flag, cvr_local_exp_invoice_flag, cvr_ad_flag, cvr_contact_flag, cvr_s_cover_notes, cvr_grnty_notes, cvr_royalty_notes, cvr_ovrg_notes, cvr_s_summary_notes, cvr_venue_sett_notes, cvr_bo_sheet_notes, cvr_bo_statements_notes, cvr_lbr_bills_notes, cvr_musician_bills_notes, cvr_local_exp_invoice_notes, cvr_ad_notes, cvr_contact_notes, email_list); #endregion #region cvrcharges and receivables assign string cvr_chgs_desc, cvr_chgs_amt, cvr_chgs_check, cvr_chgs_notes, cvr_type = "c"; for (int c = 44; c <= 52; c++) { cvr_chgs_desc = xlscvsheet.Cells[c, 1].text; cvr_chgs_amt = xlscvsheet.Cells[c, 4].text; cvr_chgs_check = xlscvsheet.Cells[c, 5].text; cvr_chgs_notes = xlscvsheet.Cells[c, 6].text; if (string.IsNullOrEmpty(cvr_chgs_desc) == false) dtchr.Rows.Add(sno, recid, cvr_chgs_desc, cvr_chgs_amt.AutoformatDecimal(), cvr_chgs_check, cvr_chgs_notes, cvr_type); } cvr_type = "r"; for (int c = 56; c <= 62; c++) { cvr_chgs_desc = xlscvsheet.Cells[c, 1].text; cvr_chgs_amt = xlscvsheet.Cells[c, 4].text; cvr_chgs_check = ""; cvr_chgs_notes = xlscvsheet.Cells[c, 5].text; if (string.IsNullOrEmpty(cvr_chgs_desc) == false) dtchr.Rows.Add(sno, recid, cvr_chgs_desc, cvr_chgs_amt.AutoformatDecimal(), cvr_chgs_check, cvr_chgs_notes, cvr_type); } #endregion #endregion #region write to db MasterDataLayer.MasterData md = new MasterDataLayer.MasterData(); md.sqlbcopy(dt, "temp_engt"); md.sqlbcopy(dt1, "temp_schedule"); md.sqlbcopy(dtcvr, "temp_cvr_documents"); md.sqlbcopy(dtchr, "temp_cvr_charges"); string result; using (StringWriter sw = new StringWriter()) { dt.WriteXml(sw); result = sw.ToString(); } #endregion } catch (Exception ex) { Int32 lineno = new System.Diagnostics.StackTrace(ex, true).GetFrame(1).GetFileLineNumber(); throw new Exception(ex.Message + " Line No.:<<" + lineno.ToString() + ">> " + path); } finally { xlWorkbook.Save(); xlWorkbook.Close(Type.Missing, Type.Missing, Type.Missing); xlApp.Quit(); Kill_Excel_Process(); xlApp = null; } }
private void boutonExporter_Click(object sender, EventArgs e) { SaveFileDialog saveFileDialog = new SaveFileDialog(); saveFileDialog.Filter = "Fichier Excel (*.xlsx)|*.xlsx"; saveFileDialog.FilterIndex = 0; saveFileDialog.RestoreDirectory = true; saveFileDialog.CreatePrompt = true; saveFileDialog.Title = "Exporter le fichier Excel vers..."; saveFileDialog.ShowDialog(); 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.Name = "Exported from gridview"; 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 ; i++) { for(int j=0;j<dataGridView1.Columns.Count;j++) { worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString(); } } workbook.SaveAs(saveFileDialog.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); app.Quit(); }