public void LoadExcelSheets(string filepath)
{
CompoundDocument _doc = CompoundDocument.Open(filepath);
byte[] bookdata = _doc.GetStreamData("Workbook"); // Doc Theo Byte
if (bookdata == null)
{
return;
}
Workbook book = WorkbookDecoder.Decode(new MemoryStream(bookdata));
foreach (Worksheet sheet in book.Worksheets)
{
ExcelTabPage sheetPage = new ExcelTabPage(sheet.Name); // Tên Sheet
sheetPage.FileName = filepath;
dgvCells = new DataGridView(); // Tao Moi Datagridview
dgvCells.Dock = DockStyle.Fill;
//dgvCells.RowCount = sheet.Cells.LastRowIndex + 1;
//dgvCells.ColumnCount = sheet.Cells.LastColIndex + 1;
dgvCells.RowCount = 500;
dgvCells.ColumnCount = 500;
dgvCells.KeyUp += dgvCells_KeyUp;
// tranverse cells
foreach (Pair <Pair <int, int>, Cell> cell in sheet.Cells)
{
dgvCells[cell.Left.Right, cell.Left.Left].Value = cell.Right.Value;
if (cell.Right.Style.BackColor != Color.White)
{
dgvCells[cell.Left.Right, cell.Left.Left].Style.BackColor = cell.Right.Style.BackColor;
}
}
// tranvers rows by Index
for (int rowIndex = sheet.Cells.FirstRowIndex; rowIndex <= sheet.Cells.LastRowIndex; rowIndex++)
{
Row row = sheet.Cells.GetRow(rowIndex);
for (int colIndex = row.FirstColIndex; colIndex <= row.LastColIndex; colIndex++)
{
Cell cell = row.GetCell(colIndex);
}
}
// tranvers rows directly
foreach (KeyValuePair <int, Row> row in sheet.Cells.Rows)
{
foreach (KeyValuePair <int, Cell> cell in row.Value)
{
}
}
_doc.Close();
sheetPage.Controls.Add(dgvCells);
tabControl1.TabPages.Add(sheetPage);
tabControl1.SelectedTab = sheetPage;
}
}
private void Save()
{
ExcelTabPage page = tabControl1.SelectedTab as ExcelTabPage;
Workbook workbook = new Workbook();
Worksheet worksheet = new Worksheet(page.Text);
DataGridView dataView = page.Controls[0] as DataGridView;
for (int i = 0; i < 100; i++)
{
worksheet.Cells[i, 0] = new Cell(string.Empty);
}
for (int i = 0; i < dataView.RowCount; i++)
{
for (int j = 0; j < dataView.ColumnCount; j++)
{
if (dataView[j, i].Value != null)
{
worksheet.Cells[i, j] = new Cell(dataView[j, i].Value);
}
}
}
workbook.Worksheets.Add(worksheet);
workbook.Save(page.FileName);
}
public void LoadExcelSheets(string filepath)
{
CompoundDocument _doc = CompoundDocument.Open(filepath);
byte[] bookdata = _doc.GetStreamData("Workbook"); // Doc Theo Byte
if (bookdata == null) return;
Workbook book = WorkbookDecoder.Decode(new MemoryStream(bookdata));
foreach (Worksheet sheet in book.Worksheets)
{
ExcelTabPage sheetPage = new ExcelTabPage(sheet.Name); // Tên Sheet
sheetPage.FileName = filepath;
dgvCells = new DataGridView(); // Tao Moi Datagridview
dgvCells.Dock = DockStyle.Fill;
//dgvCells.RowCount = sheet.Cells.LastRowIndex + 1;
//dgvCells.ColumnCount = sheet.Cells.LastColIndex + 1;
dgvCells.RowCount = 500;
dgvCells.ColumnCount = 500;
dgvCells.KeyUp += dgvCells_KeyUp;
// tranverse cells
foreach (Pair<Pair<int, int>, Cell> cell in sheet.Cells)
{
dgvCells[cell.Left.Right, cell.Left.Left].Value = cell.Right.Value;
if (cell.Right.Style.BackColor != Color.White)
{
dgvCells[cell.Left.Right, cell.Left.Left].Style.BackColor = cell.Right.Style.BackColor;
}
}
// tranvers rows by Index
for (int rowIndex = sheet.Cells.FirstRowIndex; rowIndex <= sheet.Cells.LastRowIndex; rowIndex++)
{
Row row = sheet.Cells.GetRow(rowIndex);
for (int colIndex = row.FirstColIndex; colIndex <= row.LastColIndex; colIndex++)
{
Cell cell = row.GetCell(colIndex);
}
}
// tranvers rows directly
foreach (KeyValuePair<int, Row> row in sheet.Cells.Rows)
{
foreach (KeyValuePair<int, Cell> cell in row.Value)
{
}
}
_doc.Close();
sheetPage.Controls.Add(dgvCells);
tabControl1.TabPages.Add(sheetPage);
tabControl1.SelectedTab = sheetPage;
}
}
