public static string TextoCelula(XlsFile excel, string celula)
{
var linha = Convert.ToInt16(SoNumeros(celula));
var coluna = LetrasParaNumeros(SoLetras(celula));
return(excel.GetStringFromCell(linha, coluna));
}
protected DateTime ExtractDateTime(int rowIndex, int columnIndex)
{
var val = Xls.GetStringFromCell(rowIndex, columnIndex);
DateTime dt;
if (DateTime.TryParse(val, out dt))
{
return(dt);
}
var msg = string.Format("Bad date format at row {0} column {1} of file {2}",
rowIndex, columnIndex, Xls.ActiveFileName);
throw new FormatException(msg);
}
protected override void OnCreate(Bundle bundle)
{
string[] Predefined = new string[]
{
"5", "=A1 * 3 + 7", "=Sum(A1, A2)*9", "=Sin(a1) + cos(a2)", "=Average(a1:a4)",
"", "", "", "", "", "", "", "", "", "", ""
};
base.OnCreate(bundle);
bool Restoring = false;
xls = new XlsFile(true);
if (File.Exists(ConfigFile))
{
try
{
xls.Open(ConfigFile);
Restoring = true;
}
catch
{
//if the file is corrupt, we'll just ignore it.
//Restoring will be false, and we will create a new file.
}
}
if (!Restoring)
{
xls.NewFile(1);
for (int k = 0; k < Predefined.Length; k++)
{
xls.SetCellFromString(k + 1, 1, Predefined [k]); //Initialize the grid with something so users know what they have to do.
}
}
xls.Recalc();
TextView[] Results = new TextView[xls.RowCount];
var Layout = new TableLayout(this);
for (int i = 0; i < Results.Length; i++)
{
var Row = new TableRow(Layout.Context);
var ColHeading = new TextView(Row.Context);
ColHeading.Text = new TCellAddress(i + 1, 1).CellRef;
ColHeading.Gravity = GravityFlags.Left;
EditText CellValue = new EditText(Row.Context);
CellValue.Gravity = GravityFlags.Fill;
CellValue.Text = GetCellOrFormula(i + 1);
CellValue.Tag = i;
CellValue.AfterTextChanged += (object sender, Android.Text.AfterTextChangedEventArgs e) =>
{
int z = (int)(sender as EditText).Tag;
xls.SetCellFromString(z + 1, 1, (sender as EditText).Text);
xls.Recalc();
for (int k = 0; k < Results.Length; k++)
{
Results[k].Text = xls.GetStringFromCell(k + 1, 1);
}
};
Results[i] = new TextView(Row.Context);
Results[i].Gravity = GravityFlags.Right;
Results[i].Text = xls.GetStringFromCell(i + 1, 1);
Row.AddView(ColHeading);
Row.AddView(CellValue);
Row.AddView(Results[i]);
Layout.AddView(Row);
SetContentView(Layout);
}
}
private void ImportFile(string FileName, bool Formatted)
{
try
{
//Open the Excel file.
XlsFile xls = new XlsFile(false);
DateTime StartOpen = DateTime.Now;
xls.Open(FileName);
DateTime EndOpen = DateTime.Now;
//Set up the Grid
DisplayGrid.DataBindings.Clear();
DisplayGrid.DataSource = null;
DisplayGrid.DataMember = null;
DataSet dataSet1 = new DataSet();
sheetCombo.Items.Clear();
//We will create a DataTable "SheetN" for each sheet on the Excel sheet.
for (int sheet = 1; sheet <= xls.SheetCount; sheet++)
{
xls.ActiveSheet = sheet;
sheetCombo.Items.Add(xls.SheetName);
DataTable Data = dataSet1.Tables.Add("Sheet" + sheet.ToString());
Data.BeginLoadData();
try
{
int ColCount = xls.ColCount;
//Add one column on the dataset for each used column on Excel.
for (int c = 1; c <= ColCount; c++)
{
Data.Columns.Add(TCellAddress.EncodeColumn(c), typeof(String)); //Here we will add all strings, since we do not know what we are waiting for.
}
string[] dr = new string[ColCount];
int RowCount = xls.RowCount;
for (int r = 1; r <= RowCount; r++)
{
Array.Clear(dr, 0, dr.Length);
//This loop will only loop on used cells. It is more efficient than looping on all the columns.
for (int cIndex = xls.ColCountInRow(r); cIndex > 0; cIndex--) //reverse the loop to avoid calling ColCountInRow more than once.
{
int Col = xls.ColFromIndex(r, cIndex);
if (Formatted)
{
TRichString rs = xls.GetStringFromCell(r, Col);
dr[Col - 1] = rs.Value;
}
else
{
int XF = 0; //This is the cell format, we will not use it here.
object val = xls.GetCellValueIndexed(r, cIndex, ref XF);
TFormula Fmla = val as TFormula;
if (Fmla != null)
{
//When we have formulas, we want to write the formula result.
//If we wanted the formula text, we would not need this part.
dr[Col - 1] = Convert.ToString(Fmla.Result);
}
else
{
dr[Col - 1] = Convert.ToString(val);
}
}
}
Data.Rows.Add(dr);
}
}
finally
{
Data.EndLoadData();
}
DateTime EndFill = DateTime.Now;
statusBar.Text = String.Format("Time to load file: {0} Time to fill dataset: {1} Total time: {2}", (EndOpen - StartOpen).ToString(), (EndFill - EndOpen).ToString(), (EndFill - StartOpen).ToString());
}
//Set up grid.
DisplayGrid.DataSource = dataSet1;
DisplayGrid.DataMember = "Sheet1";
sheetCombo.SelectedIndex = 0;
DisplayGrid.CaptionText = FileName;
}
catch
{
DisplayGrid.CaptionText = "Error Loading File";
DisplayGrid.DataSource = null;
DisplayGrid.DataMember = "";
sheetCombo.Items.Clear();
throw;
}
}
public bool Upload2(HttpPostedFileBase file)
{
bool formatted = false;
List <string> sheetNames = new List <string>();
DataSet dataSet = new DataSet();
MemoryStream target = new MemoryStream();
file.InputStream.CopyTo(target);
byte[] data = target.ToArray();
HttpFile httpFile = new HttpFile(file.FileName, file.ContentType, data);
Stream stream = new MemoryStream(httpFile.Buffer);
XlsFile xls = new XlsFile(false);
xls.Open(stream);
for (int sheet = 1; sheet <= xls.SheetCount; sheet++)
{
xls.ActiveSheet = sheet;
sheetNames.Add(xls.SheetName);
DataTable Data = dataSet.Tables.Add("Sheet" + sheet.ToString());
Data.BeginLoadData();
try
{
int ColCount = xls.ColCount;
//Add one column on the dataset for each used column on Excel.
//for (int c = 1; c <= ColCount; c++)
//{
// Data.Columns.Add(TCellAddress.EncodeColumn(c), typeof(String)); //Here we will add all strings, since we do not know what we are waiting for.
//}
for (int c = 1; c <= ColCount; c++)
{
int r = 1;
int XF = 0; //This is the cell format, we will not use it here.
object val = xls.GetCellValueIndexed(r, c, ref XF);
string propName = string.Empty;
TFormula Fmla = val as TFormula;
propName = Convert.ToString(Fmla != null ? Fmla.Result : val);
Data.Columns.Add(propName, typeof(String));
}
string[] dr = new string[ColCount];
int RowCount = xls.RowCount;
for (int r = 2; r <= RowCount; r++)
{
Array.Clear(dr, 0, dr.Length);
//This loop will only loop on used cells. It is more efficient than looping on all the columns.
for (int cIndex = xls.ColCountInRow(r); cIndex > 0; cIndex--) //reverse the loop to avoid calling ColCountInRow more than once.
{
int Col = xls.ColFromIndex(r, cIndex);
if (formatted)
{
TRichString rs = xls.GetStringFromCell(r, Col);
dr[Col - 1] = rs.Value;
}
else
{
int XF = 0; //This is the cell format, we will not use it here.
object val = xls.GetCellValueIndexed(r, cIndex, ref XF);
TFormula Fmla = val as TFormula;
if (Fmla != null)
{
//When we have formulas, we want to write the formula result.
//If we wanted the formula text, we would not need this part.
dr[Col - 1] = Convert.ToString(Fmla.Result);
}
else
{
dr[Col - 1] = Convert.ToString(val);
}
}
}
Data.Rows.Add(dr);
}
}
finally
{
Data.EndLoadData();
}
}
DataTable resultDt = dataSet.Tables[0];
List <Student> Studentlist = new List <Student>();
Studentlist = Convertor.ConvertToList <Student>(resultDt);
//AutoMapper.Mapper.Initialize(cfg => cfg.CreateMissingTypeMaps = true);
//var students = AutoMapper.Mapper.Map<IDataReader, IEnumerable<Student>>(resultDt.CreateDataReader());
return(true);
}
public static string TextoCelula(XlsFile excel, string coluna, int linha)
{
var numColuna = LetrasParaNumeros(coluna);
return(excel.GetStringFromCell(linha, numColuna));
}
public static DataSet ToDataSet(byte[] data, bool firstRowIsPropertyName = true)
{
bool formatted = false;
DataSet dataSet = new DataSet();
Stream stream = new MemoryStream(data);
XlsFile xls = new XlsFile(false);
xls.Open(stream);
for (int sheet = 1; sheet <= xls.SheetCount; sheet++)
{
xls.ActiveSheet = sheet;
DataTable Data = dataSet.Tables.Add("Sheet" + sheet.ToString());
Data.BeginLoadData();
try
{
int ColCount = xls.ColCount;
int beginIndexRow = 1;
if (firstRowIsPropertyName)
{
beginIndexRow = 2;
for (int c = 1; c <= ColCount; c++)
{
int r = 1;
int XF = 0; // This is the cell format, we will not use it here.
object val = xls.GetCellValueIndexed(r, c, ref XF);
string propName = string.Empty;
TFormula Fmla = val as TFormula;
propName = Convert.ToString(Fmla != null ? Fmla.Result : val);
Data.Columns.Add(propName, typeof(String));
}
}
else
{
beginIndexRow = 1;
// Add one column on the dataset for each used column on Excel.
for (int c = 1; c <= ColCount; c++)
{
// Here we will add all strings, since we do not know what we are waiting for.
Data.Columns.Add(TCellAddress.EncodeColumn(c), typeof(String));
}
}
string[] dr = new string[ColCount];
int rowCount = xls.RowCount;
for (int r = beginIndexRow; r <= rowCount; r++)
{
Array.Clear(dr, 0, dr.Length);
//This loop will only loop on used cells. It is more efficient than looping on all the columns.
for (int cIndex = xls.ColCountInRow(r); cIndex > 0; cIndex--) //reverse the loop to avoid calling ColCountInRow more than once.
{
int Col = xls.ColFromIndex(r, cIndex);
if (formatted)
{
TRichString rs = xls.GetStringFromCell(r, Col);
dr[Col - 1] = rs.Value;
}
else
{
int XF = 0; //This is the cell format, we will not use it here.
object val = xls.GetCellValueIndexed(r, cIndex, ref XF);
TFormula Fmla = val as TFormula;
if (Fmla != null)
{
//When we have formulas, we want to write the formula result.
//If we wanted the formula text, we would not need this part.
dr[Col - 1] = Convert.ToString(Fmla.Result);
}
else
{
dr[Col - 1] = Convert.ToString(val);
}
}
}
Data.Rows.Add(dr);
}
}
finally
{
Data.EndLoadData();
}
}
return(dataSet);
}
