private void button2_Click(object sender, EventArgs e)
{
IXLWorkbook wb = new XLWorkbook(); //Create workbook used closedXML
IXLWorksheet ws = wb.Worksheets.Add("Sample Sheet"); //In workbook create worksheet and give name
//To delete worksheet ---> wb.Worksheet("Sample Sheet").Delete();
//Give headers using textbox inputs
ws.Cell(1, 1).Value = box_0101.Text;
ws.Cell(1, 2).Value = box_0102.Text;
ws.Cell(1, 3).Value = box_0103.Text;
ws.Cell(1, 4).Value = box_0104.Text;
ws.Cell(1, 5).Value = box_0105.Text;
ws.Cell(1, 6).Value = box_0106.Text;
ws.Cell(1, 7).Value = box_0107.Text;
ws.Cell(1, 8).Value = box_0108.Text;
//select range in cells
//IXLRange rng = ws.Range("A1:H1");
var firstCell = ws.FirstCellUsed();
var lastCell = ws.LastCellUsed();
var range = ws.Range(firstCell.Address, lastCell.Address);
range.Style.Border.OutsideBorder = XLBorderStyleValues.Medium;
ws.Row(1).Sort();
wb.SaveAs(@"Y:\Liverpool projects\Windows form app\Ruth\test.xlsx");
}
public static Dictionary <string, string> ReadExcel(string path, int fCell, int fRow)
{
try
{
using (XLWorkbook workBook = new XLWorkbook(path))
{
IXLWorksheet workSheet = workBook.Worksheet(1);
var range = workSheet.Range(workSheet.FirstCellUsed(), workSheet.LastCellUsed());
var dict = new Dictionary <string, string>();
var i = 0;
foreach (var item in range.Rows())
{
if (i >= fRow)
{
dict.TryAdd(item.Cell(fCell).Value.ToString(), item.Cell(fCell + 2).Value.ToString());
}
i++;
}
return(dict);
}
}
catch (Exception ex)
{
throw ex;
}
}
public object ExcelToJson(string excel_path, string isHeader, string importStartRow)
{
List <Dictionary <string, object> > resultJson = new List <Dictionary <string, object> >();
List <string> header = new List <string>();
XLWorkbook workbook = new XLWorkbook(excel_path);
IXLWorksheet worksheet = workbook.Worksheets.Worksheet(1);
var firstCell = worksheet.FirstCellUsed();
var lastCell = worksheet.LastCellUsed();
var firstRow = worksheet.FirstRow();
int hearderColumnsCount = lastCell.Address.ColumnNumber;
if (isHeader == "checked")
{
for (int col = 1; col <= hearderColumnsCount; col++)
{
header.Add(worksheet.Cell(1, col).Value.ToString());
}
}
else
{
Dictionary <string, object> jo = new Dictionary <string, object>();
for (int col = 1; col <= hearderColumnsCount; col++)
{
string id = "col_" + col.ToString();
header.Add(id);
jo.Add(id, id);
}
resultJson.Add(jo);
}
int row = 1;
foreach (IXLRow dr in worksheet.Rows())
{
if (row != 1)
{
if (int.Parse(importStartRow) > row)
{
row += 1;
continue;
}
}
Dictionary <string, object> jo = new Dictionary <string, object>();
jo.Add("sort_order", row);
var i = 1;
foreach (var head in header)
{
jo.Add(head, dr.Cell(i).Value.ToString());
i += 1;
}
resultJson.Add(jo);
row += 1;
}
return(resultJson);
}
private DataTable TransferExcelToDataTable(IXLWorksheet xlWorksheet)
{
var datatable = new DataTable();
var range = xlWorksheet.Range(xlWorksheet.FirstCellUsed(), xlWorksheet.LastCellUsed());
int col = range.ColumnCount();
int row = range.RowCount();
// add columns hedars
datatable.Clear();
for (int i = 1; i <= col; i++)
{
IXLCell column = xlWorksheet.Cell(1, i);
datatable.Columns.Add(column.Value.ToString());
}
// add rows data
int firstHeadRow = 0;
foreach (var item in range.Rows())
{
if (firstHeadRow != 0)
{
var array = new object[col];
for (int y = 1; y <= col; y++)
{
array[y - 1] = item.Cell(y).Value;
}
datatable.Rows.Add(array);
}
firstHeadRow++;
}
return(datatable);
}
private int GetRowCount(IXLWorksheet worksheet)
{
var mainFirstTableCell = worksheet.FirstCellUsed();
var mainLastTableCell = worksheet.LastCellUsed();
return(mainFirstTableCell == null || mainLastTableCell == null
? 1
: worksheet.Range(mainFirstTableCell.Address, mainLastTableCell.Address).RowCount() + 1);
}
public static void ReadFromXml(string filePath)
{
XLWorkbook wb = new XLWorkbook(filePath);
IXLWorksheet ws = wb.Worksheets.First();
IXLTable table = ws.Range(ws.FirstCellUsed(), ws.LastCellUsed()).AsTable();
/*string s = "";
* table.DataRange.CellsUsed().Count();
* table.DataRange.Rows().ForEach(row =>
* {
* s += "\n";
* row.Cells().ToList().ForEach(cell =>
* {
* s += cell.GetString()+"\t|||\t";
* });
* });
* if (true)
* {
*
* }*/
TimeTable orar = new TimeTable();
IXLCell grupaCell = null;
#region grupa
//cauta coloana care contine grupa
IXLRangeColumn grupaColoana = table.DataRange.FindColumn(column =>
{
grupaCell = column.Cells()?.FirstOrDefault(cell => cell.GetString().Equals("Grupa"));
return(grupaCell != null);
//return column.Cells().Any(cell => cell.GetString().Equals("Grupa"));
});
int id = 1;
//insereaza grupele care apar in excel in orar
grupaColoana.CellsUsed().Where(cell => !cell.GetString().Equals("Grupa")).ForEach(cell =>
{
orar.Groups.Add(new GroupDto(id++, cell.GetString()));
});
#endregion
table.Range(grupaCell.Address.RowNumber + 1, grupaCell.Address.ColumnNumber + 2,
table.LastCellUsed().Address.RowNumber, table.LastCellUsed().Address.ColumnNumber)
.CellsUsed().Where(cell => !table.Cell(cell.Address.RowNumber, 3).IsEmpty()).ForEach(cell =>
{
string[] split = cell.GetString().Split(",".ToCharArray());
//Programare prog = new Programare();
string materie = split[0];
string tipMaterie = split[1];
string sala = split[2];
string profesor = split[3];
});
}
//Read Excel data to generic list - overloaded version 2.
public static IList <T> GetDataToList <T>(string filePath, string sheetName, Func <IList <string>, T> addRecord)
{
List <T> resultList = new List <T>();
using (XLWorkbook wB = new XLWorkbook(filePath))
{
IXLWorksheet wS = wB.Worksheet(1);
//DataTable dt = new DataTable();
//bool firstRow = true;
//Used for sheet row data to be added through delegation.
var firstRow = true;
int lastRow = wS.LastRowUsed().RowNumber();
var rows = wS.Rows(1, lastRow);
foreach (IXLRow row in rows)
{
if (firstRow)
{
////foreach (IXLCell cell in row.Cells())
////{
//// dt.Columns.Add(cell.Value.ToString());
////}
firstRow = false;
continue;
}
if (row.IsEmpty())
{
continue;
}
var rowData = new List <string>();
//else
//{
//dt.Rows.Add();
//int i = 0;
//rowData.Clear();
foreach (IXLCell cell in row.Cells(wS.FirstCellUsed().Address.ColumnNumber, wS.LastCellUsed().Address.ColumnNumber))
{
rowData.Add(cell.Value.ToString());
}
//foreach (IXLCell cell in row.Cells())
//{
// rowData.Add(cell.Value.ToString());
//}
resultList.Add(addRecord(rowData));
}
// }
return(resultList);
}
}
public void LastCellFromMerge()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Sheet");
ws.Range("B2:D4").Merge();
string first = ws.FirstCellUsed(true).Address.ToStringRelative();
string last = ws.LastCellUsed(true).Address.ToStringRelative();
Assert.AreEqual("B2", first);
Assert.AreEqual("D4", last);
}
private static void CreateTable <TData>(IXLWorksheet worksheet, IList <ExcelColumnInfo <TData> > excelColumnInfos)
{
var firstCell = worksheet.FirstCellUsed();
var lastCell = worksheet.LastCellUsed();
var range = worksheet.Range(firstCell.Address, lastCell.Address);
foreach (var excelColumnInfo in excelColumnInfos.Where(x => x.DataFormat != null))
{
var index = excelColumnInfos.IndexOf(excelColumnInfo) + 1;
range.Column(index).Style.NumberFormat.Format = excelColumnInfo.DataFormat;
}
range.CreateTable();
worksheet.Columns().AdjustToContents();
}
public static void AreWorksheetsContentEquals(IXLWorksheet expected, IXLWorksheet actual)
{
if (expected == actual)
{
return;
}
Assert.AreEqual(expected.CellsUsed(XLCellsUsedOptions.All).Count(), actual.CellsUsed(XLCellsUsedOptions.All).Count(), "Cells used count failed");
IXLCell expectedFirstCellUsed = expected.FirstCellUsed(XLCellsUsedOptions.All);
IXLCell actualFirstCellUsed = actual.FirstCellUsed(XLCellsUsedOptions.All);
Assert.AreEqual(expectedFirstCellUsed.Address, actualFirstCellUsed.Address, "First cell used failed");
IXLCell expectedLastCellUsed = expected.LastCellUsed(XLCellsUsedOptions.All);
IXLCell actualLastCellUsed = actual.LastCellUsed(XLCellsUsedOptions.All);
Assert.AreEqual(expectedLastCellUsed.Address, actualLastCellUsed.Address, "Last cell used failed");
IXLRange range = expected.Range(expectedFirstCellUsed, expectedLastCellUsed);
foreach (IXLCell expectedCell in range.Cells())
{
IXLCell actualCell = actual.Cell(expectedCell.Address);
if (expectedCell.HasFormula)
{
Assert.AreEqual(expectedCell.FormulaA1, actualCell.FormulaA1, $"Cell {expectedCell.Address} FormulaA1 failed.");
//// For some reason sometimes the formulas "FormulaR1C1" are different although the formulas "FormulaA1" are match
//Assert.AreEqual(expectedCell.FormulaR1C1, actualCell.FormulaR1C1, $"Cell {expectedCell.Address} FormulaR1C1 failed.");
Assert.AreEqual(expectedCell.FormulaReference, actualCell.FormulaReference, $"Cell {expectedCell.Address} FormulaReference failed.");
}
else
{
Assert.AreEqual(expectedCell.Value, actualCell.Value, $"Cell {expectedCell.Address} Value failed.");
}
Assert.AreEqual(expectedCell.DataType, actualCell.DataType, $"Cell {expectedCell.Address} DataType failed.");
Assert.AreEqual(expectedCell.Active, actualCell.Active, $"Cell {expectedCell.Address} Active failed.");
AreColumnsEquals(expectedCell.WorksheetColumn(), actualCell.WorksheetColumn(), $"Column {actualCell.WorksheetColumn().RangeAddress} {{0}} failed.");
AreRowEquals(expectedCell.WorksheetRow(), actualCell.WorksheetRow(), $"Row {actualCell.WorksheetRow().RangeAddress} {{0}} failed.");
AreCellsStyleEquals(expectedCell.Style, actualCell.Style, $"Cell {expectedCell.Address} Style {{0}} failed.");
AreCellsCommentEquals(expectedCell.Comment, actualCell.Comment, $"Cell {expectedCell.Address} Comment {{0}} failed.");
}
AreMergedRangesEquals(expected.MergedRanges, actual.MergedRanges);
AreNamedRangesEquals(expected.NamedRanges, actual.NamedRanges);
ArePageSetupEquals(expected.PageSetup, actual.PageSetup, "PageSetup {0} failed.");
}
//TODO:
//Throw Exception
public DataTable InsertTblRegisteredStudents(String FilePath)
{
DataTable dt = null;
using (XLWorkbook workBook = new XLWorkbook(FilePath))
{
//Read the first Sheet from Excel file.
IXLWorksheet workSheet = workBook.Worksheet(1);
//Create a new DataTable.
dt = new DataTable();
//Loop through the Worksheet rows.
bool firstRow = true;
foreach (IXLRow row in workSheet.Rows())
{
if (!row.Cell(1).IsEmpty())
{
//Use the first row to add columns to DataTable.
if (firstRow)
{
foreach (IXLCell cell in row.Cells())
{
dt.Columns.Add(cell.Value.ToString());
}
firstRow = false;
}
else
{
//Add rows to DataTable.
dt.Rows.Add();
int i = 0;
foreach (IXLCell cell in row.Cells(workSheet.FirstCellUsed().Address.ColumnNumber, workSheet.LastCellUsed().Address.ColumnNumber))
{
dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
i++;
}
}
}
}
}
return(dt);
}
/// <summary>
/// To the data table.
/// </summary>
/// <param name="xlWorksheet">The xl worksheet.</param>
/// <returns>DataTable.</returns>
public static DataTable ToDataTable(this IXLWorksheet xlWorksheet)
{
var datatable = new DataTable();
var errorFound = false;
try
{
var range = xlWorksheet.Range(xlWorksheet.FirstCellUsed(), xlWorksheet.LastCellUsed()).RangeUsed();
var columnCount = range.ColumnCount();
var rowCount = range.RowCount();
datatable.Clear();
//
// Create our columns bases on the first row which contains the itemName and category names
//
try
{
if (rowCount < 1)
{
return(new DataTable());
}
var goodColumnCount = 0;
for (var i = 1; i <= columnCount; i++)
{
if (xlWorksheet.Cell(1, i).Value.ToString().GetClsString().IsNullOrEmpty())
{
break;
}
goodColumnCount++;
datatable.Columns.Add(new DataColumn
{
DataType = Type.GetType("System.String"),
ColumnName = xlWorksheet.Cell(1, i).Value.ToString().GetClsString(),
Caption = xlWorksheet.Cell(1, i).Value.ToString()
}
);
}
columnCount = goodColumnCount;
}
catch (Exception ex)
{
NLogger.Warn("Unable to create the data table columns. {0}", ex.Message);
return(new DataTable());
}
//
// Copy cell data into our datatable.
//
if (rowCount < 2)
{
return(datatable);
}
for (var i = 2; i <= rowCount; i++)
{
if (range.Row(i).Cell(1).Value.ToString().IsNullOrEmpty())
{
continue;
}
var array = new object[columnCount];
for (var y = 1; y <= columnCount; y++)
{
try
{
if (range.Row(i).Cell(y).HasFormula)
{
NLogger.Warn("Unable to process cells which contain a forumlas (to remove formulas: copy -> paste special values). Check on Sheet {0} row {1} column {2}", xlWorksheet.Name, i, y);
errorFound = true;
continue;
}
array[y - 1] = range.Row(i).Cell(y).Value.ToString();
if (y == 1)
{
//if (array[y-1].ToString().IsEqualTo("Spurlock - SP03 Bed Ash Silo Chute", true)) System.Diagnostics.Debugger.Break();
NLogger.Trace(array[y - 1]);
}
}
catch (Exception ex)
{
errorFound = true;
NLogger.Warn("Error on Sheet {0} row {1} column {2}", xlWorksheet.Name, i, y);
NLogger.Trace(ex.Message);
}
}
if (ArrayHasValue(array))
{
datatable.Rows.Add(array);
}
}
if (errorFound)
{
throw new Exception("There are value errors in the spreadsheet.");
}
}
catch (Exception ex)
{
NLogger.Warn(ex.Message);
datatable = new DataTable();
}
return(datatable);
}
private DataTable ConvertWorksheetToDataTable(IXLWorksheet worksheet)
{
var dataTable = new DataTable();
List <int> formattedColumnNumbers = new List <int>();
IXLRange range = worksheet.Range(worksheet.FirstCellUsed(), worksheet.LastCellUsed());
int columnCount = range.ColumnCount();
dataTable.Clear();
for (int i = 1; i <= columnCount; i++)
{
IXLCell cell = worksheet.Cell(1, i);
Type dataTableColumnType = GetDataType(range, cell.Address.ColumnNumber);
dataTable.Columns.Add(cell.Value.ToString(), dataTableColumnType);
}
int firstHeadRow = 0;
foreach (var row in range.Rows())
{
if (firstHeadRow != 0)
{
var array = new object[columnCount];
for (int y = 1; y <= columnCount; y++)
{
if (!row.Cell(y).HasFormula)
{
if (row.Cell(y).Value == null || string.IsNullOrEmpty(row.Cell(y).Value.ToString()))
{
array[y - 1] = null;
}
else if (formattedColumnNumbers.Contains(row.Cell(y).Address.ColumnNumber))
{
array[y - 1] = row.Cell(y).GetFormattedString();
}
else if (row.Cell(y).DataType == XLDataType.DateTime)
{
DateTime dateTime = row.Cell(y).GetDateTime();
array[y - 1] = dateTime.TimeOfDay.Ticks == 0
? dateTime.ToString("MM/dd/yyyy")
: dateTime.ToString();
}
else
{
row.Cell(y).SetDataType(XLDataType.Text);
array[y - 1] = row.Cell(y).Value;
}
}
}
dataTable.Rows.Add(array);
}
firstHeadRow++;
}
return(dataTable);
}
private void prepDashboardSheets(IXLWorksheet sheet)
{
/*.
* can use htis for both types of sheets.
* Regular: List.Count -1 (end of list: should be Month) List.Count -2 (last value: Should be provider name)
* Diabetes: List.Count -1 (end of list: Month) List.Count -3 (blank space between provider name an dmonth on this one)
*/
_worksheet = sheet;
//use this one to keep track of provider name and row number. then send it to metrics to dashboard and do accordignly
Dictionary<String, IXLRangeRow> providerRows = new Dictionary<String, IXLRangeRow>();
if (_worksheet != null)
{
var firstCell = _worksheet.FirstCellUsed();
var lastCell = _worksheet.LastCellUsed();
_worksheet_range = _worksheet.Range(firstCell.Address, lastCell.Address);
if (_worksheet_range != null)
{
int nRows = _worksheet_range.RowCount();
int nCols = _worksheet_range.ColumnCount();
for (int i = 1; i < nRows + 1; i++)
{
var row = _worksheet_range.Row(i);
var newRow = _worksheet_range.Row(i + 1);
string value = row.Cell(1).Value as string;
if (value != null)
{
foreach (string provider in providers)
{
if (value.Contains(provider))
{
if (_worksheet == _dashboard.Worksheet(2))//add a new row for the depression sheets
{
newRow = _worksheet_range.Row(i + 3);
newRow.InsertRowsBelow(1);
var blankRow = _worksheet_range.Row(i + 4);
blankRow.Style.NumberFormat.NumberFormatId = 0;
providerRows.Add(value, blankRow);
}
else //add a new row for every other sheet in the dashboard: Asthma, Diabetes, Cardiovascular, Preventive
{
newRow = _worksheet_range.Row(i + 2);//this gets us int he right area and then insert the row above
newRow.InsertRowsBelow(1); //try to insert rows after we have metrics and tehn insert metrics into cells then insert row
var blankRow = _worksheet_range.Row(i + 3);
blankRow.Style.NumberFormat.NumberFormatId = 0;
providerRows.Add(value, blankRow);
}
break; //break out of the foreach provider loop, we already found one, we wont find another match, time to go to the next row instead
}
}
}
}
MetricsToDashboard(providerRows, _worksheet);//figure out what we need to send to this method , worksheet, provider / row dict etc.
}
}
}
public ViewResult saveToDatabase(HttpPostedFileBase excelFile)
{
try
{
//Save the uploaded Excel file.
string path = Server.MapPath("~/Uploads/");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
excelFile.SaveAs(path + Path.GetFileName(excelFile.FileName));
using (XLWorkbook workBook = new XLWorkbook(path + Path.GetFileName(excelFile.FileName)))
{
String columnsNamesQuery = String.Format("CREATE TABLE {0} (", excelFile.FileName.Split('.')[0]);
String insertQuery = String.Format("INSERT INTO {0} (", excelFile.FileName.Split('.')[0]);
IXLWorksheet workSheet = workBook.Worksheet(1);
bool firstRow = true;
foreach (IXLRow row in workSheet.Rows())
{
if (firstRow)
{
foreach (IXLCell cell in row.Cells())
{
columnsNamesQuery += "[" + cell.Value.ToString() + "] VARCHAR(MAX),";
insertQuery += "[" + cell.Value.ToString() + "],";
}
insertQuery = insertQuery.Remove(insertQuery.Length - 1, 1);
insertQuery += ") VALUES (";
columnsNamesQuery = columnsNamesQuery.Remove(columnsNamesQuery.Length - 1, 1);
columnsNamesQuery += ");";
//Creating data table
Command.Connection = Connection;
Connection.Open();
Command.CommandText = columnsNamesQuery;
Command.ExecuteNonQuery();
Connection.Close();
firstRow = false;
}
else
{
String rowsQuery = insertQuery;
foreach (IXLCell cell in row.Cells(workSheet.FirstCellUsed().Address.ColumnNumber, workSheet.LastCellUsed().Address.ColumnNumber))
{
if (cell.IsEmpty())
{
rowsQuery += "NULL,";
}
else
{
rowsQuery += "'" + cell.Value.ToString() + "' ,";
}
}
rowsQuery = rowsQuery.Remove(rowsQuery.Length - 1, 1);
rowsQuery += ");";
//Insert data into database
Command.Connection = Connection;
Connection.Open();
Command.CommandText = rowsQuery;
Command.ExecuteNonQuery();
Connection.Close();
}
}
}
return(View("Success"));
}
catch (Exception e)
{
ViewBag.error = "ERROR: " + e.Message + "<br />";
return(View("Index"));
}
}
//カレントSheetのデータ範囲開始行を取得
private int getStartRow()
{
var first = _currentWs.FirstCellUsed();
return(first.WorksheetRow().RowNumber());
}
private List<String> ReturnMetricsRow(IXLWorksheet sheet)
{
List<String> metricNames = new List<string>();
_worksheet = sheet;
if (_worksheet != null)
{
var firstCell = _worksheet.FirstCellUsed();
var lastCell = _worksheet.LastCellUsed();
_worksheet_range = _worksheet.Range(firstCell.Address, lastCell.Address);
if (_worksheet_range != null)
{
int nRows = _worksheet_range.RowCount();
int nCols = _worksheet_range.ColumnCount();
for (int i = 1; i < nRows + 1; i++)
{
var row = _worksheet_range.Row(i);
var newRow = _worksheet_range.Row(i + 1);
string value = row.Cell(1).Value as string;
if (value == "Month")
{
var metricRow = _worksheet_range.Row(i);
for (int x = 1; x <= metricRow.CellCount(); x++)
{
metricNames.Add(metricRow.Cell(x).Value.ToString());
}
break;
}
}
}
}
return metricNames;
}
