/// <summary>
/// Đọc một Sheet và hiển thị nó.
/// </summary>
/// <param name="objExcelSheet">Worksheet được hiển thị</param>
/// <param name="lblErrText">Hiển thị bất kỳ lỗi nào xảy ra.</param>
/// <returns>Trả về giá trị Control chứa thông tin của Worksheet</returns>
public Control ExcelSheetRead(Excel.Worksheet objExcelSheet, Label lblErrText)
{
int nMaxCol = ((Excel.Range)objExcelSheet.UsedRange).EntireColum n.Count;
int nMaxRow = ((Excel.Range)objExcelSheet.UsedRange).EntireRow.C ount;
Table tblOutput = new Table();
TableRow TRow = null;
TableCell TCell = null;
string strSize = "";
int nSizeVal = 0;
bool bMergeCells = false;
int nMergeCellCount = 0;
int nWidth = 0;
if (objExcelSheet == null)
{
return (Control)tblOutput;
}
tblOutput.CellPadding = 0;
tblOutput.CellSpacing = 0;
tblOutput.GridLines = GridLines.Both;
try
{
for (int nRowIndex = 10; nRowIndex <= nMaxRow; nRowIndex++)
{
TRow = null;
TRow = new TableRow();
for (int nColIndex = 1; nColIndex <= nMaxCol; nColIndex++)
{
TCell = null;
TCell = new TableCell();
if (((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).Value2 != null)
{
TCell.Text = ((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).Text.ToString();
//////////////////////////
if (((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).Comment != null)
{
TCell.ForeColor = System.Drawing.Color.Blue;
TCell.ToolTip = ((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).Comment.Shape.AlternativeText;
}
else
{
TCell.ForeColor = ConvertExcelColor2DotNetColor(((Excel.Range)objExc elSheet.Cells[nRowIndex, nColIndex]).Font.Color);
}
TCell.BorderWidth = 2;
TCell.Width = 140; //TCell.Width = 40;
//*
TCell.Font.Bold = (bool)((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).Font.Bold;
TCell.Font.Italic = (bool)((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).Font.Italic;
strSize = ((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).Font.Size.ToString();
nSizeVal = Convert.ToInt32(strSize);
TCell.Font.Size = FontUnit.Point(nSizeVal);
TCell.BackColor = ConvertExcelColor2DotNetColor(((Excel.Range)objExc elSheet.Cells[nRowIndex, nColIndex]).Interior.Color);
if ((bool)((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).MergeCells != false)
{
if (bMergeCells == false)
{
TCell.ColumnSpan = (int)((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).MergeArea.Columns.Count;
nMergeCellCount = (int)((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).MergeArea.Columns.Count;
nMergeCellCount--;
bMergeCells = true;
}
else if (nMergeCellCount == 0)
{
TCell.ColumnSpan = (int)((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).MergeArea.Columns.Count;
nMergeCellCount = (int)((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).MergeArea.Columns.Count;
nMergeCellCount--;
}
}
else
{
bMergeCells = false;
}
TCell.HorizontalAlign = ExcelHAlign2DotNetHAlign(((Excel.Range)objExcelShe et.Cells[nRowIndex, nColIndex]));
TCell.VerticalAlign = ExcelVAlign2DotNetVAlign(((Excel.Range)objExcelShe et.Cells[nRowIndex, nColIndex]));
TCell.Height = Unit.Point(Decimal.ToInt32(Decimal.Parse((((Excel. Range)objExcelSheet.Cells[nRowIndex, nColIndex]).RowHeight.ToString()))));
nWidth = Decimal.ToInt32(Decimal.Parse((((Excel.Range)objEx celSheet.Cells[nRowIndex, nColIndex]).ColumnWidth.ToString())));
TCell.Width = Unit.Point(nWidth * nWidth);
//*/
}
else
{
if ((bool)((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).MergeCells == false)
{
bMergeCells = false;
}
if (bMergeCells == true)
{
nMergeCellCount--;
continue;
}
TCell.Text = " ";
if (((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).Comment != null)
{
TCell.ForeColor = System.Drawing.Color.Blue;
TCell.ToolTip = ((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).Comment.Shape.AlternativeText;
}
else
{
TCell.ForeColor = ConvertExcelColor2DotNetColor(((Excel.Range)objExc elSheet.Cells[nRowIndex, nColIndex]).Font.Color);
}
TCell.Font.Bold = (bool)((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).Font.Bold;
TCell.Font.Italic = (bool)((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).Font.Italic;
strSize = ((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).Font.Size.ToString();
nSizeVal = Convert.ToInt32(strSize);
TCell.Font.Size = FontUnit.Point(nSizeVal);
TCell.BackColor = ConvertExcelColor2DotNetColor(((Excel.Range)objExc elSheet.Cells[nRowIndex, nColIndex]).Interior.Color);
TCell.Height = Unit.Point(Decimal.ToInt32(Decimal.Parse((((Excel. Range)objExcelSheet.Cells[nRowIndex, nColIndex]).RowHeight.ToString()))));
nWidth = Decimal.ToInt32(Decimal.Parse((((Excel.Range)objEx celSheet.Cells[nRowIndex, nColIndex]).ColumnWidth.ToString())));
TCell.Width = Unit.Point(nWidth * nWidth);
}
//TCell.BorderStyle = BorderStyle.Solid;
//TCell.BorderWidth = Unit.Point(1);
//TCell.BorderColor = System.Drawing.Color.Gray;
TRow.Cells.Add(TCell);
}
tblOutput.Rows.Add(TRow);
}
//DataSet ds = new DataSet();
//DataTable dt = new DataTable();
//DataRow dtRows;
//foreach (TableRow row in tblOutput.Rows)
//{
// dtRows = new DataRow();
// for (int i = 0; i < row.Cells.Count; i++)
// {
// dtRows[i] = row.Cells[i].ToString();
// }
// dt.Rows.Add(dtRows);
//}
//GridView1.DataSource = dt.DefaultView;
//GridView1.DataBind();
}
catch (Exception ex)
{
lblErrText.Text = ex.ToString();
}
return (Control)tblOutput;
}
///Tui dang nghien cu về vấn đề bóc tách dữ liệu file Exel, bạn xem và bỏ bớt mấy đoạn Code hiểu thị mầu đi là sài được