private void UnloadWorksheet()
{
CboWorkSheets.SelectedIndex = -1;
LvWorksheet.Clear();
LblWSNameTxt.Text = string.Empty;
LblAmountRowsTxt.Text = string.Empty;
LblOriginalAmountRowsTxt.Text = string.Empty;
LblAmountColumnsTxt.Text = string.Empty;
}
private void CboWorkSheets_SelectedIndexChanged(object sender, EventArgs e)
{
WaitingMessage.WaitingMessage waitingMessage = null;
LvWorksheet.Clear();
if (CboWorkSheets.SelectedIndex == -1)
{
return;
}
try
{
Cursor.Current = Cursors.WaitCursor;
LvWorksheet.Columns.Add(string.Empty);
List <string> columns = new List <string> {
" "
};
WorkSheet workSheet = OpenedFile.WorkSheets[((ItemData)CboWorkSheets.SelectedItem).Name];
waitingMessage = new WaitingMessage.WaitingMessage("Loading worksheet",
"Please wait while the worksheet is loading." + Environment.NewLine +
"This may take few seconds...",
0);
// First loop to create as many columns in the ListView as there were in the Excel file
foreach (KeyValuePair <int, string> kvp in workSheet.Cells.Keys)
{
string column = kvp.Value;
if (!columns.Contains(column))
{
LvWorksheet.Columns.Add(column, column, -1, HorizontalAlignment.Center, string.Empty);
columns.Add(column);
}
}
// and as many items as there were rows in the file
Dictionary <int, List <Cell> > cells = workSheet.Cells.Values
.GroupBy(c => c.Row)
.OrderBy(cgb => cgb.Key)
.ToDictionary(cgb => cgb.Key,
cgb => cgb.Select(c => c)
.ToList());
foreach (int row in cells.Keys)
{
ListViewItem lvi = new ListViewItem(row.ToString());
for (int noColumn = 1; noColumn < columns.Count; noColumn += 1)
{
lvi.SubItems.Add(string.Empty);
}
LvWorksheet.Items.Add(lvi);
foreach (Cell cell in cells[row])
{
cell.ListViewItem = lvi;
}
}
// Second loop to actually fill in the ListView with the data read from the Excel file
int index = 0;
foreach (Cell cell in workSheet.Cells.Values)
{
ListViewItem lvi = cell.ListViewItem;
lvi.SubItems[columns.IndexOf(cell.Column)].Text = cell.OriginalValue;
lvi.SubItems[columns.IndexOf(cell.Column)].Tag = cell;
lvi.Checked = cell.Included;
index += 1;
}
// Ajust the columns width
LvWorksheet.AutoResizeColumns(ColumnHeaderAutoResizeStyle.ColumnContent);
LblWSNameTxt.Text = workSheet.Name;
LblAmountRowsTxt.Text = LvWorksheet.Items.Count.ToString();
LblOriginalAmountRowsTxt.Text = LvWorksheet.Items[LvWorksheet.Items.Count - 1].Text;
LblAmountColumnsTxt.Text = (LvWorksheet.Columns.Count - 1).ToString();
}
catch (Exception ex)
{
ErrorManagement.Log(ex);
}
finally
{
if (waitingMessage != null)
{
waitingMessage.CloseMessage();
}
EnableSaving(false);
EnableExport(true);
EnableDelete(true);
Cursor.Current = Cursors.Default;
}
}