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 void CreateWorkbook() { var templateFile = Path.Combine(System.Windows.Forms.Application.StartupPath, @"Templates\TemplateWorkBook.xlsx"); MainForm.LogWriter.WriteDebugMessage("Creating new workbook."); //mWorkbook = mApplication.Workbooks.Add(Missing.Value); mWorkbook = mApplication.Workbooks.Add(templateFile); mMeasurementDataWorksheet = (Excel.Worksheet)mWorkbook.Sheets.Item[1]; mMiscellaneousDataWorksheet = (Excel.Worksheet)mWorkbook.Sheets.Item[2]; //var sheet3 = (Excel.Worksheet)mWorkbook.Sheets.Item[3]; //Rename first and second worksheet, delete third worksheet //mMeasurementDataWorksheet.Name = "Measurement Data"; //mMiscellaneousDataWorksheet.Name = "Miscellaneous Data"; //sheet3.Delete(); //Set measurement data worksheet as active worksheet mMeasurementDataWorksheet.Activate(); //Display windows in splitview mWorkbook.Windows.Arrange(Excel.XlArrangeStyle.xlArrangeStyleHorizontal); MainForm.HeadersWritten = false; MainForm.LogWriter.WriteDebugMessage("Created new workbook."); }
public void readExcel() { string valueString = string.Empty; objExcelApp = new Microsoft.Office.Interop.Excel.Application(); objBooks = (Excel.Workbooks)objExcelApp.Workbooks; //Open the workbook containing the address data. objBook = objBooks.Open(@"C:\Temp\data\Test.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); //Get a reference to the first sheet of the workbook. objSheets = objBook.Worksheets; objSheet = (Excel._Worksheet)objSheets.get_Item(1); //Select the range of data containing the addresses and get the outer boundaries. rngLast = objSheet.get_Range("A1").SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell); long lLastRow = rngLast.Row; long lLastCol = rngLast.Column; // Iterate through the data and concatenate the values into a comma-delimited string. for (long rowCounter = 1; rowCounter <= lLastRow; rowCounter++) { for (long colCounter = 1; colCounter <= lLastCol; colCounter++) { //Write the next value into the string. Excel.Range cell = (Excel.Range)objSheet.Cells[rowCounter, colCounter]; string cellvalue = cell.Value.ToString(); //TODO: add your business logic for retrieve cell value } } }
/** * if previous logs exist we open it and update it **/ public bool OpenWorkBook() { if (!File.Exists(m_logName)) { return false; } try { m_WorkBook = (Excel._Workbook)(m_XLApp.Workbooks.Open(m_logName)); m_Sheet = (Excel._Worksheet)m_WorkBook.ActiveSheet; } catch (Exception e) { throw new LoggerException("Cannot open log file: " + e.Message); } if (m_Sheet == null) { return false; } m_columnsMap = new Dictionary<string, int>(); string projectName; int lastRow = m_Sheet.UsedRange.Rows.Count; for (int column = 2; column < m_Sheet.UsedRange.Columns.Count + 1; column++) { projectName = GetStringFromCell(1, column); m_columnsMap.Add(projectName, column); } return true; }
public Export() { // Instantiate Excel and start a new workbook. objApp = new Excel.Application(); objBooks = objApp.Workbooks; objBook = objBooks.Add(Missing.Value); objSheets = objBook.Worksheets; objSheet = (Excel._Worksheet)objSheets.get_Item(1); }
public ExcelManipulation() { _excelApp = new Excel.Application(); _flagexcelapp = 1; _excelApp.Visible = true; //Создаём новую книгу _workBook = (Excel._Workbook)(_excelApp.Workbooks.Add(Missing.Value)); _workSheet = (Excel._Worksheet)_workBook.ActiveSheet; }
public void generatExcelNoSubmit(string f) { string file_Name = f; m_oExcelApp = new Excel.Application(); m_oExcelApp.Visible = false; m_oExcelApp.UserControl = false; m_oSheet = null; excelRange = null; try { m_oBook = m_oExcelApp.Workbooks.Add(missing); Excel.Sheets sheets = m_oBook.Worksheets; //Add new 4 Sheet //sheets.Add(System.Type.Missing, m_oBook.Sheets[3], 1, Excel.XlSheetType.xlWorksheet); //Product Sheet [Sheet1] m_oSheet = (Excel._Worksheet)m_oBook.Sheets[1]; m_oSheet.Activate(); m_oSheet.Name = "รายการเสนอซื้อ"; SetData_to_SheetNoSubmit(); string template = Application.StartupPath; string strRunReport = file_Name; //string strPass = "******"; password m_oBook.SaveAs(strRunReport, Excel.XlFileFormat.xlWorkbookNormal, null, null, null, null, Excel.XlSaveAsAccessMode.xlShared, null, null, null, null, null); m_oExcelApp.Visible = true; } catch (interop.COMException ex) { MessageBox.Show("Error accessing Excel: " + ex.ToString()); } catch (Exception ex) { MessageBox.Show("Error: " + ex.ToString()); } finally { if (m_oExcelApp == null) { m_oExcelApp.Quit(); m_oExcelApp = null; GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); } } }
private void constructExcelObject(String sheetPath) { excelApp = new Excel.Application(); excelWorkbook = excelApp.Workbooks.Open(@sheetPath); excelWorksheet = excelWorkbook.Sheets[1]; Excel.Range excelRange = excelWorksheet.UsedRange; addressMap = new Dictionary<int, Utilities.ExcelRowBinder>(); fillAddressList(excelWorkbook, excelWorksheet, excelRange); }
public Logger(Table table) { _table = table; excel = new Excel.Application(); excel.Workbooks.Add(); _worksheet = (Excel.Worksheet)excel.ActiveSheet; _iteration = 1; }
public ReportBuilder() { app = new Excel.Application(); appBooks = app.Workbooks; currentBook = appBooks.Add(Missing.Value); sheets = currentBook.Worksheets; currentSheet = (Excel._Worksheet)sheets.get_Item(1); range = currentSheet.get_Range("A1", Missing.Value); charts = currentSheet.ChartObjects(Type.Missing); chartObject = charts.Add(400, LastIndex, chartWidth, chartHeight); }
public ExcelWorksheet() { excelApp.Visible = true; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); excelWorkBook = excelApp.ActiveWorkbook; if (excelWorkBook == null) excelWorkBook = (Excel._Workbook) excelApp.Workbooks.Add(Missing.Value); excelSheet = (Excel._Worksheet) excelWorkBook.ActiveSheet; excelSheet.Name = "SpaceClaim"; }
/// <summary> /// Метод создающий новый файл /// </summary> /// <param name="filename">Имя и путь файла</param> /// <param name="background">Создать в бэкграунде</param> /// <param name="overwrite">Флаг перезаписи файле, если таковой уже существует</param> public static void CreateNewFile(string filename, bool background, bool overwrite) { if (!background) excelApp.Visible = true; else excelApp.Visible = false; excelApp.Workbooks.Add(); workSheet = excelApp.ActiveSheet; if (File.Exists(filename)) if (overwrite) File.Delete(filename); workSheet.SaveAs(filename); }
public override string Read() { try { excelApp = new Excel.Application(); excelApp.Visible = false; object missingValue = System.Reflection.Missing.Value; object readOnly = true; string filePath = (string)FilePath; workBook = excelApp.Workbooks.Open(filePath, missingValue, readOnly, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue); objectSheet = (Excel.Worksheet)excelApp.ActiveSheet; // Get full range which used. Excel.Range usedRange = objectSheet.UsedRange; string result = string.Empty; for (int i = 1; i <= usedRange.Rows.Count; i++) { for (int j = 1; j <= usedRange.Columns.Count; j++) { result += usedRange.Cells[i, j].Text; } } excelApp.Quit(); return result; } catch (Exception e) { throw e; } }
public static void superswitch(int colSour, int colID, string[] cellsCol, string openingVar, string closingVar, bool Numericalcheck, bool VariableCheck) { for (int owbb = 0; owbb < WorkBooks.Count; owbb++) { oWB = (Excel._Workbook)WorkBooks[owbb]; for (int owss = 1; owss <= oWB.Worksheets.Count; owss++) { oWS = (Excel._Worksheet)oWB.Worksheets.get_Item(owss); foreach (Excel.Range fila in oWS.UsedRange.Rows) { actualizarSID((string)(oWS.Cells[fila.Row, colSour] as Excel.Range).Value, (string)(oWS.Cells[fila.Row, colID] as Excel.Range).Value, openingVar, closingVar); if (cellValueS != null) { variablesInString = getVariablesinString(cellValueS); if (Numericalcheck == true) { numerosenSource = getNumbers(cellValueS, variablesInString); } switch (cellsCol.Length) { case 1: if (cellsCol[0] != "") { if (VariableCheck) { issuefounded = VarCompare((string)(oWS.Cells[fila.Row, int.Parse(cellsCol[0])] as Excel.Range).Value, owbb); } if ((issuefounded == false) && (Numericalcheck == true)) { NumCompare((string)(oWS.Cells[fila.Row, int.Parse(cellsCol[0])] as Excel.Range).Value, owbb); } } else { System.Windows.Forms.MessageBox.Show("Please make sure that you fill \nin all the mandatory fields properly."); } break; case 2: int[] columnass = { -1, -1 }; if ((cellsCol[0] != null) && (cellsCol[0] != "")) { columnass[0] = int.Parse(cellsCol[0]); } if ((cellsCol[1] != null) && (cellsCol[1] != "")) { columnass[1] = int.Parse(cellsCol[1]); } if ((columnass[0] != -1) && (columnass[1] != -1)) { if (columnass[0] > columnass[1]) { int exc = columnass[0]; columnass[0] = columnass[1]; columnass[1] = exc; } for (int b = columnass[0]; b <= columnass[1]; b++) { if (VariableCheck) { issuefounded = VarCompare((string)(oWS.Cells[fila.Row, int.Parse(cellsCol[0])] as Excel.Range).Value, owbb); } if ((issuefounded == false) && (Numericalcheck == true)) { NumCompare((string)(oWS.Cells[fila.Row, b] as Excel.Range).Value, owbb); } } } else { if ((columnass[0] == -1) && (columnass[1] == -1)) { System.Windows.Forms.MessageBox.Show("Please make sure that you fill \nin all the mandatory fields properly."); } else { if (columnass[0] == -1) { columnass[0] = columnass[1]; } if (VariableCheck) { issuefounded = VarCompare((string)(oWS.Cells[fila.Row, int.Parse(cellsCol[0])] as Excel.Range).Value, owbb); } if ((issuefounded == false) && (Numericalcheck == true)) { NumCompare((string)(oWS.Cells[fila.Row, columnass[0]] as Excel.Range).Value, owbb); } } } break; default: foreach (string cols in cellsCol) { if ((cols != null) && (cols != "")) { if (VariableCheck) { issuefounded = VarCompare((string)(oWS.Cells[fila.Row, int.Parse(cellsCol[0])] as Excel.Range).Value, owbb); } if ((issuefounded == false) && (Numericalcheck == true)) { NumCompare((string)(oWS.Cells[fila.Row, int.Parse(cols)] as Excel.Range).Value, owbb); } } } break; } } } } } }
private static string SaveDataToSingleExcel(List<DataTable> dataTables, List<ExcelInfo> excelInfos, ExcelInfo dataTableInfo = null) { try { OpenExcel(excelInfos[0], false); for (int n = 0; n < dataTables.Count; n++) { _objSheet = OpenSheet(excelInfos[n]); if (_objSheet == null) { return "没有指定页签"; } SaveDataToExcelImpl(dataTables[n], excelInfos[n], dataTableInfo); } SaveAs(excelInfos[0].FilePath); } catch (Exception err) { CloseExcel(); Console.WriteLine(err.Message); return err.Message; } return ""; }
public CellGetter(Excel._Worksheet worksheet) { this.worksheet = worksheet; }
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(); }
public string getStatusExcel(string fPath) { string strval = validation(fPath); try { if (strval == "true") { addlog("Getting PR status...."); Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@fPath); Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1]; Excel.Range xlRange = xlWorksheet.UsedRange; VssConnection connection2 = new VssConnection(new Uri($"https://dynamicscrm.visualstudio.com"), new VssAadCredential()); var statsAPI = new GetStatusAPI(connection2); int rowCount = xlRange.Rows.Count; int colCount = xlRange.Columns.Count; int cCnt = 1; int csCnt = 2; if (rowCount > 1) { for (int rCnt = 1; rCnt <= rowCount; rCnt++) { if (rCnt == 1) { addlog("PR Number# PR Status"); } else { if (xlRange.Cells[rCnt, cCnt] != null && xlRange.Cells[rCnt, cCnt].Value2 != null) { string PrLink = xlRange.Cells[rCnt, cCnt].Value2.ToString(); strStatus = getStatus(PrLink.ToString(), statsAPI).Result.ToString(); xlWorksheet.Cells[rCnt, csCnt] = strStatus; addlog(PRNum + " " + strStatus); xlWorkbook.Save(); } else { strStatus = "Invalid PRLink"; xlWorksheet.Cells[rCnt, csCnt] = strStatus; xlWorkbook.Save(); addlog(strStatus); } } } xlWorkbook.Close(); strStatus = "The file in the same file path has been updated successfully"; } else { xlWorkbook.Close(); strStatus = "No Data found in the given file"; } } else { addlog(strval); } } catch (Exception ex) { strStatus = ex.Message; clsStatus.WriteLog("getStatusExcel()-->" + ex.Message); } return(strStatus); }
private void bwrkLoadVFDDefs_DoWork(object sender, DoWorkEventArgs e) { xlApp = new XL.Application(); xlWorkbook = xlApp.Workbooks.Open("C:\\Users\\sferry\\source\\repos\\V1000_Drive_Programmer\\V1000_Drive_Programmer\\data\\Parameter Defaults.dat"); //xlWorkbook = xlApp.Workbooks.Open("C:\\Users\\steve\\source\\repos\\V1000_Drive_Programmer\\V1000_Drive_Programmer\\data\\Parameter Defaults.dat"); xlWorksheet = xlWorkbook.Sheets[1]; xlRange = xlWorksheet.UsedRange; V1000_xlRead_List.Clear(); ProgressArgs.VFDLoad_Total_Units = xlRange.Rows.Count - 1; for (int i = 2; i <= xlRange.Rows.Count; i++) { if (bwrkLoadVFDDefs.CancellationPending) { e.Cancel = true; bwrkLoadVFDDefs.ReportProgress(0); return; } V1000_Param_Data ParamData = new V1000_Param_Data(); if (xlRange.Cells[i, 1] != null && xlRange.Cells[i, 1].Value2 != null) { ParamData.RegAddress = Convert.ToUInt16(xlRange.Cells[i, 1].Value2); } else { ParamData.RegAddress = 0; } if (xlRange.Cells[i, 2] != null && xlRange.Cells[i, 2].Value2 != null) { ParamData.ParamNum = xlRange.Cells[i, 2].Value2.ToString(); } else { ParamData.ParamNum = "0"; } if (xlRange.Cells[i, 3] != null && xlRange.Cells[i, 3].Value2 != null) { ParamData.ParamName = xlRange.Cells[i, 3].Value2.ToString(); } else { ParamData.ParamName = "0"; } if (xlRange.Cells[i, 4] != null && xlRange.Cells[i, 4].Value2 != null) { ParamData.DefVal = Convert.ToUInt16(xlRange.Cells[i, 4].Value2); } else { ParamData.DefVal = 0; } if (xlRange.Cells[i, 5] != null && xlRange.Cells[i, 5].Value2 != null) { ParamData.Multiplier = Convert.ToUInt16(xlRange.Cells[i, 5].Value2); } else { ParamData.Multiplier = 1; } if (xlRange.Cells[i, 6] != null && xlRange.Cells[i, 6].Value2 != null) { ParamData.NumBase = Convert.ToByte(xlRange.Cells[i, 6].Value2); } else { ParamData.NumBase = 10; } if (xlRange.Cells[i, 7] != null && xlRange.Cells[i, 7].Value2 != null) { ParamData.Units = xlRange.Cells[i, 7].Value2.ToString(); } else { ParamData.Units = ""; } // Create a string version for display purposes of the actual default paramater value if (ParamData.NumBase == 16) { ParamData.DefValDisp = "0x" + ParamData.DefVal.ToString("X4"); } else { ParamData.DefValDisp = ((float)ParamData.DefVal / ParamData.Multiplier).ToString() + " " + ParamData.Units; } VFD_Vals.Add(ParamData); ProgressArgs.VFDLoad_Unit = i - 2; ProgressArgs.VFDLoad_Progress = (byte)(((float)ProgressArgs.VFDLoad_Unit / ProgressArgs.VFDLoad_Total_Units) * 100); bwrkLoadVFDDefs.ReportProgress((int)(((float)i / (xlRange.Rows.Count - 1)) * 100)); } ProgressArgs.VFDLoad_Unit = ProgressArgs.VFDLoad_Total_Units - 1; e.Result = 0x02; bwrkLoadVFDDefs.ReportProgress(100); }
public IEnumerable <Transaction> ParseFile(IFileService fileService) { int year = int.MinValue; int period = int.MinValue; using (var frmGetData = new frmRequestData()) { if (frmGetData.ShowDialog() == DialogResult.Cancel) { return(null); } year = frmGetData.Year; period = frmGetData.Period; } var transactions = new List <Transaction>(); var excelApp = new Excel.Application(); excelApp.Visible = false; excelApp.Workbooks.Open(fileService.FileName); Excel._Worksheet worksheet = excelApp.ActiveSheet; var account = worksheet.Cells[1, "A"].Value2; var ibalance = worksheet.Cells[1, "C"].Value2; int currRow = 3; do { var tdateTemp = worksheet.Cells[currRow, "C"].Value2; var transactionDate = DateTime.FromOADate(tdateTemp); var GLCode = worksheet.Cells[currRow, "B"].Value2; decimal Debit = 0.0m; decimal Credit = 0.0m; var vendorName = string.Empty; var checkNumber = string.Empty; var sequence = worksheet.Cells[currRow, "F"].Value2; if (GLCode == "AP-PY") { decimal.TryParse(worksheet.Cells[currRow, "G"].Value2.ToString(), out Debit); decimal.TryParse(worksheet.Cells[currRow, "H"].Value2.ToString(), out Credit); if (decimal.Equals(Debit, 0.0m)) { vendorName = worksheet.Cells[currRow, "D"].Value2; currRow += 1; string temp = worksheet.Cells[currRow, "A"].Value2.ToString(); checkNumber = temp.Substring(0, temp.LastIndexOf("-")).TrimStart('0'); } else { string temp = worksheet.Cells[currRow, "D"].Value2.ToString(); checkNumber = temp.Substring(0, temp.LastIndexOf("-")).TrimStart('0'); currRow += 1; vendorName = worksheet.Cells[currRow, "A"].Value2; } } if (GLCode == "GL-JE" || GLCode == "AP-IN") { decimal.TryParse(worksheet.Cells[currRow, "G"].Value2.ToString(), out Debit); decimal.TryParse(worksheet.Cells[currRow, "H"].Value2.ToString(), out Credit); vendorName = worksheet.Cells[currRow, "D"].Value2; if (GLCode == "AP-IN") { var tempVendorName = worksheet.Cells[currRow + 1, "A"].Value2; vendorName = $"{(tempVendorName == null ? string.Empty : $"{tempVendorName}:")}{vendorName}"; } if (worksheet.Cells[currRow + 1, "C"].Value2 == null) { currRow += 1; } } transactions.Add(new Transaction() { TransactionDate = transactionDate.ToString("M/d/yyyy"), Period = period.ToString(), Year = year.ToString(), Acconut = account.ToString(), IBalance = ibalance.ToString(), GLCode = GLCode.ToString(), VendorName = vendorName == null ? "" : vendorName.ToString(), CheckNumber = checkNumber, Credit = Credit.ToString(), DCredit = Credit, Debit = Debit.ToString(), DDebit = Debit, Sequence = sequence }); currRow += 1; } while (!worksheet.Cells[currRow, "A"].Value2.ToString().StartsWith("Net Change"));
public static List <Question> ReadFile(string excelFile) { int headersRow = 2; List <Question> qList = new List <Question>(); // open excel file Excel.Application excel = new Excel.Application(); Excel.Workbook workbook = excel.Workbooks.Open(excelFile); Excel._Worksheet worksheet = workbook.Sheets[1]; int rowCount = worksheet.UsedRange.Rows.Count; int colCount = worksheet.UsedRange.Columns.Count; // find data indexes //main.ShowOutput("Finding data indexes....", false); int questionTextIndex = 0; int nameIndex = 0; int categoryIndex = 0; int answerIndex = 0; int imageIndex = 0; int[] optionIndexes = new int[] { 0, 0, 0, 0 }; for (int col = 1; col <= colCount; col++) { string v = worksheet.Cells[headersRow, col].Value.ToString(); if (v == "Question Stem") { questionTextIndex = col; } if (v == "Syllabus") { categoryIndex = col; } if (v == "Correct Answer") { answerIndex = col; } if (v == "Question ID") { nameIndex = col; } if (v == "Annex") { imageIndex = col; } string[] answerFields = new string[] { "A", "B", "C", "D" }; for (int i = 0; i < 4; i++) { if (v == answerFields[i]) { optionIndexes[i] = col; } } } // check data indexes if (questionTextIndex > 0 && optionIndexes[0] > 0 && optionIndexes[1] > 0 && optionIndexes[2] > 0 && optionIndexes[3] > 0 && answerIndex > 0 && imageIndex > 0 && categoryIndex > 0 && nameIndex > 0) { //main.ShowOutput("OK"); } else { Main.myThis.ShowOutput("INDEX ERROR!!"); if (questionTextIndex == 0) { Main.myThis.ShowOutput("questionTextIndex not found. Should be\"Question Stem\""); } if (optionIndexes[0] == 0) { Main.myThis.ShowOutput("optionIndexes(A) not found. Should be\"A\""); } if (optionIndexes[0] == 0) { Main.myThis.ShowOutput("optionIndexes(B) not found. Should be\"B\""); } if (optionIndexes[0] == 0) { Main.myThis.ShowOutput("optionIndexes(C) not found. Should be\"C\""); } if (optionIndexes[0] == 0) { Main.myThis.ShowOutput("optionIndexes(D) not found. Should be\"D\""); } if (answerIndex == 0) { Main.myThis.ShowOutput("answerIndex not found. Should be\"Correct Answer\""); } if (imageIndex == 0) { Main.myThis.ShowOutput("imageIndex not found. Should be\"Annex\""); } if (categoryIndex == 0) { Main.myThis.ShowOutput("categoryIndex not found. Should be\"Syllabus\""); } if (nameIndex == 0) { Main.myThis.ShowOutput("nameIndex not found. Should be\"Question ID\""); } workbook.Close(); excel.Quit(); return(qList); } // get question data for (int row = (headersRow + 1); row <= rowCount; row++) { Question q = new Question(); q.name = JwString.Clean(worksheet.Cells[row, nameIndex].Value.ToString()); q.category = CleanCategory(JwString.Clean(worksheet.Cells[row, categoryIndex].Value.ToString())); q.text = JwString.Clean(worksheet.Cells[row, questionTextIndex].Value.ToString()); if (worksheet.Cells[row, imageIndex].Value != null) { string imageName = CleanImage(JwString.Clean(worksheet.Cells[row, imageIndex].Value.ToString())); q.AddImage(imageName, ""); } q.type = "multichoice"; string answer = worksheet.Cells[row, answerIndex].Value.Trim(); string[] answerValues = new string[] { "A", "B", "C", "D" }; for (int i = 1; i <= 4; i++) { Option o = new Option(); o.text = JwString.Clean(worksheet.Cells[row, optionIndexes[i - 1]].Value.ToString()); o.feedback = ""; o.grade = 0; if (answer == answerValues[i - 1]) { o.grade = 100; } q.options.Add(o); } qList.Add(q); } // close excel and return questions workbook.Close(); excel.Quit(); return(qList); }
public DataTable getExcelFile(string fileLocation) { DataTable dt = new DataTable(); DataColumn clID = new DataColumn("serial", typeof(string)); dt.Columns.Add(clID); //Create COM Objects. Create a COM object for everything that is referenced Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(fileLocation); Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1]; Excel.Range xlRange = xlWorksheet.UsedRange; int rowCount = xlRange.Rows.Count; int colCount = xlRange.Columns.Count; //iterate over the rows and columns and print to the console as it appears in the file //excel is not zero based!! for (int i = 1; i <= rowCount; i++) { int progree = i * 100 / rowCount; List <string> lt = new List <string>(); for (int j = 1; j <= colCount; j++) { //new line if (j == 1) { if (i != 1) { lt.Add((i - 1).ToString()); } continue; } var data = xlRange.Cells[i, j].Value2 == null? "": xlRange.Cells[i, j].Value2.ToString(); if (i == 1) { string whiteSapceRemove = Regex.Replace(data, @"[^0-9a-zA-Z]+", ""); DataColumn cl = new DataColumn(whiteSapceRemove, typeof(string)); dt.Columns.Add(cl); } else { lt.Add(data); } } if (lt.Count > 0) { dt.Rows.Add(lt.ToArray()); //dt.Rows.Add("1", "2", "3", "4"); } toolStripProgressBar.Value = progree; } //cleanup GC.Collect(); GC.WaitForPendingFinalizers(); //rule of thumb for releasing com objects: // never use two dots, all COM objects must be referenced and released individually // ex: [somthing].[something].[something] is bad //release com objects to fully kill excel process from running in the background Marshal.ReleaseComObject(xlRange); Marshal.ReleaseComObject(xlWorksheet); //close and release xlWorkbook.Close(); Marshal.ReleaseComObject(xlWorkbook); //quit and release xlApp.Quit(); Marshal.ReleaseComObject(xlApp); return(dt); }
public void ReadData() { // Setting the excel application to call it Excel.Application xlApp = new Excel.Application(); // Which excel the code shell read Excel.Workbook xlWb = xlApp.Workbooks.Open(@"C:\Users\kiels00j\Desktop\test.xlsx"); // Which sheets the code shall read from (in excel 1 is the first one and not 0 as in coding) Excel._Worksheet xlWs = xlWb.Sheets[1]; // Getting all the data contain in the predefine setting above Excel.Range xlR = xlWs.UsedRange; // Getting how many Rows and Cloumns the excel file contain to the for loop int rowCount = xlR.Rows.Count; int colCount = xlR.Columns.Count; // Making a list to contain all the data from the excel file List <Data_Used_list> dul = new List <Data_Used_list>(); // Going / loop through every row there is, if there is headers or the data is // not starting on line 1 set it to the line nr where it is for (int i = 1; i <= rowCount; i++) { // Use this if just want to read every single colums and combine them in string row //for (int j = 1; j <= colCount; j++) //{ // Console.Write(xlR.Cells[i, j].Value2.ToString() + "\t"); //} //Console.WriteLine(); // Here I add data to the list and know which space the headers are in // notice that it start with 1 and not 0, this is because in excel 1 is the first and not // 0 as in coding dul.Add(new Data_Used_list { Phone_nr = Convert.ToString(xlR.Cells[i, 1].Value2), Abo = Convert.ToString(xlR.Cells[i, 2].Value2), GID = Convert.ToString(xlR.Cells[i, 3].Value2), DataUsed = Convert.ToString(xlR.Cells[i, 4].Value2) }); } // This is only to confirm and write out the list that foreach (Data_Used_list item in dul) { Console.WriteLine("Nr: " + item.Phone_nr + " - GID: " + item.GID + " - Data: " + item.DataUsed); } // Closing down the use off the excel file so the code/software do not lock it GC.Collect(); GC.WaitForPendingFinalizers(); Marshal.ReleaseComObject(xlR); Marshal.ReleaseComObject(xlWs); xlWb.Close(); Marshal.ReleaseComObject(xlWb); xlApp.Quit(); Marshal.ReleaseComObject(xlApp); }
private void ImportData() { string ERPInDirectory = Convert.ToString(ConfigurationManager.AppSettings["ERPIN"].ToString().Trim()); if (!Directory.Exists(ERPInDirectory)) { log.Info("ERPIN路径不存在。"); return; } string ExcelPath = ERPInDirectory; List <FileInfo> ExcelFiles = getFiles(ExcelPath, ".xls"); if (ExcelFiles.Count == 0) { log.Info("没有检测到Excel文件。"); return; } foreach (FileInfo fileInfo in ExcelFiles) { try { string FilePath = fileInfo.FullName; log.Info("正在处理:" + FilePath); MSExcel.Application excelApp = new MSExcel.Application { Visible = false//是打开可见 }; MSExcel.Workbooks _workbooks = excelApp.Workbooks; MSExcel._Workbook _workbook = _workbooks.Add(FilePath); MSExcel._Worksheet whs = _workbook.Sheets[1];//获取第2张工作表 TODO:生产环境改回1 whs.Activate(); JArray ModelArray = new JArray(); JObject ModelJson = new JObject(); JArray FOrderEntrys = new JArray(); List <string> ordenoList = new List <string>(); int excelIndex = 2; int BillNoLineNumber = 0; //表头单据编号所在行数 bool IsAllSuccess = true; //是否整个文件导入成功 bool HasSuccess = false; //是否整个文件有导入成功的订单 bool HasError = false; //是否整个文件有导入失败的订单 while (true) { try { MSExcel.Range rang = (MSExcel.Range)whs.Cells[excelIndex, 32];//ERP收料通知单单号 if (rang.Value != null) { string ERPBillNo = Convert.ToString(rang.Value); string PrevFBillNo = Convert.ToString(((MSExcel.Range)whs.Cells[excelIndex - 1, 32]).Value); //上一行的ERP收料通知单单号 string NextFBillNo = Convert.ToString(((MSExcel.Range)whs.Cells[excelIndex + 1, 32]).Value); //下一行的ERP收料通知单单号 string QISBillNo = Convert.ToString(((MSExcel.Range)whs.Cells[excelIndex, 1]).Value); //QIS检验单号 string FDate = Convert.ToString(((MSExcel.Range)whs.Cells[excelIndex, 2]).Value); string MaterialNumber = Convert.ToString(((MSExcel.Range)whs.Cells[excelIndex, 7]).Value); string ComputerResult = Convert.ToString(((MSExcel.Range)whs.Cells[excelIndex, 15]).Value); //电脑判定 double CheckQty = Convert.ToDouble(((MSExcel.Range)whs.Cells[excelIndex, 14]).Value); //批量数(报检数量) double RealQty = Convert.ToDouble(((MSExcel.Range)whs.Cells[excelIndex, 22]).Value); //实收数量(合格数量) string CheckResult = Convert.ToString(((MSExcel.Range)whs.Cells[excelIndex, 16]).Value); //检验结果 double unqualifiedQty = CheckQty - RealQty; //不合格数量 //ERP收料通知单单号和上一行不同,则是另一张单据 if (!PrevFBillNo.Equals(ERPBillNo)) { BillNoLineNumber = excelIndex; log.Info("正在读取:" + ERPBillNo); } string NeedPushEntryIds = ""; //excel的一行是相同物料合并数量的,在ERP查源单,查出来可能有多行物料 List <List <object> > Bills = PUR_ReceiveBill.GetAllBill(client, "FBillNo='" + ERPBillNo + "'" + " and FMaterialID.FNumber='" + MaterialNumber + "'"); for (int i = 0; i < Bills.Count; i++) { double FQty = 0; string SrcBillID = Convert.ToString(Bills[i][0]); string SrcBillNo = Convert.ToString(Bills[i][1]); string SrcEntryID = Convert.ToString(Bills[i][8]); double FActReceiveQty = Convert.ToDouble(Bills[i][7]); //交货数量 double FCheckJoinQty = Convert.ToDouble(Bills[i][9]); //检验关联数量 double NoCheckQty = FActReceiveQty - FCheckJoinQty; //剩余未检验数量 string SrcBillTypeID = Convert.ToString(Bills[i][10]); string SrcSeq = Convert.ToString(Bills[i][11]); if (NoCheckQty > CheckQty) { FQty = CheckQty; } else { FQty = NoCheckQty; } CheckQty = CheckQty - FQty; if (FQty <= 0) { continue; } NeedPushEntryIds = NeedPushEntryIds + SrcEntryID + ","; //一行下推成一单 string NewBillId = PUR_ReceiveBill.PushToInspectBill(client, SrcEntryID); if (!"".Equals(NewBillId)) { //修改数量 List <List <object> > InspectBills = QM_InspectBill.GetAllBill(client, "FID=" + NewBillId); if (InspectBills.Count > 0) { string NewBillNo = Convert.ToString(InspectBills[0][1]); ordenoList.Add(NewBillNo); JArray MultiLanguageTextArr = new JArray(); JObject MultiLanguageTextJson = new JObject(); MultiLanguageTextJson.Add("Key", 2052); MultiLanguageTextJson.Add("Value", "QIS导入,单号" + QISBillNo); JObject Entry = new JObject(); ModelJson.Add("FID", Convert.ToString(InspectBills[0][0])); ModelJson.Add("FEntity", FOrderEntrys); ModelJson.Add("FDescription", "QIS导入,单号" + QISBillNo); FOrderEntrys.Add(Entry); Entry.Add("FEntryID", Convert.ToString(InspectBills[0][2])); Entry.Add("FInspectQty", FQty); Entry.Add("FMemo", "QIS导入,单号" + QISBillNo); if (!"合格".Equals(ComputerResult)) { Entry.Add("FInspectResult", "2"); //检验结果=不合格 JArray FPolicyDetailEntrys = new JArray(); //使用决策 子单据体 JObject FPolicyDetailEntry1 = new JObject(); JObject FPolicyDetailEntry2 = new JObject(); switch (CheckResult) { case "退货": break; case "让步接收": Entry.Add("FPolicyDetail", FPolicyDetailEntrys); FPolicyDetailEntrys.Add(FPolicyDetailEntry1); FPolicyDetailEntry1.Add("FUsePolicy", "A"); FPolicyDetailEntry1.Add("FPolicyQty", RealQty); FPolicyDetailEntrys.Add(FPolicyDetailEntry2); FPolicyDetailEntry2.Add("FUsePolicy", "B"); FPolicyDetailEntry2.Add("FPolicyQty", unqualifiedQty); break; case "挑选": Entry.Add("FPolicyDetail", FPolicyDetailEntrys); FPolicyDetailEntrys.Add(FPolicyDetailEntry1); FPolicyDetailEntry1.Add("FUsePolicy", "A"); FPolicyDetailEntry1.Add("FPolicyQty", RealQty); FPolicyDetailEntrys.Add(FPolicyDetailEntry2); FPolicyDetailEntry2.Add("FUsePolicy", "E"); FPolicyDetailEntry2.Add("FPolicyQty", unqualifiedQty); break; } } else if ("合格".Equals(ComputerResult) && "内部原因".Equals(CheckResult)) { JArray FPolicyDetailEntrys = new JArray();//使用决策 子单据体 JObject FPolicyDetailEntry1 = new JObject(); Entry.Add("FPolicyDetail", FPolicyDetailEntrys); FPolicyDetailEntrys.Add(FPolicyDetailEntry1); FPolicyDetailEntry1.Add("FDetailID", Convert.ToString(InspectBills[0][3])); FPolicyDetailEntry1.Add("FMemo1", "内部原因"); } Dictionary <string, object> ImportResult = QM_InspectBill.SaveBill(client, new StringBuilder(ModelJson.ToString()), new List <string>(ordenoList)); //提交审核检验单 CommonOperate commonOperate = new CommonOperate(); commonOperate.SubmitBill(client, "QM_InspectBill", new StringBuilder("\"" + NewBillNo + "\"")); commonOperate.AuditBill(client, "QM_InspectBill", new StringBuilder("\"" + NewBillNo + "\"")); ModelJson = new JObject(); FOrderEntrys = new JArray(); if (!Convert.ToBoolean(ImportResult["IsSuccess"])) { HasError = true; IsAllSuccess = false; } else { HasSuccess = true; } } else { log.Info("下推的单据已被删除。"); } } } //移除掉最后一个"," if (NeedPushEntryIds.Length > 0) { NeedPushEntryIds = NeedPushEntryIds.Remove(NeedPushEntryIds.LastIndexOf(","), 1); } } else { break; } } catch (Exception ex) { log.Error(ex); } finally { excelIndex++; } } string ERPBackup = Convert.ToString(ConfigurationManager.AppSettings["ERPBackup"].ToString().Trim()); string ResultDirectory = ERPBackup; //处理完后文件保存到这里 if (!Directory.Exists(ResultDirectory)) //如果不存在就创建文件夹 { Directory.CreateDirectory(ResultDirectory); } whs.SaveAs(Path.Combine(ResultDirectory, fileInfo.Name), 51); //关闭对象 Marshal.ReleaseComObject(_workbook); Marshal.ReleaseComObject(whs); excelApp.Quit(); GC.Collect(); fileInfo.Delete(); } catch (Exception ex) { log.Error(ex); } } Executed = true; }
private void outPutReciveBill() { List <List <object> > Bills = PUR_ReceiveBill.GetAllBill(client, "FDocumentStatus = 'C' and FCheckInComing = 1 and F_PAEZ_Exported=0");//F_PAEZ_Exported=0 未导出 //List<List<object>> Bills = PUR_ReceiveBill.GetAllBill(client, "FBillNo='CGSL000328'");//F_PAEZ_Exported=0 未导出 if (Bills.Count == 0) { log.Info("没有可以导出的收料通知单。"); return; } MSExcel.Application excelApp = new MSExcel.Application { Visible = false//是打开可见 }; MSExcel.Workbooks _workbooks = excelApp.Workbooks; //MSExcel._Workbook _workbook = _workbooks.Add(AppDomain.CurrentDomain.BaseDirectory+"\\DD_20200901084512--ERP输出模板.XLS"); MSExcel._Workbook _workbook = _workbooks.Add(System.Reflection.Missing.Value); MSExcel._Worksheet whs = _workbook.Sheets[1];//获取第1张工作表 try { whs.Name = "Sheet1"; //headline whs.Cells[1, 1] = "进货单号"; whs.Cells[1, 2] = "产品编号"; whs.Cells[1, 3] = "物料名称"; whs.Cells[1, 4] = "物料规格"; whs.Cells[1, 5] = "厂商"; whs.Cells[1, 6] = "报检数量"; whs.Cells[1, 7] = "厂商编码"; whs.Cells[1, 8] = "物料分类名称"; whs.Cells[1, 9] = "实收数量"; whs.Cells[1, 10] = "不良数"; whs.Cells[1, 11] = "QIS报检单号"; //同一单据内,相同物料合并数量 Hashtable AllMaterialHT = new Hashtable(); for (int i = 0; i < Bills.Count; i++) { string BillNo = Convert.ToString(Bills[i][1]); string MaterialNumber = Convert.ToString(Bills[i][2]); double Qty = Convert.ToDouble(Bills[i][7]); if (AllMaterialHT.ContainsKey(BillNo + MaterialNumber)) { JObject jObject = AllMaterialHT[BillNo + MaterialNumber] as JObject; double OldQty = Convert.ToDouble(Convert.ToString(jObject["Qty"])); jObject["Qty"] = OldQty + Qty; } else { List <object> OtherInfo = Bills[i]; JObject jObject = new JObject(); jObject.Add("MaterialNumber", MaterialNumber); jObject.Add("Qty", Qty); jObject.Add("OtherInfoIndex", i); //原来的List的索引,保存以便在下方获取原来的其他数据 AllMaterialHT.Add(BillNo + MaterialNumber, jObject); } } string DateTimeStr = DateTime.Now.ToString("yyyyMMddHHmmssf"); Dictionary <string, string> BillIDsDic = new Dictionary <string, string>(); StringBuilder sbBillNos = new StringBuilder(); //for (int i = 0; i < Bills.Count; i++) int ExcelRow = 2; foreach (string BillAndMaterialNumber in AllMaterialHT.Keys) { JObject jObject = AllMaterialHT[BillAndMaterialNumber] as JObject; string MaterialNumber = Convert.ToString(jObject["MaterialNumber"]); double Qty = Convert.ToDouble(Convert.ToString(jObject["Qty"])); int i = Convert.ToInt32(Convert.ToString(jObject["OtherInfoIndex"])); List <object> BillInfo = Bills[i]; string BillID = Convert.ToString(Bills[i][0]); string BillNo = Convert.ToString(Bills[i][1]); string FDetailEntity_FSeq = Convert.ToString(Bills[i][11]); if (!BillIDsDic.ContainsKey(BillID)) { BillIDsDic.Add(BillID, BillNo); sbBillNos.Append("\"").Append(BillNo).Append("\","); } whs.Cells[ExcelRow, 1] = BillNo; whs.Cells[ExcelRow, 2] = Bills[i][2]; whs.Cells[ExcelRow, 3] = Bills[i][3]; whs.Cells[ExcelRow, 4] = Bills[i][4]; whs.Cells[ExcelRow, 5] = Bills[i][6]; whs.Cells[ExcelRow, 6] = Qty; whs.Cells[ExcelRow, 7] = Bills[i][5]; whs.Cells[ExcelRow, 8] = Bills[i][12]; whs.Cells[ExcelRow, 9] = 0; whs.Cells[ExcelRow, 10] = 0; whs.Cells[ExcelRow, 11] = "'" + DateTimeStr + (i + 1).ToString("000"); ExcelRow++; } if (sbBillNos.Length > 0) { sbBillNos.Remove(sbBillNos.Length - 1, 1);; //移除掉最后一个"," } string ERPOUTDirectory = Convert.ToString(ConfigurationManager.AppSettings["ERPOUT"].ToString().Trim()); if (!Directory.Exists(ERPOUTDirectory))//如果不存在就创建文件夹 { Directory.CreateDirectory(ERPOUTDirectory); } string fileName = ERPOUTDirectory + "K3Cloud_DD_" + DateTimeStr + ".XLS"; whs.SaveAs(fileName, 51); log.Info("正在更新该订单的同步状态。"); PUR_ReceiveBill.ExportedToQIS(client, sbBillNos); //UpdateSyncStatus("PUR_ReceiveBill", BillIDsDic); } catch (Exception ex) { log.Error(ex); } finally { //关闭对象 Marshal.ReleaseComObject(_workbook); Marshal.ReleaseComObject(whs); excelApp.Quit(); GC.Collect(); } }
static void DisplayInExcelFull(IEnumerable <Account> accounts) { var excelApp = new Excel.Application(); // Make the object visible. excelApp.Visible = true; // Create a new, empty workbook and add it to the collection returned // by property Workbooks. The new workbook becomes the active workbook. // Add has an optional parameter for specifying a particular template. // Because no argument is sent in this example, Add creates a new workbook. excelApp.Workbooks.Add(); // This example uses a single workSheet. Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet; //<Snippet5> // Establish column headings in cells A1 and B1. workSheet.Cells[1, "A"] = "ID Number"; workSheet.Cells[1, "B"] = "Current Balance"; //</Snippet5> //<Snippet6> // Establish column headings. Both parameters must be supplied in // Visual C# 2008 or earlier versions. //workSheet.get_Range("A1", Type.Missing).Value2 = "ID Number"; //workSheet.get_Range("B1", Type.Missing).Value2 = "Current Balance"; // Move to the next row. //workSheet.get_Range("A2", Type.Missing).Select(); //</Snippet6> //<Snippet7> var row = 1; foreach (var acct in accounts) { row++; workSheet.Cells[row, "A"] = acct.ID; workSheet.Cells[row, "B"] = acct.Balance; } //</Snippet7> // Columns returns a Range object that represents all the columns // on the active worksheet. AutoFit fits the width of the columns // to their content. //ExcelApp.Columns.AutoFit(); //<Snippet13> workSheet.Columns[1].AutoFit(); workSheet.Columns[2].AutoFit(); //</Snippet13> //<Snippet14> ((Excel.Range)workSheet.Columns[1]).AutoFit(); ((Excel.Range)workSheet.Columns[2]).AutoFit(); //</Snippet14> //<Snippet15> // Call to AutoFormat in Visual C# 2010. workSheet.Range["A1", "B3"].AutoFormat( Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2); //</Snippet15> //<Snippet16> // Call to AutoFormat in Visual C# 2010. workSheet.Range["A1", "B3"].AutoFormat(Format: Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2); //</Snippet16> //<Snippet12> // Put the spreadsheet contents on the clipboard. The Copy method has one // optional parameter for specifying a destination. Because no argument // is sent, the destination is the Clipboard. workSheet.Range["A1:B3"].Copy(); //</Snippet12> //ExcelApp.get_Range("A5:C6").PasteSpecial( Transpose: true); //dynamic val = Type.Missing; //ExcelApp.get_Range("A5:C6").PasteSpecial(val, val, val, Transpose: true); //ExcelApp.get_Range("A5:C6").Copy(); }
private void WriteDataToExcel(IDictionary <string, IList <UserInvestIncomeEntity> > reportData, int deptId, string templateFilePath, string destinyFilePath) { if (reportData == null) { throw new NullReferenceException(nameof(reportData)); } Excel.Application excelApp = new Excel.Application(); Excel.Workbook workbook = excelApp.Workbooks.Open(templateFilePath); try { foreach (var item in reportData) { var investorName = item.Key; Excel._Worksheet worksheet = null; foreach (Excel.Worksheet sheet in workbook.Sheets) { if (sheet.Name == investorName) { worksheet = sheet; } } if (worksheet == null) { continue; } WorksheetFormatting(worksheet); var startRowIndex = 34; var data = item.Value; for (int i = 0; i < data.Count; i++) { var investInfo = data[i]; if (deptId != (int)EnumLibrary.AccountingDepartment.Day) { //周一(万元) worksheet.Cells[startRowIndex + i, 3] = investInfo.MondayPositionValue / (int)EnumLibrary.NumericUnit.TenThousand; //净资产(万元) worksheet.Cells[startRowIndex + i, 4] = investInfo.CurrentAsset / (int)EnumLibrary.NumericUnit.TenThousand; //持仓市值(万元) worksheet.Cells[startRowIndex + i, 9] = investInfo.PositionValue / (int)EnumLibrary.NumericUnit.TenThousand; //持仓仓位 worksheet.Cells[startRowIndex + i, 12] = investInfo.PositionRate; } //日期 worksheet.Cells[startRowIndex + i, 2] = investInfo.TradeTime; //本年累计收益额(万元) worksheet.Cells[startRowIndex + i, 5] = investInfo.AnnualActualProfit / (int)EnumLibrary.NumericUnit.TenThousand; //当日收益率 worksheet.Cells[startRowIndex + i, 6] = investInfo.CurrentIncomeRate; //日收益额(万元) worksheet.Cells[startRowIndex + i, 7] = investInfo.CurrentActualProfit / (int)EnumLibrary.NumericUnit.TenThousand; //本年累计收益率 worksheet.Cells[startRowIndex + i, 8] = investInfo.AnnualIncomeRate; } } workbook.SaveAs(destinyFilePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } catch (Exception ex) { throw ex; } finally { workbook = null; excelApp.Quit(); excelApp = null; } }
public void getExcelFile() { while (!comboBoxSelectedIndexChanged) { this.Dispatcher.Invoke(() => { tbAction.Text = "Choose a Test Type!"; }); Thread.Sleep(100); } this.Dispatcher.Invoke(() => { tbAction.Text = string.Empty; }); Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\Users\lazlo\Desktop\smokeTest.xlsx"); Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1]; Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange; int rowCount = xlRange.Rows.Count; int colCount = xlRange.Columns.Count; int row = 3; if (xlRange.Cells[2, 1] != null && xlRange.Cells[2, 1].Value2 != null) { this.Dispatcher.Invoke(() => { tbAction.Text = (xlRange.Cells[2, 1].Value2.ToString()); }); } if (xlRange.Cells[2, 2] != null && xlRange.Cells[2, 2].Value2 != null) { this.Dispatcher.Invoke(() => { tbExpected.Text = (xlRange.Cells[2, 2].Value2.ToString()); }); } while (!ButtonPressed) { Thread.Sleep(100); } ButtonPressed = false; while (row <= rowCount) { for (int col = 1; col <= colCount; col++) { switch (col) { case 1: if (xlRange.Cells[row, col] != null && xlRange.Cells[row, col].Value2 != null) { this.Dispatcher.Invoke(() => { tbAction.Text = (xlRange.Cells[row, col].Value2.ToString()); }); } break; case 2: if (xlRange.Cells[row, col] != null && xlRange.Cells[row, col].Value2 != null) { this.Dispatcher.Invoke(() => { tbExpected.Text = (xlRange.Cells[row, col].Value2.ToString()); }); } break; default: break; } } while (!ButtonPressed) { Thread.Sleep(100); } ButtonPressed = false; row++; } GC.Collect(); GC.WaitForPendingFinalizers(); Marshal.ReleaseComObject(xlRange); Marshal.ReleaseComObject(xlWorksheet); xlWorkbook.Close(); Marshal.ReleaseComObject(xlWorkbook); xlApp.Quit(); Marshal.ReleaseComObject(xlApp); }
private void bwrkReadExcelFile_DoWork(object sender, DoWorkEventArgs e) { xlApp = new XL.Application(); xlWorkbook = xlApp.Workbooks.Open("C:\\Users\\sferry\\source\\repos\\V1000_Param_Prog\\V1000_Param_Prog\\data\\Parameter List.xlsx"); xlWorksheet = xlWorkbook.Sheets[1]; xlRange = xlWorksheet.UsedRange; V1000_xlRead_List.Clear(); ProgressArgs.xlRead_Total_Units = xlRange.Rows.Count - 1; //for (int i = 2; i <= 21; i++) for (int i = 2; i <= xlRange.Rows.Count; i++) { ProgressArgs.xlRead_Unit = i - 1; ProgressArgs.xlRead_Progress = (byte)(((float)ProgressArgs.xlRead_Unit / ProgressArgs.xlRead_Total_Units) * 100); bwrkReadExcelFile.ReportProgress((int)i); if (bwrkReadExcelFile.CancellationPending) { e.Cancel = true; ProgressArgs.xlRead_Stat = 0x03; bwrkReadExcelFile.ReportProgress(0); return; } V1000_File_Data ParamData = new V1000_File_Data(); if (xlRange.Cells[i, 1] != null && xlRange.Cells[i, 1].Value2 != null) { ParamData.RegAddress = xlRange.Cells[i, 1].Value2.ToString(); } else { ParamData.RegAddress = "0"; } if (xlRange.Cells[i, 2] != null && xlRange.Cells[i, 2].Value2 != null) { ParamData.ParamNum = xlRange.Cells[i, 2].Value2.ToString(); } else { ParamData.ParamNum = "0"; } if (xlRange.Cells[i, 3] != null && xlRange.Cells[i, 3].Value2 != null) { ParamData.ParamName = xlRange.Cells[i, 3].Value2.ToString(); } else { ParamData.ParamName = "0"; } if (xlRange.Cells[i, 4] != null && xlRange.Cells[i, 4].Value2 != null) { ParamData.DefVal = xlRange.Cells[i, 4].Value2.ToString(); } else { ParamData.DefVal = "0"; } if (xlRange.Cells[i, 5] != null && xlRange.Cells[i, 5].Value2 != null) { ParamData.Multiplier = xlRange.Cells[i, 5].Value2.ToString(); } else { ParamData.Multiplier = "1"; } V1000_xlRead_List.Add(ParamData); } ProgressArgs.xlRead_Progress = 100; ProgressArgs.xlRead_Stat = 0x02; e.Result = ProgressArgs.xlRead_Stat; bwrkReadExcelFile.ReportProgress(100); }
public static void getExcelFile() { Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"E:\Json\TEST-DATA.xlsx"); Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1]; Excel.Range xlRange = xlWorksheet.UsedRange; int rowCount = xlRange.Rows.Count; int colCount = xlRange.Columns.Count; for (int i = 2; i <= rowCount; i++) { string fullName = "null"; string address1 = "null"; string address2 = "null"; string city = "null"; string stateCode = "null"; string zipCode = "null"; string countryName = "null"; string productName = "null"; string productType = "null"; string productColor = "null"; string productSize = "null"; string quantity = "null"; string productID = "null"; if (xlRange.Cells[i, 1].Value2 != null) { fullName = xlRange.Cells[i, 1].Value2.ToString().Replace("'", @"\'"); } if (xlRange.Cells[i, 2].Value2 != null) { address1 = xlRange.Cells[i, 2].Value2.ToString().Replace("'", @"\'"); } if (xlRange.Cells[i, 3].Value2 != null) { address2 = xlRange.Cells[i, 3].Value2.ToString().Replace("'", @"\'"); } if (xlRange.Cells[i, 4].Value2 != null) { city = xlRange.Cells[i, 4].Value2.ToString().Replace("'", @"\'"); } if (xlRange.Cells[i, 5].Value2 != null) { stateCode = xlRange.Cells[i, 5].Value2.ToString().Replace("'", @"\'"); } if (xlRange.Cells[i, 6].Value2 != null) { zipCode = xlRange.Cells[i, 6].Value2.ToString().Replace("'", @"\'"); } if (xlRange.Cells[i, 7].Value2 != null) { countryName = xlRange.Cells[i, 7].Value2.ToString().Replace("'", @"\'"); } if (xlRange.Cells[i, 8].Value2 != null) { productName = xlRange.Cells[i, 8].Value2.ToString().Replace("'", @"\'"); } if (xlRange.Cells[i, 9].Value2 != null) { productType = xlRange.Cells[i, 9].Value2.ToString().Replace("'", @"\'"); } if (xlRange.Cells[i, 10].Value2 != null) { productColor = xlRange.Cells[i, 10].Value2.ToString().Replace("'", @"\'"); } if (xlRange.Cells[i, 11].Value2 != null) { productSize = xlRange.Cells[i, 11].Value2.ToString().Replace("'", @"\'"); } if (xlRange.Cells[i, 12].Value2 != null) { quantity = xlRange.Cells[i, 12].Value2.ToString().Replace("'", @"\'"); } if (xlRange.Cells[i, 13].Value2 != null) { productID = xlRange.Cells[i, 13].Value2.ToString().Replace("'", @"\'"); } List <Information> information = new List <Information>(); information.Add(new Information(fullName, address1, address2, city, stateCode, zipCode, countryName, productName, productType, productColor, productSize, quantity, productID)); addData(information, i); } GC.Collect(); GC.WaitForPendingFinalizers(); Marshal.ReleaseComObject(xlRange); Marshal.ReleaseComObject(xlWorksheet); xlWorkbook.Close(); Marshal.ReleaseComObject(xlWorkbook); xlApp.Quit(); Marshal.ReleaseComObject(xlApp); }
/// <summary> /// 为单元格设置公式 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objCell">单元格</param> /// <param name="strFormula">公式</param> public void SetFormula(Excel._Worksheet CurSheet, object objCell, string strFormula) { CurSheet.get_Range(objCell, mValue).Formula = strFormula; }
public List<AccountExcel> readExcel(string sExcelPath) { var lReturn = new List<AccountExcel>(); string valueString = string.Empty; objExcelApp = new Microsoft.Office.Interop.Excel.Application(); objBooks = (Excel.Workbooks)objExcelApp.Workbooks; //Open the workbook containing the address data. objBook = objBooks.Open(sExcelPath, 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); //Get a reference to the first sheet of the workbook. objSheets = objBook.Worksheets; objSheet = (Excel._Worksheet)objSheets.get_Item(1); rngLast = objSheet.get_Range("A1").SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell); long lLastRow = rngLast.Row; long lLastCol = rngLast.Column; for (long rowCounter = 2; rowCounter <= lLastRow; rowCounter++) //FirstRow Has Headers - start at row 2 { if (ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 1]).Value) != "") { var adAccount = new AccountExcel(); adAccount.sCustomerNumber = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 1]).Value); adAccount.sAccountName = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 40]).Value); adAccount.sAddressLine1 = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 2]).Value); adAccount.sAddressLine2 = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 5]).Value); adAccount.sAddressLine3 = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 9]).Value); adAccount.sPostCode = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 15]).Value); adAccount.sTelephone = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 17]).Value); adAccount.sVatNumber = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 18]).Value); adAccount.sCountryCode = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 21]).Value); adAccount.sEmail = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 37]).Value); adAccount.sWeb = "";// ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 38]).Value); adAccount.sKAM = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 31]).Value); adAccount.sRegion = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 24]).Value); lReturn.Add(adAccount); } } //Close the Excel Object objBook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value); objBooks.Close(); objExcelApp.Quit(); Marshal.ReleaseComObject(objSheet); Marshal.ReleaseComObject(objSheets); Marshal.ReleaseComObject(objBooks); Marshal.ReleaseComObject(objBook); Marshal.ReleaseComObject(objExcelApp); objSheet = null; objSheets = null; objBooks = null; objBook = null; objExcelApp = null; GC.GetTotalMemory(false); GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.GetTotalMemory(true); return (lReturn); }
// Program entry point. static void Main(string[] args) { oXL = new Excel.Application(); oXL.Visible = true; oWB = (Excel._Workbook)(oXL.Workbooks.Add(Type.Missing)); oSheet = (Excel._Worksheet)oWB.ActiveSheet; Console.WriteLine("Excel worksheet created."); List<string> phonePages = GetListOfPhonePages(); List<string> specPages = FindPhoneSpecPages(phonePages); int i = 1; Stopwatch stopWatch = new Stopwatch(); stopWatch.Start(); foreach (string specPage in specPages) { // Specs are extracted on multiple threads to bypass // the time it takes to make web requests and load the DOM on the page. new Thread(() => { ExtractSpecs(specPage, i); }).Start(); Random random = new Random(); Thread.Sleep(random.Next(1000,3000)); i++; } for (int j = 0; j < 1000; j++) { Thread.Sleep(1000); TimeSpan ts = stopWatch.Elapsed; // Format and display the TimeSpan value. string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}", ts.Hours, ts.Minutes, ts.Seconds, ts.Milliseconds / 10); Console.WriteLine("RunTime " + elapsedTime); } }
/// <summary> /// Метдо установки темплейта для дальнейшего заполнения /// </summary> /// <param name="template_filename">Имя и путь файла</param> /// <param name="background">Создать в бэкграунде</param> public static void SetTemplate(string template_filename, bool background) { if (!background) excelApp.Visible = true; else excelApp.Visible = false; excelApp.Workbooks.Add(template_filename); workSheet = excelApp.ActiveSheet; }
private void button1_Click(object sender, EventArgs e) { /// check if user has selected a file - if not then warn the user and return /// verify a file has been selected by observing the label lbl_1. if (lbl_1.Text == "Please Select a File") { warningLabel.ForeColor = Color.Red; warningLabel.Text = "Please select a file before continuing"; return; } warningLabel.Text = ""; /// check if user has selected a number of files if (comboBox1.SelectedIndex == -1) { warningLabel.ForeColor = Color.Red; warningLabel.Text = "Please select the number of samples in the file"; return; } warningLabel.Text = ""; string[] filePath = lbl_1.Text.Split('\n'); foreach (string sFileName in filePath) { //https://coderwall.com/p/app3ya/read-excel-file-in-c //Create COM Objects. Create a COM object for everything that is referenced Excel.Application xlApp = new Excel.Application(); xlApp.Visible = false; Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(sFileName); Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1]; Excel.Range xlRange = xlWorksheet.UsedRange; //create COM objects for copy of original document Excel.Application xlAppCopy = new Excel.Application(); xlAppCopy.Visible = false; Excel.Workbook xlWorkbookCopy = xlAppCopy.Workbooks.Add(); Excel._Worksheet xlWorksheetCopy = xlWorkbookCopy.Sheets[1]; //Copy all contents from selected file to the new excel doc int nRows = xlRange.Rows.Count; int nEndDestinationCopy = nRows; string endDestinationCopy = "P" + nEndDestinationCopy.ToString(); // This will only work if all the docs are P columns wide! Excel.Range xlRangeCopy = xlWorksheetCopy.get_Range("A1", endDestinationCopy); xlRangeCopy.Value2 = xlRange.Value2; //create COM objects for output file Excel.Application xlApp2 = new Excel.Application(); xlApp2.Visible = false; Excel.Workbook xlWorkbook2 = xlApp2.Workbooks.Add(); Excel._Worksheet xlWorksheet2 = xlWorkbook2.Sheets[1]; //insert HODS codes xlWorksheet2.Cells[1, 1] = box_0101.Text; xlWorksheet2.Cells[1, 2] = box_0102.Text; xlWorksheet2.Cells[1, 3] = box_0103.Text; xlWorksheet2.Cells[1, 4] = box_0104.Text; xlWorksheet2.Cells[1, 5] = box_0105.Text; xlWorksheet2.Cells[1, 6] = box_0106.Text; xlWorksheet2.Cells[1, 7] = box_0107.Text; xlWorksheet2.Cells[1, 8] = box_0108.Text; //LOOP THROUGH SAMPLES //for loop to repeat for each sample. Can reinstate this later. for (int sample = 1; sample < comboBox1.SelectedIndex + 2; sample++) { //i is the row we're interested in int i = sample + 2; //https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.range.autofilter?view=excel-pia //PUT SAMPLE NAME IN EXCEL ///SORT AND FILTER- i is column number xlRangeCopy.Sort(xlRangeCopy.Columns[i], Excel.XlSortOrder.xlAscending, Type.Missing, Type.Missing, Excel.XlSortOrder.xlAscending, Type.Missing, Excel.XlSortOrder.xlAscending, Excel.XlYesNoGuess.xlYes); xlRangeCopy.AutoFilter(i, "<100"); //COUNT FILTERED ROWS //https://stackoverflow.com/questions/41731714/counting-rows-of-filtered-excel-range-in-c-sharp //NOTE, this includes the first row in the count, so -1 //this counts visible cells Excel.Range xlRange3 = xlRangeCopy.SpecialCells(Excel.XlCellType.xlCellTypeVisible); // -1 because of headers int nFilteredRows = xlRange3.Rows.Count - 1; string sampleLetter = ((char)(sample + 64)).ToString(); //MOVE FILTERED ROWS- will need to change the values in get range to fit the sample and number of filtered genes //-1 becaause to get 4 rows we need A2:A5 and 5-2 is 4-1 int nStartSource = 2; int nEndSource = nStartSource + nFilteredRows - 1; int nStartDestination = 2; int nEndDestination = nStartDestination + nFilteredRows - 1; string startSource = "A" + nStartSource.ToString(); string startDestination = sampleLetter + nStartDestination.ToString(); string endSource = "A" + nEndSource.ToString(); string endDestination = sampleLetter + nEndDestination.ToString(); Excel.Range xlRange2 = xlWorksheet2.get_Range(startDestination, endDestination); Excel.Range sourceRng = xlWorksheetCopy.get_Range(startSource, endSource); xlRange2.Value2 = sourceRng.Value2; xlRange2.RemoveDuplicates(1, Excel.XlYesNoGuess.xlNo); //REMOVE FILTER xlRangeCopy.AutoFilter(i); Marshal.ReleaseComObject(sourceRng); Marshal.ReleaseComObject(xlRange2); Marshal.ReleaseComObject(xlRange3); } //TRANSPOSE //Access range of data in worksheet, and dimensions Excel.Range xlRange2Used = xlWorksheet2.UsedRange; int rowsXlRange2Used = xlRange2Used.Rows.Count; int colsXlRange2Used = xlRange2Used.Columns.Count; //formulate location for transposed range string newRangeStart = "A" + (rowsXlRange2Used + 2).ToString(); string newRangeEnd = ((char)(rowsXlRange2Used + 64)).ToString() + (rowsXlRange2Used + colsXlRange2Used + 1).ToString(); Excel.Range xlRange2Replace = xlWorksheet2.get_Range(newRangeStart, newRangeEnd); //transpose into new location xlWorksheet2.Range[newRangeStart + ":" + newRangeEnd].Value = xlApp2.WorksheetFunction.Transpose(xlRange2Used); //delete columns with untransposed information xlRange2Used.EntireRow.Delete(); //SAVE OUTPUT DOCUMENT string fileName = Path.GetFileName(sFileName); //retreives the filename from the path string directoryName = Path.GetDirectoryName(sFileName); //retreives path of the directory of selected file xlWorkbook2.SaveAs(directoryName + "/" + "output_" + fileName); label_output.Text = "Output file is complete: " + directoryName + "/" + "output_" + fileName; //Close documents without displaying any prompt boxes xlApp2.DisplayAlerts = false; xlApp2.Quit(); xlAppCopy.DisplayAlerts = false; xlAppCopy.Quit(); xlApp.DisplayAlerts = false; xlApp.Quit(); //cleanup GC.Collect(); GC.WaitForPendingFinalizers(); //rule of thumb for releasing com objects: // never use two dots, all COM objects must be referenced and released individually // ex: [somthing].[something].[something] is bad //release com objects to fully kill excel process from running in the background Marshal.ReleaseComObject(xlRange); Marshal.ReleaseComObject(xlRange2Replace); Marshal.ReleaseComObject(xlRange2Used); Marshal.ReleaseComObject(xlRangeCopy); Marshal.ReleaseComObject(xlWorksheet); Marshal.ReleaseComObject(xlWorksheetCopy); Marshal.ReleaseComObject(xlWorksheet2); Marshal.ReleaseComObject(xlWorkbook); Marshal.ReleaseComObject(xlWorkbook2); Marshal.ReleaseComObject(xlWorkbookCopy); Marshal.ReleaseComObject(xlApp); Marshal.ReleaseComObject(xlApp2); Marshal.ReleaseComObject(xlAppCopy); } }
private void button1_Click(object sender, System.EventArgs e) { try { // Instantiate Excel and start a new workbook. objApp = new Microsoft.Office.Interop.Excel.Application(); objBooks = objApp.Workbooks; objBook = objBooks.Add(Missing.Value); objSheets = objBook.Worksheets; objSheet = (Microsoft.Office.Interop.Excel._Worksheet)objSheets.get_Item(1); //Get the range where the starting cell has the address //m_sStartingCell and its dimensions are m_iNumRows x m_iNumCols. range = objSheet.get_Range("A1", Missing.Value); range = range.get_Resize(5, 5); //Create an array. double[,] saRet = new double[5, 5]; //Fill the array. for (long iRow = 0; iRow < 5; iRow++) { for (long iCol = 0; iCol < 5; iCol++) { //Put a counter in the cell. saRet[iRow, iCol] = iRow * iCol * iCol; } } //Set the range value to the array. range.set_Value(Missing.Value, saRet); objApp.Visible = true; objApp.UserControl = true; } catch( Exception theException ) { String errorMessage; errorMessage = "Error: "; errorMessage = String.Concat( errorMessage, theException.Message ); errorMessage = String.Concat( errorMessage, " Line: " ); errorMessage = String.Concat( errorMessage, theException.Source ); MessageBox.Show( errorMessage, "Error" ); } Microsoft.Office.Interop.Excel.Range currentFind = null; Microsoft.Office.Interop.Excel.Range firstFind = null; string A = "16"; // You should specify all these parameters every time you call this method, // since they can be overridden in the user interface. currentFind = objSheet.Cells.Find(A, Type.Missing, Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues, Microsoft.Office.Interop.Excel.XlLookAt.xlWhole, Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows, Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext, false, Type.Missing, Type.Missing); while (currentFind != null) { // Keep track of the first range you find. if (firstFind == null) { firstFind = currentFind; //textBox1.Text = currentFind.get_Address(true, true, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, false, Missing.Value); } // If you didn't move to a new range, you are done. else if (currentFind.get_Address(Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing) == firstFind.get_Address(Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing)) { break; } currentFind.Font.Color = System.Drawing.ColorTranslator.ToOl (System.Drawing.Color.Red); currentFind.Font.Bold = true; currentFind = objSheet.Cells.FindNext(currentFind); } }
private void BtnXuatExcel_Click(object sender, EventArgs e) { //export2Excel(dtgvTonKho, @"D:\", "xuatfileTonKho"); //Tạo đối tượng lưu tệp tin SaveFileDialog fsave = new SaveFileDialog(); //chỉ ra đuôi fsave.Filter = "(Tất cả các tệp) | *.*|(Các tệp excel)|*.xlsx"; fsave.ShowDialog(); //xử lý if (fsave.FileName != "") { //tạo excel app Excel.Application app = new Excel.Application(); //tạo 1 workbook Excel.Workbook wb = app.Workbooks.Add(Type.Missing); //tạo sheet Excel._Worksheet sheet = null; try { //đọc dữ liệu từ Listview export ra file Excel có đinh dạng sheet = wb.ActiveSheet; sheet.Name = "Báo cáo tồn kho"; sheet.Columns.ColumnWidth = 25; sheet.Range[sheet.Cells[1, 1], sheet.Cells[1, dtgvTonKho.Columns.Count]].Merge(); sheet.Cells[1, 1].value = "BÁO CÁO TỒN KHO"; sheet.Cells[1, 1].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //sinh tiêu đề for (int i = 1; i <= dtgvTonKho.Columns.Count; i++) { sheet.Cells[2, i] = dtgvTonKho.Columns[i - 1].HeaderText; } //sinh du lieu for (int i = 0; i < dtgvTonKho.Rows.Count; i++) { for (int j = 0; j < dtgvTonKho.Columns.Count; j++) { if (dtgvTonKho.Rows[i].Cells[j].Value != null) { sheet.Cells[i + 3, j + 1] = dtgvTonKho.Rows[i].Cells[j].Value.ToString(); } } } //ghi lại wb.SaveAs(fsave.FileName); MessageBox.Show("Xuất thành công", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show(ex.Message, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Warning); } finally { app.Quit(); wb = null; } } else { MessageBox.Show("Bạn không chọn tệp tin nào", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Warning); } }
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(); } }
/// <summary> /// Метод открытия уже существующего файла /// </summary> /// <param name="template_filename">Имя и путь файла</param> /// <param name="background">Создать в бэкграунде</param> public static void OpenFile(string filename, bool background) { if (!background) excelApp.Visible = true; else excelApp.Visible = false; excelApp.Workbooks.Open(filename); workSheet = excelApp.ActiveSheet; }
/// <summary> /// 根据Excel表格路径将该表格添加到总表中 /// </summary> /// <param name="strFileName">原表格路径</param> public void OpenExcel(string strFileName) { object missing = System.Reflection.Missing.Value; Excel.Application excel = new Excel.Application();//lauch excel application if (excel == null) { textBoxOfResultForCreateExcel.AppendText("Can't access excel" + " " + strFileName); } else { excel.Visible = false; excel.UserControl = true; // 以只读的形式打开EXCEL文件 Excel.Workbook wb = excel.Application.Workbooks.Open(strFileName, missing, true, missing, missing, missing, missing, missing, missing, true, missing, missing, missing, missing, missing); //取得第一个工作薄 Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets.get_Item(1); //取得总记录行数 (包括标题列) int rowsint = ws.UsedRange.Cells.Rows.Count; //得到行数 //int columnsint = mySheet.UsedRange.Cells.Columns.Count;//得到列数 //取得数据范围区域 (不包括标题列) Excel.Range rng1 = ws.Cells.get_Range("A2", "A" + rowsint); Excel.Range rng2 = ws.Cells.get_Range("B2", "B" + rowsint); Excel.Range rng3 = ws.Cells.get_Range("C2", "C" + rowsint); Excel.Range rng4 = ws.Cells.get_Range("D2", "D" + rowsint); object[,] arry1 = (object[, ])rng1.Value2; //get range's value ,学号 object[,] arry2 = (object[, ])rng2.Value2; //专业 object[,] arry3 = (object[, ])rng3.Value2; //get range's value 姓名 object[,] arry4 = (object[, ])rng4.Value2; //性别 //将arry1赋给一个数组 string classId = arry1[2, 1].ToString().Remove(arry1[2, 1].ToString().Length - 2); //新建Excelapp Excel.Application excelApp = new Excel.Application(); excelApp.Visible = true; //设置不显示确认修改提示 excelApp.DisplayAlerts = false; //得到workbook对象,打开总表 Excel.Workbook excleBook = excelApp.Workbooks.Open(@"D:\总表.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); //指定要操作的sheet Excel._Worksheet excleSheet = (Excel.Worksheet)excelApp.Worksheets.get_Item(1); //获取总记录行数(包括标题列) int rowsCount = excleSheet.UsedRange.Cells.Rows.Count; //将分表的记录写入到总表中去 for (int i = 1; i <= arry1.Length; i++) { if (arry1[i, 1] == null) { excelApp.Cells[rowsCount + i, 1] = ""; } else { excelApp.Cells[rowsCount + i, 1] = arry1[i, 1].ToString(); } if (arry2[i, 1] == null) { excelApp.Cells[rowsCount + i, 2] = ""; } else { excelApp.Cells[rowsCount + i, 2] = arry2[i, 1].ToString(); } if (arry3[i, 1] == null) { excelApp.Cells[rowsCount + i, 3] = ""; } else { if (arry3[i, 1].ToString().EndsWith("0001")) { excelApp.Cells[rowsCount + i, 3] = arry3[i, 1].ToString().Remove(arry3[i, 1].ToString().Length - 4); } else { excelApp.Cells[rowsCount + i, 3] = arry3[i, 1].ToString(); } } if (arry4[i, 1] == null) { excelApp.Cells[rowsCount + i, 4] = ""; } else { excelApp.Cells[rowsCount + i, 4] = arry4[i, 1].ToString(); } excelApp.Cells[rowsCount + i, 5] = classId; } //for (int i = 1; i <= rowsint - 1; i++) // for (int i = 1; i <= rowsint - 2; i++) // { // arry[i - 1, 0] = arry1[i, 1].ToString(); // arry[i - 1, 1] = arry2[i, 1].ToString(); // arry[i - 1, 2] = arry3[i, 1].ToString(); // arry[i - 1, 3] = arry4[i, 1].ToString(); // } // string a = ""; // for (int i = 0; i <= rowsint - 3; i++) // { // a += arry[i, 0] + "|" + arry[i, 1] + "|" + arry[i, 2] + "|" + arry[i, 3] + "\n"; // } // this.label1.Text = a; //关闭打开的表 wb.Close(false, Missing.Value, Missing.Value); excleBook.Close(true, Missing.Value, Missing.Value); //把excel,workbook,worksheet设置为null,防止内存泄漏 wb = null; excleBook = null; ws = null; excleBook = null; //程序退出 excel.Quit(); excelApp.Quit(); excel = null; excelApp = null; GC.Collect(); } }
private void tmrData_Tick(object sender, EventArgs e) { int i; uint recLength; recLength = 25; // Get the Live data, to show in gauge txBuf[0] = (byte)CMD.GET_LIVE_DATA; txBuf[1] = 2; //USB.SendReceivePacket(txBuf, 2, rxBuf, ref recLength); string port = cbxPorts.SelectedItem.ToString(); if (port == "") return; System.IO.Ports.SerialPort myPort = new System.IO.Ports.SerialPort(port); if (myPort.IsOpen == false) //if not open, open the port myPort.Open(); //do your work here if (myPort.IsOpen == false) //if not open, open the port return; String data; data = myPort.ReadLine(); myPort.Close(); string [] dht=data.Split(','); //s.dataPtr = rxBuf[18]; //i = 19; //convertData(ref currData, ref i); //gTemp.Value = currData.temp; //gPressure.Value = currData.pressure; //gHumid.Value = currData.humidity; if (dht.Length != 3) return; gHumid.Value = float.Parse(dht[0]); gTemp.Value = float.Parse(dht[1]); gPressure.Value = int.Parse(dht[2]); if (stt == 0) { //Start Excel and get Application object. oXL = new Excel.Application(); oXL.Visible = true; //Get a new workbook. oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value)); oSheet = (Excel._Worksheet)oWB.ActiveSheet; } //Add table headers going cell by cell. oSheet.Cells[stt + 1, 1] = gTemp.Value.ToString(); oSheet.Cells[stt + 1, 2] = gPressure.Value.ToString(); oSheet.Cells[stt + 1, 3] = gHumid.Value.ToString(); //oSheet.Cells[1, 4] = "Salary"; DateTime timeNow = DateTime.Now; w[stt].humidity = gHumid.Value; w[stt].temp = gTemp.Value; w[stt].pressure = gPressure.Value; w[stt++].time = timeNow; if (stt == 999) stt = 0; Redraw(0); goLive = false; if (goLive) // Only load data to w if graph shows live data from PIC { recLength = 64; txBuf[0] = (byte)CMD.GET_FIRST_DATA; txBuf[1] = 2; USB.SendReceivePacket(txBuf, 2, rxBuf, ref recLength); loadData(10); for (i = 0; i < 5; i++) { txBuf[0] = (byte)CMD.GET_NEXT_DATA; txBuf[1] = 2; USB.SendReceivePacket(txBuf, 2, rxBuf, ref recLength); loadData(10); } txBuf[0] = (byte)CMD.GET_CURR_DATA; txBuf[1] = 2; USB.SendReceivePacket(txBuf, 2, rxBuf, ref recLength); loadData((int)s.dataPtr); } }
public static List <Question> ReadFileGerman(string excelFile) { List <Question> qList = new List <Question>(); // open excel file Excel.Application excel = new Excel.Application(); Excel.Workbook workbook = excel.Workbooks.Open(excelFile); Excel._Worksheet worksheet = workbook.Sheets[1]; int rowCount = worksheet.UsedRange.Rows.Count; int colCount = worksheet.UsedRange.Columns.Count; // find data indexes Main main = new Main(); main.ShowOutput("Finding data indexes....", false); int questionTextIndex = 0; int nameIndex = 0; int categoryIndex = 0; int answerIndex = 0; int[] optionIndexes = new int[] { 0, 0, 0, 0 }; for (int col = 1; col <= colCount; col++) { string v = worksheet.Cells[1, col].Value.ToString(); if (v == "Fragetext") { questionTextIndex = col; } if (v == "Themenname") { categoryIndex = col; } if (v == "Korrekte Antwort(en)") { answerIndex = col; } if (v == "ID") { nameIndex = col; } for (int i = 1; i <= 4; i++) { if (v == "Antwort " + i.ToString()) { optionIndexes[i - 1] = col; } } } // check data indexes if (questionTextIndex > 0 && optionIndexes[0] > 0 && optionIndexes[1] > 0 && optionIndexes[2] > 0 && optionIndexes[3] > 0 && answerIndex > 0 && categoryIndex > 0 && nameIndex > 0) { main.ShowOutput("OK"); } else { main.ShowOutput("Error. Indexes not found."); workbook.Close(); excel.Quit(); return(qList); } // get question data for (int row = 2; row <= rowCount; row++) { Question q = new Question(); q.name = JwString.Clean(worksheet.Cells[row, nameIndex].Value.ToString()); q.category = JwString.Clean(worksheet.Cells[row, categoryIndex].Value.ToString()); q.text = JwString.Clean(worksheet.Cells[row, questionTextIndex].Value.ToString()); q.type = "multichoice"; int answer = Int32.Parse(worksheet.Cells[row, answerIndex].Value); for (int i = 1; i <= 4; i++) { Option o = new Option(); o.text = JwString.Clean(worksheet.Cells[row, optionIndexes[i - 1]].Value.ToString()); o.feedback = ""; o.grade = 0; if (answer == i) { o.grade = 100; } q.options.Add(o); } qList.Add(q); } // close excel and return questions workbook.Close(); excel.Quit(); return(qList); }
public void SetSheet( bool bWrite=false ) { if( bWrite ) { excelApp.Workbooks.Add(); workSheet = excelApp.ActiveSheet; excelApp.Visible = true; } else { Excel.Workbook xlWorkBook = excelApp.Workbooks.Open( FileName, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); workSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); } }
private void buttonDupCheck_Click(object sender, EventArgs e) { fixDataSetNullsToEmptyStrings(); if (xlWorkbook == null) { xlApp = new Excel.Application(); OpenFileDialog ofd = new OpenFileDialog(); ofd.Filter = "Excel Documents (*.xlsx)|*.xlsx"; ofd.ShowDialog(); xlWorkbook = xlApp.Workbooks.Open(ofd.FileName, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); xlWorksheet = (Excel._Worksheet)xlWorkbook.Sheets[1]; } int checkStart = checkBoxDupeCheckAll.Checked ? 0 : Convert.ToInt32(textDupeCheckRowFrom.Text) - 1; int checkEnd = checkBoxDupeCheckAll.Checked ? dataCompanyList.Rows.Count - 1 : Convert.ToInt32(textDupeCheckRowTo.Text) - 1; fixDataSetNullsToEmptyStrings(); finalDuplicateCheck(checkStart, checkEnd); }
private static string OpenExcel(ExcelInfo excelInfo, bool isOpenSheet = true) { Console.WriteLine("Open File:【{0}】", excelInfo.FilePath); if (!File.Exists(excelInfo.FilePath)) { return $"文件【{excelInfo.FilePath}】不存在"; } _objExcel = new Excel.Application { Visible = false, DisplayAlerts = false, AlertBeforeOverwriting = false }; _objBooks = _objExcel.Workbooks; if (excelInfo.FilePath.Equals(String.Empty) || !File.Exists(excelInfo.FilePath)) { _objBook = _objBooks.Add(ObjOpt); } else { _objBook = _objBooks.Open(excelInfo.FilePath, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt); } if (isOpenSheet) { _objSheet = OpenSheet(excelInfo); if (_objSheet == null) { return "没有指定页签"; } } return ""; }
/// <summary> /// loading values from header of excel into comboboxes /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void menu_open_xlsx_Click(object sender, RoutedEventArgs e) { try { var excelFile = new OpenFileDialog { DefaultExt = ".xlsx", Filter = "(.xlsx)|*.xlsx" }; Nullable<bool> result = excelFile.ShowDialog(); if (result != true) return; RtbProgress.AppendText("Loading Excell file and checking if APIs services are available.\r...\r"); DoEvents(); CbxUlice.Items.Clear(); CbxCp.Items.Clear(); CbxCo.Items.Clear(); CbxObec.Items.Clear(); CbxPsc.Items.Clear(); _excelPath = excelFile.FileName; _excelApp = new Excel.Application(); _excelWorkBook = _excelApp.Workbooks.Open(_excelPath); _excelWorkSheet = _excelWorkBook.Sheets[1]; // counting rows and columns _rowCount = _excelWorkSheet.UsedRange.Rows.Count; _columnCount = _excelWorkSheet.UsedRange.Columns.Count; // add max rows into interval TxtInterval.Text = "[1 - " + _rowCount + "]"; // Adding selections to comboboxes for (int i = 1; i < _excelWorkSheet.UsedRange.Columns.Count + 1; i++) { CbxUlice.Items.Add(_excelWorkSheet.Cells[1, i].Value); CbxCp.Items.Add(_excelWorkSheet.Cells[1, i].Value); CbxCo.Items.Add(_excelWorkSheet.Cells[1, i].Value); CbxObec.Items.Add(_excelWorkSheet.Cells[1, i].Value); CbxPsc.Items.Add(_excelWorkSheet.Cells[1, i].Value); } // adding one null slot to comboboxes CbxUlice.Items.Add(""); CbxCp.Items.Add(""); CbxCo.Items.Add(""); CbxObec.Items.Add(""); CbxPsc.Items.Add(""); EnableObjects(true); MenuOpen.IsEnabled = false; RtbProgress.AppendText(_excelPath + " is loaded!\r"); RtbProgress.AppendText("For new excel file RESTART THE APP please.\r"); RtbProgress.ScrollToEnd(); RtbProgress.AppendText("---------------\r"); CheckIfApisAreOnline(); RtbProgress.AppendText("---------------\r"); } catch (Exception ex) { MessageBox.Show("A handled exception just occurred: " + ex.Message, "Exception", MessageBoxButton.OK, MessageBoxImage.Warning); } }
public static InnerResult GetExcelFile(string file) { //Create COM Objects. Create a COM object for everything that is referenced var xlApp = new Excel.Application(); var workbooks = xlApp.Workbooks; Excel.Workbook xlWorkbook; try { xlWorkbook = workbooks.Open(file, null, ReadOnly: true); } catch (Exception e) { MessageBox.Show("Exception Message: " + e.Message); if (e.InnerException != null) { MessageBox.Show("InnerException Message: " + e.InnerException.Message); } MessageBox.Show("Exception Trace : " + e.StackTrace); return(new InnerResult { Success = false, Message = "Error on open file." }); } var sheets = xlWorkbook.Sheets; Console.WriteLine(sheets.Count); Excel._Worksheet xlWorksheet = null; if (sheets.Count == 1) { xlWorksheet = xlWorkbook.Sheets[1]; } else { for (int i = 1; i <= sheets.Count; i++) { Excel._Worksheet tmp = sheets[i]; if (tmp.Name != "PO Upload Download Template-APP") { continue; } xlWorksheet = tmp; break; } } //check if sheet null if (xlWorksheet == null && sheets.Count > 0) { //try first one xlWorksheet = xlWorkbook.Sheets[1]; //forgot quit //xlWorkbook.Close(false); //xlApp.Quit(); //return new InnerResult { Success = false, Message = Wording.CantFindRightColumns }; } Excel.Range xlRange = xlWorksheet.UsedRange; int rowCount = xlRange.Rows.Count; int colCount = xlRange.Columns.Count; Debug.WriteLine($"row={rowCount} col={colCount}"); #region get skip var target = xlWorksheet.UsedRange.Find("Issue Date:"); if (target == null) { xlWorkbook.Close(false); xlApp.Quit(); return(new InnerResult { Success = false, Message = Wording.CantFindRightColumns }); } var skip = target.Row; #endregion #region check header //Check header var heads = xlWorksheet.Range[target, xlWorksheet.Cells[skip, Models.AdidasHeader.Length]]; var check = CheckHeader(heads, Models.AdidasHeader); if (!check.Success) { xlWorkbook.Close(false); xlApp.Quit(); return(new InnerResult { Success = false, Message = check.Message }); } #endregion #region toObject object[,] data = xlRange.Value2; DataTable dt = new DataTable(); // Create new Column in DataTable for (int cCnt = 1; cCnt <= colCount; cCnt++) { var column = new DataColumn { DataType = Type.GetType("System.String"), ColumnName = cCnt.ToString() }; dt.Columns.Add(column); // Create row for Data Table for (int rCnt = skip + 1; rCnt <= rowCount; rCnt++) { string cellVal; if (Models.DateTimeColume.Contains(cCnt)) { try { cellVal = data[rCnt, cCnt] != null ? DateTime.FromOADate(Convert.ToDouble(data[rCnt, cCnt])).ToShortDateString() : null; } catch (Exception) { cellVal = data[rCnt, cCnt]?.ToString(); } } else { cellVal = data[rCnt, cCnt]?.ToString(); } DataRow row; // Add to the DataTable if (cCnt == 1) { row = dt.NewRow(); row[cCnt.ToString()] = cellVal; dt.Rows.Add(row); } else { row = dt.Rows[rCnt - 1 - skip]; row[cCnt.ToString()] = cellVal; } } } #endregion //close and release xlWorkbook.Close(false); workbooks.Close(); //quit and release xlApp.Quit(); Marshal.ReleaseComObject(xlApp); Console.WriteLine(@"Load Success"); return(new InnerResult { Success = true, Data = dt }); }
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; }
static void Main(string[] args) { Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(@"D:\Github\SSENSE_Price_checker\data.xlsx"); Excel._Worksheet xlWorksheet = xlWorkBook.Sheets[1]; Excel.Range xlRange = xlWorksheet.UsedRange; int rowCount = xlRange.Rows.Count; int colCount = xlRange.Columns.Count; List <bontique> buylist = new List <bontique>(); for (int i = 1; i <= rowCount; i++) { bontique tmp = new bontique() { row = i, url = xlRange.Cells[i, 1].Text, lowest = Convert.ToDouble(xlRange.Cells[i, 2].Text), highest = Convert.ToDouble(xlRange.Cells[i, 3].Text), last = Convert.ToDouble(xlRange.Cells[i, 4].Text), size = xlRange.Cells[i, 5].Text }; buylist.Add(tmp); } IWebDriver driver = new FirefoxDriver(@"D:\Github\SSENSE_Price_checker"); foreach (bontique tmp1 in buylist) { /* * using (WebClient client = new WebClient()) // WebClient class inherits IDisposable * { * System.Net.ServicePointManager.SecurityProtocol = System.Net.SecurityProtocolType.Tls12; * string htmlCode = client.DownloadString(tmp1.url); * int position = htmlCode.IndexOf("class=\"price\">"); * } */ driver.Url = tmp1.url; IWebElement pricetag = driver.FindElement(By.XPath("//span[@class='price']")); string ooo = pricetag.GetAttribute("innerHTML").ToString(); double price = Convert.ToDouble(ooo.Replace(" CAD", "").Replace("$", "")); //check if size exist if (driver.FindElements(By.XPath("//select[@id='size']")).Count() != 0) { IWebElement sizeelement = driver.FindElement(By.XPath("//select[@id='size']")); string sizeString = sizeelement.Text; string[] sizeList = sizeString.Split( new[] { "\r\n", "\r", "\n" }, StringSplitOptions.None ); //only xx left, sold out, normal foreach (string tmpsize in sizeList) { if (tmpsize.Contains(tmp1.size)) { if (tmpsize.Contains("Out")) { //sold out send notification email(tmp1, 3); } else if (tmpsize.Contains("left")) { //only serveral left buybuybuy! email(tmp1, 2); } else { //do nothing } } } } if (driver.FindElements(By.XPath("//span[@class='text-no-transform']")).Count() != 0) { email(tmp1, 2); } xlRange.Cells[tmp1.row, 4].Value = price; if (price < tmp1.lowest) { xlRange.Cells[tmp1.row, 2].Value = price; tmp1.lowest = price; email(tmp1, 1); } if (price > tmp1.highest) { xlRange.Cells[tmp1.row, 3].Value = price; } } driver.Close(); driver.Quit(); xlWorkBook.Save(); xlWorkBook.Close(0); xlApp.Quit(); }
public void ConvertToCsv() { string cellValu = ""; string tempValu = ""; Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath + fileName); // + "VizientImportTest");//"Terumo.xls"); Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1]; Excel.Range xlRange = xlWorksheet.UsedRange; rowCount = xlRange.Rows.Count; colCount = xlRange.Columns.Count; if (trace) { lm.Write("Convert.ConvertToCsv"); } try { for (int r = 1; r <= rowCount; r++) { for (int c = 1; c <= colCount; c++) { //new line if (c == 1) { Console.Write("\r\n"); if (cellValu.Length > 0) { cellValu += Environment.NewLine; } } //write the value to the console if (xlRange.Cells[r, c] != null && xlRange.Cells[r, c].Value2 != null) { tempValu = RemoveCommas(xlRange.Cells[r, c].Value2.ToString()); //Console.WriteLine(xlRange.Cells[r, c].Value2.ToString() + "\t"); //tempValu = RemoveCommas(xlRange.Cells[r, c].Value2.ToString()); //if (tempValu.Length == 0) // tempValu = "x"; //cellValu += tempValu + "|"; } else { tempValu = "."; } Console.WriteLine(tempValu + "\t"); tempValu = RemoveCommas(tempValu); cellValu += tempValu + "||"; } } fileName = "ImportFile.csv"; File.WriteAllText(filePath + fileName, cellValu); xlApp.Workbooks.Close(); GC.Collect(); //GC.WaitForPendingFinalizers(); } catch (Exception ex) { lm.Write("Convert.ConvertToCsv: " + ex.Message); } }
/** * if no log file exists this method creates new spreadsheet **/ private bool CreateWorkBook() { m_WorkBook = (Excel._Workbook)(m_XLApp.Workbooks.Add(Missing.Value)); m_Sheet = (Excel._Worksheet) m_WorkBook.ActiveSheet; if (m_WorkBook == null || m_Sheet == null) { return false; } m_Sheet.Cells[1, 1] = "Date"; m_columnsMap = new Dictionary<string, int>(); foreach (KeyValuePair<string, ProjectData> entry in m_currProjectWarnings) { AddNewProjectColumn(entry.Key); } //addColumn("Total"); ApplyStyle(); return true; }
public static bool DBDataToXls(string sqlstr, string path) { // Excel object references. Excel.Application m_objExcel = null; Excel.Workbooks m_objBooks = null; Excel._Workbook m_objBook = null; Excel.Sheets m_objSheets = null; Excel._Worksheet m_objSheet = null; Excel.Range m_objRange = null; Excel.Font m_objFont = null; //Excel.QueryTables m_objQryTables = null; //Excel._QueryTable m_objQryTable = null; // Frequenty-used variable for optional arguments. object m_objOpt = System.Reflection.Missing.Value; // Paths used by the sample code for accessing and storing data. object m_strSampleFolder = "C:\\ExcelData\\"; m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt)); m_objSheets = (Excel.Sheets)m_objBook.Worksheets; m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1)); OleDbConnection conn = new OleDbConnection(DBHelper.OleConnectionString); OleDbCommand cmd = new OleDbCommand(sqlstr, conn); OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); DataTable dt = null; int fieldCount_; int rowsCount_; object[] objHeader; object[,] objData; da.Fill(ds); dt = ds.Tables[0]; fieldCount_ = dt.Columns.Count; rowsCount_ = dt.Rows.Count; if (rowsCount_ == 0) { return(false); } m_objRange = m_objSheet.get_Range(m_objSheet.Cells[1, 1], m_objSheet.Cells[1, fieldCount_]); objHeader = new string[fieldCount_]; for (int i = 0; i < fieldCount_; i++) { objHeader[i] = dt.Columns[i].ColumnName; } m_objRange.set_Value(m_objOpt, objHeader); m_objFont = m_objRange.Font; m_objFont.Bold = true; m_objFont.Size = 9; m_objFont.Name = "MS Sans Serif"; //object[rowscount,columnscount] objData = new object[rowsCount_, fieldCount_]; for (int i = 0; i < rowsCount_; i++) { for (int j = 0; j < fieldCount_; j++) { objData[i, j] = dt.Rows[i][j]; } } m_objRange = m_objSheet.get_Range("A2", m_objOpt); m_objRange = m_objRange.get_Resize(rowsCount_, fieldCount_); m_objRange.NumberFormatLocal = "@"; m_objRange.set_Value(m_objOpt, objData); m_objFont = m_objRange.Font; m_objFont.Size = 9; m_objFont.Name = "MS Sans Serif"; m_objBook.SaveAs(path, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook.Close(false, m_objOpt, m_objOpt); m_objExcel.Quit(); return(true); }
private void buttonSearch_Click(object sender, EventArgs e) { /* initiate duplicate checker with excel file */ if (xlWorkbook == null) { xlApp = new Excel.Application(); OpenFileDialog ofd = new OpenFileDialog(); ofd.Filter = "Excel Documents (*.xlsx)|*.xlsx"; ofd.ShowDialog(); xlWorkbook = xlApp.Workbooks.Open(ofd.FileName, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); xlWorksheet = (Excel._Worksheet)xlWorkbook.Sheets[1]; } /* Save settings */ SaveSettings(); checkListCategories.Items.Clear(); statusBotStrip.Text = "Status: Searching"; string[] countriesArray = new string[]{"United States", "Singapore", "Australia", "New Zealand"}; if (searchCity.Lines.Length == 0) searchCity.Text = countriesArray[searchCountry.SelectedIndex]; for (int locationline = 0; locationline < searchCity.Lines.Length; locationline++) { for (int categoryline = 0; categoryline < searchCategory.Lines.Length; categoryline++) { if (searchGoogle.Checked) { /* Conduct first page search */ string searchCritera = generateSearchQuery(categoryline, locationline); statusBotStrip.Text = "Status: Searching " + searchCritera + " on Google"; searchCritera = searchCritera.Replace(" ", "+"); string json = getMapList(searchCritera); var jarray = JsonConvert.DeserializeObject<RootObjectPlaces>(json); if (jarray.status == "OK") { /* Populate the company list */ populateGoogleCompanies(jarray, xlWorksheet); /* Conduct next page search until reached end of search */ string nextPageToken = jarray.next_page_token; while (nextPageToken != null) { json = getMapList(searchCritera + "&pagetoken=" + nextPageToken); jarray = JsonConvert.DeserializeObject<RootObjectPlaces>(json); /* Populate again */ populateGoogleCompanies(jarray, xlWorksheet); nextPageToken = jarray.next_page_token; } } else if (jarray.status == "ZERO_RESULTS") textDebugger.AppendText("Google found no results searching:" + searchCritera + Environment.NewLine); else textDebugger.AppendText("Google Search Error: " + jarray.error_message + " when searching: " + searchCritera + Environment.NewLine); } saveDataSet(); if (searchYelp.Checked) { string searchCriteria = searchCategory.Lines[categoryline]; string search_Country = searchCountry.Text; string searchArea = ""; searchArea = generateSearchQueryArea(locationline); statusBotStrip.Text = "Status: Searching " + searchArea + " " + searchCriteria + " on Yelp"; var yelp = new Yelp(Config.Options); var searchOpt = new SearchOptions(); searchOpt.GeneralOptions = new GeneralOptions() { term = searchCriteria, radius_filter = 50000, category_filter = "homeservices", sort = 1 }; searchOpt.LocationOptions = new LocationOptions() { location = searchArea }; searchOpt.LocaleOptions = new LocaleOptions() { cc = search_Country }; var task = yelp.Search(searchOpt); int pages = (int)Math.Ceiling(task.Result.total / 20.0); for (int p = 1; p <= pages; p++) { for (int i = 0; i <= 19; i++) { try { Business business = task.Result.businesses[i]; string companyName = business.name; string companyAddress = String.Join(" ", business.location.address); string companyCity = business.location.city == null ? "" : business.location.city; string companyState = business.location.state_code == null ? "" : business.location.state_code; string companyZip = business.location.postal_code == null ? "" : business.location.postal_code; string companyCountry = business.location.country_code == null ? "" : business.location.country_code; string companyPhone = business.phone == null ? "" : fixPhoneNumberFormat(business.phone); if (!checkIfExistInCurrentList(companyName, companyPhone, "No website yet", "No email on 1st check")) { string companyWebsite = findYelpCompanyWebsite(task.Result.businesses[i].url); bool companyHasPicturesOrPersonalWebsite = true; if (companyWebsite == "No company website") { companyHasPicturesOrPersonalWebsite = doesYelpCompanyHavePictures(); companyWebsite = task.Result.businesses[i].url; } if (companyHasPicturesOrPersonalWebsite) { List<string> companyCategories = new List<string>(); foreach (string[] category in task.Result.businesses[i].categories) companyCategories.Add(category[0]); string companyHouzzSearch = generateHouzzSearchLink(replaceCompanyNamePTE(companyName), searchArea.Remove(0, searchArea.LastIndexOfAny(new char[] { ',', ' ' }) + 1).Replace(" ", ""), true); AddtoList(companyName, companyAddress, companyCity, companyState, companyZip, companyCountry, companyPhone, "No email", "", companyWebsite, companyHouzzSearch, companyCategories); } } } catch { } } var searchOptions = new SearchOptions(); searchOptions.GeneralOptions = new GeneralOptions() { term = searchCriteria, offset = (p * 20), radius_filter = 50000, category_filter = "homeservices", sort = 1 //distance }; searchOptions.LocationOptions = new LocationOptions() { location = searchArea }; searchOptions.LocaleOptions = new LocaleOptions() { cc = search_Country }; task = yelp.Search(searchOptions); } } saveDataSet(); if (searchFacebook.Checked) { string searchCriteria = searchCategory.Lines[categoryline]; string searchArea = generateSearchQueryArea(locationline); statusBotStrip.Text = "Status: Searching " + searchArea + " " + searchCriteria + " on Facebook"; string[] jsonFacebookSearchResults = new string[5]; jsonFacebookSearchResults[1] = getFacebookSearchList(searchCriteria, searchArea, "page", false); jsonFacebookSearchResults[2] = getFacebookSearchList(searchCriteria, searchArea, "page", true); jsonFacebookSearchResults[3] = getFacebookSearchList(searchCriteria, searchArea, "place", false); jsonFacebookSearchResults[4] = getFacebookSearchList(searchCriteria, searchArea, "place", true); for (int i = 1; i <= 4; i++) { var jarray = JsonConvert.DeserializeObject<RootObjectFacebook>(jsonFacebookSearchResults[i]); if( jarray.data != null & jarray.data.Count > 0 ) populateFacebookCompanies(jarray); else if( jarray.error != null) textDebugger.AppendText("Facebook search error: " + jarray.error.message + Environment.NewLine); } } saveDataSet(); if (searchYellowPages.Checked) { /* Conduct first page search */ string searchCriteria = searchCategory.Lines[categoryline].Replace(" ", "+"); string search_Country = searchCountry.Text; string searchArea = generateSearchQueryArea(locationline); statusBotStrip.Text = "Status: Searching " + searchArea + " " + searchCriteria + " on YellowPages"; for (int i = 1; i <= 30; i++) /* 30 pages even if they do not reach 30 */ { string json = getYPList(searchCriteria, searchArea, i); var jarray = JsonConvert.DeserializeObject<RootObjectYP>(json); if (jarray.searchResult.metaProperties.errorCode == "") { // Populate the company list if (jarray.searchResult.metaProperties.listingCount > 0) { populateYPCompanies(jarray, xlWorksheet); } else { textDebugger.AppendText("Error on YP page " + i.ToString() + ": No results found searching: " + searchArea + " " + searchCriteria + Environment.NewLine); break; } } else textDebugger.AppendText("Error on YP page " + i.ToString() + ": " + jarray.searchResult.metaProperties.message + Environment.NewLine); } } saveDataSet(); if( searchFactual.Checked ) { string searchCriteria = searchCategory.Lines[categoryline]; string search_Country = searchCountry.Text; string search_City = "", search_State = ""; Factual factual = new Factual(apiFactualKey, apiFactualSecret); Query q = new Query().SearchExact(searchCriteria); if (search_Country != "US") { q.And(q.Field("country").Equal(search_Country), q.Limit(50)); statusBotStrip.Text = "Status: Searching" + search_Country + " " + searchCriteria + " on Factual"; } if (search_Country == "US") { q.And(q.Field("country").Equal(search_Country), q.Field("region").Equal(search_State), q.Field("locality").Equal(search_City), q.Limit(50)); search_City = searchCity.Lines[locationline]; search_State = searchState.Text; statusBotStrip.Text = "Status: Searching " + search_Country + " " + search_City + " " + searchCriteria + " on Factual"; } /* if (searchCategoryFilters != "") q.Field("category_labels").Includes(searchCategoryFilters); */ int page = 1; try { var json = factual.Fetch("places", q); var jarray = JsonConvert.DeserializeObject<RootObjectFactual>(json); while (jarray.status == "ok" & jarray.response.data.Count > 0) { /* Populate the company list */ foreach (Datum item in jarray.response.data) { string companyName = item.name; string companyAddress = item.address == null ? "" : item.address; string companyCity = item.locality == null ? "" : item.locality; string companyState = item.region == null ? "" : item.region; string companyCountry = item.country == null ? "" : item.country; string companyZip = item.postcode == null ? "" : item.postcode; string companyPhone = item.tel == null ? "" : fixPhoneNumberFormat(item.tel); string companyEmail = item.email == null ? "no email" : item.email; string companyWebsite = item.website; string companyHouzzSearch = generateHouzzSearchLink(replaceCompanyNamePTE(companyName), companyState, true); List<string> companyCategories = item.category_labels == null ? new List<string>() : item.category_labels[0]; //textDebugger.AppendText(companyName + Environment.NewLine); AddtoList(companyName, companyAddress, companyCity, companyState, companyZip, companyCountry, companyPhone, companyEmail, "", companyWebsite, companyHouzzSearch, companyCategories); } page++; int pageOffset = (page - 1) * 50; Query qnew = new Query().SearchExact(searchCriteria); if ( searchCountry.Text == "US" || searchCountry.Text == "AU") { json = factual.Fetch("places", new Query() .SearchExact(searchCriteria) .Field("country").Equal(search_Country) .Field("region").Equal(search_State) .Field("locality").Equal(search_City) .Limit(50) .Offset(pageOffset)); } else if (searchCountry.Text != "US" & searchCountry.Text != "AU") { json = factual.Fetch("places", new Query() .SearchExact(searchCriteria) .Field("country").Equal(search_Country) .Limit(50) .Offset(pageOffset)); } jarray = JsonConvert.DeserializeObject<RootObjectFactual>(json); } } catch (FactualApiException ex) { textDebugger.AppendText("Factual Requested URL: " + ex.Url + Environment.NewLine); textDebugger.AppendText("Factual Error Status Code: " + ex.StatusCode + Environment.NewLine); ; textDebugger.AppendText("Factual Error Response Message: " + ex.Response + Environment.NewLine); ; if (ex.StatusCode.ToString().Contains("RequestedRangeNotSatisfiable")) textDebugger.AppendText("Factual reached end of results on page " + (page - 1).ToString() + Environment.NewLine); } } saveDataSet(); if( searchSensis.Checked ) { var searcher = new SsapiSearcher(searchEndPoint, apiSensisKey); // Perform a search and check the response var searchResponse = searcher.SearchFor(searchCategory.Text, searchCity.Text, searchState.Text, 1); /* page 1 */ if (searchResponse.code < 200 || searchResponse.code > 299) textDebugger.AppendText("Search failed - Error " + searchResponse.code + ": " + searchResponse.message + Environment.NewLine); else { textDebugger.AppendText("Total results found: " + searchResponse.totalResults.ToString() + Environment.NewLine); textDebugger.AppendText("Total pages: " + searchResponse.totalPages.ToString() + Environment.NewLine); for (int page = 1; page < searchResponse.totalPages; page++) { // Display the results foreach (var result in searchResponse.results) { string companyWebsite = getSensisContactComponents(result.primaryContacts, "URL"); if (companyWebsite != "") { string companyName = result.name; string companyAddress = "", companyCity = "", companyState = "", companyZip = ""; if (result.primaryAddress != null) { companyAddress = result.primaryAddress.addressLine == null ? "" : result.primaryAddress.addressLine; companyCity = result.primaryAddress.suburb == null ? "" : result.primaryAddress.suburb; companyState = result.primaryAddress.state == null ? "" : result.primaryAddress.state; companyZip = result.primaryAddress.postcode == null ? "" : result.primaryAddress.postcode; } string companyCountry = "AU"; string companyEmail = getSensisContactComponents(result.primaryContacts, "EMAIL"); string companyPhone = fixPhoneNumberFormat(getSensisContactComponents(result.primaryContacts, "PHONE")); if (companyPhone == "") companyPhone = fixPhoneNumberFormat(getSensisContactComponents(result.primaryContacts, "MOBILE")); string companyContactUs = companyEmail.Contains("@") ? "" : getSensisExternalLinksComponents(result.externalLinks, "Contact Us"); string companyHouzzSearch = generateHouzzSearchLink(replaceCompanyNamePTE(companyName), companyState, true); List<string> companyCategories = result.categories == null ? new List<string>() : new List<string>(new string[] { result.categories[0].name }); //textDebugger.AppendText(companyName + companyAddress + companyCity + companyState + companyZip + companyEmail + companyPhone + companyWebsite + Environment.NewLine); AddtoList(companyName, companyAddress, companyCity, companyState, companyZip, companyCountry, companyPhone, companyEmail, companyContactUs, companyWebsite, companyHouzzSearch, companyCategories); } } searchResponse = searcher.SearchFor(searchCategory.Text, searchCity.Text, searchState.Text, page); } } } /* At the end of each category, we save the data found into an xml file*/ saveDataSet(); } } statusBotStrip.Text = "Status: Done"; /* Save settings */ SaveSettings(); }
private void exportexcel() { DateTime dt = DateTime.Now; string Name = cbthongke.Text + " (" + dt.Hour + "h" + dt.Minute + " " + dt.Day + "." + dt.Month + "." + dt.Year + ")"; string LinkSave = System.Windows.Forms.Application.StartupPath + @"\..\Reports\"; // tạo đối tượng SaveFileDialog fsave = new SaveFileDialog(); fsave.InitialDirectory = LinkSave; fsave.Filter = "(tất cả các tệp)|*.*|(các tệp tin)|*.xlsx"; fsave.FileName = Name + ".xlsx"; if (fsave.ShowDialog() == System.Windows.Forms.DialogResult.Cancel) { } else { if (fsave.FileName != "") { //khởi tạo excel Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application(); //Khởi tại WorkBook Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing); //Khởi tạo Worksheet Microsoft.Office.Interop.Excel._Worksheet worksheet = null; worksheet = workbook.Sheets["Sheet1"]; worksheet = workbook.ActiveSheet; app.Visible = false; DateTime time1 = datetimebd.Value; DateTime time2 = datetimekt.Value; string dt1 = time1.ToString("dd/MM/yyyy"); string dt2 = time2.ToString("dd/MM/yyyy"); //Đổ dữ liệu vào sheet if (cbthongke.Text == "Báo cáo khách hàng") { //worksheet.Range["E4", "E100"].NumberFormat("Text"); worksheet.Cells[1, 1] = "BÁO CÁO KHÁCH HÀNG"; worksheet.Cells[2, 1] = "Nhân Viên: " + frmQLCHG.Ten; worksheet.Cells[3, 1] = "MãKH"; worksheet.Cells[3, 2] = "Họ Và Tên"; worksheet.Cells[3, 3] = "Năm Sinh"; worksheet.Cells[3, 4] = "Giới Tính"; worksheet.Cells[3, 5] = "Số Điện Thoại"; worksheet.Cells[3, 6] = "Địa Chỉ"; worksheet.Cells[3, 7] = "Loại KH"; worksheet.Cells[3, 8] = "Tiền Tích Lũy"; for (int i = 0; i < dataviewthongke.RowCount; i++) { for (int j = 0; j < 8; j++) { worksheet.Cells[i + 4, j + 1] = dataviewthongke.Rows[i].Cells[j].Value; worksheet.Cells[i + 5, 2] = "VIP 0 = " + txtvip0.Text + ""; worksheet.Cells[i + 6, 2] = "VIP 1 = " + txtvip1.Text + ""; worksheet.Cells[i + 7, 2] = "VIP 2 = " + txtvip2.Text + ""; worksheet.Cells[i + 8, 2] = "VIP 3 = " + txtvip3.Text + ""; worksheet.Cells[i + 9, 2] = "Tổng KH = " + txttongkh.Text + ""; //Kẻ Bảng worksheet.Range["A3", "H" + (i + 4)].Borders.LineStyle = 1; worksheet.Range["A4", "H" + (i + 4)].Borders.LineStyle = 1; //Định dạng các dòng text; worksheet.Range["A4", "A" + (i + 4)].HorizontalAlignment = 3; worksheet.Range["B4", "B" + (i + 4)].HorizontalAlignment = 3; worksheet.Range["E4", "E" + (i + 4)].HorizontalAlignment = 3; worksheet.Range["F4", "F" + (i + 4)].HorizontalAlignment = 3; worksheet.Range["G4", "G" + (i + 4)].HorizontalAlignment = 3; worksheet.Range["H4", "H" + (i + 4)].HorizontalAlignment = 3; } } //Định dạng trang: worksheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait; worksheet.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4; worksheet.PageSetup.LeftMargin = 0; worksheet.PageSetup.RightMargin = 0; worksheet.PageSetup.TopMargin = 0; worksheet.PageSetup.BottomMargin = 0; //Định dạng cột worksheet.Range["A1"].ColumnWidth = 8.57; worksheet.Range["B1"].ColumnWidth = 30; worksheet.Range["C1"].ColumnWidth = 12; worksheet.Range["D1"].ColumnWidth = 11.86; worksheet.Range["E1"].ColumnWidth = 17.57; worksheet.Range["F1"].ColumnWidth = 15; worksheet.Range["G1"].ColumnWidth = 10.57; worksheet.Range["H1"].ColumnWidth = 18; //Định dạng font worksheet.Range["A1", "H100"].Font.Name = "Times New Roman"; worksheet.Range["A1", "H1"].Font.Size = 20; worksheet.Range["A2", "H100"].Font.Size = 14; worksheet.Range["A1", "H1"].MergeCells = true; worksheet.Range["A1", "H1"].Font.Bold = true; worksheet.Range["A3", "H3"].Font.Bold = true; //Định dạng các dòng text: int abc = dataviewthongke.RowCount + 2; string def = "H" + abc; worksheet.Range["A1", "H1"].HorizontalAlignment = 3; worksheet.Range["A3", def].HorizontalAlignment = 3; } if (cbthongke.Text == "Báo cáo doanh thu, lợi nhuận") { worksheet.Cells[1, 1] = "BÁO CÁO DOANH THU, LỢI NHUẬN"; worksheet.Cells[2, 1] = "Nhân Viên: " + frmQLCHG.Ten; worksheet.Cells[2, 6] = "Ngày: " + dt1 + " - " + dt2; worksheet.Cells[3, 1] = "MãHĐ"; worksheet.Cells[3, 2] = "MãTK"; worksheet.Cells[3, 3] = "MãKH"; worksheet.Cells[3, 4] = "Ngày lập"; worksheet.Cells[3, 5] = "Tổng Tiền"; worksheet.Cells[3, 6] = "Chiết Khấu"; worksheet.Cells[3, 7] = "Thuế"; worksheet.Cells[3, 8] = "Tổng Tiền Gốc"; for (int i = 0; i < dataviewthongke.RowCount; i++) { for (int j = 0; j < 8; j++) { //worksheet.Cells[i + 8, 1] = i ; worksheet.Cells[i + 4, j + 1] = dataviewthongke.Rows[i].Cells[j].Value; worksheet.Cells[i + 5, 1] = ""; worksheet.Cells[i + 6, 1] = "Tổng Doanh Thu : " + txtdoanhthu.Text + " VND"; worksheet.Cells[i + 7, 1] = "Lợi Nhuận : " + txtloinhuan.Text + " VND"; //Kẻ Bảng worksheet.Range["A3", "H" + (i + 4)].Borders.LineStyle = 1; worksheet.Range["A4", "H" + (i + 4)].Borders.LineStyle = 1; //Định dạng các dòng text; worksheet.Range["A4", "A" + (i + 4)].HorizontalAlignment = 3; worksheet.Range["B4", "B" + (i + 4)].HorizontalAlignment = 3; worksheet.Range["E4", "E" + (i + 4)].HorizontalAlignment = 3; worksheet.Range["F4", "F" + (i + 4)].HorizontalAlignment = 3; worksheet.Range["G4", "G" + (i + 4)].HorizontalAlignment = 3; worksheet.Range["H4", "H" + (i + 4)].HorizontalAlignment = 3; } } //Định dạng trang: worksheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait; worksheet.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4; worksheet.PageSetup.LeftMargin = 0; worksheet.PageSetup.RightMargin = 0; worksheet.PageSetup.TopMargin = 0; worksheet.PageSetup.BottomMargin = 0; //Định dạng cột worksheet.Range["A1"].ColumnWidth = 8.57; worksheet.Range["B1"].ColumnWidth = 8.57; worksheet.Range["C1"].ColumnWidth = 12; worksheet.Range["D1"].ColumnWidth = 21.29; worksheet.Range["E1"].ColumnWidth = 17.57; worksheet.Range["F1"].ColumnWidth = 15; worksheet.Range["G1"].ColumnWidth = 10.57; worksheet.Range["H1"].ColumnWidth = 18.86; //Định dạng font worksheet.Range["A1", "H100"].Font.Name = "Times New Roman"; worksheet.Range["A1", "H1"].Font.Size = 20; worksheet.Range["A2", "H100"].Font.Size = 14; worksheet.Range["A1", "H1"].MergeCells = true; worksheet.Range["A1", "H1"].Font.Bold = true; worksheet.Range["A3", "H3"].Font.Bold = true; //Định dạng các dòng text: int abc = dataviewthongke.RowCount + 3; string def = "H" + abc; worksheet.Range["A1", "G1"].HorizontalAlignment = 3; worksheet.Range["A3", def].HorizontalAlignment = 3; } if (cbthongke.Text == "Báo cáo số lượng tồn") { worksheet.Cells[1, 1] = "BÁO CÁO SỐ LƯỢNG TỒN"; worksheet.Cells[2, 1] = "Nhân Viên: " + frmQLCHG.Ten; worksheet.Cells[3, 1] = "MãG"; worksheet.Cells[3, 2] = "TênG"; worksheet.Cells[3, 3] = "LoạiG"; worksheet.Cells[3, 4] = "TLGao"; worksheet.Cells[3, 5] = "Giá Nhập"; worksheet.Cells[3, 6] = "Giá Bán"; worksheet.Cells[3, 7] = "Xuất Xứ"; for (int i = 0; i < dataviewthongke.RowCount; i++) { for (int j = 0; j < 7; j++) { //worksheet.Cells[i + 8, 1] = i ; worksheet.Cells[i + 4, j + 1] = dataviewthongke.Rows[i].Cells[j].Value; worksheet.Cells[i + 5, 1] = ""; //Kẻ Bảng worksheet.Range["A3", "G" + (i + 4)].Borders.LineStyle = 1; worksheet.Range["A4", "G" + (i + 4)].Borders.LineStyle = 1; //Định dạng các dòng text: worksheet.Range["A3", "A" + (i + 4)].HorizontalAlignment = 3; worksheet.Range["B3", "B" + (i + 4)].HorizontalAlignment = 3; worksheet.Range["E3", "E" + (i + 4)].HorizontalAlignment = 3; worksheet.Range["F3", "F" + (i + 4)].HorizontalAlignment = 3; worksheet.Range["G3", "G" + (i + 4)].HorizontalAlignment = 3; } } //Định dạng trang: worksheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait; worksheet.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4; worksheet.PageSetup.LeftMargin = 0; worksheet.PageSetup.RightMargin = 0; worksheet.PageSetup.TopMargin = 0; worksheet.PageSetup.BottomMargin = 0; //Định dạng cột worksheet.Range["A1"].ColumnWidth = 5.55; worksheet.Range["B1"].ColumnWidth = 10; worksheet.Range["C1"].ColumnWidth = 22; worksheet.Range["D1"].ColumnWidth = 15; worksheet.Range["E1"].ColumnWidth = 16; worksheet.Range["F1"].ColumnWidth = 13.35; worksheet.Range["G1"].ColumnWidth = 15; //Định dạng font worksheet.Range["A1", "G100"].Font.Name = "Times New Roman"; worksheet.Range["A1", "G1"].Font.Size = 20; worksheet.Range["A2", "G100"].Font.Size = 14; worksheet.Range["A1", "G1"].MergeCells = true; worksheet.Range["A1", "G1"].Font.Bold = true; worksheet.Range["A3", "G3"].Font.Bold = true; //Định dạng các dòng text: int abc = dataviewthongke.RowCount + 3; string def = "G" + abc; worksheet.Range["A1", "G1"].HorizontalAlignment = 3; worksheet.Range["A3", def].HorizontalAlignment = 3; } if (cbthongke.Text == "Báo cáo gạo sắp hết") { worksheet.Cells[1, 1] = "BÁO CÁO SỐ GẠO SẮP HẾT"; worksheet.Cells[2, 1] = "Nhân Viên: " + frmQLCHG.Ten; worksheet.Cells[3, 1] = "MãG"; worksheet.Cells[3, 2] = "TênG"; worksheet.Cells[3, 3] = "LoạiG"; worksheet.Cells[3, 4] = "TLGạo"; worksheet.Cells[3, 5] = "Giá Nhập"; worksheet.Cells[3, 6] = "Giá Bán"; worksheet.Cells[3, 7] = "Xuất Xứ"; for (int i = 0; i < dataviewthongke.RowCount; i++) { for (int j = 0; j < 7; j++) { //worksheet.Cells[i + 8, 1] = i ; worksheet.Cells[i + 4, j + 1] = dataviewthongke.Rows[i].Cells[j].Value; worksheet.Cells[i + 5, 1] = ""; //Kẻ Bảng worksheet.Range["A3", "G" + (i + 4)].Borders.LineStyle = 1; worksheet.Range["A4", "G" + (i + 4)].Borders.LineStyle = 1; //Định dạng các dòng text: worksheet.Range["A3", "A" + (i + 4)].HorizontalAlignment = 3; worksheet.Range["B3", "B" + (i + 4)].HorizontalAlignment = 3; worksheet.Range["E3", "E" + (i + 4)].HorizontalAlignment = 3; worksheet.Range["F3", "F" + (i + 4)].HorizontalAlignment = 3; worksheet.Range["G3", "G" + (i + 4)].HorizontalAlignment = 3; } } //Định dạng trang: worksheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait; worksheet.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4; worksheet.PageSetup.LeftMargin = 0; worksheet.PageSetup.RightMargin = 0; worksheet.PageSetup.TopMargin = 0; worksheet.PageSetup.BottomMargin = 0; //Định dạng cột worksheet.Range["A1"].ColumnWidth = 5.55; worksheet.Range["B1"].ColumnWidth = 10; worksheet.Range["C1"].ColumnWidth = 22; worksheet.Range["D1"].ColumnWidth = 15; worksheet.Range["E1"].ColumnWidth = 16; worksheet.Range["F1"].ColumnWidth = 13.35; worksheet.Range["G1"].ColumnWidth = 15; //Định dạng font worksheet.Range["A1", "G100"].Font.Name = "Times New Roman"; worksheet.Range["A1", "G1"].Font.Size = 20; worksheet.Range["A2", "G100"].Font.Size = 14; worksheet.Range["A1", "G1"].MergeCells = true; worksheet.Range["A1", "G1"].Font.Bold = true; worksheet.Range["A3", "G3"].Font.Bold = true; //Định dạng các dòng text: int abc = dataviewthongke.RowCount + 3; string def = "G" + abc; worksheet.Range["A1", "G1"].HorizontalAlignment = 3; worksheet.Range["A3", def].HorizontalAlignment = 3; } worksheet.SaveAs(fsave.FileName); String Message = "Xuất " + cbthongke.Text + " thành công"; MessageBox.Show(Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Information); } } }
private void button10_Click(object sender, EventArgs e) { if (xlWorkbook == null) { xlApp = new Excel.Application(); OpenFileDialog ofd = new OpenFileDialog(); ofd.Filter = "Excel Documents (*.xlsx)|*.xlsx"; ofd.ShowDialog(); xlWorkbook = xlApp.Workbooks.Open(ofd.FileName, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); xlWorksheet = (Excel._Worksheet)xlWorkbook.Sheets[1]; } finalDuplicateCheck(Convert.ToInt32(textDupeCheckRowFrom.Text)-1, Convert.ToInt32(textDupeCheckRowFrom.Text)-1); }
/// <summary> /// 设置连续区域的字体名称 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objStartCell">开始单元格</param> /// <param name="objEndCell">结束单元格</param> /// <param name="fontname">字体名称 隶书、仿宋_GB2312等</param> public void SetFontName(Excel._Worksheet CurSheet, object objStartCell, object objEndCell, string fontname) { CurSheet.get_Range(objStartCell, objEndCell).Font.Name = fontname; }
/// <summary> /// 创建一个Excel程序实例 /// </summary> private void CreateExcelRef() { _excelApp = new Excel.Application(); _books = (Excel.Workbooks)_excelApp.Workbooks; _book = (Excel._Workbook)(_books.Add(_optionalValue)); _sheets = (Excel.Sheets)_book.Worksheets; _sheet = (Excel._Worksheet)(_sheets.get_Item(1)); }
/// <summary> /// 设置连续区域的字体为黑体 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objStartCell">开始单元格</param> /// <param name="objEndCell">结束单元格</param> public void SetBold(Excel._Worksheet CurSheet, object objStartCell, object objEndCell) { CurSheet.get_Range(objStartCell, objEndCell).Font.Bold = true; }
//Method to initialize opening Excel private void ExcelInit(String path, out List<Product> idinfo) { xlApp = new Excel.Application(); string dsSheet = "datasheet"; string idinfoSheet = "idinfo"; idinfo = null; if (System.IO.File.Exists(path)) { // then go and load this into excel xlWorkBook = xlApp.Workbooks.Open(path, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); //read info datasheet first to generate product specs list info_xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(idinfoSheet); idinfo = ReadIdInfoIntoDataTable(info_xlWorkSheet); //read datasheet and load the data into product specs list //ds_xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(dsSheet); //ReadDatasheetIntoDataTable(ds_xlWorkSheet); } else { MessageBox.Show("Unable to open excel file!"); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); xlApp = null; System.Windows.Forms.Application.Exit(); } }
/// <summary> /// 设置连续区域水平居右 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objStartCell">开始单元格</param> /// <param name="objEndCell">结束单元格</param> public void SetHAlignRight(Excel._Worksheet CurSheet, object objStartCell, object objEndCell) { CurSheet.get_Range(objStartCell, objEndCell).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; }