static void Main(string[] args) { // Create new Spreadsheet Spreadsheet document = new Spreadsheet(); // Get worksheet by name Worksheet worksheet = document.Workbook.Worksheets.Add("Sample"); // Set header worksheet.Cell(0, 0).Value = "Value (Number)"; worksheet.Cell(0, 1).Value = "Formatting String"; // Set variables double plan = 3000; double result = 3100; // Fill cells for (int i = 1; i < 6; i++) { worksheet.Cell(i, 0).Value = result / plan; } // Set cells format worksheet.Cell(1, 0).NumberFormatString = "0.00"; worksheet.Cell(2, 0).NumberFormatString = "0.00%"; worksheet.Cell(3, 0).NumberFormatString = "mm:ss"; worksheet.Cell(4, 0).NumberFormatString = "hh:mm:ss"; worksheet.Cell(5, 0).NumberFormatString = "##0,0E+0"; // Set captions worksheet.Cell(1, 1).Value = "0.00"; worksheet.Cell(2, 1).Value = "0.00%"; worksheet.Cell(3, 1).Value = "mm:ss"; worksheet.Cell(4, 1).Value = "hh:mm:ss"; worksheet.Cell(5, 1).Value = "##0,0E+0"; // delete output file if exists already if (File.Exists("Sample.xls")) { File.Delete("Sample.xls"); } // Save document document.SaveAs("Sample.xls"); // Close document document.Close(); // open generated XLS document in default program Process.Start("Sample.xls"); }
static void Main(string[] args) { try { // MODIFY THE CONNECTION STRING WITH YOUR CREDENTIALS!!! string connectionString = "Data Source=localhost;Initial Catalog=master;Integrated Security=true;"; using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); // Drop test database if exists ExecuteQueryWithoutResult(connection, "IF DB_ID ('CsvTests') IS NOT NULL DROP DATABASE CsvTests"); // Create empty database ExecuteQueryWithoutResult(connection, "CREATE DATABASE CsvTests"); // Switch to created database ExecuteQueryWithoutResult(connection, "USE CsvTests"); // Create a table for CSV data ExecuteQueryWithoutResult(connection, "CREATE TABLE CsvTest (Name VARCHAR(40), FullName VARCHAR(255))"); // Load CSV document using (Spreadsheet document = new Spreadsheet()) { document.LoadFromFile("sample.csv", ";"); // ";" - delimiter Worksheet worksheet = document.Workbook.Worksheets[0]; for (int row = 0; row <= worksheet.UsedRangeRowMax; row++) { String insertCommand = string.Format("INSERT CsvTest VALUES('{0}','{1}')", worksheet.Cell(row, 0).Value, worksheet.Cell(row, 1).Value); ExecuteQueryWithoutResult(connection, insertCommand); } } // Check the data successfully exported using (SqlCommand command = new SqlCommand("SELECT * from CsvTest", connection)) { SqlDataReader reader = command.ExecuteReader(); if (reader != null) { Console.WriteLine(); Console.WriteLine("Exported CSV data:"); Console.WriteLine(); while (reader.Read()) { Console.WriteLine(String.Format("{0} | {1}", reader[0], reader[1])); } } } Console.WriteLine(); Console.WriteLine("Press any key."); Console.ReadKey(); } } catch (Exception ex) { Console.WriteLine("Error: " + ex.Message); Console.ReadKey(); } }
protected void fileDoc_FileSelected(object sender, EventArgs e) { try { int Value = 0; bool Imported = true; int ErrorCounter = 0; DateTime ValueDateTime; StringBuilder sbMensaje = new StringBuilder(); if (ddlProtocoloId.SelectedValue == "-1") { Alert.ShowInTop("Debe seleccionar un protocolo", "Pregunta"); return; } if (_TempPacientList != null) { Alert.ShowInTop("Ya existe una lista de pacientes por agendar", "Pregunta"); return; } string fileName = fileDoc.FileName; if (fileName != "") { string Ext = fileName.Substring(fileName.IndexOf('.') + 0).ToUpper(); _TempPacientList = new List <Sigesoft.Node.WinClient.BE.PacientList>(); if (Ext == ".XLSX" || Ext == ".XLS") { fileDoc.SaveAs(Server.MapPath("~/upload/" + fileName)); Spreadsheet document = new Spreadsheet(); document.LoadFromFile(Server.MapPath("~/upload/" + fileName)); Worksheet worksheet1 = document.Workbook.Worksheets.ByName("PLANTILLA"); Sigesoft.Node.WinClient.BE.PacientList TempPacient; int i = 4; int ii = 4; //Validar que el excel no esta vacio while (worksheet1.Cell(ii, 0).ValueAsString != "") { if (worksheet1.Cell(ii, 0).ValueAsString == null || worksheet1.Cell(ii, 1).ValueAsString == null || worksheet1.Cell(ii, 2).ValueAsString == null || worksheet1.Cell(ii, 3).ValueAsString == null || worksheet1.Cell(ii, 4).ValueAsString == null || worksheet1.Cell(ii, 5).ValueAsString == null || worksheet1.Cell(ii, 6).ValueAsString == null || worksheet1.Cell(ii, 7).ValueAsString == null || worksheet1.Cell(ii, 8).ValueAsString == null || worksheet1.Cell(ii, 9).ValueAsString == null || worksheet1.Cell(ii, 10).ValueAsString == null || worksheet1.Cell(ii, 11).ValueAsString == null || worksheet1.Cell(ii, 12).ValueAsString == null) { for (int y = 0; y <= 12; y++) { if (worksheet1.Cell(ii, y).ValueAsString == null) { Imported = false; sbMensaje.Append("Registro número : "); sbMensaje.Append(worksheet1.Cell(ii, 0).ValueAsString); sbMensaje.Append(". El campo " + worksheet1.Cell(3, y).ValueAsString.ToString() + " no puede estar vacio"); sbMensaje.Append("\n"); } } } ii++; } if (Imported == false) { Alert.ShowInTop(sbMensaje.ToString(), "Corregir registros en blanco"); return; } while (worksheet1.Cell(i, 0).ValueAsString != "") { TempPacient = new Sigesoft.Node.WinClient.BE.PacientList(); if (worksheet1.Cell(i, 0).ValueAsString != "") { TempPacient.i_Correlative = int.Parse(worksheet1.Cell(i, 0).ValueAsString.ToString()); Imported = true; } //Nombres if (worksheet1.Cell(i, 1).ValueAsString != "") { TempPacient.v_FirstName = worksheet1.Cell(i, 1).ValueAsString.ToString(); Imported = true; } else { ErrorCounter++; Imported = false; sbMensaje.Append("Registro número : "); sbMensaje.Append(worksheet1.Cell(i, 0).ValueAsString); sbMensaje.Append(". El campo Nombres es inválido"); sbMensaje.Append("\n"); i++; continue; } //Apellido Paterno if (worksheet1.Cell(i, 2).ValueAsString != "") { TempPacient.v_FirstLastName = worksheet1.Cell(i, 2).ValueAsString.ToString(); Imported = true; } else { ErrorCounter++; Imported = false; sbMensaje.Append("Registro número : "); sbMensaje.Append(worksheet1.Cell(i, 0).ValueAsString); sbMensaje.Append(". El campo Apellido Paterno es inválido"); sbMensaje.Append("\n"); i++; continue; } //Apellido Materno if (worksheet1.Cell(i, 3).ValueAsString != "") { TempPacient.v_SecondLastName = worksheet1.Cell(i, 3).ValueAsString.ToString(); Imported = true; } else { ErrorCounter++; Imported = false; sbMensaje.Append("Registro número : "); sbMensaje.Append(worksheet1.Cell(i, 0).ValueAsString); sbMensaje.Append(". El campo Apellido Materno es inválido"); sbMensaje.Append("\n"); i++; continue; } //ID Tipo Documento if (worksheet1.Cell(i, 4).ValueAsString == null) { ErrorCounter++; Imported = false; sbMensaje.Append("Registro número : "); sbMensaje.Append(worksheet1.Cell(i, 0).ValueAsString); sbMensaje.Append(". El campo ID Tipo Documento es inválido"); sbMensaje.Append("\n"); i++; continue; } if (int.TryParse(worksheet1.Cell(i, 4).ValueAsString.ToString(), out Value) == false) { ErrorCounter++; Imported = false; sbMensaje.Append("Registro número : "); sbMensaje.Append(worksheet1.Cell(i, 0).ValueAsString); sbMensaje.Append(". El campo ID Tipo Documento es inválido"); sbMensaje.Append("\n"); i++; continue; } else { Imported = true; TempPacient.i_DocTypeId = int.Parse(worksheet1.Cell(i, 4).ValueAsString.ToString()); } //Nombre Tipo Documento if (worksheet1.Cell(i, 5).ValueAsString != "") { TempPacient.v_DocTypeName = worksheet1.Cell(i, 5).ValueAsString.ToString(); Imported = true; } else { ErrorCounter++; Imported = false; sbMensaje.Append("Registro número : "); sbMensaje.Append(worksheet1.Cell(i, 0).ValueAsString); sbMensaje.Append(". El campo Nombre Tipo Documento es inválido"); sbMensaje.Append("\n"); i++; continue; } //Número Documento if (worksheet1.Cell(i, 6).ValueAsString != "") { if (worksheet1.Cell(i, 4).ValueAsString.ToString() == "1") // DNI { if (worksheet1.Cell(i, 6).ValueAsString.ToString().Length != 8) { ErrorCounter++; Imported = false; sbMensaje.Append("Registro número : "); sbMensaje.Append(worksheet1.Cell(i, 0).ValueAsString); sbMensaje.Append(". El campo Número de DNI debe tener 8 dígitos"); sbMensaje.Append("\n"); i++; continue; } else { Imported = true; TempPacient.v_DocNumber = worksheet1.Cell(i, 6).ValueAsString.ToString(); } } else if (worksheet1.Cell(i, 4).ValueAsString.ToString() == "2") // PASAPORTE { if (worksheet1.Cell(i, 6).ValueAsString.ToString().Length != 9) { ErrorCounter++; Imported = false; sbMensaje.Append("Registro número : "); sbMensaje.Append(worksheet1.Cell(i, 0).ValueAsString); sbMensaje.Append(". El Número PASAPORTE debe tener 9 dígitos"); sbMensaje.Append("\n"); i++; continue; } else { Imported = true; TempPacient.v_DocNumber = worksheet1.Cell(i, 6).ValueAsString.ToString(); } } else if (worksheet1.Cell(i, 4).ValueAsString.ToString() == "3") // LICENCIA DE CONDUCIR { if (worksheet1.Cell(i, 6).ValueAsString.ToString().Length != 10) { ErrorCounter++; Imported = false; sbMensaje.Append("Registro número : "); sbMensaje.Append(worksheet1.Cell(i, 0).ValueAsString); sbMensaje.Append(". El Número LICENCIA DE CONDUCIR debe tener 10 dígitos"); sbMensaje.Append("\n"); i++; continue; } else { Imported = true; TempPacient.v_DocNumber = worksheet1.Cell(i, 6).ValueAsString.ToString(); } } else if (worksheet1.Cell(i, 4).ValueAsString.ToString() == "4") // CARNET DE EXTRANJERIA { if (worksheet1.Cell(i, 6).ValueAsString.ToString().Length != 11) { ErrorCounter++; Imported = false; sbMensaje.Append("Registro número : "); sbMensaje.Append(worksheet1.Cell(i, 0).ValueAsString); sbMensaje.Append(". El Número CARNET DE EXTRANJERIA debe tener 11 dígitos"); sbMensaje.Append("\n"); i++; continue; } else { Imported = true; TempPacient.v_DocNumber = worksheet1.Cell(i, 6).ValueAsString.ToString(); } } } else { ErrorCounter++; Imported = false; sbMensaje.Append("Registro número : "); sbMensaje.Append(worksheet1.Cell(i, 0).ValueAsString); sbMensaje.Append(". El campo Número Documento es inválido"); sbMensaje.Append("\n"); i++; continue; } //ID Género if (int.TryParse(worksheet1.Cell(i, 7).ValueAsString.ToString(), out Value)) { Imported = true; TempPacient.i_SexTypeId = int.Parse(worksheet1.Cell(i, 7).ValueAsString.ToString()); } else { ErrorCounter++; Imported = false; sbMensaje.Append("Registro número : "); sbMensaje.Append(worksheet1.Cell(i, 0).ValueAsString); sbMensaje.Append(". El campo ID Género es inválido"); sbMensaje.Append("\n"); i++; continue; } // Nombre Género if (worksheet1.Cell(i, 8).ValueAsString != "") { Imported = true; TempPacient.v_SexTypeName = worksheet1.Cell(i, 8).ValueAsString.ToString(); } else { ErrorCounter++; Imported = false; sbMensaje.Append("Registro número : "); sbMensaje.Append(worksheet1.Cell(i, 0).ValueAsString); sbMensaje.Append(". El campo Nombre Género es inválido"); sbMensaje.Append("\n"); i++; continue; } //Fecha Nacimiento if (worksheet1.Cell(i, 12).ValueAsString == null) { ErrorCounter++; Imported = false; sbMensaje.Append("Registro número : "); sbMensaje.Append(worksheet1.Cell(i, 0).ValueAsString); sbMensaje.Append(". El campo Fecha Nacimiento es inválido"); sbMensaje.Append("\n"); i++; continue; } if (DateTime.TryParseExact(worksheet1.Cell(i, 12).ValueAsString.ToString(), "yyyyMMdd", CultureInfo.InvariantCulture, DateTimeStyles.None, out ValueDateTime) == false) { ErrorCounter++; Imported = false; sbMensaje.Append("Registro número : "); sbMensaje.Append(worksheet1.Cell(i, 0).ValueAsString); sbMensaje.Append(". El campo Fecha Nacimiento es inválido"); sbMensaje.Append("\n"); i++; continue; } else { Imported = true; TempPacient.d_Birthdate = DateTime.ParseExact(worksheet1.Cell(i, 12).ValueAsString.ToString(), "yyyyMMdd", CultureInfo.InvariantCulture); } //Puesto de Trabajo if (worksheet1.Cell(i, 13).ValueAsString != "") { TempPacient.v_CurrentOccupation = worksheet1.Cell(i, 13).ValueAsString.ToString(); Imported = true; } else { //ErrorCounter++; //Imported = false; //sbMensaje.Append("Registro número : "); //sbMensaje.Append(worksheet1.Cell(i,10).ValueAsString); //sbMensaje.Append(". El campo Nombres es inválido"); //sbMensaje.Append("\n"); //i++; //continue; TempPacient.v_CurrentOccupation = string.Empty; } //PROTOCOLO ID if (ddlProtocoloId.SelectedValue != "-1") { TempPacient.v_ProtocoloId = ddlProtocoloId.SelectedText.ToString(); Imported = true; } else { TempPacient.v_ProtocoloId = string.Empty; } _TempPacientList.Add(TempPacient); Session["_TempPacientList"] = _TempPacientList; var Result = _TempPacientList.FindAll(p => p.v_DocNumber == TempPacient.v_DocNumber && p.i_DocTypeId == TempPacient.i_DocTypeId); if (Result.Count > 1) { Alert.ShowInTop("El correlativo " + Result[0].i_Correlative + " tiene el mismo Número Documento que el correlativo " + Result[1].i_Correlative + " .Revise el Excel y corriga la duplicidad", "Error al cargar Excel"); return; } i++; } //lblRecordCountPacients.Text = string.Format("Se encontraron {0} registros.", _TempPacientList.Count()); if (ErrorCounter > 0) { _TempPacientList = new List <Sigesoft.Node.WinClient.BE.PacientList>(); grdData.DataSource = new List <PacientList>(); grdData.DataBind(); Alert.ShowInTop(sbMensaje.ToString(), "Registros no importados"); sbMensaje = null; btnSubir.Hidden = true; } else if (ErrorCounter == 0) { grdData.DataSource = _TempPacientList; grdData.DataBind(); Alert.ShowInTop("Se importaron " + _TempPacientList.Count() + " registros.", "Importación correcta"); btnSubir.Hidden = false; } else { _TempPacientList = new List <Sigesoft.Node.WinClient.BE.PacientList>(); grdData.DataSource = new List <Sigesoft.Node.WinClient.BE.PacientList>(); grdData.DataBind(); Alert.ShowInTop(sbMensaje.ToString(), "Registros no importados"); sbMensaje = null; } } else { Alert.ShowInTop("Solo se puede subir archivos con esta extensión : .XLSX"); return; } } } catch (Exception) { throw; } }
static void Main(string[] args) { // Create new spreadsheet Spreadsheet spreadsheet = new Spreadsheet(); // Add new worksheet Worksheet sheet = spreadsheet.Workbook.Worksheets.Add(); // Get decimal separator. Decimal separator depends from locale. string dsep = spreadsheet.Workbook.Locale.NumberFormat.CurrencyDecimalSeparator; // Get list separator. List separator depends from locale. string lsep = spreadsheet.Workbook.Locale.TextInfo.ListSeparator; // Set starting row int rowNumber = 1; // Example on SIN() function in formula sheet.Cell(rowNumber, 0).Value = "SIN(30)"; sheet.Cell(rowNumber, 1).Value = "=SIN(30)"; rowNumber += 2; // Example on COS() function in formula sheet.Cell(rowNumber, 0).Value = "COS(30)"; sheet.Cell(rowNumber, 1).Value = "=COS(30)"; rowNumber += 2; // Example on TAN() function in formula sheet.Cell(rowNumber, 0).Value = "TAN(30)"; sheet.Cell(rowNumber, 1).Value = "=TAN(30)"; rowNumber += 2; // Example on ATAN() function in formula sheet.Cell(rowNumber, 0).Value = "ATAN(0" + dsep + "5)"; sheet.Cell(rowNumber, 1).Value = "=ATAN(0" + dsep + "5)"; rowNumber += 2; // Example on ATAN2() function in formula sheet.Cell(rowNumber, 0).Value = "ATAN2(0" + dsep + "3" + lsep + "0.1)"; sheet.Cell(rowNumber, 1).Value = "=ATAN2(0" + dsep + "3" + lsep + "0.1)"; rowNumber += 2; // Example on ASIN() function in formula sheet.Cell(rowNumber, 0).Value = "ASIN(0" + dsep + "5)"; sheet.Cell(rowNumber, 1).Value = "=ASIN(0" + dsep + "5)"; rowNumber += 2; // Example on ACOS() function in formula sheet.Cell(rowNumber, 0).Value = "ACOS(0" + dsep + "5)"; sheet.Cell(rowNumber, 1).Value = "=ACOS(0" + dsep + "5)"; rowNumber += 2; // Example on EXP() function in formula sheet.Cell(rowNumber, 0).Value = "EXP(5)"; sheet.Cell(rowNumber, 1).Value = "=EXP(5)"; rowNumber += 2; // Example on SQRT() function in formula sheet.Cell(rowNumber, 0).Value = "SQRT(5)"; sheet.Cell(rowNumber, 1).Value = "=SQRT(5)"; rowNumber += 2; // Example on LN() function in formula sheet.Cell(rowNumber, 0).Value = "LN(5)"; sheet.Cell(rowNumber, 1).Value = "=LN(5)"; rowNumber += 2; // Example on LOG10() function in formula sheet.Cell(rowNumber, 0).Value = "LOG10(5)"; sheet.Cell(rowNumber, 1).Value = "=LOG10(5)"; rowNumber += 2; // Example on SUM() function in formula sheet.Cell(rowNumber, 0).Value = "SUM(5" + lsep + "3)"; sheet.Cell(rowNumber, 1).Value = "=SUM(5" + lsep + "3)"; rowNumber += 2; // Example on PRODUCT() function in formula sheet.Cell(rowNumber, 0).Value = "PRODUCT(5" + lsep + "3)"; sheet.Cell(rowNumber, 1).Value = "=PRODUCT(5" + lsep + "3)"; rowNumber += 2; // Example on VAR() function in formula sheet.Cell(rowNumber, 0).Value = "VAR(5" + lsep + "3)"; sheet.Cell(rowNumber, 1).Value = "=VAR(5" + lsep + "3)"; rowNumber += 2; // Example on VARP() function in formula sheet.Cell(rowNumber, 0).Value = "VARP(5" + lsep + "3)"; sheet.Cell(rowNumber, 1).Value = "=VARP(5" + lsep + "3)"; rowNumber += 2; // Example on STDEV() function in formula sheet.Cell(rowNumber, 0).Value = "STDEV(5" + lsep + "3)"; sheet.Cell(rowNumber, 1).Value = "=STDEV(5" + lsep + "3)"; rowNumber += 2; // Example on STDEVP() function in formula sheet.Cell(rowNumber, 0).Value = "STDEVP(5" + lsep + "3)"; sheet.Cell(rowNumber, 1).Value = "=STDEVP(5" + lsep + "3)"; rowNumber += 2; // Example on AVERAGE() function in formula sheet.Cell(rowNumber, 0).Value = "AVERAGE(50" + lsep + "30)"; sheet.Cell(rowNumber, 1).Value = "=AVERAGE(50" + lsep + "30)"; rowNumber += 2; // Example on MIN() function in formula sheet.Cell(rowNumber, 0).Value = "MIN(50" + lsep + "30)"; sheet.Cell(rowNumber, 1).Value = "=MIN(50" + lsep + "30)"; rowNumber += 2; // Example on MAX() function in formula sheet.Cell(rowNumber, 0).Value = "MAX(50" + lsep + "30)"; sheet.Cell(rowNumber, 1).Value = "=MAX(50" + lsep + "30)"; rowNumber += 2; // Example on PI() function in formula sheet.Cell(rowNumber, 0).Value = "PI()"; sheet.Cell(rowNumber, 1).Value = "=PI()"; rowNumber += 2; // Example on ABS() function in formula sheet.Cell(rowNumber, 0).Value = "ABS(-5)"; sheet.Cell(rowNumber, 1).Value = "=ABS(-5)"; rowNumber += 2; // Example on RAND() function in formula sheet.Cell(rowNumber, 0).Value = "RAND()"; sheet.Cell(rowNumber, 1).Value = "=RAND()"; rowNumber += 2; // Example on MOD() function in formula sheet.Cell(rowNumber, 0).Value = "MOD(10" + dsep + "8" + lsep + "2)"; sheet.Cell(rowNumber, 1).Value = "=MOD(10" + dsep + "8" + lsep + "2)"; rowNumber += 2; // Example on INT() function in formula sheet.Cell(rowNumber, 0).Value = "INT(10" + dsep + "8)"; sheet.Cell(rowNumber, 1).Value = "=INT(10" + dsep + "8)"; rowNumber += 2; // Example on SIGN() function in formula sheet.Cell(rowNumber, 0).Value = "SIGN(10" + dsep + "8)"; sheet.Cell(rowNumber, 1).Value = "=SIGN(10" + dsep + "8)"; rowNumber += 2; // Example on ROUND() function in formula sheet.Cell(rowNumber, 0).Value = "ROUND(10" + dsep + "862456" + lsep + "4)"; sheet.Cell(rowNumber, 1).Value = "=ROUND(10" + dsep + "862456" + lsep + "4)"; rowNumber += 2; // Example on RADIANS() function in formula sheet.Cell(rowNumber, 0).Value = "RADIANS(180)"; sheet.Cell(rowNumber, 1).Value = "=RADIANS(180)"; rowNumber += 2; // Example on DEGREES() function in formula sheet.Cell(rowNumber, 0).Value = "DEGREES(3" + dsep + "14)"; sheet.Cell(rowNumber, 1).Value = "=DEGREES(3" + dsep + "14)"; rowNumber += 2; // Example on LEN() function in formula sheet.Cell(rowNumber, 0).Value = "LEN(\"Bytescout\")"; sheet.Cell(rowNumber, 1).Value = "=LEN(\"Bytescout\")"; rowNumber += 2; // Example on MID() function in formula sheet.Cell(rowNumber, 0).Value = "MID(\"Bytescout\"" + lsep + "5" + lsep + "5)"; sheet.Cell(rowNumber, 1).Value = "=MID(\"Bytescout\"" + lsep + "5" + lsep + "5)"; rowNumber += 2; // Example on NOW() function in formula sheet.Cell(rowNumber, 0).Value = "NOW()"; sheet.Cell(rowNumber, 1).Value = "=NOW()"; rowNumber += 2; // Example on DATE() function in formula sheet.Cell(rowNumber, 0).Value = "DATE(2009" + lsep + "1" + lsep + "2)"; sheet.Cell(rowNumber, 1).Value = "=DATE(2009" + lsep + "1" + lsep + "2)"; rowNumber += 2; // Example on TIME() function in formula sheet.Cell(rowNumber, 0).Value = "TIME(1" + lsep + "1" + lsep + "2)"; sheet.Cell(rowNumber, 1).Value = "=TIME(1" + lsep + "1" + lsep + "2)"; rowNumber += 2; // Example on SECOND() function in formula sheet.Cell(rowNumber, 0).Value = "SECOND(\"18:45:02\")"; sheet.Cell(rowNumber, 1).Value = "=SECOND(\"18:45:02\")"; rowNumber += 2; // Example on MINUTE() function in formula sheet.Cell(rowNumber, 0).Value = "MINUTE(\"18:45:02\")"; sheet.Cell(rowNumber, 1).Value = "=MINUTE(\"18:45:02\")"; rowNumber += 2; // Example on HOUR() function in formula sheet.Cell(rowNumber, 0).Value = "HOUR(\"18:45:02\")"; sheet.Cell(rowNumber, 1).Value = "=HOUR(\"18:45:02\")"; rowNumber += 2; // Example on YEAR() function in formula sheet.Cell(rowNumber, 0).Value = "YEAR(NOW())"; sheet.Cell(rowNumber, 1).Value = "=YEAR(NOW())"; rowNumber += 2; // Example on MONTH() function in formula sheet.Cell(rowNumber, 0).Value = "MONTH(NOW())"; sheet.Cell(rowNumber, 1).Value = "=MONTH(NOW())"; rowNumber += 2; // Example on DAY() function in formula sheet.Cell(rowNumber, 0).Value = "DAY(NOW())"; sheet.Cell(rowNumber, 1).Value = "=DAY(NOW())"; rowNumber += 2; // Example on WEEKDAY() function in formula sheet.Cell(rowNumber, 0).Value = "WEEKDAY(NOW())"; sheet.Cell(rowNumber, 1).Value = "=WEEKDAY(NOW())"; rowNumber += 2; // Example on FALSE in formula sheet.Cell(rowNumber, 0).Value = "FALSE"; sheet.Cell(rowNumber, 1).Value = "=FALSE"; rowNumber += 2; // Example on TRUE in formula sheet.Cell(rowNumber, 0).Value = "TRUE"; sheet.Cell(rowNumber, 1).Value = "=TRUE"; rowNumber += 2; // Example on AND() function in formula sheet.Cell(rowNumber, 0).Value = "AND"; sheet.Cell(rowNumber, 1).Value = "=AND(10>1" + lsep + "10<100)"; rowNumber += 2; // Example on OR() function in formula sheet.Cell(rowNumber, 0).Value = "OR(10>1" + lsep + "10<100)"; sheet.Cell(rowNumber, 1).Value = "=OR(10>1" + lsep + "10<100)"; rowNumber += 2; // Example on NOT() function in formula sheet.Cell(rowNumber, 0).Value = "NOT(1+1=2)"; sheet.Cell(rowNumber, 1).Value = "=NOT(1+1=2)"; rowNumber += 2; // Example on ISNA() function in formula sheet.Cell(rowNumber, 0).Value = "ISNA(NA())"; sheet.Cell(rowNumber, 1).Value = "=ISNA(NA())"; rowNumber += 2; // Example on NA() function in formula sheet.Cell(rowNumber, 0).Value = "NA()"; sheet.Cell(rowNumber, 1).Value = "=NA()"; rowNumber += 2; // Example on ISERROR() function in formula sheet.Cell(rowNumber, 0).Value = "ISERROR(1/0)"; sheet.Cell(rowNumber, 1).Value = "=ISERROR(1/0)"; rowNumber += 2; // Example on ROW() function in formula sheet.Cell(rowNumber, 0).Value = "ROW()"; sheet.Cell(rowNumber, 1).Value = "=ROW()"; rowNumber += 2; // Example on COLUMN() function in formula sheet.Cell(rowNumber, 0).Value = "COLUMN()"; sheet.Cell(rowNumber, 1).Value = "=COLUMN()"; rowNumber += 2; // Example on COUNT() function in formula sheet.Cell(rowNumber, 0).Value = "COUNT(B1:B10)"; sheet.Cell(rowNumber, 1).Value = "=COUNT(B1:B10)"; rowNumber += 2; // Example on COUNTA() function in formula sheet.Cell(rowNumber, 0).Value = "COUNTA(B1:B10)"; sheet.Cell(rowNumber, 1).Value = "=COUNTA(B1:B10)"; rowNumber += 2; // Example on COUNTA() function in formula sheet.Cell(rowNumber, 0).Value = "IF(1=1" + lsep + "TRUE" + lsep + "FALSE)"; sheet.Cell(rowNumber, 1).Value = "=IF(1=1" + lsep + "TRUE" + lsep + "FALSE)"; rowNumber += 2; // Example on COUNTA() function in formula sheet.Cell(rowNumber, 0).Value = "NPV(10%" + lsep + "10000" + lsep + "1000" + lsep + "2000" + lsep + "300)"; sheet.Cell(rowNumber, 1).Value = "=NPV(10%" + lsep + "10000" + lsep + "1000" + lsep + "2000" + lsep + "300)"; try { System.IO.File.Delete("output.xls"); } catch (Exception ex) { } spreadsheet.SaveAs("output.xls"); spreadsheet.Close(); System.Diagnostics.Process.Start("output.xls"); }
public static int getRowIndex(Worksheet worksheet, string partID, string partName, string partKey, int Quanity, string productName) { string col_k = "start"; string col_i = ""; int i = 0; bool findproductName = false; while (col_k != "") { /*if(productName.Contains("r92592112") == true) * { * int aaa = 5; * }*/ col_k = worksheet.Cell(i, 10).ValueAsString; //column k if (MatchingRegex(col_k, productName) == true) { findproductName = true; col_i = worksheet.Cell(i, 8).ValueAsString; if (MatchingIDFromExcel(col_i, partKey) == true) { if (!list.Contains(i.ToString())) { list.Add(i.ToString()); return(i); } } } usedRows = i; i++; } /*while (col_k != "") * { * col_k = worksheet.Cell(i, 10).ValueAsString; //column j * col_i = worksheet.Cell(i, 8).ValueAsString; * * //if (col_k == "") return -1; * * if (MatchingRegex(col_k , productName) == true) * { * findproductName = true; * * if (MatchingMatchingID(partKey, col_i) == true) * return i; * } * * usedRows = i; * i++; * }*/ if (findproductName == true) { //MessageBox.Show("PartID: " + partID + ", PartName: " + partName + ", PartKey: " + partKey + ", ProductName: " + productName); return(-2); } else { return(-1); } }
static void Main(string[] args) { // Create new Spreadsheet object Spreadsheet spreadsheet = new Spreadsheet(); spreadsheet.RegistrationName = "demo"; spreadsheet.RegistrationKey = "demo"; // Add new worksheet Worksheet sheet = spreadsheet.Workbook.Worksheets.Add("Sample"); // Add few random numbers int length = 10; Random rnd = new Random(); for (int i = 0; i < length; i++) { sheet.Cell(i, 0).Value = rnd.Next(10); sheet.Cell(i, 1).Value = rnd.Next(10); } // Add charts to worksheet Chart lineChart = sheet.Charts.AddChartAndFitInto(1, 3, 16, 9, ChartType.Line); lineChart.SeriesCollection.Add(new Series(sheet.Range(0, 0, length - 1, 0))); lineChart.SeriesCollection.Add(new Series(sheet.Range(0, 1, length - 1, 1))); lineChart = sheet.Charts.AddChartAndFitInto(1, 10, 16, 16, ChartType.LineStacked); lineChart.SeriesCollection.Add(new Series(sheet.Range(0, 0, length - 1, 0))); lineChart.SeriesCollection.Add(new Series(sheet.Range(0, 1, length - 1, 1))); lineChart = sheet.Charts.AddChartAndFitInto(1, 17, 16, 23, ChartType.LineStacked100); lineChart.SeriesCollection.Add(new Series(sheet.Range(0, 0, length - 1, 0))); lineChart.SeriesCollection.Add(new Series(sheet.Range(0, 1, length - 1, 1))); lineChart = sheet.Charts.AddChartAndFitInto(17, 3, 32, 9, ChartType.LineMarkers); lineChart.SeriesCollection.Add(new Series(sheet.Range(0, 0, length - 1, 0))); lineChart.SeriesCollection.Add(new Series(sheet.Range(0, 1, length - 1, 1))); lineChart = sheet.Charts.AddChartAndFitInto(17, 10, 32, 16, ChartType.LineMarkersStacked); lineChart.SeriesCollection.Add(new Series(sheet.Range(0, 0, length - 1, 0))); lineChart.SeriesCollection.Add(new Series(sheet.Range(0, 1, length - 1, 1))); lineChart = sheet.Charts.AddChartAndFitInto(17, 17, 32, 23, ChartType.LineMarkersStacked100); lineChart.SeriesCollection.Add(new Series(sheet.Range(0, 0, length - 1, 0))); lineChart.SeriesCollection.Add(new Series(sheet.Range(0, 1, length - 1, 1))); lineChart = sheet.Charts.AddChartAndFitInto(33, 10, 48, 16, ChartType.Line3D); lineChart.SeriesCollection.Add(new Series(sheet.Range(0, 0, length - 1, 0))); lineChart.SeriesCollection.Add(new Series(sheet.Range(0, 1, length - 1, 1))); // Save it as XLS spreadsheet.SaveAs("Output.xls"); // Close the document spreadsheet.Close(); // Cleanup spreadsheet.Dispose(); // Open generated XLS file in default associated application Process.Start("Output.xls"); }
static void Main(string[] args) { // in this sample we scan cells with ages data for people // then mark all dates where age is between 10 and 19 years and mark these cells with red color // Create new Spreadsheet Spreadsheet document = new Spreadsheet(); document.LoadFromFile("Data.xls"); // Get worksheet by name Worksheet worksheet = document.Workbook.Worksheets.ByName("Sample"); // Years range int RangeMin = 10; int RangeMax = 19; // Minimum age DateTime YearMin = DateTime.Now; // Maximum age DateTime YearMax = DateTime.Now; // Minimum year YearMax = YearMax.AddYears(-RangeMin); // Maximum year YearMin = YearMin.AddYears(-RangeMax); // Check dates for (int i = 1; i < 8; i++) { // Set current cell Cell currentCell = worksheet.Cell(i, 1); DateTime dateBirth = currentCell.ValueAsDateTime; // Check current cell if (dateBirth < YearMin || dateBirth > YearMax) { // Set fill pattern currentCell.FillPattern = Bytescout.Spreadsheet.Constants.PatternStyle.Solid; // Markup wrong cell by red color currentCell.FillPatternForeColor = System.Drawing.Color.Red; } // Set cell format currentCell.NumberFormatString = "dd.mm.yyyy"; } // delete output file if exists already if (File.Exists("Output.xls")) { File.Delete("Output.xls"); } // Save document document.SaveAs("Output.xls"); // Close Spreadsheet document.Close(); // open generated XLS document in default program Process.Start("Output.xls"); }
static void Main(string[] args) { try { // create new Spreadsheet object Spreadsheet spreadsheet = new Spreadsheet(); // add new worksheet Worksheet sheet = spreadsheet.Workbook.Worksheets.Add("Sample"); // add a random numbers int length = 10; Random rnd = new Random(); for (int i = 0; i < length; i++) { sheet.Cell(i, 0).Value = i; sheet.Cell(i, 1).Value = rnd.NextDouble() * 10; sheet.Cell(i, 2).Value = rnd.NextDouble() * 10; } // add charts to worksheet Chart scatterChart = sheet.Charts.AddChartAndFitInto(1, 3, 16, 9, ChartType.XYScatter); scatterChart.SeriesCollection.Add(new Series(sheet.Range(0, 1, length - 1, 1), sheet.Range(0, 0, length - 1, 0))); scatterChart.SeriesCollection.Add(new Series(sheet.Range(0, 2, length - 1, 2))); scatterChart = sheet.Charts.AddChartAndFitInto(1, 10, 16, 16, ChartType.XYScatterSmooth); scatterChart.SeriesCollection.Add(new Series(sheet.Range(0, 1, length - 1, 1), sheet.Range(0, 0, length - 1, 0))); scatterChart.SeriesCollection.Add(new Series(sheet.Range(0, 2, length - 1, 2))); scatterChart = sheet.Charts.AddChartAndFitInto(1, 17, 16, 23, ChartType.XYScatterSmoothNoMarkers); scatterChart.SeriesCollection.Add(new Series(sheet.Range(0, 1, length - 1, 1), sheet.Range(0, 0, length - 1, 0))); scatterChart.SeriesCollection.Add(new Series(sheet.Range(0, 2, length - 1, 2))); scatterChart = sheet.Charts.AddChartAndFitInto(17, 10, 32, 16, ChartType.XYScatterLines); scatterChart.SeriesCollection.Add(new Series(sheet.Range(0, 1, length - 1, 1), sheet.Range(0, 0, length - 1, 0))); scatterChart.SeriesCollection.Add(new Series(sheet.Range(0, 2, length - 1, 2))); scatterChart = sheet.Charts.AddChartAndFitInto(17, 17, 32, 23, ChartType.XYScatterLinesNoMarkers); scatterChart.SeriesCollection.Add(new Series(sheet.Range(0, 1, length - 1, 1), sheet.Range(0, 0, length - 1, 0))); scatterChart.SeriesCollection.Add(new Series(sheet.Range(0, 2, length - 1, 2))); if (File.Exists("Output.xls")) { File.Delete("Output.xls"); } // Save it as XLS spreadsheet.SaveAs("Output.xls"); // close the document spreadsheet.Close(); // open output XLS Process.Start("Output.xls"); } catch (Exception e) { Console.WriteLine("CAN NOT EXECUTE: " + e.ToString()); Console.WriteLine("\nPress any key to exit"); Console.ReadKey(); } }
static void Main(string[] args) { // Create new Spreadsheet Spreadsheet document = new Spreadsheet(); // Add new worksheet Worksheet worksheet = document.Workbook.Worksheets.Add("AdvancedReport"); // Set Values worksheet.Cell(0, 0).Value = "Name"; worksheet.Cell(0, 1).Value = "Full Name"; Color headerColor = Color.FromArgb(75, 172, 198); Color contentColor = Color.FromArgb(141, 180, 227); // Set font bold for headers worksheet.Cell(0, 0).Font = new Font("Arial", 12, FontStyle.Bold); worksheet.Cell(0, 1).Font = new Font("Arial", 12, FontStyle.Bold); // Set back color for headers rows worksheet.Cell(0, 0).FillPattern = PatternStyle.Solid; worksheet.Cell(0, 0).FillPatternForeColor = headerColor; worksheet.Cell(0, 1).FillPattern = PatternStyle.Solid; worksheet.Cell(0, 1).FillPatternForeColor = headerColor; worksheet.Cell(6, 0).FillPattern = PatternStyle.Solid; worksheet.Cell(6, 0).FillPatternForeColor = headerColor; worksheet.Cell(6, 0).FontColor = Color.White; worksheet.Cell(6, 1).FillPattern = PatternStyle.Solid; worksheet.Cell(6, 1).FillPatternForeColor = headerColor; // Set back color for content rows for (int i = 1; i < 6; i++) { worksheet.Cell(i, 0).FillPattern = PatternStyle.Solid; worksheet.Cell(i, 0).FillPatternForeColor = contentColor; worksheet.Cell(i, 1).FillPattern = PatternStyle.Solid; worksheet.Cell(i, 1).FillPatternForeColor = contentColor; } worksheet.Cell(1, 0).Value = "Homer"; worksheet.Cell(1, 1).Value = "Homer Jay Simpson"; worksheet.Cell(2, 0).Value = "Marjorie"; worksheet.Cell(2, 1).Value = "Marjorie Marge Simpson (Bouvier)"; worksheet.Cell(3, 0).Value = "Bartholomew"; worksheet.Cell(3, 1).Value = "Bartholomew Jojo Bart Simpson"; worksheet.Cell(4, 0).Value = "Lisa"; worksheet.Cell(4, 1).Value = "Lisa Marie Simpson"; worksheet.Cell(5, 0).Value = "Margaret"; worksheet.Cell(5, 1).Value = "Margaret Maggie "; worksheet.Cell(6, 0).Value = "Count"; worksheet.Cell(6, 1).Value = "5"; worksheet.Columns[1].Width = 250; worksheet.Columns[2].Width = 250; // delete output file if exists already if (File.Exists("Output.xls")) { File.Delete("Output.xls"); } // Save document document.SaveAs("Output.xls"); // Close Spreadsheet document.Close(); // open generated XLS document in default program Process.Start("Output.xls"); }
/// <summary> /// Get Spreadsheet /// </summary> private static Spreadsheet _GetSpreadsheet(InvoiceData oInvoiceData) { // Create new Spreadsheet Spreadsheet document = new Spreadsheet(); document.Workbook.AutoCalculation = true; document.Workbook.DefaultFont = new SpreadsheetFont("Arial", 10); // Add new worksheet Worksheet Sheet = document.Workbook.Worksheets.Add("Invoice"); int startRow = 0; // 1. Write Company Name Sheet.Cell(startRow, 0).Value = oInvoiceData.CompanyName; Sheet.Cell(startRow, 0).Font = new Font("Arial", 15, FontStyle.Bold | FontStyle.Italic); // 2. Write Company Address Sheet.Cell((++startRow), 0).Value = oInvoiceData.CompanyAddress1; Sheet.Cell((++startRow), 0).Value = oInvoiceData.CompanyAddress2; startRow++; // 3. Write Invoice Info Sheet.Cell((++startRow), 2).Value = "Invoice No."; Sheet.Cell(startRow, 2).Font = new Font("Arial", 10, FontStyle.Bold); Sheet.Cell(startRow, 2).AlignmentHorizontal = Bytescout.Spreadsheet.Constants.AlignmentHorizontal.Right; Sheet.Cell(startRow, 3).Value = oInvoiceData.InvoiceNo; Sheet.Cell((++startRow), 2).Value = "Invoice Date."; Sheet.Cell(startRow, 2).Font = new Font("Arial", 10, FontStyle.Bold); Sheet.Cell(startRow, 2).AlignmentHorizontal = Bytescout.Spreadsheet.Constants.AlignmentHorizontal.Right; Sheet.Cell(startRow, 3).ValueAsDateTime = oInvoiceData.InvoiceDate; Sheet.Cell(startRow, 3).NumberFormatString = "mm/dd/yyyy"; // 4. Write Client Info Sheet.Cell((++startRow), 0).Value = oInvoiceData.ClientName; Sheet.Cell(startRow, 0).Font = new Font("Arial", 12, FontStyle.Bold); Sheet.Cell((++startRow), 0).Value = oInvoiceData.ClientAddress1; Sheet.Cell((++startRow), 0).Value = oInvoiceData.ClientAddress2; startRow++; // 5. Write Notes Sheet.Cell((++startRow), 0).Value = "Notes"; Sheet.Cell(startRow, 0).Font = new Font("Arial", 12, FontStyle.Bold); Sheet.Cell((++startRow), 0).Value = oInvoiceData.Notes; startRow += 2; // 6. Add Product Listing string startAddress = ""; string endAddress = ""; Sheet.Cell((++startRow), 0).Value = "Item"; _AddAllBorders(Sheet.Cell(startRow, 0)); Sheet.Cell(startRow, 0).Font = new Font("Arial", 11, FontStyle.Bold); Sheet.Cell(startRow, 1).Value = "Quantity"; _AddAllBorders(Sheet.Cell(startRow, 1)); Sheet.Cell(startRow, 1).AlignmentHorizontal = Bytescout.Spreadsheet.Constants.AlignmentHorizontal.Right; Sheet.Cell(startRow, 1).Font = new Font("Arial", 11, FontStyle.Bold); Sheet.Cell(startRow, 2).Value = "Price"; _AddAllBorders(Sheet.Cell(startRow, 2)); Sheet.Cell(startRow, 2).AlignmentHorizontal = Bytescout.Spreadsheet.Constants.AlignmentHorizontal.Right; Sheet.Cell(startRow, 2).Font = new Font("Arial", 11, FontStyle.Bold); Sheet.Cell(startRow, 3).Value = "Total"; _AddAllBorders(Sheet.Cell(startRow, 3)); Sheet.Cell(startRow, 3).AlignmentHorizontal = Bytescout.Spreadsheet.Constants.AlignmentHorizontal.Right; Sheet.Cell(startRow, 3).Font = new Font("Arial", 11, FontStyle.Bold); for (int i = 0; i < oInvoiceData.lstProducts.Count; i++) { Sheet.Cell((++startRow), 0).Value = oInvoiceData.lstProducts[i].ProductName; _AddAllBorders(Sheet.Cell(startRow, 0)); Sheet.Cell(startRow, 1).Value = oInvoiceData.lstProducts[i].Quantity; Sheet.Cell(startRow, 1).AlignmentHorizontal = Bytescout.Spreadsheet.Constants.AlignmentHorizontal.Right; _AddAllBorders(Sheet.Cell(startRow, 1)); Sheet.Cell(startRow, 2).Value = oInvoiceData.lstProducts[i].Price; Sheet.Cell(startRow, 2).AlignmentHorizontal = Bytescout.Spreadsheet.Constants.AlignmentHorizontal.Right; _AddAllBorders(Sheet.Cell(startRow, 2)); // Calculated cell string formulaMultiplication = string.Format("={0}*{1}", Sheet.Cell(startRow, 1).GetAddress().ToString(), Sheet.Cell(startRow, 2).GetAddress().ToString()); Sheet.Cell(startRow, 3).Formula = formulaMultiplication; Sheet.Cell(startRow, 3).AlignmentHorizontal = Bytescout.Spreadsheet.Constants.AlignmentHorizontal.Right; _AddAllBorders(Sheet.Cell(startRow, 3)); // Address to be used for total sum if (i == 0) { startAddress = Sheet.Cell(startRow, 3).GetAddress().ToString(); } endAddress = Sheet.Cell(startRow, 3).GetAddress().ToString(); } Sheet.Cell((++startRow), 2).Value = "TOTAL"; Sheet.Cell(startRow, 2).Font = new Font("Arial", 11, FontStyle.Bold); Sheet.Cell(startRow, 2).AlignmentHorizontal = Bytescout.Spreadsheet.Constants.AlignmentHorizontal.Right; string formulaTotalSum = string.Format("=SUM({0}:{1})", startAddress, endAddress); Sheet.Cell(startRow, 3).Formula = formulaTotalSum; Sheet.Cell(startRow, 3).Font = new Font("Arial", 11, FontStyle.Bold); Sheet.Cell(startRow, 3).AlignmentHorizontal = Bytescout.Spreadsheet.Constants.AlignmentHorizontal.Right; // AutoFit all columns Sheet.Columns[0].AutoFit(); Sheet.Columns[1].AutoFit(); Sheet.Columns[2].AutoFit(); Sheet.Columns[3].AutoFit(); // Return all formatted document return(document); }
public void OpenWorkSheet() { Spreadsheet document = new Spreadsheet(); document.LoadFromFile("Copy of validation_oct_18_MJE.xlsx"); if (_whichTest != "") { _xmlPathList = new List <String>(); if (_whichTest == "CFarmCroplands" || _whichTest == "CFarmComprehensive" || _whichTest == "All Cfarm Tests") { Worksheet worksheetAccount = document.Workbook.Worksheets.ByName("Account"); worksheetAccount.Cell(1, 1).Value = "Not Verified"; worksheetAccount.Cell(2, 1).Value = "Not Verified"; worksheetAccount.Cell(3, 1).Value = "Verified"; worksheetAccount.Cell(4, 1).Value = "Not Verified"; worksheetAccount.Cell(5, 1).Value = "Not Verified"; Worksheet worksheetActivity = document.Workbook.Worksheets.ByName("Activity"); worksheetActivity.Cell(1, 1).Value = "Verified"; worksheetActivity.Cell(2, 1).Value = "Not Verified"; worksheetActivity.Cell(3, 1).Value = "Verified"; worksheetActivity.Cell(4, 1).Value = "Not Verified"; worksheetActivity.Cell(5, 1).Value = "Not Verified"; worksheetActivity.Cell(6, 1).Value = "Verified"; worksheetActivity.Cell(7, 1).Value = "Verified"; Worksheet worksheetField = document.Workbook.Worksheets.ByName("Field"); worksheetField.Cell(1, 1).Value = "Verified"; worksheetField.Cell(2, 1).Value = "Verified"; worksheetField.Cell(3, 1).Value = "Verified"; worksheetField.Cell(4, 1).Value = "Not Verified"; worksheetField.Cell(5, 1).Value = "Not Verified"; worksheetField.Cell(6, 1).Value = "Not Verified"; worksheetField.Cell(7, 1).Value = "NotVerified"; worksheetField.Cell(8, 1).Value = "Not Verified"; worksheetField.Cell(9, 1).Value = "Verified"; worksheetField.Cell(10, 1).Value = "Not Verified"; worksheetField.Cell(11, 1).Value = "Not Verified"; worksheetField.Cell(12, 1).Value = "Not Verified"; worksheetField.Cell(13, 1).Value = "Not Verified"; worksheetField.Cell(14, 1).Value = "Not Verified"; worksheetField.Cell(15, 1).Value = "Not Verified"; Worksheet worksheetHistoric = document.Workbook.Worksheets.ByName("Historic"); worksheetHistoric.Cell(1, 1).Value = "Verified"; worksheetHistoric.Cell(2, 1).Value = "Not Verified"; worksheetHistoric.Cell(3, 1).Value = "Verified"; worksheetHistoric.Cell(4, 1).Value = "Verified"; worksheetHistoric.Cell(5, 1).Value = "Not Verified"; Worksheet worksheetCurrentFuture = document.Workbook.Worksheets.ByName("Current and Future"); worksheetCurrentFuture.Cell(1, 1).Value = "Verified"; worksheetCurrentFuture.Cell(2, 1).Value = "Verified"; worksheetCurrentFuture.Cell(3, 1).Value = "Verified"; worksheetCurrentFuture.Cell(4, 1).Value = "Verified"; worksheetCurrentFuture.Cell(5, 1).Value = "Verified"; worksheetCurrentFuture.Cell(6, 1).Value = "Verified"; worksheetCurrentFuture.Cell(7, 1).Value = "Verified"; worksheetCurrentFuture.Cell(8, 1).Value = "Verified"; worksheetCurrentFuture.Cell(9, 1).Value = "Verified"; worksheetCurrentFuture.Cell(10, 1).Value = "Not Verified"; worksheetCurrentFuture.Cell(11, 1).Value = "Verified"; worksheetCurrentFuture.Cell(12, 1).Value = "Verified"; worksheetCurrentFuture.Cell(13, 1).Value = "Verified"; worksheetCurrentFuture.Cell(14, 1).Value = "Not Verified"; worksheetCurrentFuture.Cell(15, 1).Value = "Not Verified"; worksheetCurrentFuture.Cell(16, 1).Value = "Verified"; worksheetCurrentFuture.Cell(17, 1).Value = "Not Verified"; worksheetCurrentFuture.Cell(18, 1).Value = "Verified"; worksheetCurrentFuture.Cell(19, 1).Value = "Verified"; worksheetCurrentFuture.Cell(20, 1).Value = "Verified"; worksheetCurrentFuture.Cell(21, 1).Value = "Not Verified"; Worksheet worksheetCroplandReports = document.Workbook.Worksheets.ByName("Reports"); worksheetCroplandReports.Cell(1, 1).Value = "Verified"; worksheetCroplandReports.Cell(5, 1).Value = "Verified"; } if (_whichTest == "CFarmAnimalAg" || _whichTest == "CFarmComprehensive" || _whichTest == "All Cfarm Tests") { Worksheet worksheetAnimalCat = document.Workbook.Worksheets.ByName("Animal Location and Cate"); worksheetAnimalCat.Cell(1, 1).Value = "Verified"; worksheetAnimalCat.Cell(2, 1).Value = "Verified"; worksheetAnimalCat.Cell(3, 1).Value = "Verified"; worksheetAnimalCat.Cell(4, 1).Value = "Verified"; worksheetAnimalCat.Cell(5, 1).Value = "Verified"; worksheetAnimalCat.Cell(6, 1).Value = "Verified"; worksheetAnimalCat.Cell(7, 1).Value = "Verified"; Worksheet worksheetAnimalChar = document.Workbook.Worksheets.ByName("Animal Char"); worksheetAnimalChar.Cell(1, 1).Value = "Verified"; worksheetAnimalChar.Cell(2, 1).Value = "Verified"; worksheetAnimalChar.Cell(3, 1).Value = "Verified"; worksheetAnimalChar.Cell(4, 1).Value = "Verified"; worksheetAnimalChar.Cell(5, 1).Value = "Verified"; worksheetAnimalChar.Cell(6, 1).Value = "Verified"; worksheetAnimalChar.Cell(7, 1).Value = "Verified"; worksheetAnimalChar.Cell(8, 1).Value = "Verified"; worksheetAnimalChar.Cell(9, 1).Value = "Verified"; worksheetAnimalChar.Cell(10, 1).Value = "Verified"; worksheetAnimalChar.Cell(11, 1).Value = "Verified"; worksheetAnimalChar.Cell(12, 1).Value = "Not Verified"; Worksheet worksheetAnimalAgReports = document.Workbook.Worksheets.ByName("Reports"); worksheetAnimalAgReports.Cell(2, 1).Value = "Verified"; worksheetAnimalAgReports.Cell(6, 1).Value = "Verified"; } if (_whichTest == "CFarmAgroforestry" || _whichTest == "CFarmComprehensive" || _whichTest == "All Cfarm Tests") { Worksheet worksheetAgroForestry = document.Workbook.Worksheets.ByName("Agroforestry"); worksheetAgroForestry.Cell(1, 1).Value = "Verified"; worksheetAgroForestry.Cell(2, 1).Value = "Verified"; worksheetAgroForestry.Cell(3, 1).Value = "Verified"; worksheetAgroForestry.Cell(4, 1).Value = "Verified"; worksheetAgroForestry.Cell(5, 1).Value = "Verified"; worksheetAgroForestry.Cell(6, 1).Value = "Verified"; worksheetAgroForestry.Cell(7, 1).Value = "Verified"; worksheetAgroForestry.Cell(8, 1).Value = "Verified"; worksheetAgroForestry.Cell(9, 1).Value = "Verified"; worksheetAgroForestry.Cell(10, 1).Value = "Verified"; Worksheet worksheetAgroReports = document.Workbook.Worksheets.ByName("Reports"); worksheetAgroReports.Cell(3, 1).Value = "Verified"; worksheetAgroReports.Cell(7, 1).Value = "Verified"; } if (_whichTest == "CFarmForestry" || _whichTest == "CFarmComprehensive" || _whichTest == "All Cfarm Tests") { Worksheet worksheetForestry = document.Workbook.Worksheets.ByName("Forestry"); worksheetForestry.Cell(1, 1).Value = "Verified"; worksheetForestry.Cell(2, 1).Value = "Verified"; worksheetForestry.Cell(3, 1).Value = "Verified"; worksheetForestry.Cell(4, 1).Value = "Verified"; worksheetForestry.Cell(5, 1).Value = "Verified"; worksheetForestry.Cell(6, 1).Value = "Verified"; worksheetForestry.Cell(7, 1).Value = "Verified"; worksheetForestry.Cell(8, 1).Value = "Verified"; worksheetForestry.Cell(9, 1).Value = "Verified"; worksheetForestry.Cell(10, 1).Value = "Verified"; Worksheet worksheetForestryReports = document.Workbook.Worksheets.ByName("Reports"); worksheetForestryReports.Cell(4, 1).Value = "Verified"; worksheetForestryReports.Cell(8, 1).Value = "Verified"; } } if (System.IO.File.Exists(Path.GetTempPath() + "VerifiedTest.xlsx")) { System.IO.File.Delete(Path.GetTempPath() + "VerifiedTest.xlsx"); } document.SaveAs(Path.GetTempPath() + "VerifiedTest.xlsx"); // Close Spreadsheet document.Close(); }
//VALIDAR CONTEUDO PLANILHA MATCH public Boolean ValidarConteudoPlanilha(string filePath, string fileName) { //EXTRAIR NOME DO ARQUIVO nomeArquivo = util.ExtrairDataArquivo(fileName); //IDENTIFICAR COLUNAS row = 0; i = 0; while (planilha.Cell(row, i).Value != null) { dicCols.Add(i, planilha.Cell(row, i).Value.ToString()); i++; } //SOMAR VALOR COLUNA FIN. VLR. LIQ. E VALOR COLUNA DIFERENÇA row = 1; i = 0; if (idMaquineta == "") { idMaquineta = planilha.Cell(row, i).Value.ToString(); //INICIALIZA PRIMEIRA LINHA } //PERCORRER LINHAS while (planilha.Cell(row, i).Value != null) { //PERCORRER LINHAS FinVlrLiq = FinVlrLiq + ColetarValor("Fin. Vlr. Liq.", row); Diferenca = Diferenca + ColetarValor("Diferença", row); //QUANTIFICAR OS IDS MAQUINETA if (idMaquineta != planilha.Cell(row, i).Value.ToString()) { idMaquineta = planilha.Cell(row, i).Value.ToString(); qtdeIDS++; } else { if (nomeArquivoAnterior == "") { nomeArquivoAnterior = nomeArquivo; } if (nomeArquivoAnterior != nomeArquivo) { idMaquineta = planilha.Cell(row, i).Value.ToString(); qtdeIDS++; nomeArquivoAnterior = nomeArquivo; } } row++; } finvlrliq = Convert.ToDouble(FinVlrLiq); ValAnterior = FinVlrLiq; //VALIDAR SE PASSOU DOS 1% DA SOMA TOTAL res = finvlrliq * 0.01; Resultado = Convert.ToDecimal(res); if (Diferenca > Resultado) { return(false); } else { return(true); } }
//VALIDAR CONTEUDO PLANILHA RELATORIO public Boolean ValidarConteudoPlanilhaRelatorio(string filePath, string fileName) { //verificar arquivo existente if (File.Exists(filePath + fileName)) { nomeArquivo = util.ExtrairDataArquivoRelatorio(fileName); //abrir planilha arqExcel = new Spreadsheet(); //DECLARANDO CAMINHO E ARQUIVO arqExcel.LoadFromFile(filePath + fileName); //PLANILHA CONTAGEM E ABERTURA num = arqExcel.Workbook.Worksheets.Count; somaValPOS = 0; if (num == 3) { //ABRINDO A PLANILHA var planGERAL = arqExcel.Worksheet(0).Name; var planPOS = arqExcel.Worksheet(1).Name; var planDETALHE = arqExcel.Worksheet(2).Name; //////////////////// //DADOS DE PLANILHA GERAL planilha = arqExcel.Workbook.Worksheets.ByName(planGERAL); if (planilha != null) { //IDENTIFICAR COLUNAS row = 0; i = 0; while (planilha.Cell(row, i).Value != null) { dicCols.Add(i, planilha.Cell(row, i).Value.ToString()); i++; } //COLETAR SALDO INICIAL, FINAL, DISPONIBILIZADO E CANCELAMENTOS row = 1; i = 0; while (planilha.Cell(row, i).Value != null) //PERCORRER LINHAS { //PERCORRER COLUNAS i = 0; while (i < dicCols.Count()) { //SaldoInicial SaldoFinal SaldoAnterior Disponibilizado Cancelamentos if (planilha.Cell(row, i).Value.ToString().ToUpper() == "SALDO INICIAL") { SaldoInicial = ColetarValor("Saldo", row); } if (planilha.Cell(row, i).Value.ToString().ToUpper() == "DISPONIBILIZADO") { Disponibilizado = ColetarValor("Valor", row); } if (planilha.Cell(row, i).Value.ToString().ToUpper() == "CANCELAMENTOS") { Cancelamentos = ColetarValor("Valor", row); } if (planilha.Cell(row, i).Value.ToString().ToUpper() == "SALDO FINAL") { SaldoFinal = ColetarValor("Saldo", row); } i++; } i = 0; row++; } } else { arqExcel.Dispose(); arqExcel.Close(); arqExcel = null; MessageBox.Show("Planilha " + planGERAL + "NÃO LOCALIZADA NO ARQUIVO"); return(false); } dicCols.Clear(); //////////////////////////// //DADOS DE PLANILHA POS planilha = arqExcel.Workbook.Worksheets.ByName(planPOS); if (planilha != null) { //IDENTIFICAR COLUNAS row = 0; i = 0; while (planilha.Cell(row, i).Value != null) { dicCols.Add(i, planilha.Cell(row, i).Value.ToString()); i++; } //COLETAR SALDO INICIAL, FINAL, DISPONIBILIZADO E CANCELAMENTOS row = 1; i = 0; var ativLoop = 1; var iloop = 0; somaValPOS = 0; //dicPosValor.Clear(); //PERCORRER LINHAS while (ativLoop == 1)//planilha.Cell(row, i).Value != null { if (planilha.Cell(row, i).Value == null) { if (iloop >= 2) { ativLoop = 0; break; } else { iloop = 0; iloop++; row++; } } //PERCORRER COLUNAS i = 0; //----------------- int colunaPOS = 1; //Coluna do ID POS = segunda coluna posicao 1 //para guardar no dicionario //string[] arrayValorPOS = new string[dicIDSPOS.Count()]; //string[] arraySomaValPOS = new string[dicIDSPOS.Count()]; //----------------- if (planilha.Cell(row, i).Value != null) { while (i < dicCols.Count()) { if (planilha.Cell(row, i).Value.ToString().ToUpper() == "DISPONIBILIZADO") { somaValPOS = somaValPOS + ColetarValor("Valor", row); } if (planilha.Cell(row, i).Value.ToString().ToUpper() == "CANCELAMENTOS") { somaValPOS = somaValPOS + ColetarValor("Valor", row); } if (planilha.Cell(row, i).Value.ToString().ToUpper() == "CHARGEBACKS") { somaValPOS = somaValPOS + ColetarValor("Valor", row); } if (planilha.Cell(row, i).Value.ToString().ToUpper() == "OUTROS AJUSTES") { somaValPOS = somaValPOS + ColetarValor("Valor", row); } //----------------- //se estiver na linha saldofinal, entao guarda a soma e o id if (planilha.Cell(row, i).Value.ToString().ToUpper() == "SALDO FINAL") { //aqui colocar validacao para caso somaValPOS tiver apenas uma casa depois da virgula, então adicionar um 0, exemplo: 11,5 para 11,50. int inicioAposVirgulaSomaValPOS = somaValPOS.ToString().IndexOf(","); string somaValPOSAposVirgula = somaValPOS.ToString().Substring(inicioAposVirgulaSomaValPOS + 1); string strSomaPosValor = ""; //Primeiro tratamento if (somaValPOSAposVirgula.Length == 1) { strSomaPosValor = somaValPOS.ToString() + "0"; dicPosValor.Add(indexIDS, nomeArquivo + "_" + planilha.Cell(row, colunaPOS).Value.ToString() + "_" + strSomaPosValor); } else { dicPosValor.Add(indexIDS, nomeArquivo + "_" + planilha.Cell(row, colunaPOS).Value.ToString() + "_" + somaValPOS.ToString()); } indexIDS++; //pos.Add(planilha.Cell(row, colunaPOS).Value.ToString()); //valor.Add(somaValPOS.ToString()); somaValPOS = 0; } //----------------- i++; } } else { iloop++; } ////----------------- //for (int b = 0; b < valor.Count(); b++) //{ // //dicPosValor.Add(b+1, arrayValorPOS[b] + "_" + arraySomaValPOS[b]); // dicPosValor.Add(b+1, pos[b] + "_" + valor[b]); //} ////----------------- i = 0; row++; } dicCols.Clear(); dicValues.Clear(); arqExcel.Dispose(); arqExcel.Close(); arqExcel = null; return(true); } else { arqExcel.Dispose(); arqExcel.Close(); arqExcel = null; MessageBox.Show("Planilha " + planPOS + "NÃO LOCALIZADA NO ARQUIVO"); return(false); } //////////////////////////// } } else { arqExcel.Dispose(); arqExcel.Close(); arqExcel = null; MessageBox.Show("Arquivo Não Localizado" + filePath + fileName); return(false); } return(false); }
//REALIZAR SOMA PELOS ID'S MATCHOPB public Boolean RealizarSomaIds(string filePath, string fileName) { //verificar arquivo existente if (File.Exists(filePath + fileName)) { //abrir planilha arqExcel = new Spreadsheet(); //DECLARANDO CAMINHO E ARQUIVO arqExcel.LoadFromFile(filePath + fileName); //PLANILHA CONTAGEM E ABERTURA num = arqExcel.Workbook.Worksheets.Count; if (num == 1) { //ABRINDO A PLANILHA var nomePlan = arqExcel.Worksheet(0).Name; planilha = arqExcel.Workbook.Worksheets.ByName(nomePlan); //EXTRAIR NOME DO ARQUIVO nomeArquivo = util.ExtrairDataArquivo(fileName); //////////////////////OPERACAO DE SOMA DE TODOS OS ID'S //IDENTIFICAR COLUNAS row = 0; i = 0; while (planilha.Cell(row, i).Value != null) { dicCols.Add(i, planilha.Cell(row, i).Value.ToString()); i++; } //IDENTIFICAR IDS DA PLANILHA /////////////////////////////////////////////////// //IDENTIFICANDO OS IDS MAQUINETA row = 1; i = 0; idMaquineta = ""; //INICIALIZA PRIMEIRA LINHA if (idMaquineta == "") { idMaquineta = planilha.Cell(row, i).Value.ToString(); } //PERCORRER LINHAS while (planilha.Cell(row, i).Value != null) { //QUANTIFICAR OS IDS MAQUINETA if (idMaquineta != planilha.Cell(row, i).Value.ToString()) { idMaquineta = planilha.Cell(row, i).Value.ToString(); qtdeIDS++; } row++; } //FIM QUANTIFICAR ID'S MAQUINETA /////////////////////////////////////////////////////////////////////////// //COLETAR IDS E JOGAR NO VETOR idMaquineta = ""; row = 1; i = 0; idsPOS = new string[qtdeIDS]; indexIDS = 0; //for (int j = 0; j < qtdeIDS; j++) //{ // idMaquineta = ""; // row = 1; //INICIALIZA PRIMEIRA LINHA if (idMaquineta == "") { idMaquineta = planilha.Cell(row, i).Value.ToString(); idsPOS[indexIDS] = planilha.Cell(row, i).Value.ToString(); indexIDS++; } //PERCORRER LINHAS while (planilha.Cell(row, i).Value != null) { //COLETAR IDS MAQUINETA if (idMaquineta != planilha.Cell(row, i).Value.ToString()) { idMaquineta = planilha.Cell(row, i).Value.ToString(); idsPOS[indexIDS] = planilha.Cell(row, i).Value.ToString(); indexIDS++; } row++; } //} //TRATAMENTO IDS MAQUINETA distincIDSPOS = idsPOS.Distinct().ToArray(); ///////////////////////////////////////////////// SOMA DOS VALORES POR IDS MAQUINETA indexIDS = 0; //dicIDSPOS.Clear(); //SOMA DOS VALORES for (int j = 0; j < distincIDSPOS.Count(); j++) { //SOMAR VALOR COLUNA FIN. VLR. LIQ. POR ID'S idMaquineta = ""; row = 1; i = 0; somaValPOS = 0; if (idMaquineta == "") { //INICIALIZA PRIMEIRA LINHA idMaquineta = distincIDSPOS[j]; } //PERCORRER LINHAS while (planilha.Cell(row, i).Value != null) { //QUANTIFICAR OS IDS MAQUINETA var tst = planilha.Cell(row, i).Value.ToString(); if (idMaquineta == planilha.Cell(row, i).Value.ToString()) { somaValPOS = somaValPOS + ColetarValor("Fin. Vlr. Liq.", row); } row++; } dicIDSPOS.Add(anteriorIDS, nomeArquivo + "_" + idMaquineta + "_" + somaValPOS); //somaPOS[anteriorIDS] = nomeArquivo + "_" + idMaquineta + "_" + somaValPOS; anteriorIDS++; indexIDS++; } dicCols.Clear(); //////////////////////OPERACAO DE SOMA DE TODOS OS ID'S if (arqExcel != null) { arqExcel.Dispose(); arqExcel.Close(); arqExcel = null; nomePlan = null; return(true); } } } else { if (arqExcel != null) { arqExcel.Dispose(); arqExcel.Close(); arqExcel = null; } MessageBox.Show("Arquivo Não Localizado" + filePath + fileName); return(false); } return(false); }
static void Main(string[] args) { var allInputFiles = new string[] { "Sample_Files\\InvoiceMar.pdf", "Sample_Files\\InvoiceApr.pdf" }; var resultFileName = "output.xlsx"; // Set page index var pageIndex = 0; // Create output Spreadsheet using (var document = new Spreadsheet()) { // Add new worksheet Worksheet worksheet = document.Workbook.Worksheets.Add("Invoices"); var ws_row = 0; var ws_column = 0; // Set Header value worksheet.Cell(ws_row, ws_column).Font = new Font("Arial", 11, FontStyle.Bold); worksheet.Cell(ws_row, (ws_column++)).Value = "Invoice"; worksheet.Cell(ws_row, ws_column).Font = new Font("Arial", 11, FontStyle.Bold); worksheet.Cell(ws_row, (ws_column++)).Value = "Product Name"; worksheet.Cell(ws_row, ws_column).Font = new Font("Arial", 11, FontStyle.Bold); worksheet.Cell((ws_row++), (ws_column++)).Value = "Price"; // Loop through all input files foreach (var itmFile in allInputFiles) { var invName = Path.GetFileNameWithoutExtension(itmFile); // Create TextExtractor instance using (var tableExtractor = new StructuredExtractor("demo", "demo")) { var tableExtractionArea = GetTableExtractionArea(itmFile); if (tableExtractionArea.HasValue) { // Load document from file tableExtractor.LoadDocumentFromFile(itmFile); // Set extraction area tableExtractor.SetExtractionArea(tableExtractionArea.Value); // Prepare table structure tableExtractor.PrepareStructure(pageIndex); int rowCount = tableExtractor.GetRowCount(pageIndex); // Ignoring first title row for (int row = 1; row < rowCount; row++) { int columnCount = tableExtractor.GetColumnCount(pageIndex, row); // Write Invoice Data ws_column = 0; worksheet.Cell(ws_row, (ws_column++)).Value = invName; for (int col = 0; col < columnCount; col++) { // Get table cell value var tableCellValue = tableExtractor.GetCellValue(pageIndex, row, col); // Write to spreadsheet worksheet.Cell(ws_row, (ws_column++)).Value = tableCellValue; } // Set row pointer to next row ws_row++; } } } } // Save document document.SaveAs(resultFileName, Bytescout.Spreadsheet.Constants.SpreadsheetFormatType.XLSX); } // Open generated XLSX file in default program Process.Start(resultFileName); }