partial void RandomizeClick(UIKit.UIBarButtonItem sender)
{
if (XlsUrl == null)
{
ShowHowToUse();
return;
}
XlsFile xls = new XlsFile(XlsPath, true);
//We'll go through all the numeric cells and make them random numbers
Random rnd = new Random();
for (int row = 1; row <= xls.RowCount; row++)
{
for (int colIndex = 1; colIndex < xls.ColCountInRow(row); colIndex++)
{
int XF = -1;
object val = xls.GetCellValueIndexed(row, colIndex, ref XF);
if (val is double)
{
xls.SetCellValue(row, xls.ColFromIndex(row, colIndex), rnd.Next());
}
}
}
//We can't save to the original file, we don't have permissions.
XlsPath = Path.Combine(
Environment.GetFolderPath(Environment.SpecialFolder.InternetCache),
"tmpFlexCel" + Path.GetExtension(XlsUrl.Path));
xls.Save(XlsPath);
Refresh();
}
private void AddXlsFile(string FileName)
{
XlsFile xls = new XlsFile();
try
{
xls.Open(FileName);
}
catch (FlexCelXlsAdapterException ex)
{
if (ex.ErrorCode == XlsErr.ErrInvalidPassword)
{
return;
}
throw;
}
for (int sheet = 1; sheet <= xls.SheetCount; sheet++)
{
xls.ActiveSheet = sheet;
for (int r = 1; r <= xls.RowCount; r++)
{
for (int cindex = 1; cindex <= xls.ColCountInRow(r); cindex++)
{
int XF = -1;
object cell = xls.GetCellValueIndexed(r, cindex, ref XF);
AddWord(Convert.ToString(cell), FileName); //we could use TFlxNumberFormat.FormatValue() here, but we don't care about formatted values for searching.
}
}
}
}
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 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);
}