public void ExpotToExcel(DataGridView dataGridView1,string SaveFilePath) { xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); int i = 0; int j = 0; for (i = 0; i <= dataGridView1.RowCount - 1; i++) { for (j = 0; j <= dataGridView1.ColumnCount - 1; j++) { DataGridViewCell cell = dataGridView1[j, i]; xlWorkSheet.Cells[i + 1, j + 1] = cell.Value; } } xlWorkBook.SaveAs(SaveFilePath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); MessageBox.Show("Your file is saved" + SaveFilePath); }
public Export(string embedded) { string tmp = String.Empty; try { //получаем шаблон из прикладных ресурсов Stream template = GetResourceFileStream(embedded); //создаем временный файл tmp = System.IO.Path.GetTempFileName().Replace(".tmp", ".xlsx"); //сохраняем шаблон во временный файл using (var fileStream = File.Create(tmp)) { template.CopyTo(fileStream); } } catch (Exception ex) { MessageBox.Show(ex.Message); } // Создаем приложение и открываем в нём временный файл objApp = new Excel.Application(); objBook = objApp.Workbooks.Open(tmp); objBook.Activate(); objSheets = objBook.Worksheets; objSheet = (Excel._Worksheet)objSheets.get_Item(1); }
public ExportData(System.Data.DataTable dt, string location) { //instantiate excel objects (application, workbook, worksheets) excel.Application XlObj = new excel.Application(); XlObj.Visible = false; excel._Workbook WbObj = (excel.Workbook)(XlObj.Workbooks.Add("")); excel._Worksheet WsObj = (excel.Worksheet)WbObj.ActiveSheet; //run through datatable and assign cells to values of datatable int row = 1; int col = 1; foreach (DataColumn column in dt.Columns) { //adding columns WsObj.Cells[row, col] = column.ColumnName; col++; } //reset column and row variables col = 1; row++; for (int i = 0; i < dt.Rows.Count; i++) { //adding data foreach (var cell in dt.Rows[i].ItemArray) { WsObj.Cells[row, col] = cell; col++; } col = 1; row++; } WbObj.SaveAs(location); }
public getData readData() { getData.dateT.Clear(); getData.prj.Clear(); getData.descr.Clear(); getData.taskTime.Clear(); notes.Clear(); ExcelApp = new Excel.Application(); ExcelApp.Visible = false; WorkBookExcel = ExcelApp.Workbooks.Open(_filePath, false); //открываем книгу WorkSheetExcel = (Excel.Worksheet)WorkBookExcel.Sheets[1]; //Получаем ссылку на лист 1 //excelcells = excelworksheet.get_Range("D215", Type.Missing); //Выбираем ячейку для вывода A1 // WorkSheetExcel.Cells[i, 1].Text.ToString() != "" for (int i = 2; WorkSheetExcel.Cells[i, 1].Text.ToString() != ""; i++) { getData.dateT.Add(WorkSheetExcel.Cells[i, 1].Text.ToString()); getData.prj.Add(WorkSheetExcel.Cells[i, 2].Text.ToString()); getData.descr.Add(WorkSheetExcel.Cells[i, 3].Text.ToString()); getData.taskTime.Add(WorkSheetExcel.Cells[i, 4].Text.ToString()); } WorkBookExcel.Close(false, Type.Missing, Type.Missing); //закрыл не сохраняя ExcelApp.Quit(); GC.Collect(); getData.dateT.Reverse(); getData.prj.Reverse(); getData.descr.Reverse(); getData.taskTime.Reverse(); return getData; }
public static void exportDataGridView(DataGridView dv, String fileName) { object missing = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); app.Visible = false; app.UserControl = false; Workbooks workbooks = app.Workbooks; _Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet); Sheets sheets = workbook.Worksheets; _Worksheet worksheet = (_Worksheet)sheets.get_Item(1); worksheet.Name = "Sheet1"; Boolean bl = dv.AllowUserToAddRows; dv.AllowUserToAddRows = false; for (int i = 0; i < dv.Columns.Count; i++) { worksheet.Cells[1, i + 1] = dv.Columns[i].HeaderText; } for (int i = 0; i < dv.Rows.Count; i++) { for (int j = 0; j < dv.Columns.Count; j++) { worksheet.Cells[i + 2, j + 1] = dv.Rows[i].Cells[j].Value; } } dv.AllowUserToAddRows = bl; app.DisplayAlerts = false; app.AlertBeforeOverwriting = false; workbook.Save(); workbook.SaveAs(fileName, missing, missing, missing, missing, missing, XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing); app.Quit(); app = null; }
public DocSoc() { string reportsFile = System.Configuration.ConfigurationManager.AppSettings["ReportsFile"]; string macrosFile = System.Configuration.ConfigurationManager.AppSettings["MacrosFile"]; System.IO.FileInfo macrosFileInfo = new System.IO.FileInfo(macrosFile); Hashtable reportsHash = readReportsXML(reportsFile,macrosFileInfo.Name); string[] reportsSelected = getSelectedReports(reportsHash); Excel.Application xlApp; Excel.Workbook macroBook = null; xlApp = new Excel.Application(); Console.Write("Excel opened"); try { macroBook = xlApp.Workbooks.Open(macrosFileInfo.FullName); Console.WriteLine("MacroFile opened: " + macrosFileInfo.FullName); this.processReports(xlApp, reportsHash, reportsSelected); } catch (Exception e) { Console.WriteLine(e.ToString()); System.Windows.Forms.MessageBox.Show("DocToSoc failed while processing reports.", "DocToSoc Failed", System.Windows.Forms.MessageBoxButtons.OK); } finally { macroBook.Close(); Console.WriteLine("MacroWorkBook closed: " + macrosFile); xlApp.Quit(); Console.WriteLine("Excel Closed"); } }
static void ExportToExcel(List<Car> carsInStock) { // Load up Excel, then make a new empty workbook. Excel.Application excelApp = new Excel.Application(); excelApp.Workbooks.Add(); // This example uses a single workSheet. Excel._Worksheet workSheet = excelApp.ActiveSheet; // Establish column headings in cells. workSheet.Cells[1, "A"] = "Make"; workSheet.Cells[1, "B"] = "Color"; workSheet.Cells[1, "C"] = "Pet Name"; // Now, map all data in List<Car> to the cells of the spread sheet. int row = 1; foreach (Car c in carsInStock) { row++; workSheet.Cells[row, "A"] = c.Make; workSheet.Cells[row, "B"] = c.Color; workSheet.Cells[row, "C"] = c.PetName; } // Give our table data a nice look and feel. workSheet.Range["A1"].AutoFormat( Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2); // Save the file, quit Excel and display message to user. workSheet.SaveAs(string.Format(@"{0}\Inventory.xlsx", Environment.CurrentDirectory)); excelApp.Quit(); MessageBox.Show("The Inventory.xslx file has been saved to your app folder", "Export complete!"); }
public static new Boolean Convert(String inputFile, String outputFile) { Microsoft.Office.Interop.Excel.Application app; String tmpFile = null; object oMissing = System.Reflection.Missing.Value; try { app = new Microsoft.Office.Interop.Excel.Application(); app.Visible = true; Microsoft.Office.Interop.Excel.Workbooks workbooks = null; Microsoft.Office.Interop.Excel.Workbook workbook = null; workbooks = app.Workbooks; workbook = workbooks.Open(inputFile, true, true, oMissing, oMissing, oMissing, true, oMissing, oMissing, oMissing, oMissing, oMissing, false, oMissing, oMissing); // Try and avoid xls files raising a dialog tmpFile = System.IO.Path.GetTempPath() + Guid.NewGuid().ToString() + ".xls" + (workbook.HasVBProject ? "m" : "x"); workbook.SaveAs(tmpFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, Type.Missing, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing); workbook.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF, outputFile, Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard, Type.Missing, false, Type.Missing, Type.Missing, false, Type.Missing); workbooks.Close(); app.Quit(); return true; } catch (Exception e) { Console.WriteLine(e.Message); return false; } finally { if (tmpFile != null) { System.IO.File.Delete(tmpFile); } app = null; } }
private string GetExcelSheetName(string pPath) { //打开一个Excel应用 _excelApp = new Excel.Application(); if (_excelApp == null) { throw new Exception("打开Excel应用时发生错误!"); } _books = _excelApp.Workbooks; //打开一个现有的工作薄 _book = _books.Add(pPath); _sheets = _book.Sheets; //选择第一个Sheet页 _sheet = (Excel._Worksheet)_sheets.get_Item(1); string sheetName = _sheet.Name; ReleaseCOM(_sheet); ReleaseCOM(_sheets); ReleaseCOM(_book); ReleaseCOM(_books); _excelApp.Quit(); ReleaseCOM(_excelApp); return sheetName; }
public bool exportTable() { try { //Подготовка excel= new InteropExcel.ApplicationClass (); if (excel==null) return false; excel.Visible=false; InteropExcel.Workbook workbook=excel.Workbooks.Add(); if(workbook==null) return false; InteropExcel.Worksheet sheet=(InteropExcel.worksheet) workbook.worksheets[1] sheet.Name="Таблица1" //Попълване на таблицата //Запаметяване и затваряне workbook.SaveCopyAs(getPath()); excel.DisplayAlerts=false; //изключване на всички съобщения на Excel workbook.Close(); excel.Quit (); return true; }catch{ } return false; }
private void btnGenerate_Click(object sender, EventArgs e) { //string filename = "Azure_Pass_Account" +/* datePicker.ToString() + */inputApplicant.Text; Excel.Application excel = new Excel.Application(); excel.Visible = true; Excel.Workbook wb = excel.Workbooks.Add(1); Excel.Worksheet sh = wb.Sheets.Add(); datePicker.Format = DateTimePickerFormat.Custom; datePicker.CustomFormat = "yyMMdd"; sh.Name = "Azure Account"; sh.Cells[1, "A"].Value2 = "Index"; sh.Cells[1, "B"].Value2 = "Account"; sh.Cells[1, "C"].Value2 = "Password"; sh.Cells[1, "D"].Value2 = "Applicant"; for(int index = 1; index <= Int32.Parse( inputAmount.Text); index ++) { sh.Cells[index + 1 ,"A"].Value2 = index; sh.Cells[index + 1, "B"].Value2 = "MS" + datePicker.Text + index.ToString("000") + "@outlook.com"; sh.Cells[index + 1, "C"].Value2 = "MS" + datePicker.Text; sh.Cells[index + 1, "D"].Value2 = inputApplicant.Text; } string filename = "Azure_Pass_Account_" + datePicker.Text + "_" + inputApplicant.Text + "_Auto_Generate"; SaveFileDialog mySaveFileDialog = new SaveFileDialog(); mySaveFileDialog.FileName = filename; mySaveFileDialog.Filter = "Excel files (*.xlsx)|*.xlsx"; mySaveFileDialog.ShowDialog(); excel.Quit(); }
private void button1_Click(object sender, EventArgs e) { MyApp = new Excel.Application(); MyApp.Visible = false; MyBook = MyApp.Workbooks.Open(path); MySheet = (Excel.Worksheet)MyBook.Sheets[1]; lastrow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row; BindingList<Dompet> DompetList = new BindingList<Dompet>(); for (int index = 2; index <= lastrow; index++) { System.Array MyValues = (System.Array)MySheet.get_Range ("A" + index.ToString(),"F" + index.ToString()).Cells.Value; DompetList.Add(new Dompet { JmlPemesanan = MyValues.GetValue(1,1).ToString(), JmlPekerja = MyValues.GetValue(1,2).ToString(), Peralatan = MyValues.GetValue(1,3).ToString(), JenisKulit = MyValues.GetValue(1,4).ToString(), ModelDompet = MyValues.GetValue(1,5).ToString(), Prediksi = MyValues.GetValue(1,6).ToString() }); } dataGridView1.DataSource = (BindingList<Dompet>)DompetList; dataGridView1.AutoResizeColumns(); }
/// <summary/> public PluginManager( MSExcel.Application application ) { myApplication = application; myPluginContext = new PluginContext( myApplication ); myPlugins = new List<AbstractPlugin>(); }
public LoadandMatchMetricsNames(String dashboardFile) { try { app = new Excel.Application(); app.Visible = true; dashboard = app.Workbooks.Open(dashboardFile, 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); /* for (int i = 0; i < metricsFiles.Length; i++) { metrics = app.Workbooks.Open(metricsFiles[i], 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); //this is where we read all the data into some sort of array //ExcelScanInternal(metrics); metrics.Close(false, metricsFiles[i], null); } */ //cleanup //dashboard.Close(false, dashboardFile, null); } catch (Exception ex) { Console.WriteLine(ex.Message); } }
public excel() { var excel = new Excel.Application(); //OR /* Type ExcelType = Type.GetTypeFromProgID("Excel.Application"); dynamic excel = Activator.CreateInstance(ExcelType); */ var workbook = excel.Workbooks.Add(); var sheet = excel.ActiveSheet; excel.Visible = true; excel.Cells[1, 1] = "Hi from Me"; excel.Columns[1].AutoFit(); excel.Cells[2,1]=10; excel.Cells[3, 1] = 10; excel.Cells[4, 1] = 20; excel.Cells[5, 1] = 30; excel.Cells[6, 1] = 40; excel.Cells[7, 1] = 50; excel.Cells[8, 1] = 60; var chart = workbook.Charts.Add(After:sheet); chart.ChartWizard(Source:sheet.Range("A2","A7")); }
//C# 4.0 미만에서 COM컴포넌트 사용하는 코드 public static void OldWay(string[,] data, string savePath) { Excel.Application excelApp = new Excel.Application(); //의미없는 매개변수 입력 excelApp.Workbooks.Add(Type.Missing); //형변환 Excel.Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet; for (int i = 0; i < data.GetLength(0); i++) { //형변환 ((Excel.Range)workSheet.Cells[i + 1, 1]).Value2 = data[i, 0]; ((Excel.Range)workSheet.Cells[i + 1, 2]).Value2 = data[i, 1]; } //의미없는 매개변수 입력 workSheet.SaveAs(savePath + "\\addressbook-old.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); excelApp.Quit(); }
// Create a method that is called when the testExcel button is pushed private void CreateExcel(object sender, EventArgs e) { Excel.Application excel = new Excel.Application(); excel.Visible = false; excel.DisplayAlerts = false; Excel.Workbook wb = excel.Workbooks.Add(); Excel.Worksheet sh = wb.Sheets.Add(); sh.Name = "JPMorgan"; sh.Cells[1, "A"].Value = "Vendor Name"; sh.Cells[1, "B"].Value = "City"; sh.Cells[1, "C"].Value = "Zip Code"; sh.Cells[1, "D"].Value = "Tax ID"; sh.Cells[1, "E"].Value = "Phone"; sh.Cells[1, "F"].Value = "Email"; sh.Cells[1, "G"].Value = "Currency ID"; sh.Cells[2, "A"].Value = this.pmTrxForm.PmVendorMaintenance.VendorName.Value; sh.Cells[2, "B"].Value = this.pmTrxForm.PmVendorMaintenance.City.Value; sh.Cells[2, "C"].Value = this.pmTrxForm.PmVendorMaintenance.ZipCode.Value; sh.Cells[2, "D"].Value = this.pmTrxForm.PmVendorMaintenanceAdditionalInformation.TaxIdNumber.Value; sh.Cells[2, "E"].Value = this.pmTrxForm.PmVendorMaintenance.PhoneNumber1.Value; sh.Cells[2, "F"].Value = this.pmTrxForm.PmVendorMaintenance.Comment1.Value; sh.Cells[2, "G"].Value = this.pmTrxForm.PmVendorMaintenanceAdditionalInformation.CurrencyId.Value; wb.SaveAs(@"D:\Excel\testdata.xlsx"); wb.Close(true); excel.Quit(); }
public void read(ref List<string> grpname , ref List<string> path) { createfile objcf = new createfile(); Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; Excel.Range range; object missing = System.Reflection.Missing.Value; xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Open(@"d:\database\Group.xlsx", 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", true, true, 0, true, 1, 0); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); range = xlWorkSheet.UsedRange; int i = 0; string str,str1; for (int rCnt = 1; rCnt <= range.Rows.Count; rCnt++) { str = (string)(range.Cells[rCnt, 1] as Excel.Range).Value2; str1 = (string)(range.Cells[rCnt, 2] as Excel.Range).Value2; grpname.Add(str); path.Add(str1); } xlWorkBook.Close(true, null, null); xlApp.Quit(); objcf.releaseObject(xlWorkSheet); objcf.releaseObject(xlWorkBook); objcf.releaseObject(xlApp); }
// 打开Excel文件 public void OpenExcelFile() { OpenFileDialog opd = new OpenFileDialog(); if (opd.ShowDialog() == DialogResult.OK) excelOpenFileName = opd.FileName; textBox4.Text = System.IO.Path.GetFileNameWithoutExtension(excelOpenFileName); if (excelOpenFileName !="") { try { excelApplication = new Microsoft.Office.Interop.Excel.ApplicationClass(); excelWorkBooks = excelApplication.Workbooks; excelWorkBook = ((Workbook)excelWorkBooks.Open(excelOpenFileName, 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)); excelWorkSheet = (Worksheet)excelWorkBook.Worksheets[ActiveSheetIndex]; excelApplication.Visible = false; rangeOfExcel = excelWorkSheet.UsedRange.Cells.Rows.Count;//获取EXCEL行数 stateOfSave = false; } catch (Exception e) { closeApplication(); MessageBox.Show("(1)没有安装Excel;(2)或没有安装.NET 可编程性支持;\n详细信息:" + e.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } } else { MessageBox.Show("未选择文件!"); closeApplication(); } }
/// <summary> /// Создает общий шаблон для таблицы /// </summary> /// <param name="Parent">Родительская форма</param> /// <param name="Table">Исходная таблица</param> public static void CreateTemplate(BankLab Parent, DataGridView Table) { Excel.Application ExcelApp; ExcelApp = new Excel.Application(); ResFile Maket = new ResFile(bl_res_doc.Resource1.Maket, AppDomain.CurrentDomain.BaseDirectory + "~tmp.xlsx"); Maket.CreateFile(); Parent.CurrentProgressBar.SetProgressBarValue(35); ExcelApp.Workbooks.Open(Maket.FileName); Parent.CurrentProgressBar.SetProgressBarValue(50); Table.Columns.Add("Year", ExcelApp.Cells[1, 1].Value); Table.ColumnCount = 19; Parent.CurrentProgressBar.SetProgressBarValue(60); for (int i = 1; i <= 18; i++) { Table.Columns[i].Name = "X" + i.ToString(); Table.Columns[i].HeaderText = ExcelApp.Cells[1, i + 1].Value; Table.Columns[i].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft; } Parent.CurrentProgressBar.SetProgressBarValue(90); SetTablePropertyToCreateTamplate(Table); Parent.CurrentSideBar.AddTables(); ExcelApp.Workbooks.Close(); ExcelApp.Quit(); Maket.FreeFile(); Parent.CurrentMenu.ChangeMenuItemsEnable(true); }
/// <summary> /// Импортирует Excell документ в таблицу /// </summary> /// <param name="Parent">Родительская форма</param> /// <param name="Table">Таблица назначения</param> /// <returns></returns> public static int ImportExcelFile(BankLab Parent, DataGridView Table) { String FileName = Parent.ShowOpenDialog("Файлы Excel (*.xls; *.xlsx)|*.xls;*.xlsx"); if (FileName == String.Empty) { Parent.CurrentProgressBar.HideProgressBar(); return 0; } Excel.Application ExcelApp = new Excel.Application(); ExcelApp.Workbooks.Open(FileName); Table.Visible = false; Parent.CurrentProgressBar.SetProgressBarValue(10); Table = Parent.CurrentDataTable.ReinitializeDataTable(); ExcelFunctions.CreateTemplate(Parent, Table); for (int i = 0; i < ExcelApp.Sheets[1].UsedRange.Rows.Count - 1; i++) { Table.Rows.Add(); for (int j = 1; j <= 18; j++) { Table.Rows[i].Cells[j].Value = ExcelApp.Cells[i + 2, j + 1].Value; } } Parent.CurrentProgressBar.SetProgressBarValue(90); Parent.CurrentDataTable.FillTableIntervalColumnByFirstYear(Convert.ToInt32(ExcelApp.Cells[2, 1].value)); Table.Visible = true; ExcelApp.Workbooks.Close(); ExcelApp.Quit(); Parent.CurrentProgressBar.SetProgressBarValue(100); return 1; }
public Workbook(Excel.Workbook wb, Excel.Application app, Action dispose_callback) { _app = app; _wb = wb; _wb_name = wb.Name; _dispose_callback = dispose_callback; }
public void CallMacro(string file) { Excel.Application xlApp; Workbook xlWorkBook; Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Open(file);//, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet MessageBox.Show(xlWorkSheet.get_Range("A1", "A1").Value2.ToString()); RunMacro( xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); // Application.Run "PERSONAL.XLSB!CleanDocket" //Application.Run "PERSONAL.XLSB!Create_Upcoming_Docket" //Sheets("Upcoming Hearings").Select //Sheets("Upcoming Hearings").Move Before:=Sheets(1) /* * * releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp);*/ }
private void CopyToClipboardButton_Click(object sender, EventArgs e) { if (EmailResultTextBox.Text.Length == 0) { return; } Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application(); excelApp.Visible = true; _Workbook workbook = (_Workbook)(excelApp.Workbooks.Add(Type.Missing)); _Worksheet worksheet = (_Worksheet)workbook.ActiveSheet; TicketRepository ticketRepo = new TicketRepository(); List<TicketResource> listOfTickets = ticketRepo.GetDistinctEmailAddressBetweenDates(StartingDatePicker.Value, EndingDatePicker.Value); for (int i = 0; i < listOfTickets.Count; i++) { TicketResource ticket = listOfTickets[i]; int row = i + 1; worksheet.Cells[row, "A"] = ticket.LastName; worksheet.Cells[row, "B"] = ticket.FirstName; worksheet.Cells[row, "C"] = ticket.Email; } worksheet.Columns["A:C"].AutoFit(); }
public ReportDataSet ReadDataSet(string file, int headerRowNumber) { ReportDataSet dataSet = new ReportDataSet(); try { excel = new Excel.Application(); Excel.Workbook tempBook = excel.Workbooks.Open(file); foreach (var item in tempBook.Sheets) { Excel.Worksheet sheet = item as Excel.Worksheet; ReportDataTable table = this.GetDataTable(sheet, headerRowNumber); dataSet.Tables.Add(table.Name, table); } tempBook.Save(); tempBook.Close(); tempBook = null; excel.Application.Quit(); } catch (Exception ex) { excel.Application.Quit(); } return dataSet; }
private void btnExport_Click(object sender, EventArgs e) { try { SaveFileDialog sfd = new SaveFileDialog(); Microsoft.Office.Interop.Excel.Application myExcelFile = new Microsoft.Office.Interop.Excel.Application(); sfd.Title = "Save As"; sfd.Filter = "Microsoft Excel|*.xlsx"; sfd.DefaultExt = "xlsx"; if (sfd.ShowDialog() == DialogResult.OK) { Workbook myWorkBook = myExcelFile.Workbooks.Add(XlSheetType.xlWorksheet); Worksheet myWorkSheet = (Worksheet)myExcelFile.ActiveSheet; // don't open excel file in windows during building myExcelFile.Visible = false; label1.Text = "Building the excel file. Please wait..."; // set the first row cells as column names according to the names of data grid view columns names foreach (DataGridViewColumn dgvColumn in dataGridView.Columns) { // dataGridView columns is a zero-based array, while excel sheet is a 1-based array // so, first row of excel sheet has index 1 // set columns of first row as titles of columns myWorkSheet.Cells[1, dataGridView.Columns.IndexOf(dgvColumn) + 1] = dgvColumn.HeaderText; } // since, first row has titles that are set above, start from row-2 and fill each row of excel file. for (int i = 2; i <= dataGridView.Rows.Count; i++) { for (int j = 0; j < dataGridView.Columns.Count; j++) { myWorkSheet.Cells[i, j + 1] = dataGridView.Rows[i - 2].Cells[j].Value; } } // set the font style of first row as Bold which has titles of each column myWorkSheet.Rows[1].Font.Bold = true; myWorkSheet.Rows[1].Font.Size = 12; // after filling, save the file to the specified location string savePath = sfd.FileName; myWorkBook.SaveCopyAs(savePath); } label1.Text = "File saved successfully."; if (MessageBox.Show("File saved successfully. Do you want to open it now?", "File Saved!", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) { myExcelFile.Visible = true; } } catch(Exception exc) { label1.Text = exc.Message; } }
public void DTToExcel(DataTable dt, string filename) { object[,] t = DataTableTo2DTable(dt); excel.Application eapp = new excel.Application(); excel.Workbook book = eapp.Workbooks.Add(); excel.Worksheet sheet = book.Worksheets[1]; sheet.Range["A1", sheet.Cells[t.GetLength(0), t.GetLength(1)]].Value = t; if (t.GetLength(0) > 1) { for (int j = 0; j < t.GetLength(1); j++) { if (dt.Columns[j].DataType == typeof(DateTime)) { sheet.Range[sheet.Cells[2, j + 1], sheet.Cells[t.GetLength(0), j + 1]].NumberFormat = "yyyy/m/d h:mm"; } } } //for (int i = 0; i < dt.Rows.Count; i++) //{ // for (int j = 0; j < dt.Columns.Count; j++) // { // sheet.Cells[j + 1][i + 1] = dt.Rows[i][j]; // } //} book.SaveAs(filename); eapp.Visible = true; }
private void Init() { xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Open(name, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); xlSh = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); }
private void btnExport_Click(object sender, EventArgs e) { Microsoft.Office.Interop.Excel.Application xlApp; Microsoft.Office.Interop.Excel.Workbook xlWorkBook; Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Microsoft.Office.Interop.Excel.Application(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); for (int i = 0; i <= dgSelectedParameters.RowCount - 1; i++) { for (int j = 0; j <= dgSelectedParameters.ColumnCount - 1; j++) { DataGridViewCell cell = dgSelectedParameters[j, i]; xlWorkSheet.Cells[i + 1, j + 1] = cell.Value; } } xlWorkBook.SaveAs("QPR2015_Parameters.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkSheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); MessageBox.Show("The Excel file has been saved, and can be found here: c:\\Users\\..\\Documents\\QPR2015_Parameters.xls", "File Saved"); }
private void button1_Click(object sender, EventArgs e) { try { MessageBox.Show("" + dateTimePicker1.Text); string sql = null; string data = null; int i = 0; int j = 0; Exce.Application xlApp; Exce.Workbook xlWorkBook; Exce.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Exce.Application(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Exce.Worksheet)xlWorkBook.Worksheets.get_Item(1); connection.Open(); sql = "SELECT or_no, or_date, ref_no, ref_date, d_date, c_name, net_amount, notes FROM main_sales WHERE or_date BETWEEN '" + dateTimePicker1.Text + "' AND '" + dateTimePicker2.Text + "'"; OleDbDataAdapter dscmd = new OleDbDataAdapter(sql, connection); DataSet ds = new DataSet(); dscmd.Fill(ds); xlWorkSheet.Cells[1, 1] = "Order No"; xlWorkSheet.Cells[1, 2] = "Order Date"; xlWorkSheet.Cells[1, 3] = "Reference No"; xlWorkSheet.Cells[1, 4] = "Reference Date"; xlWorkSheet.Cells[1, 5] = "Delivery Date"; xlWorkSheet.Cells[1, 6] = "Customer Name"; xlWorkSheet.Cells[1, 7] = "Net Amount"; xlWorkSheet.Cells[1, 8] = "Notes"; for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++) { for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++) { data = ds.Tables[0].Rows[i].ItemArray[j].ToString(); xlWorkSheet.Cells[i + 2, j + 1] = data; } } xlWorkBook.SaveAs("Sales Report.xls", Exce.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Exce.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); MessageBox.Show("Excel file created , you can find the file C:\\Users\\User\\Documents. Sales Report.xls"); } catch (Exception u) { MessageBox.Show("" + u); } finally { connection.Close(); } }
private void создатьСметуToolStripMenuItem1_Click(object sender, EventArgs e) { try { var excelApp = new Excel.Application(); Excel.Workbooks books = excelApp.Workbooks; Excel.Workbook sheet = books.Open(mySheet); Worksheet workSheet = (Worksheet)excelApp.ActiveSheet; countRows = dataGridView1.SelectedRows.Count; excelApp.Cells[1, 1] = comboBoxType.Text + " смета: Сумма трудоемкости работ, выполняемых ГАУ РМ «Госинформ», связанных с обслуживанием АИС «" + comboBoxAIS.Text + "»"; Excel.Range oRange; oRange = workSheet.Range[workSheet.Cells[4, 1], workSheet.Cells[4, 9]]; oRange.Merge(Type.Missing); oRange = workSheet.Range[workSheet.Cells[i + 5, 2], workSheet.Cells[i + 5, 6]]; oRange.Merge(Type.Missing); (workSheet.Cells[i + 3, 2] as Excel.Range).Font.Name = "Times new roman"; (workSheet.Cells[i + 3, 2] as Excel.Range).Font.Bold = true; (workSheet.Cells[i + 3, 2] as Excel.Range).Font.Size = 11; (workSheet.Cells[i + 4, 2] as Excel.Range).Font.Name = "Times new roman"; (workSheet.Cells[i + 4, 2] as Excel.Range).Font.Bold = true; (workSheet.Cells[i + 4, 2] as Excel.Range).Font.Size = 12; excelApp.Cells[i + 4, 2] = "ИТОГО: сумма трудоемкости работ, выполняемых ГАУ РМ «Госинформ», связанных с обслуживанием АИС «" + comboBoxAIS.Text + "»"; (workSheet.Cells[i + 4, 8] as Excel.Range).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; excelApp.Cells[i + 4, 8] = itog; (workSheet.Cells[i + 4, 9] as Excel.Range).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; (workSheet.Cells[i + 4, 9] as Excel.Range).Font.Name = "Times new roman"; excelApp.Cells[i + 4, 9] = "X"; int rowExcel = 4; for (int j = 0; j < dataGridView2.Rows.Count; j++) { if (dataGridView2.Rows[j].Cells[0].Value.ToString() == "NewRazdel") { oRange = workSheet.Range[workSheet.Cells[rowExcel, 1], workSheet.Cells[rowExcel, 9]]; (workSheet.Cells[rowExcel, 1] as Excel.Range).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; (workSheet.Cells[rowExcel, 1] as Excel.Range).Font.Bold = true; (workSheet.Cells[rowExcel, 1] as Excel.Range).Font.Size = 11; (workSheet.Cells[rowExcel, 1] as Excel.Range).Font.Name = "Times new roman"; oRange.Merge(Type.Missing); workSheet.Cells[rowExcel, 1] = dataGridView2.Rows[j].Cells[1].Value.ToString(); ++rowExcel; } else { code = dataGridView2.Rows[j].Cells[0].Value.ToString(); string rabota = dataGridView2.Rows[j].Cells[1].Value.ToString(); string edIzmer = dataGridView2.Rows[j].Cells[2].Value.ToString(); string norma = dataGridView2.Rows[j].Cells[3].Value.ToString(); string godSredneStat = dataGridView2.Rows[j].Cells[4].Value.ToString(); string godNormaZatrat = dataGridView2.Rows[j].Cells[5].Value.ToString(); string kol = dataGridView2.Rows[j].Cells[6].Value.ToString(); string god = dataGridView2.Rows[j].Cells[7].Value.ToString(); string comment = dataGridView2.Rows[j].Cells[8].Value.ToString(); if (dataGridView2.Rows[j].Cells[9].Value != null) { naCoef = dataGridView2.Rows[j].Cells[9].Value.ToString(); } (workSheet.Cells[rowExcel, "A"] as Excel.Range).Font.Name = "Times new roman"; workSheet.Cells[rowExcel, "A"] = code; (workSheet.Cells[rowExcel, "B"] as Excel.Range).Font.Name = "Times new roman"; workSheet.Cells[rowExcel, "B"] = rabota; (workSheet.Cells[rowExcel, "C"] as Excel.Range).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; (workSheet.Cells[rowExcel, "C"] as Excel.Range).Font.Name = "Times new roman"; workSheet.Cells[rowExcel, "C"] = edIzmer; (workSheet.Cells[rowExcel, "D"] as Excel.Range).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; (workSheet.Cells[rowExcel, "D"] as Excel.Range).Font.Name = "Times new roman"; workSheet.Cells[rowExcel, "D"] = norma; (workSheet.Cells[rowExcel, "E"] as Excel.Range).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; (workSheet.Cells[rowExcel, "E"] as Excel.Range).Font.Name = "Times new roman"; workSheet.Cells[rowExcel, "E"] = godSredneStat; (workSheet.Cells[rowExcel, "F"] as Excel.Range).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; (workSheet.Cells[rowExcel, "F"] as Excel.Range).Font.Name = "Times new roman"; workSheet.Cells[rowExcel, "F"] = godNormaZatrat; (workSheet.Cells[rowExcel, "G"] as Excel.Range).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; (workSheet.Cells[rowExcel, "G"] as Excel.Range).Font.Name = "Times new roman"; workSheet.Cells[rowExcel, "G"] = kol; (workSheet.Cells[rowExcel, "H"] as Excel.Range).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; (workSheet.Cells[rowExcel, "H"] as Excel.Range).Font.Name = "Times new roman"; workSheet.Cells[rowExcel, "H"] = god; (workSheet.Cells[rowExcel, "I"] as Excel.Range).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; (workSheet.Cells[rowExcel, "I"] as Excel.Range).Font.Name = "Times new roman"; workSheet.Cells[rowExcel, "I"] = comment; (workSheet.Cells[rowExcel, "J"] as Excel.Range).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; (workSheet.Cells[rowExcel, "J"] as Excel.Range).Font.Name = "Times new roman"; workSheet.Cells[rowExcel, "J"] = naCoef; ++rowExcel; } } excelApp.Visible = true; } catch (Exception ex) { MessageBox.Show("Ошибка! " + Environment.NewLine + ex.ToString()); } }
/// <summary> /// Get last used column for a row /// </summary> /// <param name="fileName"></param> /// <param name="sheetName"></param> /// <param name="row"></param> /// <returns></returns> public int LastColumnForRow(string fileName, string sheetName, int row) { int lastColumn = -1; if (File.Exists(fileName)) { Excel.Application xlApp = null; Excel.Workbooks xlWorkBooks = null; Excel.Workbook xlWorkBook = null; Excel.Worksheet xlWorkSheet = null; Excel.Sheets xlWorkSheets = null; xlApp = new Excel.Application(); xlApp.DisplayAlerts = false; xlWorkBooks = xlApp.Workbooks; xlWorkBook = xlWorkBooks.Open(fileName); xlApp.Visible = false; xlWorkSheets = xlWorkBook.Sheets; for (int x = 1; x <= xlWorkSheets.Count; x++) { xlWorkSheet = (Excel.Worksheet)xlWorkSheets[x]; if (xlWorkSheet.Name == sheetName) { Excel.Range xlCells = null; xlCells = xlWorkSheet.Cells; Excel.Range workRange = xlCells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell); Excel.Range xlColumns = xlWorkSheet.Columns; int count = xlColumns.Count; Marshal.FinalReleaseComObject(xlColumns); xlColumns = null; Excel.Range xlLastRange = (Excel.Range)xlWorkSheet.Cells[row, count]; Excel.Range xlDirRange = xlLastRange.End[Excel.XlDirection.xlToLeft]; Marshal.FinalReleaseComObject(xlLastRange); xlLastRange = null; lastColumn = xlDirRange.Column; Marshal.FinalReleaseComObject(xlDirRange); xlDirRange = null; Marshal.FinalReleaseComObject(workRange); workRange = null; Marshal.FinalReleaseComObject(xlCells); xlCells = null; break; } Marshal.FinalReleaseComObject(xlWorkSheet); xlWorkSheet = null; } xlWorkBook.Close(); xlApp.UserControl = true; xlApp.Quit(); Release(xlWorkSheets); Release(xlWorkSheet); Release(xlWorkBook); Release(xlWorkBooks); Release(xlApp); return(lastColumn); } else { throw new Exception("'" + fileName + "' not found."); } }
private void btnxuatexcel_Click(object sender, EventArgs e) { moFile.ShowDialog(); string file = moFile.FileName; if (string.IsNullOrEmpty(file)) { return; } if (file == "openFileDialog1") { return; } else { img = moFile.FileName.ToString(); } Console.WriteLine(img); try { string saveExcelFile = img; Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) { MessageBox.Show("Lỗi không thể sử dụng được thư viện EXCEL"); return; } xlApp.Visible = false; object misValue = System.Reflection.Missing.Value; Workbook wb = xlApp.Workbooks.Add(misValue); Worksheet ws = (Worksheet)wb.Worksheets[1]; if (ws == null) { MessageBox.Show("Không thể tạo được WorkSheet"); return; } int row = 1; string fontName = "Times New Roman"; int fontSizeTieuDe = 18; int fontSizeTenTruong = 14; int fontSizeNoiDung = 12; //Xuất dòng tiêu đề của file Range row1_TieuDe_ThongKeSanPham = ws.get_Range("A1", "E1"); row1_TieuDe_ThongKeSanPham.Merge(); row1_TieuDe_ThongKeSanPham.Font.Size = fontSizeTieuDe; row1_TieuDe_ThongKeSanPham.Font.Name = fontName; row1_TieuDe_ThongKeSanPham.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; row1_TieuDe_ThongKeSanPham.Value2 = "Thống kê hóa đơn"; //Tạo Ô Số Thứ Tự (STT) Range row23_STT = ws.get_Range("A2");//Cột A dòng 2 và dòng 3 row23_STT.Font.Size = fontSizeTenTruong; row23_STT.Font.Name = fontName; row23_STT.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter; row23_STT.Value2 = "STT"; //Tạo Ô Mã Sản phẩm : Range row23_MaSP = ws.get_Range("B2");//Cột B dòng 2 và dòng 3 row23_MaSP.Font.Size = fontSizeTenTruong; row23_MaSP.Font.Name = fontName; row23_MaSP.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter; row23_MaSP.Value2 = "Mã Hóa Đơn"; row23_MaSP.ColumnWidth = 20; //Tạo Ô Tên Sản phẩm : Range row23_TenSP = ws.get_Range("C2");//Cột C dòng 2 và dòng 3 row23_TenSP.Font.Size = fontSizeTenTruong; row23_TenSP.Font.Name = fontName; row23_TenSP.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter; row23_TenSP.ColumnWidth = 20; row23_TenSP.Value2 = "Ngày Lập"; //Tạo Ô Giá Nhập: Range row3_GiaNhap = ws.get_Range("D2");//Ô D3 row3_GiaNhap.Font.Size = fontSizeTenTruong; row3_GiaNhap.Font.Name = fontName; row3_GiaNhap.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; row3_GiaNhap.Value2 = "Nhân Viên"; row3_GiaNhap.ColumnWidth = 20; //Tạo Ô Giá Xuất: Range row3_GiaXuat = ws.get_Range("E2");//Ô E3 row3_GiaXuat.Font.Size = fontSizeTenTruong; row3_GiaXuat.Font.Name = fontName; row3_GiaXuat.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; row3_GiaXuat.Value2 = "Tổng Tiền"; row3_GiaXuat.ColumnWidth = 20; //Tô nền vàng các cột tiêu đề: Range row23_CotTieuDe = ws.get_Range("A2", "E2"); //nền vàng row23_CotTieuDe.Interior.Color = ColorTranslator.ToOle(System.Drawing.Color.Yellow); //in đậm row23_CotTieuDe.Font.Bold = true; //chữ đen row23_CotTieuDe.Font.Color = ColorTranslator.ToOle(System.Drawing.Color.Black); int stt = 0; row = 2; foreach (HoaDon hd in trangsuc.HoaDons) { stt++; row++; dynamic[] day = { stt, hd.MaHD.Trim(), Convert.ToDateTime(hd.TGLap), hd.UserName.Trim(), hd.TongTien }; Range rowData = ws.get_Range("A" + row, "E" + row); rowData.Font.Size = fontSizeNoiDung; rowData.Font.Name = fontName; rowData.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; rowData.Value2 = day; } //Kẻ khung toàn bộ BorderAround(ws.get_Range("A2", "E" + row)); //Lưu file excel xuống Ổ cứng wb.SaveAs(saveExcelFile); //đóng file để hoàn tất quá trình lưu trữ wb.Close(true, misValue, misValue); //thoát và thu hồi bộ nhớ cho COM xlApp.Quit(); //Mở File excel sau khi Xuất thành công System.Diagnostics.Process.Start(saveExcelFile); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
void ExportarExcel(string filename) { Excel._Application xlApp; Excel._Workbook xlLibro; Excel._Worksheet xlHoja; Excel.Sheets xlHojas; xlApp = new Excel.Application(); filename = Path.Combine(Directory.GetCurrentDirectory(), "Excel\\ShippingReportVinceStyle.xlsx"); xlLibro = xlApp.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); xlHojas = xlLibro.Sheets; xlHoja = (Excel._Worksheet)xlHojas[1]; Excel.Range xlRange; Cursor.Current = Cursors.WaitCursor; xlHoja.Shapes.AddPicture(Path.Combine(Directory.GetCurrentDirectory(), "Logo.jpg"), Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 1, 1, 100, 60); try { int Row = 7; int RowDetail = 8; int RowCertificate = 9; int FirstRowRange = 7; int SecondRowRange = 11; decimal decTotalPO = 0; decimal decTotalShip = 0; xlHoja.Cells[2, 1] = "SHIPPING REPORT VINCE STYLE # " + txtNameStyle.Text; mLista = new ReporteProgramProductionBL().ListadoShippingReportVinceStyleGeneral(Parametros.intEmpresaId, 7, intIdStyle, Convert.ToInt32(cboSeason.EditValue)); if (mLista.Count > 0) { xlHoja.Cells[4, 2] = mLista[0].NameVendor; foreach (var item in mLista) { xlHoja.Cells[Row, 1] = item.NameStyle; xlHoja.Cells[Row, 2] = item.Description; xlHoja.Cells[Row, 4] = item.Detail; xlHoja.Cells[Row, 5] = item.NumberPO + "\\" + item.Dyelot; xlHoja.Cells[Row, 6] = item.NameDestination; xlHoja.Cells[RowDetail, 9] = item.XXS; xlHoja.Cells[RowDetail, 10] = item.XS; xlHoja.Cells[RowDetail, 11] = item.S; xlHoja.Cells[RowDetail, 12] = item.M; xlHoja.Cells[RowDetail, 13] = item.L; xlHoja.Cells[RowDetail, 14] = item.XL; xlHoja.Cells[RowDetail, 15] = item.XXL; Row = Row + 7; RowDetail = RowDetail + 7; decTotalPO = decTotalPO + item.XS + item.S + item.M + item.L + item.XL + item.XXL; } //COLOREAMOS LAS FILAS SEGUN CRITERIO var lstPOS = from lista in mLista group lista by lista.NumberPO into newGroup orderby newGroup.Key select newGroup; string strNumberPO; Color[] ColorSet = { Color.DodgerBlue, Color.MediumPurple, Color.LightGreen, Color.NavajoWhite, Color.LightCoral, Color.Aquamarine, Color.Brown, Color.CornflowerBlue, Color.Cyan, Color.DarkMagenta, Color.DarkOrange, Color.DeepPink, Color.OrangeRed, Color.MediumSlateBlue, Color.Orange, Color.DarkRed }; int intColor = 0; foreach (var itempo in lstPOS) { strNumberPO = itempo.Key; foreach (var itemrange in mLista) { if (itemrange.NumberPO == strNumberPO) { xlRange = xlHoja.get_Range("A" + FirstRowRange + ":" + "O" + SecondRowRange); xlRange.Interior.Color = ColorSet[intColor]; FirstRowRange = FirstRowRange + 7; SecondRowRange = SecondRowRange + 7; } } intColor = intColor + 1; } } //INSPECTION CERTIFICATE mListaInspeccionCertificate = new ReporteInspectionCertificateBL().ListadoShippingReportVinceStyle(Parametros.intEmpresaId, 7, txtNameStyle.Text.Trim()); if (mListaInspeccionCertificate.Count > 0) { foreach (var item in mListaInspeccionCertificate) { xlHoja.Cells[RowCertificate, 9] = item.XXS; xlHoja.Cells[RowCertificate, 10] = item.XS; xlHoja.Cells[RowCertificate, 11] = item.S; xlHoja.Cells[RowCertificate, 12] = item.M; xlHoja.Cells[RowCertificate, 13] = item.L; xlHoja.Cells[RowCertificate, 14] = item.XL; xlHoja.Cells[RowCertificate, 15] = item.XXL; RowCertificate = RowCertificate + 7; decTotalShip = decTotalShip + item.XS + item.S + item.M + item.L + item.XL + item.XXL; } } xlHoja.Cells[181, 8] = decTotalPO; xlHoja.Cells[182, 8] = decTotalShip; xlLibro.SaveAs("C:\\Excel\\ShippingReportVinceStyle.xlsx", Excel.XlFileFormat.xlWorkbookDefault, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); xlLibro.Close(true, Missing.Value, Missing.Value); xlApp.Quit(); Cursor.Current = Cursors.Default; BSUtils.OpenExcel("C:\\Excel\\ShippingReportVinceStyle.xlsx"); } catch (Exception ex) { xlLibro.Close(false, Missing.Value, Missing.Value); xlApp.Quit(); Cursor.Current = Cursors.Default; MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Warning); } }
private void btn1_Click_1(object sender, RoutedEventArgs e) { clearComments(); string showFileName = Directory.GetCurrentDirectory() + @"\hotel.xls"; string src_file_name = Directory.GetCurrentDirectory() + @"\Files\list.csv"; string dest_file_name = Directory.GetCurrentDirectory() + @"\Files\list.xlsx"; MsExcel.Application oExcApp; //Excel Application; MsExcel.Workbook oExcBook; // try { if (File.Exists(dest_file_name)) { File.Delete(dest_file_name); } oExcApp = new MsExcel.Application(); object missing = System.Reflection.Missing.Value; oExcBook = oExcApp.Workbooks.Add(true); MsExcel.Worksheet worksheet1 = (MsExcel.Worksheet)oExcBook.Worksheets["sheet1"]; worksheet1.Activate(); oExcApp.Visible = false; oExcApp.DisplayAlerts = false; MsExcel.Range range1 = worksheet1.get_Range("B1", "H2"); range1.Columns.ColumnWidth = 8; range1.Columns.RowHeight = 20; range1.Merge(false); //设置垂直居中和水平居中 range1.VerticalAlignment = MsExcel.XlVAlign.xlVAlignCenter; range1.HorizontalAlignment = MsExcel.XlHAlign.xlHAlignCenter; //range1.Font.Color = Color.FromRgb(0, 0, 255); range1.Font.Size = 20; range1.Font.Bold = true; worksheet1.Cells[1, 2] = "学生成绩单"; worksheet1.Cells[3, 1] = "学号"; worksheet1.Cells[3, 2] = "姓名"; worksheet1.Columns[1].ColumnWidth = 12; StreamReader sw = new StreamReader(src_file_name); string a_str; string[] str_list; int i = 4; a_str = sw.ReadLine(); while (a_str != null) { str_list = a_str.Split(",".ToCharArray()); worksheet1.Cells[i, 1] = str_list[0]; worksheet1.Cells[i, 2] = str_list[1]; i++; a_str = sw.ReadLine(); } sw.Close(); DataTable showdata = GetExcelData(showFileName); foreach (DataRow row in showdata.Rows) { foreach (DataColumn column in showdata.Columns) { showComment(row[column].ToString()); } } for (int i1 = 0; i1 < 5; i1++) { for (int j = 0; j < 8; j++) { worksheet1.Cells[i1 + 18, j + 3].Value2 = "=CEILING.MATH(RAND()*100)"; worksheet1.Cells[i1 + 4, j + 3].Value2 = worksheet1.Cells[i1 + 18, j + 3].Value; } } //添加图表 MsExcel.Shape theShape = worksheet1.Shapes.AddChart(MsExcel.XlChartType.xl3DColumn, 120, 130, 380, 250); worksheet1.Cells[3, 3].Value2 = "美术"; worksheet1.Cells[3, 4].Value2 = "物理"; worksheet1.Cells[3, 5].Value2 = "政治"; worksheet1.Cells[3, 6].Value2 = "化学"; worksheet1.Cells[3, 7].Value2 = "体育"; worksheet1.Cells[3, 8].Value2 = "英语"; worksheet1.Cells[3, 9].Value2 = "数学"; worksheet1.Cells[3, 10].Value2 = "历史"; //设定图表的数据区域 MsExcel.Range range = worksheet1.get_Range("b3:j8"); theShape.Chart.SetSourceData(range, Type.Missing); //设置图标题文本 theShape.Chart.HasTitle = true; theShape.Chart.ChartTitle.Text = "学生成绩"; theShape.Chart.ChartTitle.Caption = "学生成绩"; //设置单元格边框线型 range1 = worksheet1.get_Range("a3", "j8"); range1.Borders.LineStyle = MsExcel.XlLineStyle.xlContinuous; oExcBook.RefreshAll(); worksheet1 = null; object file_name = dest_file_name; oExcBook.Close(true, file_name, null); System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcBook); oExcBook = null; oExcApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcApp); oExcApp = null; System.GC.Collect(); } catch (Exception e2) { MessageBox.Show(e2.Message); } finally { Console.WriteLine(" 正在结束 excel 进程"); showComment("正在结束excel进程"); //关闭 excel 进程 Process[] AllProces = Process.GetProcesses(); for (int j = 0; j < AllProces.Length; j++) { string theProcName = AllProces[j].ProcessName; if (String.Compare(theProcName, "EXCEL") == 0) { if (AllProces[j].Responding && !AllProces[j].HasExited) { AllProces[j].Kill(); } } } //Close excel Process. OpenExcel(dest_file_name); } }
/// <summary> /// 使用COM读取Excel /// </summary> /// <param name="excelFilePath">路径</param> /// <returns>DataTabel</returns> public DataTable GetExcelData(string excelFilePath) { MsExcel.Application app = new MsExcel.Application(); MsExcel.Sheets sheets; MsExcel.Workbook workbook = null; object oMissiong = System.Reflection.Missing.Value; DataTable dt = new DataTable(); wath.Start(); try { if (app == null) { return(null); } workbook = app.Workbooks.Open(excelFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong); //将数据读入到DataTable中——Start sheets = workbook.Worksheets; MsExcel.Worksheet worksheet = (MsExcel.Worksheet)sheets.get_Item(1);//读取第一张表 if (worksheet == null) { return(null); } string cellContent; int iRowCount = worksheet.UsedRange.Rows.Count; int iColCount = worksheet.UsedRange.Columns.Count; MsExcel.Range range; //负责列头Start DataColumn dc; int ColumnID = 1; range = (MsExcel.Range)worksheet.Cells[1, 1]; while (range.Text.ToString().Trim() != "") { dc = new DataColumn(); dc.DataType = System.Type.GetType("System.String"); dc.ColumnName = range.Text.ToString().Trim(); dt.Columns.Add(dc); range = (MsExcel.Range)worksheet.Cells[1, ++ColumnID]; } //End for (int iRow = 2; iRow <= iRowCount; iRow++) { DataRow dr = dt.NewRow(); for (int iCol = 1; iCol <= iColCount; iCol++) { range = (MsExcel.Range)worksheet.Cells[iRow, iCol]; cellContent = (range.Value2 == null) ? "" : range.Text.ToString(); dr[iCol - 1] = cellContent; } dt.Rows.Add(dr); } wath.Stop(); TimeSpan ts = wath.Elapsed; //将数据读入到DataTable中——End return(dt); } catch { return(null); } finally { workbook.Close(false, oMissiong, oMissiong); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); workbook = null; app.Workbooks.Close(); app.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(app); app = null; GC.Collect(); GC.WaitForPendingFinalizers(); } }
public ActionResult ShowDoc(HttpPostedFileBase file, string Title,string Index, int AcceptedUserId) { Document oldDocument; Document newDocument; using (var db = new DocumentDbContext()) { oldDocument = GetOldOne(db, Index); newDocument = GetNewOne(db, Index); //stworzenie sciezki do zapisania pliku string pathUser = Environment.GetFolderPath(Environment.SpecialFolder.UserProfile); string pathDownload = Path.Combine(pathUser, "Downloads"); string title = Title + DateTime.Now; title = title.Replace("-", ""); title = title.Replace(":", ""); string title2 = "temp" + ".pdf"; string EndPath = Path.Combine(pathDownload, title); string EndPathPDF = Path.Combine(pathDownload, title2); int currentVersion = oldDocument.Version+1; //stworzenie tablicy wielkosci newDocument.DocumentFile = new byte[file.InputStream.Length]; file.InputStream.Read(newDocument.DocumentFile, 0, newDocument.DocumentFile.Length); System.IO.File.WriteAllBytes(EndPath, newDocument.DocumentFile); MyApp = new Microsoft.Office.Interop.Excel.Application(); MyApp.Visible = false; MyBook = MyApp.Workbooks.Open(EndPath); MyWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)MyBook.Sheets[1]; MyWorkSheet.Cells[2, 3] = oldDocument.Index; MyWorkSheet.Cells[3, 3] = db.Lines.Where(x => x.LineId ==oldDocument.LineId).First().LineName; MyWorkSheet.Cells[2, 5] = Title; MyWorkSheet.Cells[2, 13] = currentVersion; MyWorkSheet.Cells[4, 3] = db.Operations.Where(x => x.OperationId ==oldDocument.OperationId).First().OperationNumber.ToString(); MyWorkSheet.Cells[5, 5] = db.DocumentTypes.Where(x => x.DocumentTypeId == oldDocument.DocumentTypeId).First().Description; UserAccount tempAuthor; User authorUser; var user = (string)Session["UserName"]; using (var db1 = new AccountDbContext()) { tempAuthor = db1.userAccount.Where(x => x.UserName == user).First(); authorUser = db.Users.Where(x => x.LastName == tempAuthor.LastName).First(); MyWorkSheet.Cells[4, 13] = authorUser.FirstName + " " + authorUser.LastName; } MyApp.Save(); MyBook.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, EndPathPDF); MyBook.Close(); byte[] f1 = new byte[15728640]; f1 = System.IO.File.ReadAllBytes(EndPath); byte[] fPDF = new byte[15728640]; fPDF = System.IO.File.ReadAllBytes(EndPathPDF); newDocument.DocumentFileExcel = f1; newDocument.DocumentFile = fPDF; newDocument.Status = db.Status.Where(x => x.StatusId == 1).First(); newDocument.Version = currentVersion; newDocument.Title = Title; newDocument.LineId =oldDocument.LineId; newDocument.DateOfAdding = DateTime.Now; newDocument.OperationId = oldDocument.OperationId; newDocument.DocumentTypeId = oldDocument.DocumentTypeId; newDocument.AuthorUserId = authorUser.UserId; newDocument.AcceptedUserId = AcceptedUserId; db.Documents.Add(newDocument); db.SaveChanges(); if (System.IO.File.Exists(EndPath)) { System.IO.File.Delete(EndPath); } if (System.IO.File.Exists(EndPathPDF)) { System.IO.File.Delete(EndPathPDF); } } return RedirectToAction("ShowMessage"); }
public static DataTable getExcelDataToDataTable() { DataTable dt = new DataTable(); OpenFileDialog openFile = new OpenFileDialog(); openFile.DefaultExt = ".xlsx"; openFile.Filter = "(.xlsx)|*.xlsx|All Files(*.*)|*.*"; var browsefile = openFile.ShowDialog(); if (browsefile == true) { string txtFilePath = (openFile.FileName).ToString(); Excel.Application excelApp = new Excel.Application(); //Static File From Base Path........... //Microsoft.Office.Interop.Excel.Workbook excelBook = excelApp.Workbooks.Open(AppDomain.CurrentDomain.BaseDirectory + "TestExcel.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); //Dynamic File Using Uploader........... Excel.Workbook excelBook = excelApp.Workbooks.Open(txtFilePath, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); Excel.Worksheet excelSheet = (Excel.Worksheet)excelBook.Worksheets.get_Item(1);; Excel.Range excelRange = excelSheet.UsedRange; string strCellData = ""; double douCellData; int rowCnt = 0; int colCnt = 0; for (colCnt = 1; colCnt <= excelRange.Columns.Count; colCnt++) { string strColumn = ""; strColumn = (string)(excelRange.Cells[1, colCnt] as Excel.Range).Value2; dt.Columns.Add(strColumn, typeof(string)); } for (rowCnt = 2; rowCnt <= excelRange.Rows.Count; rowCnt++) { string strData = ""; for (colCnt = 1; colCnt <= excelRange.Columns.Count; colCnt++) { try { strCellData = (string)(excelRange.Cells[rowCnt, colCnt] as Excel.Range).Value2; strData += strCellData + "|"; } catch (Exception ex) { douCellData = (excelRange.Cells[rowCnt, colCnt] as Excel.Range).Value2; strData += douCellData.ToString() + "|"; } } strData = strData.Remove(strData.Length - 1, 1); dt.Rows.Add(strData.Split('|')); } excelBook.Close(true, null, null); excelApp.Quit(); return(dt); } else { return(null); } }
/// <summary> /// /// </summary> /// <param name="fileName">file to get information form</param> /// <param name="sheetName">valid sheet name to get last row and column</param> /// <returns>ExcelLast</returns> public ExcelLast UsedRowsColumns(string fileName, string sheetName) { int RowsUsed = -1; int ColsUsed = -1; if (File.Exists(fileName)) { Excel.Application xlApp = null; Excel.Workbooks xlWorkBooks = null; Excel.Workbook xlWorkBook = null; Excel.Worksheet xlWorkSheet = null; Excel.Sheets xlWorkSheets = null; xlApp = new Excel.Application(); xlApp.DisplayAlerts = false; xlWorkBooks = xlApp.Workbooks; xlWorkBook = xlWorkBooks.Open(fileName); xlApp.Visible = false; xlWorkSheets = xlWorkBook.Sheets; for (int x = 1; x <= xlWorkSheets.Count; x++) { xlWorkSheet = (Excel.Worksheet)xlWorkSheets[x]; if (xlWorkSheet.Name == sheetName) { Excel.Range xlCells = null; xlCells = xlWorkSheet.Cells; Excel.Range workRange = xlCells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell); RowsUsed = workRange.Row; ColsUsed = workRange.Column; Marshal.FinalReleaseComObject(workRange); workRange = null; Marshal.FinalReleaseComObject(xlCells); xlCells = null; break; } Marshal.FinalReleaseComObject(xlWorkSheet); xlWorkSheet = null; } xlWorkBook.Close(); xlApp.UserControl = true; xlApp.Quit(); Release(xlWorkSheets); Release(xlWorkSheet); Release(xlWorkBook); Release(xlWorkBooks); Release(xlApp); return(new ExcelLast() { Row = RowsUsed, Column = ColsUsed }); } else { throw new Exception("'" + fileName + "' not found."); } }
public void EntityToExcelSheet(string excelFilePath, string sheetName, IQueryable result, ObjectContext ctx) { //Query to grab excel data // string query = "select * from Employees"; // string excelFilePath = ""; //string sheetName = ""; //IQueryable result; //ObjectContext ctx; //Oledb set up Excel.Application appObj; Excel.Workbook workBookObj; Excel.Worksheet sheetObj; Excel.Range rangeCheck; try { //Excel / application object reference appObj = new Excel.Application(); // Setting properties appObj.Visible = true; appObj.DisplayAlerts = false; //New workbook workBookObj = appObj.Workbooks.Add(Missing.Value); //Get active sheetObj = (Excel.Worksheet)workBookObj.ActiveSheet; sheetObj.Name = sheetName; // Process the DataTable // BE SURE TO CHANGE THIS LINE TO USE *YOUR* DATATABLE DataTable dt = EntityToDataTable(result, ctx); int rowCount = 1; foreach (DataRow dr in dt.Rows) { rowCount += 1; for (int i = 1; i < dt.Columns.Count + 1; i++) { // Add the header the first time through if (rowCount == 2) { sheetObj.Cells[1, i] = dt.Columns[i - 1].ColumnName; } sheetObj.Cells[rowCount, i] = dr[i - 1].ToString(); } } // Resize the columns rangeCheck = sheetObj.Range[sheetObj.Cells[1, 1], sheetObj.Cells[rowCount, dt.Columns.Count]]; rangeCheck.Columns.AutoFit(); // Save the sheet and close sheetObj = null; rangeCheck = null; workBookObj.SaveAs(excelFilePath, Excel.XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value); workBookObj.Close(Missing.Value, Missing.Value, Missing.Value); workBookObj = null; appObj.Quit(); } catch (Exception ex) { throw ex; } }
private void Form1_Load(object sender, EventArgs e) { Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\Users\user\Downloads\monte-carlo\monte-carlo\monte-carlo\monte-carlo\bin\Debug\data1.xlsx"); Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1]; Excel.Range xlRange = xlWorksheet.UsedRange; int k = 0; for (int i = 2; ; i++) { for (int j = 1; j <= 2; j++) { //write the value to the console if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null) { if (j % 2 == 1) { clist pnn = new clist(); pnn.val = Convert.ToDouble(xlRange.Cells[i, j].Value2); tot += pnn.val; pnn.cumulative = tot; demand.Add(pnn); dataGridView1.Rows.Add(); dataGridView1.Rows[k].Cells[0].Value = pnn.val; } else { clist pnn = new clist(); pnn.val = Convert.ToDouble(xlRange.Cells[i, j].Value2); tot2 += pnn.val; pnn.cumulative = tot2; freqency.Add(pnn); dataGridView1.Rows.Add(); dataGridView1.Rows[k].Cells[1].Value = pnn.val; dataGridView1.Rows[k].Cells[3].Value = pnn.cumulative; } } else { stop = true; } //add useful things here! } k++; if (stop) { break; } } tot = 0; for (int i = 0; i < freqency.Count; i++) { clist pnn = new clist(); pnn.val = freqency[i].val / freqency[freqency.Count - 1].cumulative; tot += pnn.val; pnn.relativecumulative = tot; relativefreq.Add(pnn); dataGridView1.Rows[i].Cells[2].Value = pnn.val; dataGridView1.Rows[i].Cells[4].Value = pnn.relativecumulative; } for (int i = 0; i < freqency.Count; i++) { if (i == 0) { freqency[i].range = 0; freqency[i].rangeend = freqency[i].cumulative; dataGridView1.Rows[i].Cells[5].Value = freqency[i].range + "-" + freqency[i].rangeend; } else { freqency[i].range = (freqency[i - 1].cumulative + 1); freqency[i].rangeend = freqency[i].cumulative; dataGridView1.Rows[i].Cells[5].Value = freqency[i].range + "-" + freqency[i].rangeend; } } xlWorkbook.Close(true); xlApp.Quit(); }
//Create the Excel file from dataset public void Okuri(DataTable ds, string SOKOCD) { string saveFileName = "送り状兼受領書_" + SOKOCD + "_" + DateTime.Now.ToString("yyyyMMddHHmmss"); string templetFile = @"template\送り状兼受領書.xlsx"; SaveFileDialog saveDialog = new SaveFileDialog { Filter = "Excel文件 |*.xlsx", FileName = saveFileName, RestoreDirectory = true }; if (saveDialog.ShowDialog() == DialogResult.OK) { saveFileName = saveDialog.FileName; File.Copy(templetFile, saveFileName, true); Excel.Application excel = new Excel.Application(); Excel.Workbook workbook = excel.Workbooks.Open(saveFileName); Excel.Worksheet worksheet = workbook.Worksheets[1]; excel.Visible = false; try { log.Info("EXEC BEGIN"); var distinctIds = ds.AsEnumerable() .Select(s => new { area = s.Field <string>("AREANM"), homen = s.Field <string>("HOMEN"), //soko = s.Field<string>("SOKONM"), //nukn = s.Field<string>("NUKNNM"), }) .Distinct().ToList(); if (distinctIds.Count > 1) { int no = 0; foreach (var a in distinctIds) { string expression; DataRow[] foundRows; expression = "AREANM = '" + a.area.ToString() + "' AND HOMEN = '" + a.homen.ToString() + "'"; foundRows = ds.Select(expression); worksheet.Name = foundRows[0][11] + "_" + foundRows[0][10]; workbook.Worksheets[1].Name = workbook.Worksheets[1].Name.Split(' ')[0]; worksheet.Cells[1, "H"] = foundRows[0][11]; worksheet.Cells[1, "AD"] = foundRows[0][10]; worksheet.Cells[5, "B"] = foundRows[0][2]; worksheet.Cells[5, "H"] = foundRows[0][5]; worksheet.Cells[5, "N"] = foundRows[0][3]; worksheet.Cells[5, "Y"] = foundRows[0][1]; worksheet.Cells[5, "AS"] = foundRows[0][6]; worksheet.Cells[5, "AW"] = foundRows[0][7]; worksheet.Cells[5, "BC"] = foundRows[0][8]; worksheet.Cells[5, "BM"] = foundRows[0][9]; worksheet.Cells[5, "CD"] = foundRows[0][12]; worksheet.Cells[5, "CN"] = "サイン"; for (int i = 0; i < foundRows.Length - 1; i++) { Excel.Range RngToCopy = worksheet.get_Range("B5").EntireRow; Excel.Range RngToInsert = worksheet.get_Range("B" + (i + 6)).EntireRow; RngToInsert.Insert(Excel.XlInsertShiftDirection.xlShiftDown, RngToCopy.Copy()); worksheet.Cells[6 + i, "B"] = foundRows[i + 1][2]; worksheet.Cells[6 + i, "H"] = foundRows[i + 1][5]; worksheet.Cells[6 + i, "N"] = foundRows[i + 1][3]; worksheet.Cells[6 + i, "Y"] = foundRows[i + 1][1]; worksheet.Cells[6 + i, "AS"] = foundRows[i + 1][6]; worksheet.Cells[6 + i, "AW"] = foundRows[i + 1][7]; worksheet.Cells[6 + i, "BC"] = foundRows[i + 1][8]; worksheet.Cells[6 + i, "BM"] = foundRows[i + 1][9]; worksheet.Cells[6 + i, "CD"] = foundRows[i + 1][12]; worksheet.Cells[6 + i, "CN"] = "サイン"; } no++; if (no < distinctIds.Count) { worksheet.Copy(workbook.Worksheets[1], Type.Missing); worksheet.get_Range("B5:B1000").EntireRow.ClearContents(); worksheet.get_Range("CN6:CN1000").EntireRow.Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone; } } } else { foreach (var a in distinctIds) { string expression; DataRow[] foundRows; expression = "AREANM = '" + a.area.ToString() + "' AND HOMEN = '" + a.homen.ToString() + "'"; foundRows = ds.Select(expression); worksheet.Name = foundRows[0][11] + "_" + foundRows[0][10]; workbook.Worksheets[1].Name = workbook.Worksheets[1].Name.Split(' ')[0]; worksheet.Cells[1, "H"] = foundRows[0][11]; worksheet.Cells[1, "AD"] = foundRows[0][10]; worksheet.Cells[5, "B"] = foundRows[0][2]; worksheet.Cells[5, "H"] = foundRows[0][5]; worksheet.Cells[5, "N"] = foundRows[0][3]; worksheet.Cells[5, "Y"] = foundRows[0][1]; worksheet.Cells[5, "AS"] = foundRows[0][6]; worksheet.Cells[5, "AW"] = foundRows[0][7]; worksheet.Cells[5, "BC"] = foundRows[0][8]; worksheet.Cells[5, "BM"] = foundRows[0][9]; worksheet.Cells[5, "CD"] = foundRows[0][12]; worksheet.Cells[5, "CN"] = "サイン"; for (int i = 1; i < foundRows.Length; i++) { Excel.Range RngToCopy = worksheet.get_Range("B5").EntireRow; Excel.Range RngToInsert = worksheet.get_Range("B" + (i + 5)).EntireRow; RngToInsert.Insert(Excel.XlInsertShiftDirection.xlShiftDown, RngToCopy.Copy()); worksheet.Cells[5 + i, "B"] = foundRows[i][2]; worksheet.Cells[5 + i, "H"] = foundRows[i][5]; worksheet.Cells[5 + i, "N"] = foundRows[i][3]; worksheet.Cells[5 + i, "Y"] = foundRows[i][1]; worksheet.Cells[5 + i, "AS"] = foundRows[i][6]; worksheet.Cells[5 + i, "AW"] = foundRows[i][7]; worksheet.Cells[5 + i, "BC"] = foundRows[i][8]; worksheet.Cells[5 + i, "BM"] = foundRows[i][9]; worksheet.Cells[5 + i, "CD"] = foundRows[i][12]; worksheet.Cells[5 + i, "CN"] = "サイン"; } } } } catch (Exception e) { log.Error("ERROR" + e.Message); } finally { excel.DisplayAlerts = false; Application.DoEvents(); workbook.Save(); excel.Quit(); Marshal.FinalReleaseComObject(excel); log.Info("EXEC END"); } } else { MessageBox.Show("処理を中止しました。"); } }
public static string FillExcel <T>(string Path, string[] columnName, string name, List <T> list) { System.Data.DataTable dt = ListToDataTable.ListToTable <T>(list, columnName); Microsoft.Office.Interop.Excel.Application ex = new Microsoft.Office.Interop.Excel.Application(); Workbooks wbk2 = ex.Workbooks; _Workbook _wbk2 = wbk2.Add(true); //复制完关闭excel模板 Worksheet ws = _wbk2.Sheets[1]; //整体写入方法 object[,] objData = new object[dt.Rows.Count + 1, dt.Columns.Count]; //首先将数据写入到一个二维数组中 for (int m = 0; m < dt.Columns.Count; m++) { //if (!dt.Columns[m].ColumnName.StartsWith("F")) objData[0, m] = dt.Columns[m].ColumnName; //else // objData[0, m] = null; } if (dt.Rows.Count > 0) { for (int m = 0; m < dt.Rows.Count; m++) { for (int j = 0; j < dt.Columns.Count; j++) { objData[m + 1, j] = dt.Rows[m][j]; } } } string startCol = "A"; int iCnt = (dt.Columns.Count / 26); string endColSignal = (iCnt == 0 ? "" : ((char)('A' + (iCnt - 1))).ToString()); string endCol = endColSignal + ((char)('A' + dt.Columns.Count - iCnt * 26 - 1)).ToString(); Microsoft.Office.Interop.Excel.Range range = ws.get_Range(startCol + "1", endCol + (dt.Rows.Count - iCnt * 26 + 1).ToString()); //Microsoft.Office.Interop.Excel.Range range = ws.get_Range(startCol + "1", endCol + dt.Rows.Count.ToString()); range.Value = objData; //逐个单元格写入方法 // for (int r = 0; r < dt.Rows.Count; r++) // { // for (int l = 0; l < dt.Columns.Count; l++) // { // ws.Cells[r + 2, l + 1] = dt.Rows[r][l].ToString(); // } // } //保存 ex.DisplayAlerts = false; ex.AlertBeforeOverwriting = false; try { _wbk2.SaveAs(Path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } catch (Exception e) { } finally { System.Runtime.InteropServices.Marshal.ReleaseComObject(ws); System.Runtime.InteropServices.Marshal.ReleaseComObject(_wbk2); System.Runtime.InteropServices.Marshal.ReleaseComObject(wbk2); ex.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(ex); } return(Path); }
private void Design_IRC_Abutment_Bridges_Box_Type() { string file_path = Get_Project_Folder(); //file_path = Path.Combine(file_path, "BoQ_Bridges_Box_Type.xlsx"); file_path = Path.Combine(file_path, "IRC Abutment Design Box Type.xlsx"); string copy_path = file_path; //file_path = Path.Combine(Application.StartupPath, @"DESIGN\Abutment\Abutment Design IRC\IRC Abutment Design.xlsx"); file_path = Path.Combine(Application.StartupPath, @"DESIGN\Abutment\Abutment Design IRC\IRC ABUTMENT Design_Box_Type.xlsx"); if (File.Exists(file_path)) { File.Copy(file_path, copy_path, true); } Excel.Application myExcelApp; Excel.Workbooks myExcelWorkbooks; Excel.Workbook myExcelWorkbook; object misValue = System.Reflection.Missing.Value; myExcelApp = new Excel.Application(); myExcelApp.Visible = true; //myExcelApp.Visible = false; myExcelWorkbooks = myExcelApp.Workbooks; //myExcelWorkbook = myExcelWorkbooks.Open(fileName, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue); myExcelWorkbook = myExcelWorkbooks.Open(copy_path, 0, false, 5, "2011ap", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); //Excel.Worksheet myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.ActiveSheet; Excel.Worksheet myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["DLSUP"]; //Excel.Range formatRange; //formatRange = myExcelWorksheet.get_Range("b" + (dgv_box_gen_data.RowCount + 8), "L" + (dgv_box_gen_data.RowCount + 8)); //formatRange.Interior.Color = System.Drawing. //ColorTranslator.ToOle(System.Drawing.Color.LightGreen); List <string> list = new List <string>(); #region Section Input myExcelWorksheet.get_Range("A9").Formula = txt_sec_B1.Text; myExcelWorksheet.get_Range("B9").Formula = txt_sec_B2.Text; myExcelWorksheet.get_Range("C9").Formula = txt_sec_B3.Text; myExcelWorksheet.get_Range("E9").Formula = txt_sec_B4.Text; myExcelWorksheet.get_Range("D15").Formula = txt_sec_B5.Text; myExcelWorksheet.get_Range("G17").Formula = txt_sec_B6.Text; myExcelWorksheet.get_Range("A17").Formula = txt_sec_H1.Text; myExcelWorksheet.get_Range("E12").Formula = txt_sec_H2.Text; myExcelWorksheet.get_Range("G12").Formula = txt_sec_H3.Text; myExcelWorksheet.get_Range("I12").Formula = txt_sec_H4.Text; myExcelWorksheet.get_Range("D13").Formula = txt_sec_H5.Text; myExcelWorksheet.get_Range("E20").Formula = txt_sec_H6.Text; #endregion Section Input DataGridView dgv = dgv_sidl; int rindx = 0; #region SIDL Input string v1 = ""; double n, w, d, uu; myExcelWorksheet.get_Range("L118").Formula = txt_sidl_spc1.Text; myExcelWorksheet.get_Range("M118").Formula = txt_sidl_spc2.Text; for (int i = 0; i < dgv.RowCount; i++) { n = MyList.StringToDouble(dgv[2, i].Value.ToString(), 0.0); w = MyList.StringToDouble(dgv[3, i].Value.ToString(), 0.0); d = MyList.StringToDouble(dgv[4, i].Value.ToString(), 0.0); uu = MyList.StringToDouble(dgv[5, i].Value.ToString(), 0.0); myExcelWorksheet.get_Range("D" + (i + 117)).Formula = dgv[2, i].Value.ToString(); myExcelWorksheet.get_Range("E" + (i + 117)).Formula = dgv[3, i].Value.ToString(); myExcelWorksheet.get_Range("F" + (i + 117)).Formula = dgv[4, i].Value.ToString(); myExcelWorksheet.get_Range("G" + (i + 117)).Formula = dgv[5, i].Value.ToString(); try { if ((n * w * d * uu) == 0.0) { myExcelWorksheet.get_Range("H" + (i + 117)).Formula = dgv[6, i].Value.ToString(); } } catch (Exception ex0) { //throw; } } #endregion SIDL Input double L = MyList.StringToDouble(txt_Ana_L.Text, 0.0); double og = MyList.StringToDouble(txt_Ana_og.Text, 0.0); double eff_L = (L - 2 * og); myExcelWorksheet.get_Range("B153").Formula = txt_Ana_og.Text; myExcelWorksheet.get_Range("C152").Formula = (eff_L / 4.0).ToString("f3"); myExcelWorksheet.get_Range("D160").Formula = eff_L.ToString("f3"); #region LL myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["LL"]; myExcelWorksheet.get_Range("A9").Formula = og.ToString(); myExcelWorksheet.get_Range("I10").Formula = eff_L.ToString(); myExcelWorksheet.get_Range("S9").Formula = og.ToString(); #endregion LL #region Earth pr myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["Earth pr"]; dgv = dgv_earth_pressure; for (int i = 0; i < dgv.RowCount; i++) { myExcelWorksheet.get_Range("F" + (i + 7)).Formula = dgv[1, i].Value.ToString(); } #endregion Earth pr #region Base Pressure myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["Base Pressure"]; rindx = 0; dgv = dgv_base_pressure; List <string> ldbl = new List <string>(); for (int i = 0; i < dgv.RowCount; i++) { try { if (dgv[1, i].Value.ToString() != "") { ldbl.Add(dgv[1, i].Value.ToString()); } } catch (Exception exx) {} } rindx = 0; for (int i = 7; i < 55; i++) { if (i == 18 || i == 19 || i == 25 || (i >= 28 && i <= 30) || i == 35 || i == 44 || i == 47) { continue; } else { myExcelWorksheet.get_Range("F" + i).Formula = ldbl[rindx++].ToString(); } } #endregion Earth pr rindx = 0; myExcelWorkbook.Save(); releaseObject(myExcelWorkbook); //iapp.Excel_Open_Message(); }
public JsonResult GAnalytics(string path, string orig_filename, int month, int year, DateTime dt) { //string path = "C:\\Users\\Abel\\Downloads\\AugustAnalytics\\BMRACING.xlsx"; var _app = new Excel.Application(); var _workbooks = _app.Workbooks.Open(path); var _worksheet = _workbooks.ActiveSheet; string cell_data = ""; Microsoft.Office.Interop.Excel.Range range = _worksheet.UsedRange; //Read the first cell string text_value = ""; int end_row = range.Rows.Count; var end_column = range.Columns.Count; var group_field = ""; var subgroup_field_text = ""; var subgroup_field_column2 = ""; var subgroup_field_column3 = ""; var subgroup_field_column4 = ""; var insert = false; var value = ""; var name = ""; //[row,column] for (var index_row = 1; index_row <= end_row; index_row++) { insert = true; value = ""; name = ""; for (var index_column = 1; index_column <= end_column; index_column++) { text_value = _worksheet.Cells[index_row, index_column].Text.ToString(); if (text_value != "" || index_column == 2 || index_column == 3 || index_column == 4) { if (index_column == 1) { if (text_value == "Audience" || text_value == "New vs Returning Visitor" || text_value == "Devices" || text_value == "Acquisition" || text_value == "Behavior" || text_value == "Site Speed" ) { group_field = text_value; insert = false; } else if (text_value.Contains("Country")) { group_field = "Country"; insert = false; } } if (!insert) { if (index_column == 2) { subgroup_field_column2 = text_value; } else if (index_column == 3) { subgroup_field_column3 = text_value; //subgroup_field_text = subgroup_field_column3; } else if (index_column == 4) { subgroup_field_column4 = text_value; // subgroup_field_text = subgroup_field_column4; } } else { if (index_column == 1) { name = text_value; } if (index_column == 2) { subgroup_field_text = subgroup_field_column2; value = text_value; } else if (index_column == 3) { subgroup_field_text = subgroup_field_column3; value = text_value; } else if (index_column == 4) { subgroup_field_text = subgroup_field_column4; value = text_value; } } string fileName = Path.GetFileNameWithoutExtension(orig_filename); //cell_data += group_field + index_row + ":" + index_column + " " + text_value; if (insert && value != "") { Library.Execute(@"insert into tblBrandGoogleAanalyticsData (brand,group_field,subgroup_id, subgroup_field, name, value, month, year, dt) values('" + fileName + "','" + group_field + "','" + (index_column - 1) + "','" + subgroup_field_text + "','" + name + "','" + value + "','" + month + "','" + year + "','" + dt.Date + @"' ) "); // cell_data += "group_field: " + group_field + " subgroup_field:(" + (index_column - 1) + ")" + subgroup_field_text + " " + "name: " + name + " " + value; } } else { // subgroup_field_column2 = ""; subgroup_field_column3 = ""; subgroup_field_column4 = ""; } } } _workbooks.Close(); _app.Quit(); if (_worksheet != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(_worksheet); } if (_workbooks != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(_workbooks); } System.Runtime.InteropServices.Marshal.ReleaseComObject(_app); return(Json(new { path = path })); }
protected override void Execute(CodeActivityContext context) { //Load Input Fields unto local variables string filePath = FilePath.Get(context); //Validate that file path is not empty if (!String.IsNullOrEmpty(filePath)) { if (File.Exists(filePath)) { try { //Initialize Excel Interop objects; object m = Type.Missing; Excel.Application xlApp = new Excel.Application(); Excel.Workbooks xlWorkbooks = xlApp.Workbooks; Excel.Workbook xlWorkbook = xlWorkbooks.Open(filePath, m, true, m, m, m, m, m, m, m, m, m, m, m, m); Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1]; Excel.Range xlRange = xlWorksheet.UsedRange; // Gather Row and Col counts Int32 lastRow = xlRange.Rows.Count; Int32 lCol = xlRange.Columns.Count; Int32 sCol = lCol + 1; // Create local variables for processing int i; string vLstCol; //iterate by the existing columns to determine if a current Status column exists for (i = 1; i <= lCol; i++) { vLstCol = xlWorksheet.Cells[1, i].Value; if (vLstCol == "Status") { sCol = i; lCol = i - 1; } } //Count all rows that Status Column has the value of "Complete" int countComplete = (int)xlApp.WorksheetFunction.CountIf(xlRange.Columns[sCol, m], "Complete") + 1; //Count all rows that are not empty on the first column (Item-Index column) int countNotNull = (int)xlApp.WorksheetFunction.CountA(xlRange.Columns[1, m]); //Validate that complete rows match the lastRow count if (countComplete == countNotNull) { //Return True for Complete isComplete.Set(context, true); } else { //Return False for not complete isComplete.Set(context, false); } //DebugOut Output DebugOut.Set(context, "CC: " + countComplete.ToString() + " | CA: " + countNotNull.ToString() + " | LR: " + lastRow.ToString()); //Close Workbook no Save xlWorkbook.Close(false, m, m); //CLOSE AND GARBAGE COLLECT Marshal.ReleaseComObject(xlWorksheet); xlWorksheet = null; Marshal.ReleaseComObject(xlWorkbooks); xlWorkbooks = null; Marshal.ReleaseComObject(xlWorkbook); xlWorkbook = null; xlApp.Quit(); Marshal.ReleaseComObject(xlApp); xlApp = null; GC.Collect(); //Garbage Collect GC.WaitForPendingFinalizers(); //Wait until Garbage Collect completes } catch { throw; } } else { throw new FileNotFoundException(); } } else { throw new ArgumentNullException(); } }
private void button1_Click(object sender, EventArgs e) { // Khởi động chương trình Excel COMExcel.Application exApp = new COMExcel.Application(); COMExcel.Workbook exBook; COMExcel.Worksheet exSheet; COMExcel.Range exRange; string sql; int hang = 0, cot = 0; DataTable tblThongtinHD, tblThongtinHang; exBook = exApp.Workbooks.Add(COMExcel.XlWBATemplate.xlWBATWorksheet); exSheet = exBook.Worksheets[1]; // Định dạng chung exRange = exSheet.Cells[1, 1]; exRange.Range["A1:B3"].Font.Size = 10; exRange.Range["A1:B3"].Font.Name = "Times new roman"; exRange.Range["A1:B3"].Font.Bold = true; exRange.Range["A1:B3"].Font.ColorIndex = 5; //Màu xanh da trời exRange.Range["A1:A1"].ColumnWidth = 7; exRange.Range["B1:B1"].ColumnWidth = 15; exRange.Range["A1:B1"].MergeCells = true; exRange.Range["A1:B1"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range["A1:B1"].Value = "E Store"; exRange.Range["A2:B2"].MergeCells = true; exRange.Range["A2:B2"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range["A2:B2"].Value = "Việt Nam"; exRange.Range["A3:B3"].MergeCells = true; exRange.Range["A3:B3"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range["A3:B3"].Value = "Điện thoại: 0972594994"; exRange.Range["C2:E2"].Font.Size = 16; exRange.Range["C2:E2"].Font.Name = "Times new roman"; exRange.Range["C2:E2"].Font.Bold = true; exRange.Range["C2:E2"].Font.ColorIndex = 3; //Màu đỏ exRange.Range["C2:E2"].MergeCells = true; exRange.Range["C2:E2"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range["C2:E2"].Value = "BÁO CÁO"; //Lấy thông tin các mặt hàng sql = @"SELECT tongtien, makh FROM tb_HDB WHERE ngayban BETWEEN '" + d1.Text + "' AND '" + d2.Text + "'"; tblThongtinHang = cn.taobang(sql); //Tạo dòng tiêu đề bảng exRange.Range["A11:F11"].Font.Bold = true; exRange.Range["A11:F11"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range["C11:F11"].ColumnWidth = 12; exRange.Range["A11:A11"].Value = "STT"; exRange.Range["B11:B11"].Value = "Mã KH"; exRange.Range["C11:C11"].Value = "Số Lượng"; for (hang = 0; hang <= tblThongtinHang.Rows.Count - 1; hang++) { //Điền số thứ tự vào cột 1 từ dòng 12 exSheet.Cells[1][hang + 12] = hang + 1; for (cot = 0; cot <= tblThongtinHang.Columns.Count - 1; cot++) { //Điền thông tin hàng từ cột thứ 2, dòng 12 exSheet.Cells[cot + 2][hang + 12] = tblThongtinHang.Rows[hang][cot].ToString(); } } exRange = exSheet.Cells[1][hang + 15]; //Ô A1 exRange.Range["A1:F1"].MergeCells = true; exRange.Range["A1:F1"].Font.Bold = true; exRange.Range["A1:F1"].Font.Italic = true; exRange.Range["A1:F1"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignRight; exRange = exSheet.Cells[4][hang + 17]; //Ô A1 exRange.Range["A1:C1"].MergeCells = true; exRange.Range["A1:C1"].Font.Italic = true; exRange.Range["A1:C1"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; DateTime d = DateTime.Now; exRange.Range["A1:C1"].Value = "Hà Nội, ngày " + d.Day + " tháng " + d.Month + " năm " + d.Year; exRange.Range["A2:C2"].MergeCells = true; exRange.Range["A2:C2"].Font.Italic = true; exRange.Range["A2:C2"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range["A6:C6"].MergeCells = true; exRange.Range["A6:C6"].Font.Italic = true; exRange.Range["A6:C6"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exSheet.Name = "Báo Cáo"; exApp.Visible = true; }
private void EigyoReport(DataGridView tempDGV) { const int S_GYO = 4; //エクセルファイル見出し行(明細は3行目から印字) const int S_ROWSMAX = 8; //エクセルファイル列最大値 string sMidashi; double gTL = 0; try { //マウスポインタを待機にする this.Cursor = Cursors.WaitCursor; string sAppPath = System.AppDomain.CurrentDomain.BaseDirectory; Excel.Application oXls = new Excel.Application(); Excel.Workbook oXlsBook = (Excel.Workbook)(oXls.Workbooks.Open(sAppPath + Properties.Settings.Default.営業売上表, 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)); Excel.Worksheet oxlsSheet = (Excel.Worksheet)oXlsBook.Sheets[1]; Excel.Range[] rng = new Microsoft.Office.Interop.Excel.Range[2]; try { for (int iX = 0; iX <= tempDGV.RowCount - 1; iX++) { sMidashi = this.comboBox1.Text + " "; if (tDate.Checked == true) { sMidashi += this.tDate.Value.ToShortDateString() + " 〜 "; } if (tDate2.Checked == true) { if (tDate.Checked == false) { sMidashi += " 〜 "; } sMidashi += this.tDate2.Value.ToShortDateString(); } oxlsSheet.Cells[S_GYO - 2, 1] = sMidashi; oxlsSheet.Cells[iX + S_GYO, 1] = tempDGV[colNDt, iX].Value.ToString(); oxlsSheet.Cells[iX + S_GYO, 2] = tempDGV[colClient, iX].Value.ToString(); oxlsSheet.Cells[iX + S_GYO, 3] = tempDGV[colNyukin, iX].Value.ToString(); oxlsSheet.Cells[iX + S_GYO, 4] = tempDGV[colEganka, iX].Value.ToString(); oxlsSheet.Cells[iX + S_GYO, 5] = tempDGV[colArari1, iX].Value.ToString(); double g = Utility.strToDouble(tempDGV[colGaichuhi, iX].Value.ToString()) + Utility.strToDouble(tempDGV[colGaichuhi2, iX].Value.ToString()) + Utility.strToDouble(tempDGV[colGaichuhi3, iX].Value.ToString()); oxlsSheet.Cells[iX + S_GYO, 6] = g.ToString(); oxlsSheet.Cells[iX + S_GYO, 7] = tempDGV[colArari2, iX].Value.ToString(); oxlsSheet.Cells[iX + S_GYO, 8] = tempDGV[colArariSai, iX].Value.ToString(); //セル下部へ実線ヨコ罫線を引く rng[0] = (Excel.Range)oxlsSheet.Cells[iX + S_GYO, 1]; rng[1] = (Excel.Range)oxlsSheet.Cells[iX + S_GYO, S_ROWSMAX]; oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; gTL += g; } ////セル上部へ実線ヨコ罫線を引く //rng[0] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 1]; //rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX]; //oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; ////セル下部へ実線ヨコ罫線を引く //rng[0] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 1]; //rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX]; //oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //表全体に実線縦罫線を引く rng[0] = (Excel.Range)oxlsSheet.Cells[S_GYO, 1]; rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX]; oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //表全体の左端縦罫線 rng[0] = (Excel.Range)oxlsSheet.Cells[S_GYO, 1]; rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 1]; oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //表全体の右端縦罫線 rng[0] = (Excel.Range)oxlsSheet.Cells[S_GYO, S_ROWSMAX]; rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX]; oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //合計 oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count + 1, 3] = this.label5.Text; oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 4] = this.lblEgenka.Text; oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 5] = this.lblArari1.Text; oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 6] = gTL.ToString("#,##0"); oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 7] = this.lblArari2.Text; oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 8] = this.lblArarisai.Text; //マウスポインタを元に戻す this.Cursor = Cursors.Default; // 確認のためExcelのウィンドウを表示する oXls.Visible = true; //印刷 oxlsSheet.PrintPreview(true); // ウィンドウを非表示にする oXls.Visible = false; //保存処理 oXls.DisplayAlerts = false; DialogResult ret; string msgHD = ""; if (tDate.Checked == true) { msgHD += tDate.Value.ToLongDateString() + "から"; } if (tDate2.Checked == true) { msgHD += tDate2.Value.ToLongDateString() + "まで"; } //ダイアログボックスの初期設定 saveFileDialog1.Title = MESSAGE_CAPTION; saveFileDialog1.OverwritePrompt = true; saveFileDialog1.RestoreDirectory = true; saveFileDialog1.FileName = MESSAGE_CAPTION + "_" + comboBox1.Text + "_" + msgHD; saveFileDialog1.Filter = "Microsoft Office Excelファイル(*.xls)|*.xls|全てのファイル(*.*)|*.*"; //ダイアログボックスを表示し「保存」ボタンが選択されたらファイル名を表示 string fileName; ret = saveFileDialog1.ShowDialog(); if (ret == System.Windows.Forms.DialogResult.OK) { fileName = saveFileDialog1.FileName; oXlsBook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } //Bookをクローズ oXlsBook.Close(Type.Missing, Type.Missing, Type.Missing); //Excelを終了 oXls.Quit(); } catch (Exception e) { MessageBox.Show(e.Message, MESSAGE_CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Exclamation); //Bookをクローズ oXlsBook.Close(Type.Missing, Type.Missing, Type.Missing); //Excelを終了 oXls.Quit(); } finally { // COM オブジェクトの参照カウントを解放する System.Runtime.InteropServices.Marshal.ReleaseComObject(oxlsSheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(oXlsBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(oXls); //マウスポインタを元に戻す this.Cursor = Cursors.Default; } } catch (Exception e) { MessageBox.Show(e.Message, "営業別売上表", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } //マウスポインタを元に戻す this.Cursor = Cursors.Default; }
public void SaveExcel(string saveFileName, DataGridView dataGridView1) { if (dataGridView1.Rows.Count > 0) { Microsoft.Office.Interop.Excel.Application app; Workbooks workBooks; Workbook workBook; Worksheet workSheet; try { app = new Microsoft.Office.Interop.Excel.Application(); workBooks = app.Workbooks; workBook = workBooks.Add(XlWBATemplate.xlWBATWorksheet); workSheet = (Worksheet)workBook.Worksheets[1]; } catch (System.Exception ex) { Trace.WriteLine(ex.ToString()); MessageBox.Show(" 您可能没有安装 Office ,请安装再使用该功能 "); return; } for (int i = 0; i < dataGridView1.Columns.Count; i++) { workSheet.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText; } for (int i = 0; i < dataGridView1.Rows.Count - 1; i++) { for (int j = 0; j < dataGridView1.Columns.Count; j++) { if (dataGridView1[j, i].ValueType == typeof(string)) { workSheet.Cells[i + 2, j + 1] = "'" + dataGridView1[j, i].Value.ToString(); } else { workSheet.Cells[i + 2, j + 1] = dataGridView1[j, i].Value.ToString(); } } } if (saveFileName != "") { try { workBook.Saved = true; workBook.SaveCopyAs(saveFileName); } catch (Exception ex) { MessageBox.Show("导出文件时出错,文件可能正被打开!\n" + ex.Message); } } app.Quit(); app = null; } else { MessageBox.Show("数据表中没有要保存的数据"); } }
public ExcelReader() { xlApp = new Excel.Application(); xlApp.DisplayAlerts = false; xlWorkbooks = xlApp.Workbooks; }
private void button1_Click(object sender, EventArgs e) { // Khởi động chương trình Excel COMExcel.Application exApp = new COMExcel.Application(); COMExcel.Workbook exBook; COMExcel.Worksheet exSheet; COMExcel.Range exRange; string sql; int hang = 0, cot = 0; DataTable tblThongtinHD, tblThongtinHang; exBook = exApp.Workbooks.Add(COMExcel.XlWBATemplate.xlWBATWorksheet); exSheet = exBook.Worksheets[1]; // Định dạng chung exRange = exSheet.Cells[1, 1]; exRange.Range["A1:B3"].Font.Size = 10; exRange.Range["A1:B3"].Font.Name = "Times new roman"; exRange.Range["A1:B3"].Font.Bold = true; exRange.Range["A1:B3"].Font.ColorIndex = 5; //Màu xanh da trời exRange.Range["A1:A1"].ColumnWidth = 7; exRange.Range["B1:B1"].ColumnWidth = 15; exRange.Range["A1:B1"].MergeCells = true; exRange.Range["A1:B1"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range["A1:B1"].Value = "E Store"; exRange.Range["A2:B2"].MergeCells = true; exRange.Range["A2:B2"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range["A2:B2"].Value = "Việt Nam"; exRange.Range["A3:B3"].MergeCells = true; exRange.Range["A3:B3"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range["A3:B3"].Value = "Điện thoại: 0972594994"; exRange.Range["C2:E2"].Font.Size = 16; exRange.Range["C2:E2"].Font.Name = "Times new roman"; exRange.Range["C2:E2"].Font.Bold = true; exRange.Range["C2:E2"].Font.ColorIndex = 3; //Màu đỏ exRange.Range["C2:E2"].MergeCells = true; exRange.Range["C2:E2"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range["C2:E2"].Value = "HÓA ĐƠN NHẬP"; // Biểu diễn thông tin chung của hóa đơn bán sql = @"SELECT tb_HDB.sohdb, tb_Khachhang.tenkh, tb_Khachhang.diachi, tb_Nhanvien.tennv,tb_HDB.ngayban FROM tb_CTHDB INNER JOIN tb_HDB ON tb_CTHDB.sohdb = tb_HDB.sohdb INNER JOIN tb_Khachhang ON tb_HDB.makh = tb_Khachhang.makh INNER JOIN tb_Nhanvien ON tb_HDB.manv = tb_Nhanvien.manv where tb_HDB.sohdb='" + cbhd.Text + "'"; tblThongtinHD = cn.taobang(sql); exRange.Range["B6:C9"].Font.Size = 12; exRange.Range["B6:C9"].Font.Name = "Times new roman"; exRange.Range["B6:B6"].Value = "Mã hóa đơn:"; exRange.Range["C6:E6"].MergeCells = true; exRange.Range["C6:E6"].Value = tblThongtinHD.Rows[0][0].ToString(); exRange.Range["B7:B7"].Value = "Nhà Cung Cấp:"; exRange.Range["C7:E7"].MergeCells = true; exRange.Range["C7:E7"].Value = tblThongtinHD.Rows[0][1].ToString(); exRange.Range["B8:B8"].Value = "Địa chỉ:"; exRange.Range["C8:E8"].MergeCells = true; exRange.Range["C8:E8"].Value = tblThongtinHD.Rows[0][2].ToString(); exRange.Range["B9:B9"].Value = "Điện thoại:"; exRange.Range["C9:E9"].MergeCells = true; exRange.Range["C9:E9"].Value = tblThongtinHD.Rows[0][3].ToString(); //Lấy thông tin các mặt hàng sql = @"SELECT tb_Hanghoa.tenhang, tb_CTHDB.soluong,tb_Hanghoa.dongiaban, tb_CTHDB.giamgia, tb_CTHDB.thanhtien FROM tb_CTHDB INNER JOIN tb_Hanghoa ON tb_CTHDB.mahang = tb_Hanghoa.mahang where tb_CTHDB.sohdb='" + cbhd.Text + "'"; tblThongtinHang = cn.taobang(sql); //Tạo dòng tiêu đề bảng exRange.Range["A11:F11"].Font.Bold = true; exRange.Range["A11:F11"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range["C11:F11"].ColumnWidth = 12; exRange.Range["A11:A11"].Value = "STT"; exRange.Range["B11:B11"].Value = "Tên hàng"; exRange.Range["C11:C11"].Value = "Số lượng"; exRange.Range["D11:D11"].Value = "Đơn giá"; exRange.Range["E11:E11"].Value = "Giảm giá"; exRange.Range["F11:F11"].Value = "Thành tiền"; for (hang = 0; hang <= tblThongtinHang.Rows.Count - 1; hang++) { //Điền số thứ tự vào cột 1 từ dòng 12 exSheet.Cells[1][hang + 12] = hang + 1; for (cot = 0; cot <= tblThongtinHang.Columns.Count - 1; cot++) { //Điền thông tin hàng từ cột thứ 2, dòng 12 exSheet.Cells[cot + 2][hang + 12] = tblThongtinHang.Rows[hang][cot].ToString(); } } exRange = exSheet.Cells[1][hang + 15]; //Ô A1 exRange.Range["A1:F1"].MergeCells = true; exRange.Range["A1:F1"].Font.Bold = true; exRange.Range["A1:F1"].Font.Italic = true; exRange.Range["A1:F1"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignRight; exRange = exSheet.Cells[4][hang + 17]; //Ô A1 exRange.Range["A1:C1"].MergeCells = true; exRange.Range["A1:C1"].Font.Italic = true; exRange.Range["A1:C1"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; DateTime d = Convert.ToDateTime(tblThongtinHD.Rows[0][4]); exRange.Range["A1:C1"].Value = "Hà Nội, ngày " + d.Day + " tháng " + d.Month + " năm " + d.Year; exRange.Range["A2:C2"].MergeCells = true; exRange.Range["A2:C2"].Font.Italic = true; exRange.Range["A2:C2"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range["A2:C2"].Value = "Nhân viên Bán hàng"; exRange.Range["A6:C6"].MergeCells = true; exRange.Range["A6:C6"].Font.Italic = true; exRange.Range["A6:C6"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range["A6:C6"].Value = tblThongtinHD.Rows[0][3]; exSheet.Name = "Hóa đơn Bán"; exApp.Visible = true; }
/// <summary> /// 启动合并 /// </summary> /// <param name="dirPath"></param> public void Start(string dirPath) { if (this.isStarted) { return; } this.isStarted = true; this.total = ExcelHelper.GetAvailableExcelCount(dirPath); Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); new AsyncTask <_Workbook>(() => { Workbooks workbooks = excel.Workbooks; _Workbook result = workbooks.Add();//创建一个空的excel文件 DirectoryInfo dir = new DirectoryInfo(dirPath); FileSystemInfo[] fsList = dir.GetFileSystemInfos(); foreach (FileSystemInfo fs in fsList) { if (fs is FileInfo) { if (fs.Attributes.HasFlag(FileAttributes.Temporary) || fs.Attributes.HasFlag(FileAttributes.Hidden) || fs.Attributes.HasFlag(FileAttributes.NotContentIndexed)) { continue; } if (fs.Extension.Equals(".xls", StringComparison.OrdinalIgnoreCase) || fs.Extension.Equals(".xlsx", StringComparison.OrdinalIgnoreCase)) { _Workbook wb = workbooks.Open(fs.FullName); foreach (_Worksheet sheet in wb.Sheets) { Range range = sheet.UsedRange; range.Copy(((_Worksheet)result.Worksheets[1]).Range[string.Format("A{0}", currentRowCount + 1), Missing.Value]); currentRowCount += range.Rows.Count; } wb.Close(); this.finished++;//每处理完一个文件 完成数+1 } } } return(result); }).Run((result, ex) => { string path = string.Empty; string name = "合并后的excel" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx"; if (ex != null) { OnErrored(ex.Message); } else { path = ExcelHelper.SaveTo(name); } try { if (result != null) { if (!string.IsNullOrEmpty(path)) { result.SaveAs(path); } else { if (!Directory.Exists(cacheDirPath)) { Directory.CreateDirectory(cacheDirPath); } result.SaveAs(System.IO.Path.Combine(cacheDirPath, name)); } result.Close(); } excel.Quit(); if (!string.IsNullOrEmpty(path)) { ExcelHelper.OpenFileFolder(path); } } catch (Exception ex2) { OnErrored(ex2.Message); } finally { this.currentRowCount = 0; //清理上一次记录行号 this.finished = 0; this.isStarted = false; OnCompleted(); } }); }
public static void Senddata(string name, string unit, int count, int price, double discount, Excel.Application excelapp, Excel.Worksheet excelworksheet, int rubdiscount) { int k = 10; int l; //Поиск пустой строки for (k = 10; k < 200; k++) { Excel.Range cell = (Excel.Range)excelworksheet.Cells[k, 3]; if (cell.Value == null) { break; } } //Очистка на всякий пожарный, перед записью for (l = k; l < k + 6; l++) { for (int i = 2; i < 9; i++) { Excel.Range cell = (Excel.Range)excel.excelworksheet1.Cells[l, i]; cell.Value = null; } } Excel.Range cell2 = (Excel.Range)excelworksheet.Cells[k + 1, 2]; cell2.Value = ""; cell2 = (Excel.Range)excelworksheet.Cells[k + 1, 3]; cell2.Value = ""; cell2 = (Excel.Range)excelworksheet.Cells[k + 2, 2]; cell2.Value = ""; cell2 = (Excel.Range)excelworksheet.Cells[k + 2, 3]; cell2.Value = ""; // //Заполнение строки и динамическая отрисовка таблицы cell2 = (Excel.Range)excelworksheet.Cells[k, 3]; cell2.Value = name; //Ориентация cell2.HorizontalAlignment = Excel.Constants.xlLeft; cell2.VerticalAlignment = Excel.Constants.xlCenter; cell2.WrapText = true; //Обводка cell2.Borders.ColorIndex = 0; cell2.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; cell2.Borders.Weight = Excel.XlBorderWeight.xlThin; cell2 = (Excel.Range)excelworksheet.Cells[k, 4]; cell2.Value = unit; //Ориентация cell2.HorizontalAlignment = Excel.Constants.xlCenter; cell2.VerticalAlignment = Excel.Constants.xlCenter; cell2.WrapText = true; //Обводка cell2.Borders.ColorIndex = 0; cell2.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; cell2.Borders.Weight = Excel.XlBorderWeight.xlThin; cell2 = (Excel.Range)excelworksheet.Cells[k, 5]; cell2.Value = count; //Ориентация cell2.HorizontalAlignment = Excel.Constants.xlCenter; cell2.VerticalAlignment = Excel.Constants.xlCenter; cell2.WrapText = true; //Обводка cell2.Borders.ColorIndex = 0; cell2.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; cell2.Borders.Weight = Excel.XlBorderWeight.xlThin; cell2 = (Excel.Range)excelworksheet.Cells[k, 6]; cell2.Value = price; //Ориентация cell2.HorizontalAlignment = Excel.Constants.xlCenter; cell2.VerticalAlignment = Excel.Constants.xlCenter; cell2.WrapText = true; //Обводка cell2.Borders.ColorIndex = 0; cell2.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; cell2.Borders.Weight = Excel.XlBorderWeight.xlThin; cell2 = (Excel.Range)excelworksheet.Cells[k, 8]; cell2.FormulaLocal = "=G" + Convert.ToString(k) + "*" + Convert.ToString(discount / 100); cell2 = excelworksheet.get_Range("F" + k); cell2.Font.Bold = false; cell2 = (Excel.Range)excelworksheet.Cells[k, 7]; //cell2.Value = count * price; cell2.FormulaLocal = "=E" + Convert.ToString(k) + "*F" + Convert.ToString(k); //Ориентация cell2.HorizontalAlignment = Excel.Constants.xlCenter; cell2.VerticalAlignment = Excel.Constants.xlCenter; cell2.WrapText = true; //Обводка cell2.Borders.ColorIndex = 0; cell2.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; cell2.Borders.Weight = Excel.XlBorderWeight.xlThin; cell2 = excelworksheet.get_Range("G" + k); cell2.Font.Bold = false; // столбец № cell2 = excelworksheet.get_Range("B11:B" + k); cell2.Merge(); cell2.Borders.ColorIndex = 0; cell2.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; cell2.Borders.Weight = Excel.XlBorderWeight.xlThin; cell2 = excelworksheet.get_Range("B10", "G" + (k)); cell2.Font.Bold = false; // k = k + 1; cell2 = excelworksheet.get_Range("F" + k); cell2.Value = "Итого:"; cell2.Font.Bold = true; cell2 = excelworksheet.get_Range("G" + k); cell2.FormulaLocal = "=СУММ(G11:G" + (k - 1) + ")"; cell2.Font.Bold = true; cell2 = excelworksheet.get_Range("F" + (k + 1)); cell2.Value = "Сумма скидки:"; cell2.Font.Bold = true; cell2 = excelworksheet.get_Range("G" + (k + 1)); cell2.FormulaLocal = "=СУММ(H11:H" + (k - 1) + ")"; cell2.Font.Bold = true; if (rubdiscount != 0) { cell2 = excel.excelworksheet1.get_Range("G" + (k + 1)); cell2.Value = rubdiscount; } cell2 = excelworksheet.get_Range("F" + (k + 2)); cell2.Value = "Итого со скидкой:"; cell2.Font.Bold = true; cell2 = excelworksheet.get_Range("G" + (k + 2)); cell2.FormulaLocal = "=G" + k + "-G" + (k + 1); cell2.Font.Bold = true; //Итого прописью: cell2 = (Excel.Range)excel.excelworksheet1.Cells[k + 2, 2]; cell2.Value = "Итого: "; cell2 = (Excel.Range)excel.excelworksheet1.Cells[k + 2, 3]; Excel.Range cell3 = (Excel.Range)excel.excelworksheet1.Cells[k + 2, 7]; cell2.Value = NumByWords.RurPhrase(Convert.ToDecimal(cell3.Value)); /* Excel.Workbooks excelappworkbooks = excelapp.Workbooks; * Excel.Workbook excelappworkbook = excelappworkbooks["Bobmaster Calculator"]; * excelappworkbook.Saved = false;*/ //excelapp.Windows[1].Close(true, "E:\\Bobmaster\\Bobmaster Calculator"); }
/// <summary> /// Считывание разбивки на режимы (используется для формирования R-file) из xlsx файла формата 9.41-сц2 /// </summary> /// <param name="filename"></param> /// <returns></returns> public override List <KadrIntervals> KadrIntervalsReadFromExcel(string filename) { //считываем данные из Excel файла в двумерный массив Excel.Application xlApp = new Excel.Application(); //Excel Excel.Workbook xlWB; //рабочая книга Excel.Worksheet xlSht; //лист Excel xlWB = xlApp.Workbooks.Open(filename); //название файла Excel int NShts = xlWB.Worksheets.Count; List <KadrIntervals> KadrIntervalsList = new List <KadrIntervals>(); try { foreach (Excel.Worksheet sheet in xlWB.Worksheets) { int iLastRow = sheet.Cells[sheet.Rows.Count, "A"].End[Excel.XlDirection.xlUp].Row; var arrData = (object[, ])sheet.Range["A5:H" + iLastRow].Value; //берём данные с листа Excel KadrIntervals kadrIntervals = new KadrIntervals(); List <KadrInterval> intervals = new List <KadrInterval>(); //заполняем intervals данными из массива int i; for (i = 1; i <= arrData.GetUpperBound(0); i++) { double t = (double)arrData[i, 1] * 3_600_000 * 24; long tbeg = (long)t; double te = t + 3_600_000 * 24; if (i != arrData.GetUpperBound(0)) { te = (double)arrData[i + 1, 1] * 3_600_000 * 24; } long tend = (long)te; string[] kadrs = new string[arrData.GetUpperBound(1) - 1]; int j; for (j = 2; j <= arrData.GetUpperBound(1); j++) { kadrs[j - 2] = (string)arrData[i, j]; } KadrInterval I = new KadrInterval(kadrs, tbeg, tend); intervals.Add(I); } kadrIntervals.Intervals = intervals; kadrIntervals.Id = sheet.Cells[2, "H"].Value.ToString(); kadrIntervals.tags = new List <string>(); var arrDataTags = (object[, ])sheet.Range["A2:H2"].Value; for (i = 1; i <= 8; i++) { if (arrDataTags[1, i] != null) { kadrIntervals.tags.Add(arrDataTags[1, i].ToString()); } } kadrIntervals.filename = "NONE!"; KadrIntervalsList.Add(kadrIntervals); Console.WriteLine(sheet.Name); } } catch (Exception e) { MessageBox.Show("SpecialFor9_41_SCENARY4.KadrIntervalsReadFromExcel - Ошибка считывания файла " + filename + " : " + e.Message + " Stacktrace:" + e.StackTrace); } finally { xlWB.Close(false); //закрываем книгу, изменения не сохраняем xlApp.Quit(); //закрываем Excel } return(KadrIntervalsList); }
public void AttempImprimir() { Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog(); dlg.DefaultExt = ".xlsx"; dlg.Filter = "Documentos Excel (.xlsx)|*.xlsx"; if (dlg.ShowDialog() == true) { string filename = dlg.FileName; Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); excel.Visible = false; Workbook excelPrint = excel.Workbooks.Open(@"C:\Programs\ElaraInventario\Resources\SalidaPrestamo.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, Missing.Value, Missing.Value); Worksheet excelSheetPrint = (Worksheet)excelPrint.Worksheets[1]; //Folio excel.Cells[8, 6] = _movimientoModel.UnidMovimiento.ToString(); //Fecha excel.Cells[8, 23] = _movimientoModel.FechaMovimiento; //Solicitante y su área excel.Cells[13, 12] = _movimientoModel.Solicitante.SOLICITANTE_NAME; excel.Cells[15, 12] = _movimientoModel.Solicitante.Departamento.DEPARTAMENTO_NAME; try { //Recibe excel.Cells[21, 12] = _movimientoModel.Tecnico.TECNICO_NAME; //Procedencia excel.Cells[17, 12] = "Almacén: " + _movimientoModel.AlmacenProcedencia.ALMACEN_NAME; //Destino string p = ""; if (_movimientoModel.ProveedorDestino != null) { p = "Proveedor : " + _movimientoModel.ProveedorDestino.PROVEEDOR_NAME; } else if (_movimientoModel.AlmacenDestino != null) { p = "Almacén: " + _movimientoModel.AlmacenDestino.ALMACEN_NAME; } else { p = "Cliente: " + _movimientoModel.ClienteDestino.CLIENTE1; } excel.Cells[19, 12] = p; } catch (Exception Ex) { } //TT excel.Cells[33, 12] = _movimientoModel.Tt; //Empresa excel.Cells[11, 12] = _movimientoModel.Empresa.EMPRESA_NAME; //Transporte excel.Cells[27, 12] = _movimientoModel.Transporte.TRANSPORTE_NAME; //Contacto excel.Cells[31, 12] = _movimientoModel.Contacto; //Guia excel.Cells[29, 12] = _movimientoModel.Guia; //Nombre de Sitio excel.Cells[23, 12] = _movimientoModel.NombreSitio; //Sitio/Enlace excel.Cells[25, 12] = _movimientoModel.SitioEnlace; int X = 40; Microsoft.Office.Interop.Excel.Borders borders; for (int i = 0; i < ItemModel.ItemModel.Count; i++) { //for (int i = 0; i < 5; i++) { //No. excel.Range[excel.Cells[X, 2], excel.Cells[X, 3]].Merge(); excel.Range[excel.Cells[X, 2], excel.Cells[X, 3]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; excel.Cells[X, 2] = (i + 1).ToString() + ".-"; borders = excel.Range[excel.Cells[X, 2], excel.Cells[X, 3]].Borders; borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //DESCRIPCIÓN excel.Range[excel.Cells[X, 4], excel.Cells[X, 22]].Merge(); excel.Range[excel.Cells[X, 4], excel.Cells[X, 22]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; excel.Cells[X, 4] = ItemModel.ItemModel[i].Articulo.ARTICULO1; borders = excel.Range[excel.Cells[X, 4], excel.Cells[X, 22]].Borders; borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //N° DE SERIE excel.Range[excel.Cells[X, 23], excel.Cells[X, 26]].Merge(); excel.Range[excel.Cells[X, 23], excel.Cells[X, 26]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; excel.Cells[X, 23] = ItemModel.ItemModel[i].NUMERO_SERIE; borders = excel.Range[excel.Cells[X, 23], excel.Cells[X, 26]].Borders; borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //SKU excel.Range[excel.Cells[X, 27], excel.Cells[X, 30]].Merge(); excel.Range[excel.Cells[X, 27], excel.Cells[X, 30]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; excel.Cells[X, 27] = ItemModel.ItemModel[i].SKU; borders = excel.Range[excel.Cells[X, 27], excel.Cells[X, 30]].Borders; borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //CANTIDAD excel.Range[excel.Cells[X, 31], excel.Cells[X, 34]].Merge(); excel.Range[excel.Cells[X, 31], excel.Cells[X, 34]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; excel.Cells[X, 31] = ItemModel.ItemModel[i].CantidadMovimiento; borders = excel.Range[excel.Cells[X, 31], excel.Cells[X, 34]].Borders; borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; X++; } X += 2; excel.Cells[X, 3] = "OBSERVACIONES:"; excel.Range[excel.Cells[X, 9], excel.Cells[X + 2, 33]].Merge(); borders = excel.Range[excel.Cells[X, 9], excel.Cells[X + 2, 33]].Borders; borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; X += 4; excel.Range[excel.Cells[X, 2], excel.Cells[X, 17]].Merge(); excel.Range[excel.Cells[X, 2], excel.Cells[X, 17]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; excel.Cells[X, 2] = "ENTREGADO POR:"; excel.Cells[X, 2].Font.Bold = true; excel.Range[excel.Cells[X, 18], excel.Cells[X, 34]].Merge(); excel.Range[excel.Cells[X, 18], excel.Cells[X, 34]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; excel.Cells[X, 18] = "RECIBIDO POR:"; excel.Cells[X, 18].Font.Bold = true; X += 1; excel.Range[excel.Cells[X, 2], excel.Cells[X + 2, 17]].Merge(); borders = excel.Range[excel.Cells[X, 2], excel.Cells[X + 2, 17]].Borders; borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; excel.Range[excel.Cells[X, 18], excel.Cells[X + 2, 34]].Merge(); borders = excel.Range[excel.Cells[X, 18], excel.Cells[X + 2, 34]].Borders; borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; excelSheetPrint.SaveAs(filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); excel.Visible = true; } }
// Метод автоматического перебора файлов-доноров из указанной папки и записи значений в шаблон /* * public void ReadFile(int counter, String[] dirs, String template, ProgressBar progressBar1, Label label3, Button Cancel) * { * * //CloseProcess("start"); * * var app = new Excel.Application(); * app.Visible = false; * * var outbook = app.Workbooks.Open(template); * //Console.WriteLine(Directory.GetCurrentDirectory() + "/" + template); * * progressBar1.Visible = true; * * int t = 1; * int n = 1; * //int stoper = 0; * * foreach (string dir in dirs) * { * var inbook = app.Workbooks.Open(dir); * Application.DoEvents(); * //button4.Focus(); * Cancel.Enabled = true; * // Лист 1 * * // 3 - Номер платежного документа * CopyRange( * inbook.Sheets[1].Range["I1", "I1"], * outbook.Sheets[1].Range["C" + (t + 3).ToString(), "C" + (t + 3).ToString()]); * * Application.DoEvents(); * Cancel.Focus(); * * // 4 - Расчетный период * CopyRange( * inbook.Sheets[1].Range["D21", "D21"], * outbook.Sheets[1].Range["D" + (t + 3).ToString(), "D" + (t + 3).ToString()]); * * Application.DoEvents(); * Cancel.Focus(); * * // 5 - Общая площадь * CopyRange( * inbook.Sheets[1].Range["F26", "F26"], * outbook.Sheets[1].Range["E" + (t + 3).ToString(), "E" + (t + 3).ToString()]); * * Application.DoEvents(); * Cancel.Focus(); * * // 7 - Отапливаемая площадь * CopyRange( * inbook.Sheets[1].Range["I26", "I26"], * outbook.Sheets[1].Range["G" + (t + 3).ToString(), "G" + (t + 3).ToString()]); * * Application.DoEvents(); * Cancel.Focus(); * * // 8 - Количество проживающих (зарегестрированных) * CopyRange( * inbook.Sheets[1].Range["M26", "M26"], * outbook.Sheets[1].Range["H" + (t + 3).ToString(), "H" + (t + 3).ToString()]); * * Application.DoEvents(); * Cancel.Focus(); * * // 9 - Сумма к оплате за расчетный период * CopyRange( * inbook.Sheets[1].Range["N14", "N14"], * outbook.Sheets[1].Range["X" + (t + 3).ToString(), "X" + (t + 3).ToString()]); * * Application.DoEvents(); * Cancel.Focus(); * * // 16 - БИК банка * * CopyRangeReg( * inbook.Sheets[1].Range["J3", "J3"], * outbook.Sheets[1].Range["P" + (t + 3).ToString(), "P" + (t + 3).ToString()], * @"Кор\/сч: \d+ БИК: (\d+)"); * * Application.DoEvents(); * Cancel.Focus(); * //button4.Focus(); * * // 17 - Расчетный счет * * CopyRangeReg( * inbook.Sheets[1].Range["I2", "I2"], * outbook.Sheets[1].Range["Q" + (t + 3).ToString(), "Q" + (t + 3).ToString()], * @"ИНН\/КПП: \d+ \/ \d+ Р\/сч: (\d+)"); * * Application.DoEvents(); * * // Лист 2 * * if (t == 1) n = 1; * else n += 16; * * // 01 - Номер платежного документа * CopyRange( * inbook.Sheets[1].Range["I1", "I1"], * outbook.Sheets[2].Range["A" + (n + 4).ToString(), "A" + (n + 4).ToString()]); * * Application.DoEvents(); * Cancel.Focus(); * * // 02 - Заполняем поля затрат * * // Техобслуживание * outbook.Sheets[2].Range["B" + (n + 4).ToString(), "B" + (n + 4).ToString()] = "Техобслуживание"; * * CopyRange( * inbook.Sheets[1].Range["F42", "I42"], * outbook.Sheets[2].Range["G" + (n + 4).ToString(), "H" + (n + 4).ToString()]); * * CopyRange( * inbook.Sheets[1].Range["N42", "N42"], * outbook.Sheets[2].Range["AC" + (n + 4).ToString(), "AC" + (n + 4).ToString()]); * * Application.DoEvents(); * Cancel.Focus(); * * // Вывоз ТОП * outbook.Sheets[2].Range["B" + (n + 4 + 1).ToString(), "B" + (n + 4 + 1).ToString()] = "Вывоз ТОП"; * * CopyRange( * inbook.Sheets[1].Range["F43", "I43"], * outbook.Sheets[2].Range["G" + (n + 4 + 1).ToString(), "H" + (n + 4 + 1).ToString()]); * * CopyRange( * inbook.Sheets[1].Range["N43", "N43"], * outbook.Sheets[2].Range["AC" + (n + 4 + 1).ToString(), "AC" + (n + 4 + 1).ToString()]); * * Application.DoEvents(); * Cancel.Focus(); * * // ХВС * outbook.Sheets[2].Range["B" + (n + 4 + 2).ToString(), "B" + (n + 4 + 2).ToString()] = "ХВС"; * * CopyRange( * inbook.Sheets[1].Range["F44", "I44"], * outbook.Sheets[2].Range["G" + (n + 4 + 2).ToString(), "H" + (n + 4 + 2).ToString()]); * * CopyRange( * inbook.Sheets[1].Range["N44", "N44"], * outbook.Sheets[2].Range["AC" + (n + 4 + 2).ToString(), "AC" + (n + 4 + 2).ToString()]); * * Application.DoEvents(); * Cancel.Focus(); * * // ГВС * outbook.Sheets[2].Range["B" + (n + 4 + 3).ToString(), "B" + (n + 4 + 3).ToString()] = "ГВС"; * * CopyRange( * inbook.Sheets[1].Range["F45", "I45"], * outbook.Sheets[2].Range["G" + (n + 4 + 3).ToString(), "H" + (n + 4 + 3).ToString()]); * * CopyRange( * inbook.Sheets[1].Range["N45", "N45"], * outbook.Sheets[2].Range["AC" + (n + 4 + 3).ToString(), "AC" + (n + 4 + 3).ToString()]); * * Application.DoEvents(); * Cancel.Focus(); * * // Водоотведение * outbook.Sheets[2].Range["B" + (n + 4 + 4).ToString(), "B" + (n + 4 + 4).ToString()] = "Водоотведение"; * * CopyRange( * inbook.Sheets[1].Range["F46", "I46"], * outbook.Sheets[2].Range["G" + (n + 4 + 4).ToString(), "H" + (n + 4 + 4).ToString()]); * * CopyRange( * inbook.Sheets[1].Range["N46", "N46"], * outbook.Sheets[2].Range["AC" + (n + 4 + 4).ToString(), "AC" + (n + 4 + 4).ToString()]); * * Application.DoEvents(); * Cancel.Focus(); * * // Электроснабжение * outbook.Sheets[2].Range["B" + (n + 4 + 5).ToString(), "B" + (n + 4 + 5).ToString()] = "Электроснабжение"; * * CopyRange( * inbook.Sheets[1].Range["F47", "I47"], * outbook.Sheets[2].Range["G" + (n + 4 + 5).ToString(), "H" + (n + 4 + 5).ToString()]); * * CopyRange( * inbook.Sheets[1].Range["N47", "N47"], * outbook.Sheets[2].Range["AC" + (n + 4 + 5).ToString(), "AC" + (n + 4 + 5).ToString()]); * * Application.DoEvents(); * Cancel.Focus(); * * // Отопление * outbook.Sheets[2].Range["B" + (n + 4 + 6).ToString(), "B" + (n + 4 + 6).ToString()] = "Отопление"; * * CopyRange( * inbook.Sheets[1].Range["F49", "I49"], * outbook.Sheets[2].Range["G" + (n + 4 + 6).ToString(), "H" + (n + 4 + 6).ToString()]); * * CopyRange( * inbook.Sheets[1].Range["N49", "N49"], * outbook.Sheets[2].Range["AC" + (n + 4 + 6).ToString(), "AC" + (n + 4 + 6).ToString()]); * * Application.DoEvents(); * Cancel.Focus(); * * // ХВС * outbook.Sheets[2].Range["B" + (n + 4 + 7).ToString(), "B" + (n + 4 + 7).ToString()] = "ХВС"; * * CopyRange( * inbook.Sheets[1].Range["F50", "I50"], * outbook.Sheets[2].Range["G" + (n + 4 + 7).ToString(), "H" + (n + 4 + 7).ToString()]); * * CopyRange( * inbook.Sheets[1].Range["N50", "N50"], * outbook.Sheets[2].Range["AC" + (n + 4 + 7).ToString(), "AC" + (n + 4 + 7).ToString()]); * * Application.DoEvents(); * Cancel.Focus(); * * // Стоки ХВС * outbook.Sheets[2].Range["B" + (n + 4 + 8).ToString(), "B" + (n + 4 + 8).ToString()] = "Стоки ХВС"; * * CopyRange( * inbook.Sheets[1].Range["F51", "I51"], * outbook.Sheets[2].Range["G" + (n + 4 + 8).ToString(), "H" + (n + 4 + 8).ToString()]); * * CopyRange( * inbook.Sheets[1].Range["N51", "N51"], * outbook.Sheets[2].Range["AC" + (n + 4 + 8).ToString(), "AC" + (n + 4 + 8).ToString()]); * * Application.DoEvents(); * Cancel.Focus(); * * // ГВС * outbook.Sheets[2].Range["B" + (n + 4 + 9).ToString(), "B" + (n + 4 + 9).ToString()] = "ГВС"; * * CopyRange( * inbook.Sheets[1].Range["F52", "I52"], * outbook.Sheets[2].Range["G" + (n + 4 + 9).ToString(), "H" + (n + 4 + 9).ToString()]); * * CopyRange( * inbook.Sheets[1].Range["N52", "N52"], * outbook.Sheets[2].Range["AC" + (n + 4 + 9).ToString(), "AC" + (n + 4 + 9).ToString()]); * * Application.DoEvents(); * Cancel.Focus(); * * // Стоки ГВС * outbook.Sheets[2].Range["B" + (n + 4 + 10).ToString(), "B" + (n + 4 + 10).ToString()] = "Стоки ГВС"; * * CopyRange( * inbook.Sheets[1].Range["F53", "I53"], * outbook.Sheets[2].Range["G" + (n + 4 + 10).ToString(), "H" + (n + 4 + 10).ToString()]); * * CopyRange( * inbook.Sheets[1].Range["N53", "N53"], * outbook.Sheets[2].Range["AC" + (n + 4 + 10).ToString(), "AC" + (n + 4 + 10).ToString()]); * * Application.DoEvents(); * Cancel.Focus(); * * // Электроснабжение * outbook.Sheets[2].Range["B" + (n + 4 + 11).ToString(), "B" + (n + 4 + 11).ToString()] = "Электроснабжение"; * * CopyRange( * inbook.Sheets[1].Range["F54", "I54"], * outbook.Sheets[2].Range["G" + (n + 4 + 11).ToString(), "H" + (n + 4 + 11).ToString()]); * * CopyRange( * inbook.Sheets[1].Range["N54", "N54"], * outbook.Sheets[2].Range["AC" + (n + 4 + 11).ToString(), "AC" + (n + 4 + 11).ToString()]); * * Application.DoEvents(); * Cancel.Focus(); * * // Электроснабжение * outbook.Sheets[2].Range["B" + (n + 4 + 12).ToString(), "B" + (n + 4 + 12).ToString()] = "Электроснабжение"; * * CopyRange( * inbook.Sheets[1].Range["F55", "I55"], * outbook.Sheets[2].Range["G" + (n + 4 + 12).ToString(), "H" + (n + 4 + 12).ToString()]); * * CopyRange( * inbook.Sheets[1].Range["N55", "N55"], * outbook.Sheets[2].Range["AC" + (n + 4 + 12).ToString(), "AC" + (n + 4 + 12).ToString()]); * * Application.DoEvents(); * Cancel.Focus(); * * // Текущ. ремонт * outbook.Sheets[2].Range["B" + (n + 4 + 13).ToString(), "B" + (n + 4 + 13).ToString()] = "Текущ. ремонт"; * * CopyRange( * inbook.Sheets[1].Range["F56", "I56"], * outbook.Sheets[2].Range["G" + (n + 4 + 13).ToString(), "H" + (n + 4 + 13).ToString()]); * * CopyRange( * inbook.Sheets[1].Range["N56", "N56"], * outbook.Sheets[2].Range["AC" + (n + 4 + 13).ToString(), "AC" + (n + 4 + 13).ToString()]); * * Application.DoEvents(); * Cancel.Focus(); * * // Антенна * outbook.Sheets[2].Range["B" + (n + 4 + 14).ToString(), "B" + (n + 4 + 14).ToString()] = "Антенна"; * * CopyRange( * inbook.Sheets[1].Range["F57", "I57"], * outbook.Sheets[2].Range["G" + (n + 4 + 14).ToString(), "H" + (n + 4 + 14).ToString()]); * * CopyRange( * inbook.Sheets[1].Range["N57", "N57"], * outbook.Sheets[2].Range["AC" + (n + 4 + 14).ToString(), "AC" + (n + 4 + 14).ToString()]); * * Application.DoEvents(); * Cancel.Focus(); * * // Услуги банка * outbook.Sheets[2].Range["B" + (n + 4 + 15).ToString(), "B" + (n + 4 + 15).ToString()] = "Услуги банка"; * * CopyRange( * inbook.Sheets[1].Range["F58", "I58"], * outbook.Sheets[2].Range["G" + (n + 4 + 15).ToString(), "H" + (n + 4 + 15).ToString()]); * * CopyRange( * inbook.Sheets[1].Range["N58", "N58"], * outbook.Sheets[2].Range["AC" + (n + 4 + 15).ToString(), "AC" + (n + 4 + 15).ToString()]); * * Application.DoEvents(); * Cancel.Focus(); * * Console.WriteLine("Обработано файлов {0} из {1}.", t, dirs.Length); * * inbook.Close(false); * progressBar1.Value += 1; * //MessageBox.Show("Обработан файл " + t); * label3.Text = "Обработано " + t + " из " + dirs.Length; * t += 1; * * if (stop_cycle == "stop") * { * label3.Text = "Обработка остановлена."; * stop_cycle = "no"; * progressBar1.Value = 0; * break; * } * } * * outbook.Close(true); * app.Visible = false; * * app.Quit(); * * Console.WriteLine(); * Console.WriteLine("Шаблон \"{0}\" обновлён.", template); * Console.WriteLine("Нажмите клавишу Enter для выхода..."); * Console.Read(); * } */ // Метод автоматического перебора файлов-доноров из указанной папки и записи значений в шаблон public void ReadFile1(int counter, String[] dirs, String template, ProgressBar progressBar1, Label label3, Button Cancel) { Regex regex = new Regex(@"[A-Z]{1,2}[1-9]{0,2}"); //CloseProcess("start"); var app = new Excel.Application(); app.Visible = false; var outbook = app.Workbooks.Open(template); progressBar1.Visible = true; int t = 1; int n = 1; //int stoper = 0; foreach (var dir in dirs) { var inbook = app.Workbooks.Open(dir); Application.DoEvents();; Cancel.Enabled = true; button3.Enabled = false; String account = dataGridView1.Rows[0].Cells[2].Value.ToString(); if (t == 1) { n = 1; } else { n += 16; } for (int i = 0; i < dataGridView1.RowCount - 1; i++) { String name = dataGridView1.Rows[i].Cells[0].Value.ToString(); int list_num = Convert.ToInt32(dataGridView1.Rows[i].Cells[1].Value.ToString()); String kvitancia_link = dataGridView1.Rows[i].Cells[2].Value.ToString(); String template_link = dataGridView1.Rows[i].Cells[3].Value.ToString(); int count_shift = Convert.ToInt32(dataGridView1.Rows[i].Cells[4].Value.ToString()); if (i < 8) // Заполняем первый лист { /* * CopyRange( * inbook.Sheets[1].Range[kvitancia_link, kvitancia_link], * outbook.Sheets[(int)list_num].Range[template_link + (t + 3).ToString(), template_link + (t + 3).ToString()]); */ if (regex.IsMatch(kvitancia_link)) { CopyRange( inbook.Sheets[1].Range[kvitancia_link, kvitancia_link], outbook.Sheets[(int)list_num].Range[template_link + (t + 3).ToString(), template_link + (t + 3).ToString()]); } else { outbook.Sheets[(int)list_num].Range[template_link + (t + 3).ToString(), template_link + (t + 3).ToString()] = kvitancia_link; } Application.DoEvents(); Cancel.Focus(); } else // Заполняем второй лист { if (regex.IsMatch(account)) { CopyRange( inbook.Sheets[1].Range[account, account], outbook.Sheets[(int)list_num].Range["A" + (n + 4 + count_shift).ToString(), "A" + (n + 4 + count_shift).ToString()]); } else { outbook.Sheets[(int)list_num].Range["A" + (n + 4 + count_shift).ToString(), "A" + (n + 4 + count_shift).ToString()] = account; } /* * CopyRange( * inbook.Sheets[1].Range[kvitancia_link, kvitancia_link], * outbook.Sheets[(int)list_num].Range[template_link + (n + 4 + count_shift).ToString(), template_link + (n + 4 + count_shift).ToString()]); */ if (regex.IsMatch(kvitancia_link)) { CopyRange( inbook.Sheets[1].Range[kvitancia_link, kvitancia_link], outbook.Sheets[(int)list_num].Range[template_link + (n + 4 + count_shift).ToString(), template_link + (n + 4 + count_shift).ToString()]); } else { outbook.Sheets[(int)list_num].Range[template_link + (n + 4 + count_shift).ToString(), template_link + (n + 4 + count_shift).ToString()] = kvitancia_link; } Application.DoEvents(); Cancel.Focus(); } } Console.WriteLine("Обработано файлов {0} из {1}.", t, dirs.Length); inbook.Close(false); progressBar1.Value += 1; //MessageBox.Show("Обработан файл " + t); label3.Text = "Обработано " + t + " из " + dirs.Length; t += 1; if (stop_cycle == "stop") { label3.Text = "Обработка остановлена."; stop_cycle = "no"; progressBar1.Value = 0; button3.Enabled = true; break; } } outbook.Close(true); app.Visible = false; app.Quit(); Console.WriteLine(); Console.WriteLine("Шаблон \"{0}\" обновлён.", template); Console.WriteLine("Нажмите клавишу Enter для выхода..."); Console.Read(); if (label3.Text != "Обработка остановлена.") { label3.Text = "Обработка завершена."; } }
/// <summary> /// Считывание разбивки на режимы (используется для формирования R-file) из xlsx файла формата 9.41-сц2 /// </summary> /// <param name="filename"></param> /// <returns></returns> public override List <SeparatorIntervals> SeparatorIntervalsReadFromExcel(string filename) { //считываем данные из Excel файла в двумерный массив Excel.Application xlApp = new Excel.Application(); //Excel Excel.Workbook xlWB; //рабочая книга Excel.Worksheet xlSht; //лист Excel xlWB = xlApp.Workbooks.Open(filename); //название файла Excel int NShts = xlWB.Worksheets.Count; List <SeparatorIntervals> SeparatorIntervalsList = new List <SeparatorIntervals>(); // xlSht = xlWB.Worksheets[1]; //название листа или 1-й лист в книге xlSht = xlWB.Worksheets[1]; try { foreach (Excel.Worksheet sheet in xlWB.Worksheets) { SeparatorIntervals separatorIntervals = new SeparatorIntervals(); List <Interval> intervals = new List <Interval>(); int i; int iLastRow = sheet.Cells[sheet.Rows.Count, "A"].End[Excel.XlDirection.xlUp].Row; var arrData = (object[, ])sheet.Range["A5:B" + iLastRow].Value; //берём данные с листа Excel for (i = 1; i < arrData.GetUpperBound(0); i++) { double t = (double)arrData[i, 1] * 3_600_000 * 24; long tbeg = (long)t; double te = (double)arrData[i + 1, 1] * 3_600_000 * 24; long tend = (long)te; Interval I = new Interval( ((string)arrData[i, 2]).Trim(), tbeg, tend); intervals.Add(I); } separatorIntervals.Intervals = intervals; separatorIntervals.Id = sheet.Cells[2, "H"].Value.ToString(); separatorIntervals.tags = new List <string>(); var arrDataTags = (object[, ])sheet.Range["A2:H2"].Value; for (i = 1; i <= 8; i++) { if (arrDataTags[1, i] != null) { separatorIntervals.tags.Add(arrDataTags[1, i].ToString()); } } separatorIntervals.filename = "NONE!"; SeparatorIntervalsList.Add(separatorIntervals); } } catch (Exception e) { MessageBox.Show("SpecialFor9_41_SCENARY4.SeparatorIntervalsReadFromExcel. Ошибка считывания файла " + filename + ": " + e.Message + " Stacktrace:" + e.StackTrace); } finally { xlWB.Close(false); //закрываем книгу, изменения не сохраняем xlApp.Quit(); //закрываем Excel } return(SeparatorIntervalsList); }
private void button1_Click(object sender, System.EventArgs e) { button1.Enabled = false; textBox_message.Text = "报表生成中......."; dateTimePicker_report.Focus(); //获取输入的时间 DateTime dt = dateTimePicker_report.Value; //用来限定访问数据的范围 string firstDay = dt.Year + "-" + dt.Month + "-" + 1; string lastDay; if (dt.Month.Equals(12)) { lastDay = (dt.Year + 1) + "-" + 1 + "-" + 1; } else { lastDay = dt.Year + "-" + (dt.Month + 1) + "-" + 1; } try { // 访问Aras string url = Properties.Settings.Default.url; string username = Properties.Settings.Default.username; string pwd = Properties.Settings.Default.pwd; string database = Properties.Settings.Default.database; Item loginItem = IomFactory.CreateHttpServerConnection(url, database, username, pwd).Login(); if (loginItem.isError()) { MessageBox.Show("访问数据库失败,请重试"); button1.Enabled = true; textBox_message.Text = String.Empty; } else { Innovator inn = loginItem.getInnovator(); //EB25E105EFDF478980005105489F6E74表示项目List的cofigId; Item projectSql = inn.applySQL("SELECT value FROM[IPD].[innovator].VALUE where SOURCE_ID = 'EB25E105EFDF478980005105489F6E74' "); //D1593C4073EE49218E67A6B9F28CA943表示用户list的configId Item nameSql = inn.applySQL("SELECT value,label FROM [IPD].[innovator].VALUE a where SOURCE_ID = 'D1593C4073EE49218E67A6B9F28CA943' "); Dictionary <string, string> nameMap = new Dictionary <string, string>(); for (int i = 0; i < nameSql.getItemCount(); i++) { Item item = nameSql.getItemByIndex(i); string nameE = item.getProperty("value"); string nameC = item.getProperty("label"); if (nameE != null) { nameMap[nameE] = nameC; } } Item allDateSql = inn.applySQL("SELECT a.[SOURCE_ID] " + ", a.[PROJECT_NAME] " + ", a.[PROJECT_TIME] " + ", a.[PROJECT_COMMENT] " + ", b.MY_NAME " + " , b.WORKING_DATE " + "FROM[IPD].[innovator].[LAUREL_PROJECT_AND_TIME] a " + "Left Join IPD.innovator.MANHOUR_REGISTER b on b.CONFIG_ID = a.SOURCE_ID " + "and b.WORKING_DATE > ' " + firstDay + " ' and b.WORKING_DATE <'" + lastDay + "' "); if (allDateSql.getItemCount() == -1 || allDateSql.getItemCount() == 0) { MessageBox.Show("未查询到数据,请重试"); button1.Enabled = true; textBox_message.Text = String.Empty; return; } //把所有的数据制作成Map Dictionary <string, List <Item> > dictionary = new Dictionary <string, List <Item> >(); for (int i = 0; i < allDateSql.getItemCount(); i++) { Item item = allDateSql.getItemByIndex(i); string projectName = item.getProperty("project_name"); string myName = item.getProperty("my_name"); if (!string.IsNullOrWhiteSpace(projectName) && !string.IsNullOrWhiteSpace(myName)) { if (dictionary.ContainsKey(projectName)) { List <Item> list = dictionary[projectName]; list.Add(item); } else { List <Item> list = new List <Item>(); list.Add(item); dictionary[projectName] = list; } } } //新建Excel和工作簿 Microsoft.Office.Interop.Excel.Application oXL; Microsoft.Office.Interop.Excel._Workbook oWB; //启动Excel并获取应用程序对象 oXL = new Microsoft.Office.Interop.Excel.Application(); oXL.Visible = false; oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(Missing.Value)); //遍历所有的名字 for (int i = 0; i < projectSql.getItemCount(); i++) { Item item = projectSql.getItemByIndex(i); string name = item.getProperty("value"); //map制作完成, //防止list中的项目在Map中不存在 if (dictionary.ContainsKey(name)) { List <Item> sqlResult = dictionary[name]; if (sqlResult.Count() <= 0) { MessageBox.Show(name + "无查询结果"); button1.Enabled = true; textBox_message.Text = String.Empty; } else { makeSheet(sqlResult, dt, oXL, oWB, name, nameMap); } } } oXL.Visible = true; oXL.UserControl = true; this.Close(); } } catch (Exception theException) { String errorMessage; errorMessage = "Error: "; errorMessage = String.Concat(errorMessage, theException.Message); errorMessage = String.Concat(errorMessage, " Line: "); errorMessage = String.Concat(errorMessage, theException.InnerException); errorMessage = String.Concat(errorMessage, theException.Source); errorMessage = String.Concat(errorMessage, theException.ToString()); errorMessage = String.Concat(errorMessage, theException.TargetSite); MessageBox.Show(errorMessage, "Error"); } }
private void Design_IRC_Abutment_Bridges_Girder_Type() { string file_path = Get_Project_Folder(); //file_path = Path.Combine(file_path, "BoQ_Bridges_Box_Type.xlsx"); file_path = Path.Combine(file_path, "IRC Abutment Design Girder Type.xlsx"); string copy_path = file_path; file_path = Path.Combine(Application.StartupPath, @"DESIGN\Abutment\Abutment Design IRC\IRC ABUTMENT Design_Girder_Type.xlsx"); if (File.Exists(file_path)) { File.Copy(file_path, copy_path, true); } Excel.Application myExcelApp; Excel.Workbooks myExcelWorkbooks; Excel.Workbook myExcelWorkbook; object misValue = System.Reflection.Missing.Value; myExcelApp = new Excel.Application(); myExcelApp.Visible = true; //myExcelApp.Visible = false; myExcelWorkbooks = myExcelApp.Workbooks; //myExcelWorkbook = myExcelWorkbooks.Open(fileName, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue); myExcelWorkbook = myExcelWorkbooks.Open(copy_path, 0, false, 5, "2011ap", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); //Excel.Worksheet myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.ActiveSheet; Excel.Worksheet myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["DLSUP"]; //Excel.Range formatRange; //formatRange = myExcelWorksheet.get_Range("b" + (dgv_box_gen_data.RowCount + 8), "L" + (dgv_box_gen_data.RowCount + 8)); //formatRange.Interior.Color = System.Drawing. //ColorTranslator.ToOle(System.Drawing.Color.LightGreen); List <string> list = new List <string>(); #region Section Input myExcelWorksheet.get_Range("B4").Formula = txt_Ana_L.Text; myExcelWorksheet.get_Range("B5").Formula = txt_Ana_og.Text; myExcelWorksheet.get_Range("B6").Formula = txt_total_weight.Text; myExcelWorksheet.get_Range("B7").Formula = txt_total_sidl.Text; #endregion Section Input DataGridView dgv = dgv_sidl; int rindx = 0; #region SIDL Input //for (int i = 0; i < dgv.RowCount; i++) //{ // myExcelWorksheet.get_Range("E" + (i + 117)).Formula = dgv[2, i].Value.ToString(); // myExcelWorksheet.get_Range("F" + (i + 117)).Formula = dgv[3, i].Value.ToString(); // myExcelWorksheet.get_Range("G" + (i + 117)).Formula = dgv[4, i].Value.ToString(); //} #endregion SIDL Input #region Earth pr myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["Earth pr"]; dgv = dgv_earth_pressure; for (int i = 0; i < dgv.RowCount; i++) { myExcelWorksheet.get_Range("F" + (i + 7)).Formula = dgv[1, i].Value.ToString(); } #endregion Earth pr #region Base Pressure myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["Base Pressure"]; rindx = 0; dgv = dgv_base_pressure; List <string> ldbl = new List <string>(); for (int i = 0; i < dgv.RowCount; i++) { try { if (dgv[1, i].Value.ToString() != "") { ldbl.Add(dgv[1, i].Value.ToString()); } } catch (Exception exx) { } } rindx = 0; for (int i = 7; i < 55; i++) { if (i == 18 || i == 19 || i == 25 || (i >= 28 && i <= 30) || i == 35 || i == 44 || i == 47) { continue; } else { myExcelWorksheet.get_Range("F" + i).Formula = ldbl[rindx++].ToString(); } } #endregion Earth pr rindx = 0; myExcelWorkbook.Save(); releaseObject(myExcelWorkbook); iapp.Excel_Open_Message(); }
private void Form1_Load(object sender, EventArgs e) { app = new Excel.Application(); app.Visible = false; workbook = app.Workbooks.Open(Application.StartupPath + "\\Дни рождения сотрудников.xls"); worksheet = (Excel.Worksheet) workbook.ActiveSheet; int i = 0; names = worksheet.get_Range("A1", "A" + Convert.ToString(worksheet.UsedRange.Rows.Count)); departaments1 = worksheet.get_Range("B1", "A" + Convert.ToString(worksheet.UsedRange.Rows.Count)); departaments2 = worksheet.get_Range("C1", "A" + Convert.ToString(worksheet.UsedRange.Rows.Count)); birthdays = worksheet.get_Range("D1", "A" + Convert.ToString(worksheet.UsedRange.Rows.Count)); string current_date = DateTime.Now.ToString("dd.MM"); for (i = 1; i <= worksheet.UsedRange.Rows.Count; i++) { string departament1_value = Convert.ToString(departaments1.Cells[i]); string departament2_value = Convert.ToString(departaments1.Cells[i]); string birthday_value = Convert.ToString(departaments1.Cells[i]); string name_value = Convert.ToString(departaments1.Cells[i]); if (departament1_value == "Іб та ПД" || departament2_value == "Іб та ПД") { if (birthday_value == current_date) label1.Text = "Сьогодні свій день народження відзначає " + name_value; } } app.Quit(); }