public static void ReadXlsSheetDataBySpreadSheet(GemBox.Spreadsheet.ExcelFile ExcelFileObj, DataTable SourceDataTable)
{
try
{
int RowCount = ExcelFileObj.Worksheets[0].Rows.Count;
for (int i = 1; i < RowCount; i++)
{
DataRow dr = SourceDataTable.NewRow();
bool newRow = false;
for (int j = 0; j < SourceDataTable.Columns.Count; j++)
{
dr[j] = ExcelFileObj.Worksheets[0].Rows[i].Cells[j].Value == null ? DBNull.Value : ExcelFileObj.Worksheets[0].Rows[i].Cells[j].Value;
if (dr[j] != DBNull.Value)
newRow = true;
}
if(newRow)
SourceDataTable.Rows.Add(dr);
}
}
catch
{
throw;
}
}
/// <summary>
/// 设置表格的title
/// </summary>
/// <param name="dcc"></param>
/// <param name="worksheet"></param>
void SetExcelTitle(DataColumnCollection dcc, GemBox.Spreadsheet.ExcelWorksheet worksheet)
{
int iColumnCount = dcc.Count;
GemBox.Spreadsheet.CellRange range = worksheet.Cells.GetSubrangeAbsolute(0, 0, 0, iColumnCount - 1);//合并单元格
range.Merged = true;
worksheet.Rows[0].Cells[0].SetBorders(borderLeft, color, LineSyleThick);
worksheet.Rows[0].Cells[0].SetBorders(borderRight, color, LineSyleThick);
worksheet.Rows[0].Cells[0].SetBorders(borderTop, color, LineSyleThick);
worksheet.Rows[0].Cells[0].SetBorders(borderBottom, color, LineSyleThick);
string from = this.CADateTimeFrom.IsDateEmpty ? string.Empty : this.CADateTimeFrom.SelectedDate.ToString("yyyy-MM-dd");
string to = this.CADateTimeTo.IsDateEmpty ? string.Empty : this.CADateTimeTo.SelectedDate.ToString("yyyy-MM-dd");
worksheet.Rows[0].Cells[0].Value = string.Format("Nontrade List Report ({0} - {1})", from, to);
for (int i = 0; i < iColumnCount; i++)
{
if (i == 0)
{
worksheet.Rows[1].Cells[i].SetBorders(borderLeft, color, LineSyleThick);
}
else if (i == iColumnCount - 1)
{
worksheet.Rows[1].Cells[i].SetBorders(borderLeft, color, LineSyleThin);
worksheet.Rows[1].Cells[i].SetBorders(borderRight, color, LineSyleThick);
}
else
{
worksheet.Rows[1].Cells[i].SetBorders(borderLeft, color, LineSyleThin);
}
worksheet.Rows[1].Cells[i].SetBorders(borderTop, color, LineSyleThick);
worksheet.Rows[1].Cells[i].SetBorders(borderBottom, color, LineSyleMedium);
worksheet.Rows[1].Cells[i].Value = dcc[i].ColumnName;
}
//worksheet.Rows[1].Cells[iColumnCount - 1].SetBorders(borderRight, color, LineSyleThick);
}
void SetContent(DataTable dt, GemBox.Spreadsheet.ExcelWorksheet worksheet)
{
int iCount = dt.Rows.Count;
int iColumnCount = dt.Columns.Count;
DataColumnCollection dcc = dt.Columns;
for (int i = 0; i < iCount; i++)
{
for (int j = 0; j < iColumnCount; j++)
{
int iRowNO = i + 2;
if (j == 0)//第一列
{
worksheet.Rows[iRowNO].Cells[j].SetBorders(borderLeft, color, LineSyleThick);
}
else if (j == (iColumnCount - 1))//最后一列
{
worksheet.Rows[iRowNO].Cells[j].SetBorders(borderRight, color, LineSyleThick);
worksheet.Rows[iRowNO].Cells[j].SetBorders(borderLeft, color, LineSyleThin);
}
else//中间列
{
worksheet.Rows[iRowNO].Cells[j].SetBorders(borderLeft, color, LineSyleThin);
}
if (i != (iCount - 1))
{
worksheet.Rows[iRowNO].Cells[j].SetBorders(borderBottom, color, LineSyleThin);
}
else //最后一行。
{
worksheet.Rows[iRowNO].Cells[j].SetBorders(borderBottom, color, LineSyleThick);
}
worksheet.Rows[iRowNO].Cells[j].Value = dt.Rows[i][j];
}
}
}
/// <summary>
/// 设置excel内的样式
/// </summary>
/// <param name="worksheet1"></param>
/// <param name="iLastRowNO"></param>
/// <param name="listColumnName"></param>
void SetStyle(GemBox.Spreadsheet.ExcelWorksheet worksheet1, int iLastRowNO, List<string> listColumnName)
{
/*GemBox.Spreadsheet.MultipleBorders borderTop = GemBox.Spreadsheet.MultipleBorders.Top;
GemBox.Spreadsheet.MultipleBorders borderLeft = GemBox.Spreadsheet.MultipleBorders.Left;
GemBox.Spreadsheet.MultipleBorders borderRight = GemBox.Spreadsheet.MultipleBorders.Right;
GemBox.Spreadsheet.MultipleBorders borderBottom = GemBox.Spreadsheet.MultipleBorders.Bottom;*/
System.Drawing.Color color = System.Drawing.Color.Black;
// GemBox.Spreadsheet.LineStyle LineSyleThin = GemBox.Spreadsheet.LineStyle.Thin;//细线
GemBox.Spreadsheet.LineStyle LineSyleThick = GemBox.Spreadsheet.LineStyle.Thick;//粗实线
GemBox.Spreadsheet.CellStyle cs14 = new GemBox.Spreadsheet.CellStyle();
cs14.Font.Size = 200;
cs14.HorizontalAlignment = GemBox.Spreadsheet.HorizontalAlignmentStyle.Center;
/* GemBox.Spreadsheet.CellRange range = worksheet1.Cells.GetSubrangeAbsolute(0, 0, 0, 3);//合并单元格
range.Merged = true;
worksheet1.Rows[0].Cells[0].Value = "PRPOREPORT";
worksheet1.Rows[0].Cells[0].SetBorders(borderTop, color, LineSyleThick);
worksheet1.Rows[0].Cells[0].SetBorders(borderLeft, color, LineSyleThick);
worksheet1.Rows[0].Cells[0].SetBorders(borderRight, color, LineSyleThick);
worksheet1.Rows[0].Cells[0].Style.Font.Size=50;// = cs20;
*/
int iCo = listColumnName.Count;
for (int i = 0; i < iCo; i++)
{
worksheet1.Rows[0].Cells[i].Value = listColumnName[i];
worksheet1.Rows[0].Cells[i].Style = cs14;
}
}