private void ExportToExcel(DataGridView dgView) { Microsoft.Office.Interop.Excel.Application excelApp = null; try { // instantiating the excel application class excelApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook currentWorkbook = excelApp.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel.Worksheet currentWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)currentWorkbook.ActiveSheet; currentWorksheet.Columns.ColumnWidth = 18; if (dgView.Rows.Count > 0) { currentWorksheet.Cells[1, 1] = cmbSubject.SelectedItem.ToString(); int i = 1; foreach (DataGridViewColumn dgviewColumn in dgView.Columns) { // Excel work sheet indexing starts with 1 currentWorksheet.Cells[2, i] = dgviewColumn.Name; ++i; } Microsoft.Office.Interop.Excel.Range headerColumnRange = currentWorksheet.get_Range("A2", "T2"); headerColumnRange.Font.Bold = true; headerColumnRange.Font.Color = 0xFF0000; //headerColumnRange.EntireColumn.AutoFit(); int rowIndex = 0; for (rowIndex = 0; rowIndex < dgView.Rows.Count; rowIndex++) { DataGridViewRow dgRow = dgView.Rows[rowIndex]; for (int cellIndex = 0; cellIndex < dgRow.Cells.Count; cellIndex++) { currentWorksheet.Cells[rowIndex + 3, cellIndex + 1] = dgRow.Cells[cellIndex].Value; } } Microsoft.Office.Interop.Excel.Range fullTextRange = currentWorksheet.get_Range("A1", "G" + (rowIndex + 1).ToString()); fullTextRange.WrapText = true; fullTextRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; } else { currentWorksheet.Cells[1, 1] = tcpserver.subject; } using (SaveFileDialog exportSaveFileDialog = new SaveFileDialog()) { exportSaveFileDialog.Title = "Select Excel File"; exportSaveFileDialog.Filter = "Microsoft Office Excel Workbook(*.xlsx)|*.xlsx"; if (DialogResult.OK == exportSaveFileDialog.ShowDialog()) { string fullFileName = exportSaveFileDialog.FileName; // currentWorkbook.SaveCopyAs(fullFileName); // indicating that we already saved the workbook, otherwise call to Quit() will pop up // the save file dialogue box currentWorkbook.SaveAs(fullFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, System.Reflection.Missing.Value, Missing.Value, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true, Missing.Value, Missing.Value, Missing.Value); currentWorkbook.Saved = true; MetroMessageBox.Show(this, "Database exported successfully", "Exported to Excel", MessageBoxButtons.OK, MessageBoxIcon.Information); } } } catch (Exception ex) { MetroMessageBox.Show(this, ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { if (excelApp != null) { excelApp.Quit(); } } }
private List <Point3d> pegaDadosExcel(string nomeArquivo, double fatorAmpliacao) { string nomeWorksheet = "Lista"; List <Point3d> listaDados = new List <Point3d>(); OleDbConnection conexao = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + nomeArquivo + ";Extended Properties='Excel 12.0 Xml;HDR=NO;IMEX=1'"); OleDbDataAdapter adapter = new OleDbDataAdapter("Select * from [" + nomeWorksheet + "]", conexao); DataSet ds = new DataSet(); Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application { Visible = false }; Microsoft.Office.Interop.Excel.Workbook wbExcel = excelApp.Workbooks.Open(nomeArquivo, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, null, null); Microsoft.Office.Interop.Excel.Worksheet activeSheet = (Microsoft.Office.Interop.Excel.Worksheet)wbExcel.ActiveSheet; Microsoft.Office.Interop.Excel.Worksheet wsPlanilha = (Microsoft.Office.Interop.Excel.Worksheet)wbExcel.Worksheets.get_Item(1); try { double X = 0; double Y = 0; double Z = 0; string sX = wsPlanilha.get_Range("E2", "E2").Text; string sY = wsPlanilha.get_Range("F2", "F2").Text; string sZ = wsPlanilha.get_Range("G2", "G2").Text; string colX = "E2"; string colY = "F2"; string colZ = "G2"; int linha = 2; int indicador = 0; Point3d ponto = new Point3d(0, 0, 0); while (indicador == 0) { sX = wsPlanilha.get_Range(colX, colX).Text; if (sX == null || sX == "") { break; } sY = wsPlanilha.get_Range(colY, colY).Text; if (sY == null || sY == "") { break; } sZ = wsPlanilha.get_Range(colZ, colZ).Text; if (sZ == null || sZ == "") { break; } X = ConverterParaNumero(sX); Y = ConverterParaNumero(sY); Z = fatorAmpliacao * ConverterParaNumero(sZ); ponto = new Point3d(X, Y, Z); listaDados.Add(ponto); linha++; colX = string.Concat("E", linha.ToString()); colY = string.Concat("F", linha.ToString()); colZ = string.Concat("G", linha.ToString()); } //listaDados = OrdenarListaPorAbcissa(listaDados); } catch (Exception ex) { MessageBox.Show("Erro ao acessar arquivo: " + ex.Message); } excelApp.Workbooks.Close(); excelApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(wbExcel); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); return(listaDados); }
/// <summary> /// 加载数据 /// </summary> /// <param name="obj"></param> private void AddData(FarPoint.Win.Spread.FpSpread obj) { WaitDialogForm wait = null; wait = new WaitDialogForm("", "正在加载数据, 请稍候..."); try { //打开Excel表格 //清空工作表 fpSpread1.Sheets.Clear(); obj.OpenExcel(System.Windows.Forms.Application.StartupPath + "\\xls\\Sheet2_1.xls"); PF.SpreadRemoveEmptyCells(obj); this.barEditItem1.Visibility = DevExpress.XtraBars.BarItemVisibility.Never; for (int i = 0; i < fpSpread1.Sheets.Count; ++i) { fpSpread1.Sheets[i].CellChanged += new FarPoint.Win.Spread.SheetViewEventHandler(FrmSheet2_1_CellChanged); } } catch (System.Exception e) { //如果打开出错则重新生成并保存 LoadData(); //判断文件夹是否存在,不存在则创建 if (!Directory.Exists(System.Windows.Forms.Application.StartupPath + "\\xls")) { Directory.CreateDirectory(System.Windows.Forms.Application.StartupPath + "\\xls"); } //保存EXcel文件 obj.SaveExcel(System.Windows.Forms.Application.StartupPath + "\\xls\\Sheet2_1.xls", FarPoint.Excel.ExcelSaveFlags.NoFlagsSet); // 定义要使用的Excel 组件接口 // 定义Application 对象,此对象表示整个Excel 程序 Microsoft.Office.Interop.Excel.Application excelApp = null; // 定义Workbook对象,此对象代表工作薄 Microsoft.Office.Interop.Excel.Workbook workBook; // 定义Worksheet 对象,此对象表示Execel 中的一张工作表 Microsoft.Office.Interop.Excel.Worksheet ws = null; Microsoft.Office.Interop.Excel.Range range = null; excelApp = new Microsoft.Office.Interop.Excel.Application(); string filename = System.Windows.Forms.Application.StartupPath + "\\xls\\Sheet2_1.xls"; workBook = excelApp.Workbooks.Open(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); for (int i = 1; i <= workBook.Worksheets.Count; i++) { ws = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[i]; //取消保护工作表 ws.Unprotect(Missing.Value); //有数据的行数 int row = ws.UsedRange.Rows.Count; //有数据的列数 int col = ws.UsedRange.Columns.Count; //创建一个区域 range = ws.get_Range(ws.Cells[1, 1], ws.Cells[row, col]); //设区域内的单元格自动换行 range.WrapText = true; //保护工作表 ws.Protect(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } //保存工作簿 workBook.Save(); //关闭工作簿 excelApp.Workbooks.Close(); } wait.Close(); }
// // GET: /Forecast/Excel public ActionResult Excel(int YYYY = -1, int MM = -1, int DD = -1) { try { DateTime currDate; try { currDate = new DateTime(YYYY, MM, DD); } catch { currDate = DateTime.Now; } int[] HydroPostCodeArray = { 6005, 6010, 6016, 6022, 6024, 6027, 6030, 5002, 5004, 5012, 5016, 5019, 5020, 5024, 6280, 6286, 6291, 6295,6364, 6369 }; BorshchForecast theModel = new BorshchForecast(currDate); foreach (var j in HydroPostCodeArray) { River theRiver = River.GetByDate(currDate, j); if (theRiver == null) { theRiver = new River(); theRiver.HydroPostCode = j; } theModel.theRiverList.Add(theRiver); } theModel.theReservoirCOSMO = Reservoir.GetByDate(currDate, "COSMO"); if (theModel.theReservoirCOSMO == null) { theModel.theReservoirCOSMO = new Reservoir(); } theModel.theReservoirJMA = Reservoir.GetByDate(currDate, "JMA"); if (theModel.theReservoirJMA == null) { theModel.theReservoirJMA = new Reservoir(); } theModel.theReservoirNCEP = Reservoir.GetByDate(currDate, "NCEP"); if (theModel.theReservoirNCEP == null) { theModel.theReservoirNCEP = new Reservoir(); } theModel.theReservoirUKMO = Reservoir.GetByDate(currDate, "UKMO"); if (theModel.theReservoirUKMO == null) { theModel.theReservoirUKMO = new Reservoir(); } string nameExcel = HttpContext.Server.MapPath("~/App_Data/"); string fileName = "BorshchForecast-" + theModel.forecastDate.ToString("yyyy-MM-dd") + ".xls"; string fileNameTemp = string.Format(@"{0}.xls", Guid.NewGuid()); nameExcel += fileNameTemp; Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) { throw new Exception("Не удалось создать объект Excel"); } xlApp.Visible = false; xlApp.DisplayAlerts = false; Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(); Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); int i = 1; xlWorkSheet.get_Range("a1", "m2").Merge(false); var chartRange = xlWorkSheet.get_Range("a1", "m2"); chartRange.FormulaR1C1 = "Фактические уровни воды в бассейне р.Амур на " + theModel.forecastDate.ToString("dd.MM.yyyy") + " года и \nпрогноз уровней воды (в см над нулем графика поста) на " + theModel.forecastDate.AddDays(1).ToString("dd.MM") + " - " + theModel.forecastDate.AddDays(6).ToString("dd.MM.yyyy") + " года"; chartRange.HorizontalAlignment = 3; chartRange.VerticalAlignment = 3; i++; i++; xlWorkSheet.Cells[i, 1] = "Индекс"; xlWorkSheet.Cells[i, 2] = "Река - Пункт"; xlWorkSheet.Cells[i, 3] = "Пойма"; xlWorkSheet.Cells[i, 4] = "НЯ, см"; xlWorkSheet.Cells[i, 5] = "ОЯ, см"; xlWorkSheet.Cells[i, 6] = "Н факт, см"; xlWorkSheet.Cells[i, 7] = "Н прогноз " + theModel.forecastDate.AddDays(1).ToString("dd.MM.yy"); xlWorkSheet.Cells[i, 8] = "Н прогноз " + theModel.forecastDate.AddDays(2).ToString("dd.MM.yy"); xlWorkSheet.Cells[i, 9] = "Н прогноз " + theModel.forecastDate.AddDays(3).ToString("dd.MM.yy"); xlWorkSheet.Cells[i, 10] = "Н прогноз " + theModel.forecastDate.AddDays(4).ToString("dd.MM.yy"); xlWorkSheet.Cells[i, 11] = "Н прогноз " + theModel.forecastDate.AddDays(5).ToString("dd.MM.yy"); xlWorkSheet.Cells[i, 12] = "Н прогноз " + theModel.forecastDate.AddDays(6).ToString("dd.MM.yy"); xlWorkSheet.Cells[i, 13] = "\"0\" графика поста"; foreach (var river in theModel.theRiverList) { i++; xlWorkSheet.Cells[i, 1] = river.HydroPostCode.ToString(); xlWorkSheet.Cells[i, 2] = river.Gauge.ToString(); xlWorkSheet.Cells[i, 3] = river.FloodPlaneMark.ToString(); xlWorkSheet.Cells[i, 4] = river.AdverseFact.ToString(); xlWorkSheet.Cells[i, 5] = river.DangerFact.ToString(); xlWorkSheet.Cells[i, 6] = river.Level_obs.ToString(); xlWorkSheet.Cells[i, 7] = river.Level_for1.ToString(); xlWorkSheet.Cells[i, 8] = river.Level_for2.ToString(); xlWorkSheet.Cells[i, 9] = river.Level_for3.ToString(); xlWorkSheet.Cells[i, 10] = river.Level_for4.ToString(); xlWorkSheet.Cells[i, 11] = river.Level_for5.ToString(); xlWorkSheet.Cells[i, 12] = river.Level_for6.ToString(); xlWorkSheet.Cells[i, 13] = river.Height.ToString(); } i += 2; xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()).Merge(false); chartRange = xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()); xlWorkSheet.Cells[i, 1] = "Нуль поста - высота отметки нуля графика поста в м Б.С."; i += 2; xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()).Merge(false); chartRange = xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()); xlWorkSheet.Cells[i, 1] = "Нф - фактический уровень воды на 8-00 местного времени по сотоянию " + theModel.forecastDate.ToString("dd.MM.yyyy"); i += 2; xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()).Merge(false); chartRange = xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()); xlWorkSheet.Cells[i, 1] = "Критические значения уровня воды в см над нулем графика поста:"; i += 1; xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()).Merge(false); chartRange = xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()); xlWorkSheet.Cells[i, 1] = "Пойма - уровень, при котором происходит выход воды на пойму"; i += 1; xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()).Merge(false); chartRange = xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()); xlWorkSheet.Cells[i, 1] = "НЯ - отметка неблагоприятного явления"; i += 1; xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()).Merge(false); chartRange = xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()); xlWorkSheet.Cells[i, 1] = "ОЯ - отметка опасного явления"; i += 2; xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()).Merge(false); chartRange = xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()); xlWorkSheet.Cells[i, 1] = "Дата выпуска прогноза: " + theModel.forecastDate.ToShortDateString(); // Зейское водохранилище i += 3; xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()).Merge(false); chartRange = xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()); xlWorkSheet.Cells[i, 1] = "Прогноз суточного притока воды к Зейскому водохранилищу (куб. м/с) на " + theModel.forecastDate.AddDays(1).ToString("dd.MM") + " - " + theModel.forecastDate.AddDays(6).ToString("dd.MM.yyyy"); i += 1; xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()).Merge(false); chartRange = xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()); xlWorkSheet.Cells[i, 1] = "Приток фактический на " + theModel.forecastDate.ToString("dd.MM.yyyy"); i += 1; xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()).Merge(false); chartRange = xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()); xlWorkSheet.Cells[i, 1] = "Q в/б = " + theModel.forecastDate.ToString("dd.MM.yyyy") + " куб м/с"; i += 1; xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()).Merge(false); chartRange = xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()); xlWorkSheet.Cells[i, 1] = "Q г/м = " + theModel.forecastDate.ToString("dd.MM.yyyy") + " куб м/с"; i += 1; xlWorkSheet.Cells[i, 1] = "Модель"; xlWorkSheet.Cells[i, 2] = "Q прогноз " + theModel.forecastDate.AddDays(1).ToString("dd.MM.yy"); xlWorkSheet.Cells[i, 3] = "Q прогноз " + theModel.forecastDate.AddDays(2).ToString("dd.MM.yy"); xlWorkSheet.Cells[i, 4] = "Q прогноз " + theModel.forecastDate.AddDays(3).ToString("dd.MM.yy"); xlWorkSheet.Cells[i, 5] = "Q прогноз " + theModel.forecastDate.AddDays(4).ToString("dd.MM.yy"); xlWorkSheet.Cells[i, 6] = "Q прогноз " + theModel.forecastDate.AddDays(5).ToString("dd.MM.yy"); i += 1; xlWorkSheet.Cells[i, 1] = theModel.theReservoirCOSMO.MeteoModel; xlWorkSheet.Cells[i, 2] = theModel.theReservoirCOSMO.Inflow_for1; xlWorkSheet.Cells[i, 3] = theModel.theReservoirCOSMO.Inflow_for2; xlWorkSheet.Cells[i, 4] = theModel.theReservoirCOSMO.Inflow_for3; xlWorkSheet.Cells[i, 5] = theModel.theReservoirCOSMO.Inflow_for4; xlWorkSheet.Cells[i, 6] = theModel.theReservoirCOSMO.Inflow_for5; i += 1; xlWorkSheet.Cells[i, 1] = theModel.theReservoirNCEP.MeteoModel; xlWorkSheet.Cells[i, 2] = theModel.theReservoirNCEP.Inflow_for1; xlWorkSheet.Cells[i, 3] = theModel.theReservoirNCEP.Inflow_for2; xlWorkSheet.Cells[i, 4] = theModel.theReservoirNCEP.Inflow_for3; xlWorkSheet.Cells[i, 5] = theModel.theReservoirNCEP.Inflow_for4; xlWorkSheet.Cells[i, 6] = theModel.theReservoirNCEP.Inflow_for5; i += 1; xlWorkSheet.Cells[i, 1] = theModel.theReservoirUKMO.MeteoModel; xlWorkSheet.Cells[i, 2] = theModel.theReservoirUKMO.Inflow_for1; xlWorkSheet.Cells[i, 3] = theModel.theReservoirUKMO.Inflow_for2; xlWorkSheet.Cells[i, 4] = theModel.theReservoirUKMO.Inflow_for3; xlWorkSheet.Cells[i, 5] = theModel.theReservoirUKMO.Inflow_for4; xlWorkSheet.Cells[i, 6] = theModel.theReservoirUKMO.Inflow_for5; i += 1; xlWorkSheet.Cells[i, 1] = theModel.theReservoirJMA.MeteoModel; xlWorkSheet.Cells[i, 2] = theModel.theReservoirJMA.Inflow_for1; xlWorkSheet.Cells[i, 3] = theModel.theReservoirJMA.Inflow_for2; xlWorkSheet.Cells[i, 4] = theModel.theReservoirJMA.Inflow_for3; xlWorkSheet.Cells[i, 5] = theModel.theReservoirJMA.Inflow_for4; xlWorkSheet.Cells[i, 6] = theModel.theReservoirJMA.Inflow_for5; i += 2; xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()).Merge(false); chartRange = xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()); xlWorkSheet.Cells[i, 1] = "COSMO - модель COSMO (Россия)"; i += 1; xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()).Merge(false); chartRange = xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()); xlWorkSheet.Cells[i, 1] = "NCEP - модель Национального центра прогнозов (США)"; i += 1; xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()).Merge(false); chartRange = xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()); xlWorkSheet.Cells[i, 1] = "UKMO - модель Метеорологического бюро Великобритании"; i += 1; xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()).Merge(false); chartRange = xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()); xlWorkSheet.Cells[i, 1] = "JMA - модель Японского метеорологического центра"; i += 2; xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()).Merge(false); chartRange = xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()); xlWorkSheet.Cells[i, 1] = "Дата выпуска прогноза: " + theModel.forecastDate.ToShortDateString(); xlWorkBook.SaveAs(nameExcel); xlWorkBook.Close(true); xlApp.Quit(); byte[] fileBytes = System.IO.File.ReadAllBytes(nameExcel); System.IO.File.Delete(nameExcel); return(File(fileBytes, System.Net.Mime.MediaTypeNames.Application.Octet, fileName)); } catch (Exception ex) { ViewBag.Error = ex.Message + "\n" + ex.StackTrace; return(View()); } }
private void btneExcelReport_Click(object sender, EventArgs e) { try { DataTable dtAccount = new DataTable(); //Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); //Microsoft.Office.Interop.Excel.Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); //Microsoft.Office.Interop.Excel.Sheets xlSheets = null; //Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null; //xlSheets = ExcelApp.Sheets; Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); ExcelApp.Workbooks.Add(); Microsoft.Office.Interop.Excel.Worksheet xlWorkSheetToUpload = default(Microsoft.Office.Interop.Excel.Worksheet); // xlWorkSheetToUpload = ExcelApp.Sheets["Sheet1"]; ExcelApp.Visible = true; Microsoft.Office.Interop.Excel.Sheets xlSheets = null; //Create Excel Sheets xlSheets = ExcelApp.Sheets; xlWorkSheetToUpload = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing); System.Data.DataSet gstR3B = gstR3BController.getGSTR3BReport(Convert.ToDateTime(dtpFrom.Value.ToShortDateString()), Convert.ToDateTime(dtpTo.Value.ToShortDateString()), Utility.FinancilaYearId, "GSTR3B"); dtAccount = gstR3B.Tables[0]; dtAccount.TableName = "GST 3B Report"; xlWorkSheetToUpload.Name = dtAccount.TableName; xlWorkSheetToUpload.Cells[8, 2] = "3.1 Details of Outward Supplies and inward supplies liable to reverse charge"; xlWorkSheetToUpload.Range["B8:G8"].MergeCells = true; int iRowCnt = 10; xlWorkSheetToUpload.Cells[iRowCnt - 1, 2] = "Nature of Supplies"; xlWorkSheetToUpload.Cells[iRowCnt - 1, 3] = "Total Taxable Value"; xlWorkSheetToUpload.Cells[iRowCnt - 1, 4] = "Integrated Tax"; xlWorkSheetToUpload.Cells[iRowCnt - 1, 5] = "Central Tax"; xlWorkSheetToUpload.Cells[iRowCnt - 1, 6] = "State UT Tax"; xlWorkSheetToUpload.Cells[iRowCnt - 1, 7] = "Cess"; // SHOW THE EXCEL SHEET. // SETTING IT VISIBLE WILL ALLOW YOU TO SEE HOW IT WRITES DATA TO EACH CELL. // Storing Each row and column value to excel sheet for (var i = 0; i <= dtAccount.Rows.Count - 1; i++) { xlWorkSheetToUpload.Cells[iRowCnt, 2] = dtAccount.Rows[i]["NatureofSupplies"]; xlWorkSheetToUpload.Cells[iRowCnt, 3] = dtAccount.Rows[i]["TotalTaxableValue"]; xlWorkSheetToUpload.Cells[iRowCnt, 4] = dtAccount.Rows[i]["IntegratedTax"]; xlWorkSheetToUpload.Cells[iRowCnt, 5] = dtAccount.Rows[i]["CentralTax"]; xlWorkSheetToUpload.Cells[iRowCnt, 6] = dtAccount.Rows[i]["StateUTTax"]; xlWorkSheetToUpload.Cells[iRowCnt, 7] = dtAccount.Rows[i]["Cess"]; iRowCnt = iRowCnt + 1; } xlWorkSheetToUpload.Cells[18, 2] = "4. Eligible ITC"; xlWorkSheetToUpload.Range["B18:F18"].MergeCells = true; xlWorkSheetToUpload.Cells[19, 2] = "Details"; xlWorkSheetToUpload.Cells[19, 3] = "Integrated Tax"; xlWorkSheetToUpload.Cells[19, 4] = "Central Tax"; xlWorkSheetToUpload.Cells[19, 5] = "State UT Tax"; xlWorkSheetToUpload.Cells[19, 6] = "Cess"; xlWorkSheetToUpload.Cells[20, 2] = "1"; xlWorkSheetToUpload.Cells[20, 3] = "2"; xlWorkSheetToUpload.Cells[20, 4] = "3"; xlWorkSheetToUpload.Cells[20, 5] = "4"; xlWorkSheetToUpload.Cells[20, 6] = "5"; // Storing Each row and column value to excel sheet int newRow = 21; for (var i = 0; i <= gstR3B.Tables[1].Rows.Count - 1; i++) { xlWorkSheetToUpload.Cells[newRow, 2] = gstR3B.Tables[1].Rows[i]["Details"]; xlWorkSheetToUpload.Cells[newRow, 3] = gstR3B.Tables[1].Rows[i]["IntegratedTax"]; xlWorkSheetToUpload.Cells[newRow, 4] = gstR3B.Tables[1].Rows[i]["CentralTax"]; xlWorkSheetToUpload.Cells[newRow, 5] = gstR3B.Tables[1].Rows[i]["StateUTTax"]; xlWorkSheetToUpload.Cells[newRow, 6] = gstR3B.Tables[1].Rows[i]["Cess"]; newRow = newRow + 1; } Microsoft.Office.Interop.Excel.Range formatRange; formatRange = xlWorkSheetToUpload.get_Range("B18:F18"); xlWorkSheetToUpload.get_Range("B8:G8").Font.Bold = true; xlWorkSheetToUpload.get_Range("B8:G8").Font.Size = 13; xlWorkSheetToUpload.get_Range("B9:G9").Font.Size = 11; xlWorkSheetToUpload.get_Range("B9:G9").Font.Bold = true; // formatRange.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; xlWorkSheetToUpload.Columns.AutoFit(); ExcelApp = null; } catch (Exception ex) { MessageBox.Show(ex.Message); } }
/// <summary> /// 导出文本 /// </summary> private void ExoprtText() { Microsoft.Office.Interop.Excel.Workbook xBook = null; Microsoft.Office.Interop.Excel.Worksheet xSheet = null; Microsoft.Office.Interop.Excel.Worksheet beforeSheet = null; // 设定保存的文件名 string fileName = this.baseFile; if (string.IsNullOrEmpty(fileName)) { fileName = @"TextExport.xlsx"; } // 先删除原来的文件 if (File.Exists(fileName)) { File.Delete(fileName); } // 显示进度条 this.ResetProcessBar(this.textFiles.Count); try { // 创建Application对象 this.xApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); // 追加一个WorkBook xBook = this.xApp.Workbooks.Add(Missing.Value); for (int j = 0; j < this.textFiles.Count; j++) { // 追加一个Sheet FilePosInfo filePosInfo = this.textFiles[j]; string jpText = string.Empty; string cnText = string.Empty; this.Invoke((MethodInvoker) delegate() { // 更新当前文本 this.fileList.SelectedIndex = j; // 取得日文、中文文本 jpText = this.txtJp.Text; cnText = this.txtCn.Text; }); // 设置当前Sheet名(如果有重复的就累加编号) string sheetName = Util.GetShortFileName(filePosInfo.File); int sameNameCount = 0; for (int i = 0; i < j; i++) { if (Util.GetShortFileName(this.textFiles[i].File).IndexOf(sheetName) >= 0) { sameNameCount++; } } if (beforeSheet == null) { xSheet = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value); } else { xSheet = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets.Add(beforeSheet, Missing.Value, Missing.Value, Missing.Value); } xSheet.Name = sheetName + (sameNameCount > 0 ? "_" + sameNameCount.ToString() : string.Empty); beforeSheet = xSheet; // 将每行文本保存到Sheet中 string[] jpTexts = jpText.Split('\n'); string[] cnTexts = cnText.Split('\n'); for (int i = 0; i < jpTexts.Length; i++) { // 写入日文文本 Microsoft.Office.Interop.Excel.Range rngJp = xSheet.get_Range("A" + (i + 1), Missing.Value); rngJp.Value2 = jpTexts[i]; } for (int i = 0; i < cnTexts.Length; i++) { // 写入中文文本 Microsoft.Office.Interop.Excel.Range rngCn = xSheet.get_Range("G" + (i + 1), Missing.Value); rngCn.Value2 = cnTexts[i]; } // 更新进度条 this.ProcessBarStep(); } } finally { // 保存 xSheet.SaveAs( fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); // 隐藏进度条 this.CloseProcessBar(); // 清空各种对象 xSheet = null; xBook = null; if (this.xApp != null) { this.xApp.Quit(); this.xApp = null; } // 显示保存完成信息 MessageBox.Show("导出完成!"); } }
private void buttonOst_Click(object sender, EventArgs e) { //массив0 квитанции refreshOst(); int[,] arr = new int [listViewKvit.Items.Count, listViewKvit.Columns.Count]; for (int x = 0, maxX = listViewKvit.Items.Count; x < maxX; x++) { for (int y = 0, maxY = listViewKvit.Columns.Count; y < maxY; y++) { arr[x, y] = int.Parse(listViewKvit.Items[x].SubItems[y].Text.Trim()); } } //массив1 журнал refreshOst1(); int[] arr1 = new int[listViewKvit.Items.Count]; for (int x = 0, maxX = listViewKvit.Items.Count; x < maxX; x++) { arr1[x] = int.Parse(listViewKvit.Items[x].Text.Trim()); } string[,] b = new string [10, 10]; refreshKvit(); List <int> ARR = new List <int>(); List <int> ARR1 = new List <int>(); List <int> ARR2 = new List <int>(); List <int> ARR3 = new List <int>(); List <object> unique = new List <object>(); List <object> unique2 = new List <object>(); //диапазоны из нач/кон значений квитанции for (int x = 0; x <= ((arr.Length / 2) - 1); x++) { for (int i = arr[x, 0], maxi = arr[x, 1]; i <= maxi; i++) { ARR.Add(i); } } //журнал foreach (int g in arr1) { ARR1.Add(g); } //удаление того, что есть в журнале, но нет в билетах for (int x = 0; x < ARR.Count; x++) { for (int y = 0; y < ARR1.Count; y++) { if (ARR[x].Equals(ARR1[y])) { ARR3.Add(ARR[x]); } } } //объединение списков ARR2.AddRange(ARR.ToArray()); ARR2.AddRange(ARR3.ToArray()); ARR2.Sort(); //удаление двойных for (int m = 0; m < ARR2.Count; m++) { for (int j = m + 1; j < ARR2.Count; j++) { if (ARR2[j].Equals(ARR2[m])) { ARR2.RemoveAt(j--); ARR2.RemoveAt(m); } } } //приведение к начальным for (int x = 0; x <= ((arr.Length / 2) - 1); x++) { for (int i = arr[x, 0], maxi = arr[x, 1]; i <= maxi; i++) { if (ARR2.Contains(i)) { unique.Add(x + 1); unique.Add(i); } } } //собственно диапазоны остатка for (int x = 0; x < unique.Count; x = x + 2) { int m; if (x == 0) { m = x + 1; } else { m = x - 2; } if (!unique[x].Equals(unique[m])) { unique2.Add(unique[x]); unique2.Add(unique[x + 1]); } int f; if (x < unique.Count - 2) { f = x + 2; } else { f = x - 1; } if (!unique[x].Equals(unique[f])) { unique2.Add(unique[x + 1]); /*for (int y = 0; y < b.Length/3; y++) * { * if (unique[x + 1].Equals(int.Parse(b[y, 0]))) * { * unique2.Add(b[y, 1]); * unique2.Add(b[y, 2]); * } * }*/ } } //массив для екселя object[,] c = new object[unique2.Count, 3]; for (int i = 0, k = 0; i < unique2.Count; i = i + 3, k++) { c[k, 0] = unique2[i]; } for (int i = 1, k = 0; i < unique2.Count; i = i + 3, k++) { c[k, 1] = unique2[i]; } for (int i = 2, k = 0; i < unique2.Count; i = i + 3, k++) { c[k, 2] = unique2[i]; } /*for (int i = 3, k = 0; i < unique2.Count; i = i + 5, k++) * { * c[k, 3] = unique2[i]; * } * * for (int i = 4, k = 0; i < unique2.Count; i = i + 5, k++) * { * c[k, 4] = unique2[i]; * }*/ object[] a = unique2.ToArray(); Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); excelApp.Visible = true; BringToFront(); string workbookPath = Application.StartupPath + "\\templates/bilety.xls"; Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0, true, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); Microsoft.Office.Interop.Excel.Sheets excelSheets = excelWorkbook.Worksheets; string currentSheet = "Бланк"; Microsoft.Office.Interop.Excel.Worksheet excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelSheets.get_Item(currentSheet); Microsoft.Office.Interop.Excel.Range excelCellName = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("A4", "C" + unique2.Count); excelCellName.Value2 = c; }
public void WriteExcell(DataSet ds, string Requisition, string FileName, string strSavePath, string FilePath) { //Pick up the RFQ format file // string strPath= Server.MapPath(".") + "\Technical\ExcelFile\RFQ_FormatFile.xls"; // string path = System.AppDomain.CurrentDomain.BaseDirectory + @"RFQ_FormatFile.xls"; string path = FilePath + @"/RFQ_FormatFile.xls"; CheckExcellProcesses(); ExlApp = new Microsoft.Office.Interop.Excel.Application(); try { ExlWrkBook = ExlApp.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); ExlWrkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExlWrkBook.ActiveSheet; ExlWrkSheet.Cells[1, 3] = ds.Tables[2].Rows[0]["Vessel_name"].ToString(); ExlWrkSheet.Cells[2, 3] = ds.Tables[0].Rows[0]["QUOTATION_CODE"].ToString(); ExlWrkSheet.Cells[3, 3] = ds.Tables[0].Rows[0]["Quotation_Due_Date"].ToString(); ExlWrkSheet.Cells[4, 3] = ds.Tables[0].Rows[0]["SHORT_NAME"].ToString(); ExlWrkSheet.Cells[11, 3] = ds.Tables[0].Rows[0]["System_Description"].ToString(); ExlWrkSheet.Cells[6, 12] = ds.Tables[0].Rows[0]["BUYER_COMMENTS"].ToString(); ExlWrkSheet.Cells[7, 3] = ds.Tables[0].Rows[0]["SHORT_NAME"].ToString(); ExlWrkSheet.Cells[1, 13] = ds.Tables[0].Rows[0]["Vessel_code"].ToString(); ExlWrkSheet.Cells[2, 13] = ds.Tables[0].Rows[0]["DOCUMENT_CODE"].ToString(); ExlWrkSheet.Cells[3, 13] = ds.Tables[0].Rows[0]["ITEM_SYSTEM_CODE"].ToString(); ExlWrkSheet.Cells[5, 13] = DateTime.Now.ToString("yyyy/MM/dd"); ExlWrkSheet.Cells[6, 13] = ds.Tables[0].Rows[0]["Quotation_CODE"].ToString(); ExlWrkSheet.Cells[7, 13] = ds.Tables[0].Rows[0]["QUOTATION_SUPPLIER"].ToString(); ExlWrkSheet.Cells[12, 3] = ds.Tables[0].Rows[0]["System_Description"].ToString(); if (ds.Tables[4].Rows.Count > 0) { ExlWrkSheet.Cells[10, 7] = ds.Tables[4].Rows[0]["MechInfo"].ToString(); ExlWrkSheet.Cells[11, 7] = ds.Tables[4].Rows[0]["Model_Type"].ToString(); ExlWrkSheet.Cells[12, 7] = ds.Tables[4].Rows[0]["MakerName"].ToString() + ' ' + ds.Tables[4].Rows[0]["MakerAddress"].ToString() + ' ' + ds.Tables[4].Rows[0]["MakerCity"].ToString() + ' ' + ds.Tables[4].Rows[0]["MakerEmail"].ToString() + ' ' + ds.Tables[4].Rows[0]["MakerCONTACT"].ToString() + ' ' + ds.Tables[4].Rows[0]["MakerPhone"].ToString() + ' ' + ds.Tables[4].Rows[0]["MakerFax"].ToString() + ' ' + ds.Tables[4].Rows[0]["MakerTELEX"].ToString() + ' ' + ds.Tables[4].Rows[0]["System_Serial_Number"].ToString(); } Microsoft.Office.Interop.Excel.Range xlsRange; int i = 15; foreach (DataRow dr in ds.Tables[1].Rows) { //S.nO. ExlWrkSheet.Cells[i, 1] = dr["ID"].ToString(); //DRAWING NO. ExlWrkSheet.Cells[i, 2] = dr["Drawing_Number"].ToString(); //PART NO ExlWrkSheet.Cells[i, 3] = dr["Part_Number"].ToString(); //Item Ref Code ExlWrkSheet.Cells[i, 4] = dr["ITEM_REF_CODE"].ToString(); //Item ExlWrkSheet.Cells[i, 5] = dr["Short_Description"].ToString(); ExlWrkSheet.Cells[i + 1, 5] = dr["Long_Description"].ToString(); ExlWrkSheet.Cells[i + 2, 5] = dr["ITEM_COMMENT"].ToString(); ExlWrkSheet.Cells[i, 6] = dr["Unit_and_Packings"].ToString(); ExlWrkSheet.Cells[i, 7] = dr["REQUESTED_QTY"].ToString(); i = i + 3; } ExlWrkSheet.get_Range("A" + (ds.Tables[1].Rows.Count * 3 + 15).ToString(), "N1639").Delete(Microsoft.Office.Interop.Excel.XlDirection.xlUp); ExlWrkSheet.Cells[ds.Tables[1].Rows.Count * 3 + 15, 1] = ds.Tables[3].Rows[0]["LegalTerm"].ToString(); //ExlWrkSheet.get_Range("F15", "H" + (ds.Tables[1].Rows.Count * 3 + 14).ToString()).Locked = false; //ExlWrkSheet.get_Range("J15", "J" + (ds.Tables[1].Rows.Count * 3 + 14).ToString()).Locked = false; //ExlWrkSheet.get_Range("I7", "I9").Locked = false; //ExlWrkSheet.get_Range("I10", "K11").Locked = false; ExlWrkSheet.get_Range("G9", "G9").NumberFormat = "#0.00"; //ExlWrkSheet.get_Range("I5", "I6").Locked = false; //ExlWrkSheet.get_Range("L1", "M10").EntireColumn.Hidden = true; ExlWrkSheet.get_Range("M1", "M10").EntireColumn.Hidden = true; ExlWrkSheet.Protect("tessmave", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing); ExlWrkBook.SaveAs(strSavePath + 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, true); string s = Server.MapPath("~"); string destFile = Server.MapPath("Uploads/Purchase") + "\\" + FileName;; File.Copy(strSavePath + FileName, destFile, true); } catch (Exception ex) { throw ex; } finally { ExlWrkBook.Close(null, null, null); //ExlApp.Workbooks.Close(); ExlApp.Quit(); KillExcel(); } }
public static bool ExportDataToExcel(System.Data.DataTable srcDataTable, string excelFilePath) { Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); object missing = System.Reflection.Missing.Value; //导出到execl try { if (xlApp == null) { MessageBox.Show("无法创建Excel对象,可能您的电脑未安装Excel!"); return(false); } Microsoft.Office.Interop.Excel.Workbooks xlBooks = xlApp.Workbooks; Microsoft.Office.Interop.Excel.Workbook xlBook = xlBooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets[1]; //让后台执行设置为不可见,为true的话会看到打开一个Excel,然后数据在往里写 xlApp.Visible = false; object[,] objData = new object[srcDataTable.Rows.Count + 1, srcDataTable.Columns.Count]; //首先将数据写入到一个二维数组中 for (int i = 0; i < srcDataTable.Columns.Count; i++) { objData[0, i] = srcDataTable.Columns[i].ColumnName; } if (srcDataTable.Rows.Count > 0) { for (int i = 0; i < srcDataTable.Rows.Count; i++) { for (int j = 0; j < srcDataTable.Columns.Count; j++) { objData[i + 1, j] = srcDataTable.Rows[i][j]; } } } string startCol = "A"; int iCnt = (srcDataTable.Columns.Count / 26); string endColSignal = (iCnt == 0 ? "" : ((char)('A' + (iCnt - 1))).ToString()); string endCol = endColSignal + ((char)('A' + srcDataTable.Columns.Count - iCnt * 26 - 1)).ToString(); Microsoft.Office.Interop.Excel.Range range = xlSheet.get_Range(startCol + "1", endCol + (srcDataTable.Rows.Count - iCnt * 26 + 1).ToString()); range.Value = objData; //给Exccel中的Range整体赋值 range.EntireColumn.AutoFit(); //设定Excel列宽度自适应 xlSheet.get_Range(startCol + "1", endCol + "1").Font.Bold = 1; //Excel文件列名 字体设定为Bold //设置禁止弹出保存和覆盖的询问提示框 xlApp.DisplayAlerts = false; xlApp.AlertBeforeOverwriting = false; //if (File.Exists(excelFilePath)) // File.Delete(excelFilePath); if (xlSheet != null) { xlSheet.SaveAs(excelFilePath, missing, missing, missing, missing, missing, missing, missing, missing, missing); xlApp.Quit(); //KillProcess(xlApp); //SystemUnit.KillProcess(xlApp); } } catch (Exception ex) { Log.WriteLog("SystemUnit", "ERROR", "【ExportDataToExcel】 " + ex.Message); xlApp.Quit(); //KillProcess(xlApp); //throw ex; return(false); } return(true); }
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e) { progressBar1.Step = 1; progressBar1.Value = 0; progressBar1.Maximum = dataGridView1.RowCount; progressBar1.Minimum = 0; ModifyProgressBarColor.SetState(progressBar1, 1); h = 2; for (int i = 0; i < dataGridView1.RowCount; i++) { string tempImageName = ""; wrksheet.Cells[h, 1] = tempImageName = dataGridView1[0, i].Value + ""; //Trường 01/ Tên hình //wrksheet.Cells[h, 2] = dataGridView1[1, i].Value + ""; //Trường Flag Error wrksheet.Cells[h, 2] = dataGridView1[1, i].Value + ""; //Trường 03 wrksheet.Cells[h, 3] = dataGridView1[2, i].Value + ""; wrksheet.Cells[h, 4] = dataGridView1[3, i].Value + ""; wrksheet.Cells[h, 5] = dataGridView1[4, i].Value + ""; wrksheet.Cells[h, 6] = dataGridView1[5, i].Value + ""; wrksheet.Cells[h, 7] = dataGridView1[6, i].Value + ""; wrksheet.Cells[h, 8] = dataGridView1[7, i].Value + ""; wrksheet.Cells[h, 9] = dataGridView1[8, i].Value + ""; //Trường 10 //Trường 11 string tempTruong11 = ""; tempTruong11 = dataGridView1[9, i].Value + ""; if (tempTruong11.IndexOf("?") >= 0) { wrksheet.Cells[h, 10] = "?"; } else if (tempTruong11 == "●") { wrksheet.Cells[h, 10] = "●"; } else if (tempTruong11.Length == 6) { if (int.Parse(tempTruong11) < 291001) { wrksheet.Cells[h, 10] = "1001"; } else if (int.Parse(tempTruong11) > 291231) { tempImageName = tempImageName.Substring(0, 3); switch (tempImageName) { case "01_": case "02_": wrksheet.Cells[h, 10] = "1031"; break; case "03_": case "04_": wrksheet.Cells[h, 10] = "1130"; break; case "05_": case "06_": wrksheet.Cells[h, 10] = "1231"; break; case "07_": wrksheet.Cells[h, 10] = "1231"; break; } } else { wrksheet.Cells[h, 10] = tempTruong11.Substring(2, 4); } } else if (tempTruong11.Length < 6) { tempImageName = tempImageName.Substring(0, 3); switch (tempImageName) { case "01_": case "02_": wrksheet.Cells[h, 10] = "1031"; break; case "03_": case "04_": wrksheet.Cells[h, 10] = "1130"; break; case "05_": case "06_": wrksheet.Cells[h, 10] = "1231"; break; case "07_": wrksheet.Cells[h, 10] = "1231"; break; } } else if (tempTruong11.Length > 6) { wrksheet.Cells[h, 10] = tempTruong11; } wrksheet.Cells[h, 11] = dataGridView1[10, i].Value + ""; //Trường 12 wrksheet.Cells[h, 12] = dataGridView1[11, i].Value + ""; //Trường 13 wrksheet.Cells[h, 13] = dataGridView1[12, i].Value + ""; //Trường 14 lb_Complete.Text = (i + 1) + "/" + dataGridView1.RowCount; progressBar1.PerformStep(); h++; } Microsoft.Office.Interop.Excel.Range rowHead = wrksheet.get_Range("A1", "M" + (h - 1)); rowHead.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid; }
/// <summary> /// Skp的中文文本追加0 /// </summary> private void AddCnSkpTxtZero() { Microsoft.Office.Interop.Excel.Workbook xBook = null; Microsoft.Office.Interop.Excel.Worksheet xSheet = null; try { // 创建Application对象 this.xApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); // 得到WorkBook对象, 打开已有的文件 xBook = this.xApp.Workbooks._Open( @"E:\Study\MySelfProject\Hanhua\TodoCn\TalesOfSymphonia\chtTextCnEnMap2.xlsx", Missing.Value, Missing.Value, Missing.Value, Missing.Value , Missing.Value, Missing.Value, Missing.Value, Missing.Value , Missing.Value, Missing.Value, Missing.Value, Missing.Value); // 显示进度条 this.ResetProcessBar(xBook.Sheets.Count); for (int i = xBook.Sheets.Count; i >= 1; i--) { // 取得相应的Sheet xSheet = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets[i]; for (int j = 1; j < 50; j++) { Microsoft.Office.Interop.Excel.Range rngCn = xSheet.get_Range("L" + j, Missing.Value); if (rngCn != null && rngCn.Value2 != null && !string.IsNullOrEmpty(rngCn.Value2 as string)) { rngCn.Value2 = rngCn.Value2 + "^0^"; } } // 更新进度条 this.ProcessBarStep(); } // 隐藏进度条 this.CloseProcessBar(); // 保存 xSheet.SaveAs( @"E:\Study\MySelfProject\Hanhua\TodoCn\TalesOfSymphonia\chtTextCnEnMap3.xlsx", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } catch (Exception me) { MessageBox.Show(me.Message); } finally { // 隐藏进度条 this.CloseProcessBar(); // 清空各种对象 xSheet = null; xBook = null; if (this.xApp != null) { this.xApp.Quit(); this.xApp = null; } } }
/// <summary> /// 复制Skp的中文文本 /// </summary> private void CopyCnSkpTxt() { Microsoft.Office.Interop.Excel.Workbook xBook = null; Microsoft.Office.Interop.Excel.Worksheet xSheet = null; List <string> enSb = new List <string>(); List <string> cnSb = new List <string>(); Dictionary <string, List <string> > txtMap = new Dictionary <string, List <string> >(); try { // 创建Application对象 this.xApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); // 得到WorkBook对象, 打开已有的文件 xBook = this.xApp.Workbooks._Open( @"E:\Study\MySelfProject\Hanhua\TodoCn\TalesOfSymphonia\TOS_skp.xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value , Missing.Value, Missing.Value, Missing.Value, Missing.Value , Missing.Value, Missing.Value, Missing.Value, Missing.Value); // 取得相应的Sheet xSheet = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets[1]; int lineNum = 1; while (lineNum < 3697) { enSb.Add(xSheet.get_Range("B" + lineNum, Missing.Value).Value2 as string); cnSb.Add(xSheet.get_Range("C" + lineNum, Missing.Value).Value2 as string); lineNum++; } xSheet = null; xBook = null; if (this.xApp != null) { this.xApp.Quit(); this.xApp = null; } //// 创建Application对象 //this.xApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); //xBook = this.xApp.Workbooks._Open( // @"E:\Study\MySelfProject\Hanhua\TodoCn\TalesOfSymphonia\chtText_En.xlsx", // Missing.Value, Missing.Value, Missing.Value, Missing.Value // , Missing.Value, Missing.Value, Missing.Value, Missing.Value // , Missing.Value, Missing.Value, Missing.Value, Missing.Value); //// 显示进度条 //this.ResetProcessBar(xBook.Sheets.Count); //for (int i = xBook.Sheets.Count; i >= 1; i--) //{ // // 取得相应的Sheet // xSheet = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets[i]; // string enTxt = xSheet.get_Range("G1", Missing.Value).Value2 as string; // if (string.IsNullOrEmpty(enTxt)) // { // enTxt = string.Empty; // } // enTxt = enTxt.Replace("^0a^", "\n").Replace("^0^", string.Empty); // for (int j = 0; j < enSb.Count; j++) // { // if (!string.IsNullOrEmpty(enSb[j]) && !string.IsNullOrEmpty(enTxt) && enSb[j].StartsWith(enTxt, StringComparison.OrdinalIgnoreCase)) // { // List<string> cnItemSb = new List<string>(); // for (int k = j; k < j + 35 && k < cnSb.Count; k++) // { // cnItemSb.Add(cnSb[k]); // } // txtMap.Add(xSheet.Name, cnItemSb); // break; // } // } // // 更新进度条 // this.ProcessBarStep(); //} //// 隐藏进度条 //this.CloseProcessBar(); //xSheet = null; //xBook = null; //if (this.xApp != null) //{ // this.xApp.Quit(); // this.xApp = null; //} // 创建Application对象 this.xApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); xBook = this.xApp.Workbooks._Open( @"E:\Study\MySelfProject\Hanhua\TodoCn\TalesOfSymphonia\chtTextCnEnMap.xlsx", Missing.Value, Missing.Value, Missing.Value, Missing.Value , Missing.Value, Missing.Value, Missing.Value, Missing.Value , Missing.Value, Missing.Value, Missing.Value, Missing.Value); // 显示进度条 this.ResetProcessBar(xBook.Sheets.Count); for (int i = xBook.Sheets.Count; i >= 1; i--) { // 取得相应的Sheet xSheet = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets[i]; Microsoft.Office.Interop.Excel.Range rngCn = xSheet.get_Range("L1", Missing.Value); string line1Val = rngCn.Value2 as string; rngCn = xSheet.get_Range("L2", Missing.Value); string line2Val = rngCn.Value2 as string; rngCn = xSheet.get_Range("L3", Missing.Value); string line3Val = rngCn.Value2 as string; rngCn = xSheet.get_Range("L4", Missing.Value); string line4Val = rngCn.Value2 as string; rngCn = xSheet.get_Range("L5", Missing.Value); string line5Val = rngCn.Value2 as string; int lineNo = -1; if (!string.IsNullOrEmpty(line1Val) && string.IsNullOrEmpty(line2Val) && string.IsNullOrEmpty(line3Val) && string.IsNullOrEmpty(line4Val) && string.IsNullOrEmpty(line5Val)) { lineNo = 1; } else if (string.IsNullOrEmpty(line1Val) && !string.IsNullOrEmpty(line2Val) && string.IsNullOrEmpty(line3Val) && string.IsNullOrEmpty(line4Val) && string.IsNullOrEmpty(line5Val)) { lineNo = 2; } else if (string.IsNullOrEmpty(line1Val) && string.IsNullOrEmpty(line2Val) && !string.IsNullOrEmpty(line3Val) && string.IsNullOrEmpty(line4Val) && string.IsNullOrEmpty(line5Val)) { lineNo = 3; } else if (string.IsNullOrEmpty(line1Val) && string.IsNullOrEmpty(line2Val) && string.IsNullOrEmpty(line3Val) && !string.IsNullOrEmpty(line4Val) && string.IsNullOrEmpty(line5Val)) { lineNo = 4; } else if (string.IsNullOrEmpty(line1Val) && string.IsNullOrEmpty(line2Val) && string.IsNullOrEmpty(line3Val) && string.IsNullOrEmpty(line4Val) && !string.IsNullOrEmpty(line5Val)) { lineNo = 5; } if (lineNo >= 0) { rngCn = xSheet.get_Range("L" + lineNo, Missing.Value); string lineVal = rngCn.Value2 as string; int linIndex = 1; for (int j = 0; j < enSb.Count; j++) { if (!string.IsNullOrEmpty(enSb[j]) && enSb[j].IndexOf(lineVal) >= 0) { int startPos = j - lineNo + 1; for (int k = startPos; k < startPos + 30; k++) { rngCn = xSheet.get_Range("L" + (linIndex++), Missing.Value); rngCn.Value2 = cnSb[k]; } break; } } } // 更新进度条 this.ProcessBarStep(); } // 隐藏进度条 this.CloseProcessBar(); // 保存 xSheet.SaveAs( @"E:\Study\MySelfProject\Hanhua\TodoCn\TalesOfSymphonia\chtTextCnEnMap2.xlsx", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } catch (Exception me) { MessageBox.Show(me.Message); } finally { // 隐藏进度条 this.CloseProcessBar(); // 清空各种对象 xSheet = null; xBook = null; if (this.xApp != null) { this.xApp.Quit(); this.xApp = null; } } }
/// <summary> /// 复制Map的中文文本 /// </summary> private void CopyCnMapTxt() { Microsoft.Office.Interop.Excel.Workbook xBook = null; Microsoft.Office.Interop.Excel.Worksheet xSheet = null; Microsoft.Office.Interop.Excel.Workbook xBook2 = null; Microsoft.Office.Interop.Excel.Worksheet xSheet2 = null; List <string> enSb = new List <string>(); List <string> cnSb = new List <string>(); Dictionary <string, List <string> > txtMap = new Dictionary <string, List <string> >(); try { // 创建Application对象 this.xApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); // 得到WorkBook对象, 打开已有的文件 xBook = this.xApp.Workbooks._Open( @"E:\Study\MySelfProject\Hanhua\TodoCn\TalesOfSymphonia\mapText_jp.xlsx", Missing.Value, Missing.Value, Missing.Value, Missing.Value , Missing.Value, Missing.Value, Missing.Value, Missing.Value , Missing.Value, Missing.Value, Missing.Value, Missing.Value); xBook2 = this.xApp.Workbooks._Open( @"E:\Study\MySelfProject\Hanhua\TodoCn\TalesOfSymphonia\mapText_en.xlsx", Missing.Value, Missing.Value, Missing.Value, Missing.Value , Missing.Value, Missing.Value, Missing.Value, Missing.Value , Missing.Value, Missing.Value, Missing.Value, Missing.Value); // 显示进度条 this.ResetProcessBar(xBook.Sheets.Count); int sheetIndex = -1; int line = 1; for (int i = 1; i <= xBook.Sheets.Count; i++) { // 更新进度条 this.ProcessBarStep(); xSheet = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets[i]; sheetIndex = -1; for (int j = i; j <= xBook2.Sheets.Count; j++) { xSheet2 = (Microsoft.Office.Interop.Excel.Worksheet)xBook2.Sheets[j]; if (xSheet.Name.Equals(xSheet2.Name)) { sheetIndex = j; break; } } if (sheetIndex == -1) { continue; } line = 1; while (true) { Microsoft.Office.Interop.Excel.Range rngJp = xSheet.get_Range("G" + line, Missing.Value); Microsoft.Office.Interop.Excel.Range rngEn = xSheet2.get_Range("G" + line, Missing.Value); if (rngEn != null && !string.IsNullOrEmpty(rngEn.Value2 as string)) { rngJp.Value2 = rngEn.Value2; line++; } else { break; } } } xSheet = null; xBook = null; xSheet2 = null; xBook2 = null; if (this.xApp != null) { this.xApp.Quit(); this.xApp = null; } // 隐藏进度条 this.CloseProcessBar(); // 保存 xSheet.SaveAs( @"E:\Study\MySelfProject\Hanhua\TodoCn\TalesOfSymphonia\mapText_jp2.xlsx", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } catch (Exception me) { MessageBox.Show(me.Message); } finally { // 隐藏进度条 this.CloseProcessBar(); // 清空各种对象 xSheet = null; xBook = null; xSheet2 = null; xBook2 = null; if (this.xApp != null) { this.xApp.Quit(); this.xApp = null; } } }
private void fillDocumentExcel(string _filename) { Object wMissing = System.Reflection.Missing.Value; Object wTrue = true; Object wFalse = false; string[] text = inputAppFullName.Text.Split(';'); string d = text[0]; Microsoft.Office.Interop.Excel.Application appExcel = new Microsoft.Office.Interop.Excel.Application(); // Microsoft.Office.Interop.Excel.Workbook bookExcel = new Microsoft.Office.Interop.Excel.Workbook(); Microsoft.Office.Interop.Excel.Worksheet worksheetExcel = new Microsoft.Office.Interop.Excel.Worksheet(); Microsoft.Office.Interop.Excel.Sheets sheetsExcel; Microsoft.Office.Interop.Excel.Range rangeExcel; appExcel.Visible = true; //Работа с документом - Договор appExcel.Workbooks.Open(_filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); sheetsExcel = appExcel.Worksheets; worksheetExcel = (Microsoft.Office.Interop.Excel.Worksheet)sheetsExcel.get_Item(1); rangeExcel = worksheetExcel.get_Range("A2", "AE2"); rangeExcel.Merge(Type.Missing); rangeExcel.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter; rangeExcel.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter; rangeExcel.Value2 = "№" + inputNumContract.Text + " от" + " 2012г."; rangeExcel = worksheetExcel.get_Range("F5", "AH5"); rangeExcel.Merge(Type.Missing); rangeExcel.Value2 = inputAppNameOrgOrPF.Text; rangeExcel = worksheetExcel.get_Range("D9", "G9"); rangeExcel.Merge(Type.Missing); rangeExcel.Value2 = "Путёвка в санаторий " + " \" " + inputAppWay.Text + " \" " + inputNumDays.Text + "дн.," + " \" " + inputAppNumberAndCategory.Text + " \" " + ", с " + inputAppStartTour.Text + " " + d; rangeExcel = worksheetExcel.get_Range("Z9", "AA9"); rangeExcel.Merge(Type.Missing); rangeExcel.Value2 = inputAppCost.Text; rangeExcel = worksheetExcel.get_Range("AD9", "AE9"); rangeExcel.Merge(Type.Missing); rangeExcel.Value2 = inputAppCost.Text; rangeExcel = worksheetExcel.get_Range("AD11", "AE11"); rangeExcel.Merge(Type.Missing); rangeExcel.Value2 = inputAppCost.Text; rangeExcel = worksheetExcel.get_Range("B14", "AE14"); rangeExcel.Merge(Type.Missing); rangeExcel.Value2 = "Всего оказана услуг 1, на сумму" + " " + inputAppCost.Text + " " + "рублей"; }
public static bool DataGridViewExportToExcel(DataTable mDataTable, String strFileName, ref String strMsg) { strMsg = ""; // 创建Excel对象 Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); if (xlApp == null) { strMsg = "Excel无法启动"; return(false); } // 创建Excel工作薄 Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true); Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets[1]; Microsoft.Office.Interop.Excel.Range range = null; /* * // 设置标题 * range = xlSheet.get_Range(xlApp.Cells[1,1],xlApp.Cells[1,ts.GridColumnStyles.Count]); * range.MergeCells = true; * xlApp.ActiveCell.FormulaR1C1 = p_ReportName; * xlApp.ActiveCell.Font.Size = 20; * xlApp.ActiveCell.Font.Bold = true; * xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter; */ // 列索引,行索引,总列数,总行数 int colIndex = 0; int RowIndex = 0; int colCount = mDataTable.Columns.Count; int RowCount = mDataTable.Rows.Count + 1; // 创建缓存数据 object[,] objData = new object[RowCount + 1, colCount]; // 获取列标题 foreach (DataColumn dc in mDataTable.Columns) { objData[RowIndex, colIndex++] = dc.ColumnName; } // 获取数据 for (RowIndex = 1; RowIndex < RowCount; RowIndex++) { for (colIndex = 0; colIndex < colCount; colIndex++) { objData[RowIndex, colIndex] = mDataTable.Rows[RowIndex - 1][colIndex].ToString(); } } // 写入Excel //((Excel.Range)xlSheet.Columns["A:A ",System.Reflection.Missing.Value]).ColumnWidth = 16; range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[RowCount, colCount]); range.Value2 = objData; range = (Microsoft.Office.Interop.Excel.Range)xlSheet.Columns["A:B", System.Type.Missing]; range.ColumnWidth = 20; // 保存 try { xlBook.Saved = true; xlBook.SaveCopyAs(strFileName); MessageBox.Show("数据转存为Excel成功"); } catch (Exception ee) { strMsg = ee.Message; return(false); } finally { range = null; xlSheet = null; xlBook = null; xlApp.Quit(); xlApp = null; GC.Collect(); } return(true); }
private void fillDocumentExcel2(string _filename) { Object wMissing = System.Reflection.Missing.Value; Object wTrue = true; Object wFalse = false; string[] text = inputAppFullName.Text.Split(';'); string d = text[0]; Microsoft.Office.Interop.Excel.Application appExcel1 = new Microsoft.Office.Interop.Excel.Application(); // Microsoft.Office.Interop.Excel.Workbook bookExcel = new Microsoft.Office.Interop.Excel.Workbook(); Microsoft.Office.Interop.Excel.Worksheet worksheetExcel1 = new Microsoft.Office.Interop.Excel.Worksheet(); Microsoft.Office.Interop.Excel.Sheets sheetsExcel1; Microsoft.Office.Interop.Excel.Range rangeExcel1; appExcel1.Visible = true; //Работа с документом - Приходный ордер appExcel1.Workbooks.Open(_filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); sheetsExcel1 = appExcel1.Worksheets; worksheetExcel1 = (Microsoft.Office.Interop.Excel.Worksheet)sheetsExcel1.get_Item(1); rangeExcel1 = worksheetExcel1.get_Range("I17", "K17"); rangeExcel1.Merge(Type.Missing); rangeExcel1.Value2 = inputNumContract.Text; rangeExcel1 = worksheetExcel1.get_Range("L17", "N17"); rangeExcel1.Merge(Type.Missing); rangeExcel1.Value2 = dateRec.Text; rangeExcel1 = worksheetExcel1.get_Range("G27:G28", "J27:J28"); rangeExcel1.Merge(Type.Missing); rangeExcel1.Value2 = sumRec.Text + "руб. 00 коп."; rangeExcel1 = worksheetExcel1.get_Range("C29:C30", "N29:N30"); rangeExcel1.Merge(Type.Missing); rangeExcel1.Value2 = "через " + d; rangeExcel1 = worksheetExcel1.get_Range("C31:C34", "N31:N34"); rangeExcel1.Merge(Type.Missing); rangeExcel1.Value2 = inputArBase.Text; rangeExcel1 = worksheetExcel1.get_Range("C36", "N36"); rangeExcel1.Merge(Type.Missing); rangeExcel1.Value2 = inputAppCost.Text; rangeExcel1 = worksheetExcel1.get_Range("S8:S9", "Y8:Y9"); rangeExcel1.Merge(Type.Missing); rangeExcel1.Value2 = "через" + d; rangeExcel1 = worksheetExcel1.get_Range("S13:S14", "Y13:Y14"); rangeExcel1.Merge(Type.Missing); rangeExcel1.Value2 = inputArBase.Text; rangeExcel1 = worksheetExcel1.get_Range("S18:S19", "Y18:Y19"); rangeExcel1.Merge(Type.Missing); rangeExcel1.Value2 = sumRec.Text; rangeExcel1 = worksheetExcel1.get_Range("S31:S32", "Y31:Y32"); rangeExcel1.Merge(Type.Missing); rangeExcel1.Value2 = dateRec.Text; }
/// <summary> /// 通用导出Excle /// </summary> /// <param name="dt"></param> /// <returns></returns> public ActionResult Excle(DataTable dt) { string path = "~/DownLoadTemplate/" + DateTime.Now.Year + DateTime.Now.Month + DateTime.Now.Day + DateTime.Now.Millisecond + ".xls"; if (dt != null) { Microsoft.Office.Interop.Excel.Application xlApp = null; try { xlApp = new Microsoft.Office.Interop.Excel.Application(); } catch (Exception ex) { throw ex; } if (xlApp != null) { try { Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true); object oMissing = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Worksheet xlSheet = null; xlSheet = xlBook.Worksheets[1]; xlSheet.Name = dt.TableName; int rowIndex = 1; int colIndex = 1; int colCount = dt.Columns.Count; int rowCount = dt.Rows.Count; //列名的处理 for (int i = 0; i < colCount; i++) { xlSheet.Cells[rowIndex, colIndex] = dt.Columns[i].ColumnName; colIndex++; } //列名加粗显示 xlSheet.get_Range(xlSheet.Cells[rowIndex, 1], xlSheet.Cells[rowIndex, colCount]).Font.Bold = true; xlSheet.get_Range(xlSheet.Cells[rowIndex, 1], xlSheet.Cells[rowCount + 1, colCount]).Font.Name = "Arial"; xlSheet.get_Range(xlSheet.Cells[rowIndex, 1], xlSheet.Cells[rowCount + 1, colCount]).Font.Size = "10"; rowIndex++; for (int i = 0; i < rowCount; i++) { colIndex = 1; for (int j = 0; j < colCount; j++) { xlSheet.Cells[rowIndex, colIndex] = dt.Rows[i][j].ToString(); colIndex++; } rowIndex++; } xlSheet.Cells.EntireColumn.AutoFit(); xlApp.DisplayAlerts = false; path = Path.GetFullPath(path); xlBook.SaveCopyAs(path); xlBook.Close(false, null, null); xlApp.Workbooks.Close(); Marshal.ReleaseComObject(xlSheet); Marshal.ReleaseComObject(xlBook); xlBook = null; } catch (Exception ex) { return(Json(new ReturnResult { Data = "", Message = ex.Message, Result = 1 })); } finally { xlApp.Quit(); Marshal.ReleaseComObject(xlApp); int generation = System.GC.GetGeneration(xlApp); xlApp = null; System.GC.Collect(generation); } } } return(Json(new ReturnResult { Data = "", Message = "导出成功", Result = 0 })); }
public void SaveOutputToFile(string Path) { Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook ExcelWorkBook = null; Microsoft.Office.Interop.Excel.Worksheet ExcelWorkSheet = null; //ExcelApp.Visible = true; ExcelWorkBook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); //ExcelWorkBook.Worksheets.Add(); //Adding New Sheet in Excel Workbook //try { ExcelWorkSheet = ExcelWorkBook.Worksheets[1]; // Compulsory Line in which sheet you want to write data int CurrentRow = 2; int CurrentColumn = 1; ExcelWorkSheet.Cells[1, 1] = "Rejestracja"; ExcelWorkSheet.Cells[1, 2] = "Kilometry"; ExcelWorkSheet.Cells[1, 3] = "Koszt AdBlue"; ExcelWorkSheet.Cells[1, 4] = "Ilosc AdBlue"; ExcelWorkSheet.Cells[1, 5] = "Diesel koszt"; ExcelWorkSheet.Cells[1, 6] = "Ilosc Diesel"; ExcelWorkSheet.Cells[1, 7] = "Podatek Drogowy"; ExcelWorkSheet.Cells[1, 8] = "Inne Koszty"; ExcelWorkSheet.Cells[1, 9] = "Litry Diesel / 100 km"; ExcelWorkSheet.Cells[1, 10] = "Litry AdBlue / 100 km"; ExcelWorkSheet.Cells[1, 11] = "EUR Diesel / 100 km"; ExcelWorkSheet.Cells[1, 12] = "EUR AdBlue / 100 km"; ExcelWorkSheet.Cells[1, 13] = "Opłaty autostradowe / 100 km"; ExcelWorkSheet.Cells[1, 14] = "Inne opłaty / 100 km"; ExcelWorkSheet.Cells[1, 15] = "Wszystkie koszty / 100 km"; foreach (Truck element in TruckData) { String R = element.GetRegistration(); ExcelWorkSheet.Cells[CurrentRow, CurrentColumn] = R; CurrentColumn++; double K = Math.Round((double)element.Kilometers, 2); ExcelWorkSheet.Cells[CurrentRow, CurrentColumn] = K; CurrentColumn++; K = Math.Round((double)element.AdblueCost, 2); ExcelWorkSheet.Cells[CurrentRow, CurrentColumn] = K; CurrentColumn++; K = Math.Round((double)element.AdblueL, 2); ExcelWorkSheet.Cells[CurrentRow, CurrentColumn] = K; CurrentColumn++; K = Math.Round((double)element.DieselCost, 2); ExcelWorkSheet.Cells[CurrentRow, CurrentColumn] = K; CurrentColumn++; K = Math.Round((double)element.DieselL, 2); ExcelWorkSheet.Cells[CurrentRow, CurrentColumn] = K; CurrentColumn++; K = Math.Round((double)element.RoadTax, 2); ExcelWorkSheet.Cells[CurrentRow, CurrentColumn] = K; CurrentColumn++; K = Math.Round((double)element.OtherCost, 2); ExcelWorkSheet.Cells[CurrentRow, CurrentColumn] = K; CurrentColumn++; // PER 100 if (element.Kilometers != 0) { // LDiesel / 100 //TODO: K = Math.Round((double)(element.DieselL / (float)element.Kilometers * 100.0f), 2); ExcelWorkSheet.Cells[CurrentRow, CurrentColumn] = K; CurrentColumn++; // LAdBlue / 100 K = Math.Round((double)(element.AdblueL / (float)element.Kilometers * 100.0f), 2); ExcelWorkSheet.Cells[CurrentRow, CurrentColumn] = K; CurrentColumn++; // Eur Diesel / 100 K = Math.Round((double)(element.DieselCost / (float)element.Kilometers * 100.0f), 2); ExcelWorkSheet.Cells[CurrentRow, CurrentColumn] = K; CurrentColumn++; // Eur AdBlue / 100 K = Math.Round((double)(element.AdblueCost / (float)element.Kilometers * 100.0f), 2); ExcelWorkSheet.Cells[CurrentRow, CurrentColumn] = K; CurrentColumn++; // RoadTax / 100 K = Math.Round((double)(element.RoadTax / (float)element.Kilometers * 100.0f), 2); ExcelWorkSheet.Cells[CurrentRow, CurrentColumn] = K; CurrentColumn++; // Other / 100 K = Math.Round((double)(element.OtherCost / (float)element.Kilometers * 100.0f), 2); ExcelWorkSheet.Cells[CurrentRow, CurrentColumn] = K; CurrentColumn++; // All / 100 K = Math.Round((double)(( element.DieselL + element.DieselCost + element.AdblueL + element.AdblueCost + element.OtherCost + element.RoadTax) / (float)element.Kilometers * 100.0f), 2); ExcelWorkSheet.Cells[CurrentRow, CurrentColumn] = K; } CurrentColumn = 1; CurrentRow++; } //cosmetics ExcelWorkBook.Worksheets[1].Name = "Raport";//Renaming the Sheet1 to MySheet Microsoft.Office.Interop.Excel.Range aRange = ExcelWorkSheet.get_Range("A1", "O1"); aRange.Columns.AutoFit(); if (notFoundTrucks.Count != 0) { //the worksheet with not found trucks ExcelWorkSheet = ExcelWorkBook.Sheets.Add(After: ExcelWorkBook.Sheets[ExcelWorkBook.Sheets.Count]); ExcelWorkSheet.Name = "Brakujące rejestracje"; ExcelWorkSheet.Cells[1, 1] = "Plik"; ExcelWorkSheet.Cells[1, 2] = "Linia"; ExcelWorkSheet.Cells[1, 3] = "Rejestracja"; ExcelWorkSheet.Cells[1, 4] = "Dodatkowe informacje"; int currentRow = 2, currentColumn = 1; foreach (TruckNotFound element in notFoundTrucks) { String filename = element.getFilename(); ExcelWorkSheet.Cells[currentRow, currentColumn] = filename; currentColumn++; int linia = element.getLine(); ExcelWorkSheet.Cells[currentRow, currentColumn] = linia; currentColumn++; string registration = element.GetRegistration(); ExcelWorkSheet.Cells[currentRow, currentColumn] = registration; currentColumn++; string extras = element.getOtherData(); ExcelWorkSheet.Cells[currentRow, currentColumn] = extras; currentColumn++; currentRow++; currentColumn = 1; } Microsoft.Office.Interop.Excel.Range anotherRange = ExcelWorkSheet.get_Range("A:A", "O:O"); anotherRange.Columns.AutoFit(); } ExcelWorkBook.SaveAs(Path); ExcelWorkBook.Close(false); ExcelApp.Quit(); //Marshal.ReleaseComObject(ExcelWorkSheet); //Marshal.ReleaseComObject(ExcelWorkBook); //Marshal.ReleaseComObject(ExcelApp); } //catch (Exception exHandle) { //Console.WriteLine("Exception: " + exHandle.Message); //Console.ReadLine(); } }
/// <summary> /// 导出Excel 的方法 /// </summary> private void tslExport_Click(string fileName, DataGridView myDGV) { string saveFileName = ""; //bool fileSaved = false; SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.DefaultExt = "xls"; saveDialog.Filter = "Excel文件|*.xls"; 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 = 1; i < myDGV.ColumnCount; i++) { worksheet.Cells[1, i] = myDGV.Columns[i].HeaderText; } //写入数值 for (int r = 0; r < myDGV.Rows.Count; r++) { for (int i = 1; i < myDGV.ColumnCount; i++) { worksheet.Cells[r + 2, i] = myDGV.Rows[r].Cells[i].Value; } System.Windows.Forms.Application.DoEvents(); } worksheet.Columns.EntireColumn.AutoFit();//列宽自适应 Microsoft.Office.Interop.Excel.Range rang = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[myDGV.Rows.Count + 2, 2]); rang.NumberFormat = "000000000000"; if (saveFileName != "") { try { workbook.Saved = true; workbook.SaveCopyAs(saveFileName); //fileSaved = true; } catch (Exception ex) { //fileSaved = false; MessageBox.Show("导出文件时出错,文件可能正被打开!\n" + ex.Message); } } //else //{ // fileSaved = false; //} xlApp.Quit(); GC.Collect();//强行销毁 // if (fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //打开EXCEL MessageBox.Show(fileName + ",保存成功", "提示", MessageBoxButtons.OK); }
public bool TableToExcel(string strfilename) { try { dataGridView1.DataSource = null; dataGridView1.DataSource = Global.Db.ExportExcel_Getsu(cbb_Batch.Text); Microsoft.Office.Interop.Excel.Application App = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook book = App.Workbooks.Open(strfilename, 0, true, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); Microsoft.Office.Interop.Excel.Worksheet wrksheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet; int h = 2; int i = 0; progressBarControl1.EditValue = 0; progressBarControl1.Properties.Step = 1; progressBarControl1.Properties.PercentView = true; progressBarControl1.Properties.Maximum = dataGridView1.Rows.Count; progressBarControl1.Properties.Minimum = 0; progressBarControl1.Properties.StartColor = Color.DarkRed; // choose the color progressBarControl1.Properties.EndColor = Color.Green; // choose the color foreach (DataGridViewRow dr in dataGridView1.Rows) { wrksheet.Cells[h, 1] = dr.Cells[0].Value?.ToString() ?? ""; //tên image wrksheet.Cells[h, 2] = dr.Cells[1].Value?.ToString() ?? ""; //1 wrksheet.Cells[h, 3] = dr.Cells[2].Value?.ToString() ?? ""; if ((dr.Cells[3].Value?.ToString() ?? "").Length == 6) { wrksheet.Cells[h, 4] = (dr.Cells[3].Value?.ToString() ?? "").Substring(0, 2); wrksheet.Cells[h, 5] = (dr.Cells[3].Value?.ToString() ?? "").Substring(2, 2); wrksheet.Cells[h, 6] = (dr.Cells[3].Value?.ToString() ?? "").Substring(4, 2); } else { wrksheet.Cells[h, 4] = ""; wrksheet.Cells[h, 5] = ""; wrksheet.Cells[h, 6] = dr.Cells[3].Value?.ToString() ?? ""; } wrksheet.Cells[h, 7] = dr.Cells[4].Value?.ToString() ?? ""; wrksheet.Cells[h, 8] = dr.Cells[5].Value?.ToString() ?? ""; wrksheet.Cells[h, 9] = dr.Cells[6].Value?.ToString() ?? ""; wrksheet.Cells[h, 10] = dr.Cells[7].Value?.ToString() ?? ""; wrksheet.Cells[h, 11] = dr.Cells[8].Value?.ToString() ?? ""; wrksheet.Cells[h, 12] = dr.Cells[9].Value?.ToString() ?? ""; wrksheet.Cells[h, 13] = dr.Cells[10].Value?.ToString() ?? ""; wrksheet.Cells[h, 14] = dr.Cells[11].Value?.ToString() ?? ""; if ((dr.Cells[12].Value?.ToString() ?? "").Length == 4) { wrksheet.Cells[h, 15] = (dr.Cells[12].Value?.ToString() ?? "").Substring(0, 2); wrksheet.Cells[h, 16] = (dr.Cells[12].Value?.ToString() ?? "").Substring(2, 2); } else { wrksheet.Cells[h, 15] = ""; wrksheet.Cells[h, 16] = dr.Cells[12].Value?.ToString() ?? ""; } wrksheet.Cells[h, 17] = dr.Cells[13].Value?.ToString() ?? ""; wrksheet.Cells[h, 18] = dr.Cells[14].Value?.ToString() ?? ""; wrksheet.Cells[h, 19] = dr.Cells[15].Value?.ToString() ?? ""; wrksheet.Cells[h, 20] = dr.Cells[16].Value?.ToString() ?? ""; wrksheet.Cells[h, 21] = dr.Cells[17].Value?.ToString() ?? ""; wrksheet.Cells[h, 22] = dr.Cells[18].Value?.ToString() ?? ""; wrksheet.Cells[h, 23] = dr.Cells[19].Value?.ToString() ?? ""; wrksheet.Cells[h, 24] = dr.Cells[20].Value?.ToString() ?? ""; wrksheet.Cells[h, 25] = dr.Cells[21].Value?.ToString() ?? ""; wrksheet.Cells[h, 26] = dr.Cells[22].Value?.ToString() ?? ""; wrksheet.Cells[h, 27] = dr.Cells[23].Value?.ToString() ?? ""; wrksheet.Cells[h, 28] = dr.Cells[24].Value?.ToString() ?? ""; wrksheet.Cells[h, 29] = dr.Cells[25].Value?.ToString() ?? ""; wrksheet.Cells[h, 30] = dr.Cells[26].Value?.ToString() ?? ""; lb_SoDong.Text = (h - 1) + @"/" + dataGridView1.Rows.Count; Microsoft.Office.Interop.Excel.Range rowHead = wrksheet.get_Range("A1", "AD" + h); rowHead.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid; i++; h++; progressBarControl1.PerformStep(); progressBarControl1.Update(); } string savePath = ""; saveFileDialog1.Title = "Save Excel Files"; saveFileDialog1.Filter = "Excel files (*.xlsx)|*.xlsx"; saveFileDialog1.FileName = cbb_Batch.Text + "_Getsu"; saveFileDialog1.RestoreDirectory = true; if (saveFileDialog1.ShowDialog() == DialogResult.OK) { book.SaveCopyAs(saveFileDialog1.FileName); book.Saved = true; savePath = Path.GetDirectoryName(saveFileDialog1.FileName); App.Quit(); } else { MessageBox.Show(@"Error exporting excel!"); return(false); } Process.Start(savePath); return(true); } catch (Exception ex) { MessageBox.Show(ex.Message); return(false); } }
private void GenerateExcel(bool isPDF, SaveFileDialog sfd) { CopyAlltoClipboard(); object misValue = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Application xlexcel = new Microsoft.Office.Interop.Excel.Application(); xlexcel.DisplayAlerts = false; Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue); Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); int colStart = 1; int addCol = 0; int logoCellLeft = 0; if (cboReports.SelectedValue.ToString() == "Absentees Report" || cboReports.SelectedValue.ToString() == "No Time Out Report") { addCol = 2; colStart += addCol; logoCellLeft = 100; } else if (cboReports.SelectedValue.ToString() == "Attendance Report") { logoCellLeft = 12; } Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[8, colStart]; xlWorkSheet.Shapes.AddPicture(Path.GetDirectoryName(Assembly.GetEntryAssembly().Location) + @LOGO_PATH, MsoTriState.msoFalse, MsoTriState.msoCTrue, logoCellLeft, 0, 90, 90); xlWorkSheet.get_Range("A1", "A1").Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; xlWorkSheet.get_Range("A1", "A1").Style.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignTop; CR.Select(); xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); CR.Rows.AutoFit(); CR.get_Range("A1").Columns.EntireColumn.AutoFit(); CR.get_Range("C1", "K1").Columns.EntireColumn.AutoFit(); CR.get_Range("B1").Columns.ColumnWidth = 35; if (cboReports.SelectedValue.ToString().Trim() == "Attendance Report") { CR.get_Range("D1", "K1").Columns.EntireColumn.AutoFit(); CR.get_Range("C1").Columns.ColumnWidth = 20; } CR.get_Range("B1").Cells.Style.WrapText = true; xlWorkSheet.Cells[3, 3 + addCol - 1] = "' " + cboReports.SelectedValue.ToString(); xlWorkSheet.Cells[3, 3 + addCol - 1].Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; xlWorkSheet.Cells[4, 3 + addCol - 1] = "' " + dtFrom.SelectedDate.Value.ToString("dd MMMMM yyyy") + " to " + dtTo.SelectedDate.Value.ToString("dd MMMM yyyy"); xlWorkSheet.Cells[4, 3 + addCol - 1].Cells.Style.WrapText = false; xlWorkSheet.Cells[4, 3 + addCol - 1].Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; xlWorkSheet.Cells[5, 3 + addCol - 1] = "' " + GetFilterUsed().Replace("_", ""); xlWorkSheet.Cells[5, 3 + addCol - 1].Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; int ctr = 1; while (ctr <= dgResults.Columns.Count) { ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[8, ctr + addCol]).Interior.Color = ColorTranslator.ToOle(Color.AliceBlue); ctr++; } for (int ctrC = 1; ctrC <= dgResults.Columns.Count; ctrC++) { for (int ctrR = 8; ctrR <= dgResults.Items.Count + 8; ctrR++) { ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[ctrR, ctrC + addCol]).BorderAround(LineStyle.Thin, Microsoft.Office.Interop.Excel.XlBorderWeight.xlHairline, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, ColorTranslator.ToOle(Color.AliceBlue)); } } Microsoft.Office.Interop.Excel.Range rg = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, colStart]; rg.EntireColumn.NumberFormat = "yyyy/MM/dd"; if (cboReports.SelectedValue.ToString() == "Consolidated Report") { rg.EntireColumn.NumberFormat = "@"; } xlWorkBook.SaveAs(sfd.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlexcel.DisplayAlerts = true; xlWorkBook.Close(true, misValue, misValue); xlexcel.Quit(); ReleaseObject(xlWorkSheet); ReleaseObject(xlWorkBook); ReleaseObject(xlexcel); dgResults.UnselectAllCells(); Clipboard.Clear(); if (File.Exists(sfd.FileName) && !isPDF) { CheckIfFileIsOpen(sfd); //System.Diagnostics.Process.Start(sfd.FileName); System.Diagnostics.Process.Start(Path.GetDirectoryName(sfd.FileName)); } }
/// <summary> /// 导入文本 /// </summary> private void ImportText(params object[] param) { List <string> listItems = (List <string>)param[0]; Microsoft.Office.Interop.Excel.Workbook xBook = null; Microsoft.Office.Interop.Excel.Worksheet xSheet = null; // 显示进度条 this.ResetProcessBar(listItems.Count); try { StringBuilder failFiles = new StringBuilder(); // 创建Application对象 this.xApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); // 得到WorkBook对象, 打开已有的文件 xBook = this.xApp.Workbooks._Open( this.baseFile, Missing.Value, Missing.Value, Missing.Value, Missing.Value , Missing.Value, Missing.Value, Missing.Value, Missing.Value , Missing.Value, Missing.Value, Missing.Value, Missing.Value); for (int i = xBook.Sheets.Count; i >= 1; i--) { // 取得相应的Sheet xSheet = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets[i]; int sheetIndex = -1; string sheetName = string.Empty; for (int j = 0; j < listItems.Count; j++) { sheetName = listItems[j].Split(' ')[0]; if (xSheet.Name.Equals(sheetName, StringComparison.OrdinalIgnoreCase)) { sheetIndex = j; break; } } if (sheetIndex > -1) { // 更新当前文本 this.fileList.Invoke((MethodInvoker) delegate() { this.fileList.SelectedIndex = sheetIndex; }); // 取得当前Sheet的中文文本 int lineNum = 1; int blankNum = 0; StringBuilder sb = new StringBuilder(); while (blankNum < 4) { string cellValue = xSheet.get_Range("G" + lineNum, Missing.Value).Value2 as string; sb.Append(cellValue).Append("\n"); if (string.IsNullOrEmpty(cellValue)) { blankNum++; } else { blankNum = 0; } lineNum++; } sb = sb.Replace("\n\n\n\n\n", "\n"); this.Invoke((MethodInvoker) delegate() { this.txtCn.Text = sb.ToString(); // 保存 if (!this.Save()) { //throw new Exception("有文件长度检查失败"); failFiles.Append("\n").Append(sheetName); } }); } else { failFiles.Append("\n未找到 ").Append(xSheet.Name); } // 更新进度条 this.ProcessBarStep(); } // 隐藏进度条 this.CloseProcessBar(); // 显示保存完成信息 if (failFiles.Length == 0) { MessageBox.Show("完全成功导入!"); } else { MessageBox.Show("导入完成,下面文件失败" + failFiles.ToString()); } // 导入成功后的处理 this.AfterImport(); } catch (Exception me) { MessageBox.Show(this.baseFile + "\n" + me.Message); } finally { // 隐藏进度条 this.CloseProcessBar(); // 清空各种对象 xSheet = null; xBook = null; if (this.xApp != null) { this.xApp.Quit(); this.xApp = null; } } }
///////////////////////////////////////////////////////////////////////////////////////////////// ///////////////////////////////////////////////////////////////////////////////////////////////// private void button1_Click(object sender, EventArgs e) { onay = MessageBox.Show("Onaylıyor musunuz?", "Onay", MessageBoxButtons.YesNo); if (onay == DialogResult.Yes) { try { mysqlbaglan.Open(); string komut = "insert into fatura(sayin,vdno,no,adres,adres2,tarih,fisNo,tarih1,aciklama,adet,kab,cins,kilo,fiyat,tutar,fisNo2,tarih2,aciklama2,adet2,kab2,cins2,kilo2,fiyat2,tutar2,fisNo3,tarih3,aciklama3,adet3,kab3,cins3,kilo3,fiyat3,tutar3,fisNo4,tarih4,aciklama4,adet4,kab4,cins4,kilo4,fiyat4,tutar4,fisNo5,tarih5,aciklama5,adet5,kab5,cins5,kilo5,fiyat5,tutar5,fisNo6,tarih6,aciklama6,adet6,kab6,cins6,kilo6,fiyat6,tutar6,toplam,genelToplam,tesellumNo) values('" + comboBox1.Text + "', '" + textBox2.Text + "','" + textBox1.Text + "','" + textBox16.Text + "','" + textBox23.Text + "', '" + dateTimePicker1.Text + "', '" + comboBox2.Text + "', '" + textBox4.Text + "', '" + textBox5.Text + "', '" + textBox6.Text + "', '" + textBox7.Text + "', '" + textBox8.Text + "', '" + textBox9.Text + "','" + textBox50.Text + "','" + textBox56.Text + "','" + comboBox3.Text + "', '" + textBox15.Text + "', '" + textBox14.Text + "', '" + textBox13.Text + "', '" + textBox12.Text + "', '" + textBox11.Text + "', '" + textBox10.Text + "','" + textBox49.Text + "','" + textBox55.Text + "','" + comboBox4.Text + "', '" + textBox22.Text + "', '" + textBox21.Text + "', '" + textBox20.Text + "', '" + textBox19.Text + "', '" + textBox18.Text + "', '" + textBox17.Text + "','" + textBox48.Text + "','" + textBox54.Text + "','" + comboBox5.Text + "', '" + textBox29.Text + "', '" + textBox28.Text + "', '" + textBox27.Text + "', '" + textBox26.Text + "', '" + textBox25.Text + "', '" + textBox24.Text + "','" + textBox47.Text + "','" + textBox53.Text + "','" + comboBox6.Text + "', '" + textBox36.Text + "', '" + textBox35.Text + "', '" + textBox34.Text + "', '" + textBox33.Text + "', '" + textBox32.Text + "', '" + textBox31.Text + "','" + textBox46.Text + "','" + textBox52.Text + "','" + comboBox7.Text + "', '" + textBox43.Text + "', '" + textBox42.Text + "', '" + textBox41.Text + "', '" + textBox40.Text + "', '" + textBox39.Text + "', '" + textBox38.Text + "','" + textBox45.Text + "','" + textBox51.Text + "','" + label1.Text + "','" + label2.Text + "','" + textBox3.Text + "')"; MySqlCommand kmt = new MySqlCommand(komut, mysqlbaglan); kmt.ExecuteNonQuery(); Microsoft.Office.Interop.Excel.Application objExcel = new Microsoft.Office.Interop.Excel.Application(); objExcel.Visible = true; Microsoft.Office.Interop.Excel.Workbook objBook = objExcel.Workbooks.Open("c:\\fatura.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); Microsoft.Office.Interop.Excel.Worksheet objSheet = (Microsoft.Office.Interop.Excel.Worksheet)objBook.Worksheets.get_Item(1); Microsoft.Office.Interop.Excel.Range objRange; objRange = objSheet.get_Range("K7", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, dateTimePicker1.Text); objRange = objSheet.get_Range("b7", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox2.Text); objRange = objSheet.get_Range("e7", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox1.Text); objRange = objSheet.get_Range("k4", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox3.Text); objRange = objSheet.get_Range("b3", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, comboBox1.Text); objRange = objSheet.get_Range("A4", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox16.Text); objRange = objSheet.get_Range("A5", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox23.Text); objRange = objSheet.get_Range("A23", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox30.Text); // 1. SIRA objRange = objSheet.get_Range("A10", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, comboBox2.Text); objRange = objSheet.get_Range("B10", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox4.Text); objRange = objSheet.get_Range("C10", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox5.Text); objRange = objSheet.get_Range("F10", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox6.Text); objRange = objSheet.get_Range("G10", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox7.Text); objRange = objSheet.get_Range("H10", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox8.Text); objRange = objSheet.get_Range("I10", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox9.Text); objRange = objSheet.get_Range("J10", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox50.Text); objRange = objSheet.get_Range("K10", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox56.Text); // 2. SIRA objRange = objSheet.get_Range("A12", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, comboBox3.Text); objRange = objSheet.get_Range("B12", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox15.Text); objRange = objSheet.get_Range("C12", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox14.Text); objRange = objSheet.get_Range("F12", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox13.Text); objRange = objSheet.get_Range("G12", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox12.Text); objRange = objSheet.get_Range("H12", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox11.Text); objRange = objSheet.get_Range("I12", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox10.Text); objRange = objSheet.get_Range("J12", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox49.Text); objRange = objSheet.get_Range("K12", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox55.Text); //3. SIRA objRange = objSheet.get_Range("A14", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, comboBox4.Text); objRange = objSheet.get_Range("B14", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox22.Text); objRange = objSheet.get_Range("C14", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox21.Text); objRange = objSheet.get_Range("F14", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox20.Text); objRange = objSheet.get_Range("G14", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox19.Text); objRange = objSheet.get_Range("H14", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox18.Text); objRange = objSheet.get_Range("I14", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox17.Text); objRange = objSheet.get_Range("J14", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox48.Text); objRange = objSheet.get_Range("K14", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox54.Text); //4. SIRA objRange = objSheet.get_Range("A16", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, comboBox5.Text); objRange = objSheet.get_Range("B16", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox29.Text); objRange = objSheet.get_Range("C16", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox28.Text); objRange = objSheet.get_Range("F16", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox27.Text); objRange = objSheet.get_Range("G16", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox26.Text); objRange = objSheet.get_Range("H16", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox25.Text); objRange = objSheet.get_Range("I16", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox24.Text); objRange = objSheet.get_Range("J16", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox47.Text); objRange = objSheet.get_Range("K16", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox53.Text); //5. SIRA objRange = objSheet.get_Range("A18", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, comboBox6.Text); objRange = objSheet.get_Range("B18", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox36.Text); objRange = objSheet.get_Range("C18", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox35.Text); objRange = objSheet.get_Range("F18", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox34.Text); objRange = objSheet.get_Range("G18", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox33.Text); objRange = objSheet.get_Range("H18", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox32.Text); objRange = objSheet.get_Range("I18", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox31.Text); objRange = objSheet.get_Range("J18", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox46.Text); objRange = objSheet.get_Range("K18", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox52.Text); //6. SIRA objRange = objSheet.get_Range("A20", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, comboBox7.Text); objRange = objSheet.get_Range("B20", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox43.Text); objRange = objSheet.get_Range("C20", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox42.Text); objRange = objSheet.get_Range("F20", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox41.Text); objRange = objSheet.get_Range("G20", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox40.Text); objRange = objSheet.get_Range("H20", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox39.Text); objRange = objSheet.get_Range("I20", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox38.Text); objRange = objSheet.get_Range("J20", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox45.Text); objRange = objSheet.get_Range("K20", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, textBox51.Text); objSheet.PrintOutEx(1, 1, 2, true); Form anasayfa = new anasayfa(); anasayfa.Show(); this.Hide(); } catch (Exception h) { MessageBox.Show("Beklenmeyen bir hata oluştu lütfen tekrar deneyiniz" + h, "HATA", MessageBoxButtons.OK, MessageBoxIcon.Stop); } } }
private void fpSpread_addsheet() { WaitDialogForm wait = null; wait = new WaitDialogForm("", "正在加载数据, 请稍候..."); try { //打开Excel表格 //清空工作表 fpSpread1.Sheets.Clear(); fpSpread1.OpenExcel(Tcommon.ExcelFilePath + ExcelName); TC.SpreadRemoveEmptyCells(fpSpread1); } catch (System.Exception e) { //如果打开出错则重新生成并保存 fpSpread1.Sheets.Clear(); Firstadddata(); //判断文件夹是否存在,不存在则创建 if (!Directory.Exists(Tcommon.CurrentPath + Tcommon.ExcelDir)) { Directory.CreateDirectory(Tcommon.CurrentPath + Tcommon.ExcelDir); } //保存excel文件 fpSpread1.SaveExcel(Tcommon.ExcelFilePath + ExcelName); //以下是打开文件设表格自动换行 // 定义要使用的Excel 组件接口 // 定义Application 对象,此对象表示整个Excel 程序 Microsoft.Office.Interop.Excel.Application excelApp = null; // 定义Workbook对象,此对象代表工作薄 Microsoft.Office.Interop.Excel.Workbook workBook; // 定义Worksheet 对象,此对象表示Execel 中的一张工作表 Microsoft.Office.Interop.Excel.Worksheet ws = null; Microsoft.Office.Interop.Excel.Range range = null; excelApp = new Microsoft.Office.Interop.Excel.Application(); string filename = Tcommon.ExcelFilePath + ExcelName; workBook = excelApp.Workbooks.Open(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); for (int i = 1; i <= workBook.Worksheets.Count; i++) { ws = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[i]; //取消保护工作表 ws.Unprotect(Missing.Value); //有数据的行数 int row = ws.UsedRange.Rows.Count; //有数据的列数 int col = ws.UsedRange.Columns.Count; //创建一个区域 range = ws.get_Range(ws.Cells[1, 1], ws.Cells[row, col]); //设区域内的单元格自动换行 range.WrapText = true; //保护工作表 ws.Protect(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } //保存工作簿 workBook.Save(); //关闭工作簿 excelApp.Workbooks.Close(); } wait.Close(); }
/// <summary> /// 导出Excel 的方法 /// </summary> private void tslExport_Excel(string fileName, DataGridView myDGV) { string saveFileName = ""; SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.DefaultExt = "xls"; saveDialog.Filter = "Excel文件|*.xls"; 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; } ISdao = true; groupBox3.Visible = true; btnSet.Text = "取消导出"; progressBar1.Maximum = myDGV.SelectedRows.Count; progressBar1.Value = 0; label19.Text = "正在导出:" + fileName; 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 Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range("A1", "Z" + (myDGV.SelectedRows.Count + 10)); //把Execl设置问文本格式 range.NumberFormatLocal = "@"; //写入标题 for (int i = 1; i < myDGV.ColumnCount; i++) { worksheet.Cells[1, i] = myDGV.Columns[i].HeaderText; } //写入数值 int s = 0; for (int r = 0; r < myDGV.SelectedRows.Count; r++) { if (ISdao) { for (int i = 1; i < myDGV.ColumnCount; i++) { worksheet.Cells[s + 2, i] = myDGV.Rows[r].Cells[i].Value; } System.Windows.Forms.Application.DoEvents(); s++; progressBar1.Value++; } else { break; } } worksheet.Columns.EntireColumn.AutoFit();//列宽自适应 Microsoft.Office.Interop.Excel.Range rang = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[myDGV.Rows.Count + 2, 2]); rang.NumberFormat = "000000000000"; if (saveFileName != "") { try { workbook.Saved = true; workbook.SaveCopyAs(saveFileName); //fileSaved = true; } catch { //fileSaved = false; MessageBox.Show("导出文件时出错,文件可能正被打开!\n"); } } //else //{ // fileSaved = false; //} xlApp.Quit(); GC.Collect();//强行销毁 if (progressBar1.Value >= progressBar1.Maximum) { label19.Text = fileName; btnSet.Text = "导出完成"; } }
private void ExcelFeedBack_User(List <string> idimage) { if (File.Exists(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\Feedback_User.xlsx")) { File.Delete(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\Feedback_User.xlsx"); File.WriteAllBytes((Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "/Feedback_User.xlsx"), Properties.Resources.Feedback_User); } else { File.WriteAllBytes( (Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "/Feedback_User.xlsx"), Properties.Resources.Feedback_User); } int r = 1; //int distance = 15; App = new Microsoft.Office.Interop.Excel.Application(); book = App.Workbooks.Open(System.Environment.GetFolderPath(System.Environment.SpecialFolder.MyDocuments) + "\\Feedback_User.xlsx", 0, true, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); wrksheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet; string pathServer = Global.Webservice + Folder + "/" + cbb_batch.Text; for (int i = 0; i < idimage.Count; i++) { string id = idimage[i]; Microsoft.Office.Interop.Excel.Range oRange = wrksheet.Cells[r + 1, 2]; float Left = (float)((double)oRange.Left); float Top = (float)((double)oRange.Top + 2); Microsoft.Office.Interop.Excel.Range final = wrksheet.Cells[r + 20, 9]; float leftFinal = (float)((double)final.Left) - Left - 1; float topFinal = (float)((double)final.Top + 2) - Top; wrksheet.Shapes.AddPicture(pathServer + "/" + idimage[i], Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, Left, Top, leftFinal, topFinal);//365, 270); // oRange.RowHeight = 367; // wrksheet.Cells[1, 20] = wrksheet.Shapes.AddPicture(pathServer + "/" + idimage[i], Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 20, distance, 365, 270); //Image oImage = Image.FromFile(pathServer + "/" + idimage[i]); //wrksheet.Shapes.AddPicture(pathServer + "/" + idimage[i], Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 20, distance, 365, 270); // distance += 300; var deso = Global.Db.FeedBackExcel_User(id, cbb_username.Text, cbb_batch.Text).ToList(); int h = 9; wrksheet.Cells[r + 1, h] = "UserName"; wrksheet.Cells[r + 2, h] = "Trường 1"; wrksheet.Cells[r + 3, h] = "Trường 3"; wrksheet.Cells[r + 4, h] = "Trường 4"; wrksheet.Cells[r + 5, h] = "Trường 5"; wrksheet.Cells[r + 6, h] = "Trường 6"; wrksheet.Cells[r + 7, h] = "Trường 7"; wrksheet.Cells[r + 8, h] = "Trường 8.1"; wrksheet.Cells[r + 9, h] = "Trường 8.2"; wrksheet.Cells[r + 10, h] = "Trường 9"; wrksheet.Cells[r + 11, h] = "Trường 10"; wrksheet.Cells[r + 12, h] = "Trường 11"; wrksheet.Cells[r + 13, h] = "Trường 12"; wrksheet.Cells[r + 14, h] = "Trường 13"; wrksheet.Cells[r + 15, h] = "Trường 14"; // wrksheet.Cells[r + 15, h] = "FlagError"; wrksheet.Cells[r + 20, 1] = id + ""; wrksheet.Cells[r + 1, 1] = i + 1; for (int j = 0; j < deso.Count(); j++) { h++; wrksheet.Cells[r + 1, h] = deso[j].UserName + ""; wrksheet.Cells[r + 2, h] = deso[j].TruongSo01 + ""; wrksheet.Cells[r + 3, h] = deso[j].TruongSo03 + ""; wrksheet.Cells[r + 4, h] = deso[j].TruongSo04 + ""; wrksheet.Cells[r + 5, h] = deso[j].TruongSo05 + ""; wrksheet.Cells[r + 6, h] = deso[j].TruongSo06 + ""; wrksheet.Cells[r + 7, h] = deso[j].TruongSo07 + ""; wrksheet.Cells[r + 8, h] = deso[j].TruongSo08 + ""; wrksheet.Cells[r + 9, h] = deso[j].TruongSo08_2 + ""; wrksheet.Cells[r + 10, h] = deso[j].TruongSo09 + ""; wrksheet.Cells[r + 11, h] = deso[j].TruongSo10 + ""; wrksheet.Cells[r + 12, h] = deso[j].TruongSo11 + ""; wrksheet.Cells[r + 13, h] = deso[j].TruongSo12 + ""; wrksheet.Cells[r + 14, h] = deso[j].TruongSo13 + ""; wrksheet.Cells[r + 15, h] = deso[j].TruongSo14 + ""; // wrksheet.Cells[r + 15, h] = deso[j].FlagError + ""; } Microsoft.Office.Interop.Excel.Range cellImage1 = wrksheet.Cells[h - 10][r + 1]; Microsoft.Office.Interop.Excel.Range cellImage2 = wrksheet.Cells[h][r + 20]; Microsoft.Office.Interop.Excel.Range rangeImage = wrksheet.get_Range(cellImage1, cellImage2); rangeImage.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, 1); Microsoft.Office.Interop.Excel.Range cell1 = wrksheet.Cells[h - 2][r + 1]; Microsoft.Office.Interop.Excel.Range cell2 = wrksheet.Cells[h][r + 15]; Microsoft.Office.Interop.Excel.Range range = wrksheet.get_Range(cell1, cell2); range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid; range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGreen); r += 21; } string savePath = ""; saveFileDialog1.Title = "Save Excel Files"; saveFileDialog1.Filter = "Excel files (*.xlsx)|*.xlsx"; saveFileDialog1.FileName = "Feedback_" + cbb_batch.Text + "_" + cbb_username.Text; saveFileDialog1.RestoreDirectory = true; if (saveFileDialog1.ShowDialog() == DialogResult.OK) { book.SaveCopyAs(saveFileDialog1.FileName); book.Saved = true; savePath = Path.GetDirectoryName(saveFileDialog1.FileName); App.Quit(); } else { MessageBox.Show(@"Error exporting excel!"); return; } Process.Start(savePath); }
/// <summary> /// 导出Execl 全选 /// </summary> /// <param name="dt"></param> protected void ExportExcel(string fileName, DataTable dt) { try { if (dt == null || dt.Rows.Count == 0) { return; } string saveFileName = ""; SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.DefaultExt = "xls"; saveDialog.Filter = "Excel文件|*.xls"; saveDialog.FileName = fileName; saveDialog.ShowDialog(); saveFileName = saveDialog.FileName; if (saveFileName.IndexOf(":") < 0) { return; //被点了取消 } ISdao = true; groupBox3.Visible = true; btnSet.Text = "取消导出"; progressBar1.Maximum = dt.Columns.Count; progressBar1.Value = 0; label19.Text = "正在导出:" + fileName; 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 Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range("A1", "Z" + (dt.Rows.Count + 10)); range.NumberFormatLocal = "@"; //写入标题 for (int i = 0; i < dt.Columns.Count; i++) { worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName; } //写入数值 int s = 0; for (int r = 0; r < dt.Columns.Count; r++) { if (ISdao) { for (int i = 0; i < dt.Rows.Count; i++) { worksheet.Cells[i + 2, r + 1] = dt.Rows[i][r].ToString(); } System.Windows.Forms.Application.DoEvents(); progressBar1.Value++; } else { break; } } worksheet.Columns.EntireColumn.AutoFit();//列宽自适应 Microsoft.Office.Interop.Excel.Range rang = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[dt.Rows.Count + 2, 2]); rang.NumberFormat = "000000000000"; if (saveFileName != "") { try { workbook.Saved = true; workbook.SaveCopyAs(saveFileName); } catch { MessageBox.Show("导出文件时出错,文件可能正被打开!\n" + ""); } } xlApp.Quit(); GC.Collect();//强行销毁 //MessageBox.Show(fileName + ",保存成功", "提示", MessageBoxButtons.OK); if (progressBar1.Value >= progressBar1.Maximum) { label19.Text = fileName; btnSet.Text = "导出完成"; } } catch (System.Exception ex) { MessageBox.Show(fileName + ",导出错误", "提示", MessageBoxButtons.OK); } }
private void btnPrint_Click(object sender, EventArgs e) { string FileName = @"C:\temp\temp.xls"; // Copy DataGridView results to clipboard dataGridView1.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText; dataGridView1.SelectAll(); DataObject dataObj = dataGridView1.GetClipboardContent(); if (dataObj != null) { Clipboard.SetDataObject(dataObj); } object misValue = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Application xlexcel = new Microsoft.Office.Interop.Excel.Application(); xlexcel.DisplayAlerts = false; // Without this you will get two confirm overwrite prompts Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue); Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); // Format column D as text before pasting results, this was required for my data //Microsoft.Office.Interop.Excel.Range rng = xlWorkSheet.get_Range("D:D").Cells; //rng.NumberFormat = "@"; // Paste clipboard results to worksheet range Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, 1]; CR.Select(); xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); // For some reason column A is always blank in the worksheet. ¯\_(ツ)_/¯ // Delete blank column A and select cell A1 //Microsoft.Office.Interop.Excel.Range delRng = xlWorkSheet.get_Range("A:A").Cells; //delRng.Delete(Type.Missing); xlWorkSheet.get_Range("A1").Select(); Microsoft.Office.Interop.Excel.Worksheet ws = xlexcel.ActiveWorkbook.Worksheets[1]; Microsoft.Office.Interop.Excel.Range range = ws.UsedRange; //ws.Columns.ClearFormats(); //ws.Rows.ClearFormats(); //range.EntireColumn.AutoFit(); //range.EntireRow.AutoFit(); xlWorkSheet.Range["A1:D1"].Interior.Color = System.Drawing.Color.LightSkyBlue; ws.Columns.AutoFit(); ws.Rows.AutoFit(); range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; range.Borders.Color = ColorTranslator.ToOle(Color.Black); // Save the excel file under the captured location from the SaveFileDialog xlWorkBook.SaveAs(FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlexcel.DisplayAlerts = true; xlWorkBook.Close(true, misValue, misValue); xlexcel.Quit(); //releaseObject(xlWorkSheet); //releaseObject(xlWorkBook); //releaseObject(xlexcel); // Clear Clipboard and DataGridView selection Clipboard.Clear(); dataGridView1.ClearSelection(); // Open the newly saved excel file if (File.Exists(FileName)) { System.Diagnostics.Process.Start(FileName); } Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbooks workbooks; Microsoft.Office.Interop.Excel.Workbook excelBook; //app = null; //app = new Excel.Application(); // create a new instance excelApp.DisplayAlerts = false; //turn off annoying alerts that make me want to cryyyy uint processID = 0; workbooks = excelApp.Workbooks; excelBook = workbooks.Add(FileName); Microsoft.Office.Interop.Excel.Sheets sheets = excelBook.Worksheets; Microsoft.Office.Interop.Excel.Worksheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)(sheets[1]); //Range.Rows.AutoFit(); //Range.Columns.AutoFit(); }
public void UniteCells(Microsoft.Office.Interop.Excel.Worksheet ws, int x1, int y1, int x2, int y2) //合并单元格 { ws.get_Range(ws.Cells[x1, y1], ws.Cells[x2, y2]).Merge(Type.Missing); }
public static void generateExcel() { packages = DBConnector.getInstance().getPackages(); buckets = DBConnector.getInstance().getBuckets(); generatePackageList(); checkProcess(); generateBucketList(); /******************** create a workbook *************************/ excel = new Microsoft.Office.Interop.Excel.Application(); excel.Visible = false; excel.DisplayAlerts = false; excelworkBook = excel.Workbooks.Add(Type.Missing); /********************* create new sheet (Activity List) ***************************/ excelSheetAll = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet; excelSheetAll.Name = "Activity List"; int row = 1; int tb1_start_x = row; int tb1_start_y = 1; excelSheetAll.Cells[row, 1] = "Process Name"; excelSheetAll.Cells[row, 2] = "Duration"; excelSheetAll.Cells[row, 3] = "Main Window Title"; row++; foreach (KeyValuePair<string, Activity> pair in activityList) { excelSheetAll.Cells[row, 1] = pair.Value.processName; excelSheetAll.Cells[row, 2] = pair.Value.duration.ToString("g"); excelSheetAll.Cells[row, 3] = pair.Key; row++; } int tb1_end_x = row - 1; int tb1_end_y = 3; excelCellrange = excelSheetAll.Range[excelSheetAll.Cells[tb1_start_x, tb1_start_y], excelSheetAll.Cells[tb1_end_x, tb1_end_y]]; excelCellrange.NumberFormat = "hh:mm:ss.000"; excelCellrange.EntireColumn.AutoFit(); Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders; border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; border.Weight = 2d; excelCellrange = excelSheetAll.Range[excelSheetAll.Cells[tb1_start_x, tb1_start_y], excelSheetAll.Cells[tb1_start_x, tb1_end_y]]; FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true); /*************************** create new sheet (Packaged Activity List) ****************************/ excelSheetPackaged = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.Worksheets.Add(); excelSheetPackaged.Name = "Packaged Activity List"; row = 1; int tb2_start_x = row; int tb2_start_y = 1; excelSheetPackaged.Cells[row, 1] = "Package Name"; excelSheetPackaged.Cells[row, 2] = "Duration"; row++; foreach (KeyValuePair<string, TimeSpan> pair in packagedList) { excelSheetPackaged.Cells[row, 1] = pair.Key; excelSheetPackaged.Cells[row, 2] = pair.Value.ToString("g"); row++; } int tb2_end_x = row - 1; int tb2_end_y = 2; excelCellrange = excelSheetPackaged.Range[excelSheetPackaged.Cells[tb2_start_x, tb2_start_y], excelSheetPackaged.Cells[tb2_end_x, tb2_end_y]]; excelCellrange.NumberFormat = "hh:mm:ss.000"; excelCellrange.EntireColumn.AutoFit(); border = excelCellrange.Borders; border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; border.Weight = 2d; excelCellrange = excelSheetPackaged.Range[excelSheetPackaged.Cells[tb2_start_x, tb2_start_y], excelSheetPackaged.Cells[tb2_start_x, tb2_end_y]]; FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true); chartObjects = (Microsoft.Office.Interop.Excel.ChartObjects)excelSheetPackaged.ChartObjects(Type.Missing); chartObject = (Microsoft.Office.Interop.Excel.ChartObject)chartObjects.Add(220, 0, 400, 300); chart = chartObject.Chart; chart.HasTitle = true; chart.ChartTitle.Text = "Packaged Activity List"; chartRange = excelSheetPackaged.get_Range("A" + tb2_start_x, "B" + tb2_end_x); chart.SetSourceData(chartRange, System.Reflection.Missing.Value); chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlPie; chartObjects = (Microsoft.Office.Interop.Excel.ChartObjects)excelSheetPackaged.ChartObjects(Type.Missing); chartObject = (Microsoft.Office.Interop.Excel.ChartObject)chartObjects.Add(220, 320, 400, 300); chart = chartObject.Chart; chart.HasTitle = true; chart.ChartTitle.Text = "Packaged Activity List"; chartRange = excelSheetPackaged.get_Range("A" + tb2_start_x, "B" + tb2_end_x); chart.SetSourceData(chartRange, System.Reflection.Missing.Value); chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered; /************************* create new sheet (Bucketed Activity List) ******************************/ excelSheetBucketed = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.Worksheets.Add(); excelSheetBucketed.Name = "Bucketed Activity List"; row = 1; int tb3_start_x = row; int tb3_start_y = 1; excelSheetBucketed.Cells[row, 1] = "Bucket Name"; excelSheetBucketed.Cells[row, 2] = "Duration"; row++; foreach (KeyValuePair<string, TimeSpan> pair in bucketedList) { excelSheetBucketed.Cells[row, 1] = pair.Key; excelSheetBucketed.Cells[row, 2] = pair.Value.ToString("g"); row++; } int tb3_end_x = row - 1; int tb3_end_y = 2; excelCellrange = excelSheetBucketed.Range[excelSheetBucketed.Cells[tb3_start_x, tb3_start_y], excelSheetBucketed.Cells[tb3_end_x, tb3_end_y]]; excelCellrange.NumberFormat = "hh:mm:ss.000"; excelCellrange.EntireColumn.AutoFit(); border = excelCellrange.Borders; border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; border.Weight = 2d; excelCellrange = excelSheetBucketed.Range[excelSheetBucketed.Cells[tb3_start_x, tb3_start_y], excelSheetBucketed.Cells[tb3_start_x, tb3_end_y]]; FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true); chartObjects = (Microsoft.Office.Interop.Excel.ChartObjects)excelSheetBucketed.ChartObjects(Type.Missing); chartObject = (Microsoft.Office.Interop.Excel.ChartObject)chartObjects.Add(220, 0, 400, 300); chart = chartObject.Chart; chart.HasTitle = true; chart.ChartTitle.Text = "Buckted Activity List"; chartRange = excelSheetBucketed.get_Range("A" + tb3_start_x, "B" + tb3_end_x); chart.SetSourceData(chartRange, System.Reflection.Missing.Value); chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlPie; chartObjects = (Microsoft.Office.Interop.Excel.ChartObjects)excelSheetBucketed.ChartObjects(Type.Missing); chartObject = (Microsoft.Office.Interop.Excel.ChartObject)chartObjects.Add(220, 320, 400, 300); chart = chartObject.Chart; chart.HasTitle = true; chart.ChartTitle.Text = "Buckted Activity List"; chartRange = excelSheetBucketed.get_Range("A" + tb3_start_x, "B" + tb3_end_x); chart.SetSourceData(chartRange, System.Reflection.Missing.Value); chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered; /*************** save excel *******************/ //UserPrincipal.Current.DisplayName String filePath = "C:\\Users\\" + Environment.UserName + "\\Desktop\\ActivityList-" + Environment.UserName + "-" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + ".xlsx"; excelworkBook.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, true, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //excelworkBook.SaveAs(filePath); excelworkBook.Close(); excel.Quit(); Console.WriteLine("-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------"); Console.WriteLine("Export to Excel"); Thread.Sleep(1000); System.Diagnostics.Process.Start(filePath); }
public TravelServer GetTravelServerFromXLSAndChangeProgressBar(string machineName, ProgressBar pgb) { int max = 0; if (pgb != null) { max = pgb.Maximum; } int i = 2; System.Array myvalues; TravelServer travelServer = new TravelServer(machineName); travelServer.Databases = new List <DatabaseItem>(); TXTReader txtReader = TXTReader.CreateInstance(); txtReader.GetServerPortPair(); Microsoft.Office.Interop.Excel.Application ExcelObj = null; ExcelObj = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(file, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets; Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1); Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range("A" + i.ToString(), "B" + i.ToString()); if (pgb != null) { pgb.Invoke((MethodInvoker) delegate { pgb.Maximum = worksheet.Rows.Row; }); } while (!range.Text.Equals("")) { myvalues = (System.Array)range.Cells.Value2; if (machineName.Equals("All") || myvalues.GetValue(1, 1).ToString().Equals(machineName)) { DatabaseItem databaseItem = new DatabaseItem(myvalues.GetValue(1, 2).ToString(), myvalues.GetValue(1, 2).ToString(), "PPE database", "CHC-APPWG01.idx.expedmz.com," + txtReader.GetPortByServerName(myvalues.GetValue(1, 1).ToString()).Value.ToString(), "Update8!", "expdev"); travelServer.Databases.Add(databaseItem); } i++; range = worksheet.get_Range("A" + i.ToString(), "B" + i.ToString()); if (pgb != null) { pgb.Invoke((MethodInvoker) delegate { pgb.PerformStep(); GlobalOperator.SetProgressBarText(pgb, "Reloading databases of " + machineName); }); } } theWorkbook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlDoNotSaveChanges, Type.Missing, Type.Missing); ExcelObj.Quit(); if (pgb != null) { pgb.Invoke((MethodInvoker) delegate { pgb.Maximum = max; }); } return(travelServer); }
private void fillDocumentExcel1(string _filename) { Object wMissing = System.Reflection.Missing.Value; Object wTrue = true; Object wFalse = false; string[] text = inputAppFullName.Text.Split(';'); string d = text[0]; Microsoft.Office.Interop.Excel.Application appExcel1 = new Microsoft.Office.Interop.Excel.Application(); // Microsoft.Office.Interop.Excel.Workbook bookExcel = new Microsoft.Office.Interop.Excel.Workbook(); Microsoft.Office.Interop.Excel.Worksheet worksheetExcel1 = new Microsoft.Office.Interop.Excel.Worksheet(); Microsoft.Office.Interop.Excel.Sheets sheetsExcel1; Microsoft.Office.Interop.Excel.Range rangeExcel1; appExcel1.Visible = true; //Работа с документом - Договор appExcel1.Workbooks.Open(_filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); sheetsExcel1 = appExcel1.Worksheets; worksheetExcel1 = (Microsoft.Office.Interop.Excel.Worksheet)sheetsExcel1.get_Item(1); rangeExcel1 = worksheetExcel1.get_Range("A12", "I12"); rangeExcel1.Merge(Type.Missing); rangeExcel1.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter; rangeExcel1.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter; rangeExcel1.Value2 = "Счёт № " + inputNumContract.Text + " от " + dateAcc.Text; rangeExcel1 = worksheetExcel1.get_Range("A14", "I14"); rangeExcel1.Merge(Type.Missing); rangeExcel1.Value2 = "Плательщик: " + inputAppNameOrgOrPF.Text; rangeExcel1 = worksheetExcel1.get_Range("A15:A16", "I15:I16"); rangeExcel1.Merge(Type.Missing); rangeExcel1.Value2 = "Грузополучатель: " + JFCargoCarrier; rangeExcel1 = worksheetExcel1.get_Range("B19", "E19"); rangeExcel1.Merge(Type.Missing); rangeExcel1.Value2 = "Путёвка в санаторий " + inputAppWay.Text + ", " + inputNumDays.Text + "дн., \" " + inputAppNumberAndCategory.Text + " \" c " + inputAppStartTour.Text + " , " + d; rangeExcel1 = worksheetExcel1.get_Range("H19", Type.Missing); rangeExcel1.Merge(Type.Missing); rangeExcel1.Value2 = sumAcc.Text; rangeExcel1 = worksheetExcel1.get_Range("I19", Type.Missing); rangeExcel1.Merge(Type.Missing); rangeExcel1.Value2 = sumAcc.Text; rangeExcel1 = worksheetExcel1.get_Range("I26", Type.Missing); rangeExcel1.Merge(Type.Missing); rangeExcel1.Value2 = sumAcc.Text; rangeExcel1 = worksheetExcel1.get_Range("E27", "I27"); rangeExcel1.Merge(Type.Missing); rangeExcel1.Value2 = sumAcc.Text; }
private void dataGridViewExportAsExcel(DataGridView dgv, string FileFullPath) { string strExamPath = FileFullPath.Substring(0, FileFullPath.LastIndexOf('\\')); if (!System.IO.Directory.Exists(strExamPath)) { MessageBox.Show(FileFullPath, "目录错误", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } Microsoft.Office.Interop.Excel.Application excelApplication = new Microsoft.Office.Interop.Excel.Application(); excelApplication.EnableEvents = false; excelApplication.Application.DisplayAlerts = false; excelApplication.Workbooks.Add(true); Microsoft.Office.Interop.Excel.Worksheet myWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelApplication.ActiveSheet; excelApplication.Visible = false; int nRowIndex = 0; int nColumnIndex = 0; object[,] strArr = new object[gvDataRecord.RowCount + 1, gvDataRecord.ColumnCount]; foreach (DataGridViewColumn dgvc in dgv.Columns) { strArr[nRowIndex, nColumnIndex] = dgvc.HeaderText; ++nColumnIndex; } ++nRowIndex; nColumnIndex = 0; foreach (DataGridViewRow dgvr in dgv.Rows) { foreach (DataGridViewCell dgvcell in dgvr.Cells) { strArr[nRowIndex, nColumnIndex] = dgvcell.Value.ToString(); ++nColumnIndex; } ++nRowIndex; nColumnIndex = 0; } string strExcelMaxColumnIndex = GetExcelMaxColumnIndex(dgv.ColumnCount, dgv.RowCount + 1); Microsoft.Office.Interop.Excel.Range myRange = (Microsoft.Office.Interop.Excel.Range)myWorkSheet.get_Range("A1", strExcelMaxColumnIndex); myRange.get_Resize(dgv.RowCount + 1, dgv.ColumnCount); try { myRange.Value2 = strArr; myRange.Columns.AutoFit(); myRange.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; myWorkSheet.SaveAs(FileFullPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } catch (Exception ex) { MessageBox.Show(ex.ToString()); return; } MessageBox.Show("文件成功保存到了" + FileFullPath, "保存成功", MessageBoxButtons.OK, MessageBoxIcon.Information); excelApplication.Quit(); killexcel(excelApplication); GC.Collect(); }
private void fillDocumentExcel3(string _filename) { Object wMissing = System.Reflection.Missing.Value; Object wTrue = true; Object wFalse = false; string[] text = inputAppFullName.Text.Split(';'); string d = text[0]; Microsoft.Office.Interop.Excel.Application appExcel1 = new Microsoft.Office.Interop.Excel.Application(); // Microsoft.Office.Interop.Excel.Workbook bookExcel = new Microsoft.Office.Interop.Excel.Workbook(); Microsoft.Office.Interop.Excel.Worksheet worksheetExcel1 = new Microsoft.Office.Interop.Excel.Worksheet(); Microsoft.Office.Interop.Excel.Sheets sheetsExcel1; Microsoft.Office.Interop.Excel.Range rangeExcel1; appExcel1.Visible = true; //Работа с документом - Приходный ордер appExcel1.Workbooks.Open(_filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); sheetsExcel1 = appExcel1.Worksheets; worksheetExcel1 = (Microsoft.Office.Interop.Excel.Worksheet)sheetsExcel1.get_Item(1); rangeExcel1 = worksheetExcel1.get_Range("N10", "P10"); rangeExcel1.Merge(Type.Missing); rangeExcel1.Value2 = inputNumContract.Text; rangeExcel1 = worksheetExcel1.get_Range("Q10", "S10"); rangeExcel1.Merge(Type.Missing); rangeExcel1.Value2 = DateDeb.Text; rangeExcel1 = worksheetExcel1.get_Range("M18:M19", "P18:P19"); rangeExcel1.Merge(Type.Missing); rangeExcel1.Value2 = sumDeb.Text + "руб. 00 коп."; rangeExcel1 = worksheetExcel1.get_Range("D21", "S21"); rangeExcel1.Merge(Type.Missing); rangeExcel1.Value2 = d; }
void CreateExcelDocument() { System.Windows.Input.Mouse.OverrideCursor = System.Windows.Input.Cursors.Wait; Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbooks xlWorkBooks = xlApp.Workbooks; Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlWorkBooks.Add(1); Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); try { xlWorkSheet.Name = (rm as ResourceManager).GetString("Menu_Contact"); Microsoft.Office.Interop.Excel.Range formatRange = xlWorkSheet.get_Range("A1:C1"); formatRange.Font.Bold = true; formatRange.WrapText = true; string rang = "A1:" + "C" + (contList.Count + 1); xlWorkSheet.get_Range(rang).Cells.Font.Name = "Comic Sans MS"; xlWorkSheet.Range[rang].Font.Size = 16; xlWorkSheet.Range[rang].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Pink); xlWorkSheet.Range[rang].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White); xlWorkSheet.Range[rang].Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); formatRange = xlWorkSheet.get_Range(rang); formatRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic); xlWorkSheet.Range[rang].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Range[rang].VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; xlWorkSheet.Cells[1, 1] = LB_Name.Content; xlWorkSheet.Cells[1, 2] = LB_Phone.Content; xlWorkSheet.Cells[1, 3] = LB_Email.Content; for (int i = 0; i < contList.Count; i++) { xlWorkSheet.Cells[2 + i, 1] = contList[i].Con_Name.Trim(); xlWorkSheet.Cells[2 + i, 2] = contList[i].Con_Phone.Trim(); xlWorkSheet.Cells[2 + i, 3] = contList[i].Con_Email.Trim(); } xlWorkSheet.get_Range(rang).Columns.AutoFit(); xlApp.DisplayAlerts = false; Microsoft.Win32.SaveFileDialog saveFileDialog = new Microsoft.Win32.SaveFileDialog(); saveFileDialog.FileName = (rm as ResourceManager).GetString("Menu_Contact"); saveFileDialog.Filter = (rm as ResourceManager).GetString("SaveFileDialogFilter"); saveFileDialog.RestoreDirectory = true; saveFileDialog.CreatePrompt = true; saveFileDialog.Title = (rm as ResourceManager).GetString("SaveFileDialogTitle"); if (saveFileDialog.ShowDialog() == true) { System.IO.FileInfo file = new System.IO.FileInfo(saveFileDialog.FileName); if (f.IsFileLocked(file, (rm as ResourceManager), ResourceNames) == false) { xlWorkBook.SaveAs(saveFileDialog.FileName); ViewModel.WinMessageBoxItem wmsgbi = new ViewModel.WinMessageBoxItem((rm as ResourceManager).GetString("MessageBoxSaveTitle"), (rm as ResourceManager).GetString("MessageBoxSaveText"), MaterialDesignThemes.Wpf.PackIconKind.InformationCircle); Windows.WinMessageBox wmsg = new Windows.WinMessageBox(wmsgbi, (rm as ResourceManager), ResourceNames, false); wmsg.Show(); } } xlWorkBook.Close(false, Type.Missing, Type.Missing); xlApp.Quit(); GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkBook); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp); } catch (Exception ex) { System.Windows.Input.Mouse.OverrideCursor = null; ViewModel.WinMessageBoxItem wmsb = new ViewModel.WinMessageBoxItem("Error", ex.Message, MaterialDesignThemes.Wpf.PackIconKind.Error); Windows.WinMessageBox msb = new Windows.WinMessageBox(wmsb, (rm as ResourceManager), ResourceNames, false); msb.Show(); xlWorkBook.Close(false, Type.Missing, Type.Missing); xlApp.Quit(); GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkBook); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp); } System.Windows.Input.Mouse.OverrideCursor = null; }