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);
}
