public void SaveAsTreeExcel(string filePath)
{
if (this.ColumnNames == null || this.ColumnNames.Length == 0)
{
throw new Exception("Please Set Property: ColumnsName!");
}
if (this.ColumnFields == null || this.ColumnFields.Length == 0)
{
throw new Exception("Please Set Property: ColumnFields!");
}
if (this.ColumnDetailNames == null || this.ColumnDetailNames.Length == 0)
{
throw new Exception("Please Set Property: ColumnDetailNames!");
}
if (this.ColumnDetailFields == null || this.ColumnDetailFields.Length == 0)
{
throw new Exception("Please Set Property: ColumnDetailFields!");
}
if (this.DataSourceForTreeExcel == null)
{
throw new Exception("Please Set Property: DataSource");
}
if (filePath.Equals(string.Empty))
{
throw new Exception("Please input file path");
}
Workbook workBook = new Workbook();
Worksheet workSheet = null;
workSheet = workBook.Worksheets.Add(this.SheetName);
workSheet.DefaultColumnWidth = 3000;
int rowIndex = 0;
#region -- 生成表头 --
for (int i = 0; i < this.ColumnNames.Length; i++)
{
workSheet.Rows[rowIndex].Cells[i].Value = this.ColumnNames[i];
workSheet.Rows[rowIndex].Cells[i].CellFormat.Alignment = HorizontalCellAlignment.Center;
workSheet.Rows[rowIndex].Cells[i].CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
}
#endregion
rowIndex++;
#region -- 生成数据行 --
foreach (DataRow row in this.DataSourceForTreeExcel.Tables[0].Rows)
{
ExcelRow excelRow = new ExcelRow();
excelRow.Cells = new List <ExcelCell>();
for (int i = 0; i < this.ColumnFields.Length; i++)
{
ExcelCell excelCell = new ExcelCell();
excelCell.Value = row[this.ColumnFields[i]].ToString();
excelCell.DataType = ExcelCellType.String;
excelRow.Cells.Add(excelCell);
}
DataRowEventArgs drEventArgs = new DataRowEventArgs();
drEventArgs.ExcelRow = excelRow;
drEventArgs.DataRow = row;
//触发事件
this.OnDataRowBind(this, drEventArgs);
for (int i = 0; i < this.ColumnFields.Length; i++)
{
if (((ExcelCell)excelRow.Cells[i]).DataType == ExcelCellType.String)
{
workSheet.Rows[rowIndex].Cells[i].Value = excelRow.Cells[i].Value;
workSheet.Rows[rowIndex].Cells[i].CellFormat.Alignment = HorizontalCellAlignment.Left;
workSheet.Rows[rowIndex].Cells[i].CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
}
else if (((ExcelCell)excelRow.Cells[i]).DataType == ExcelCellType.Int)
{
workSheet.Rows[rowIndex].Cells[i].Value = excelRow.Cells[i].Value;
workSheet.Rows[rowIndex].Cells[i].CellFormat.Alignment = HorizontalCellAlignment.Right;
workSheet.Rows[rowIndex].Cells[i].CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
}
else
{
}
}
rowIndex++;
string selectSQL = "";
for (int n = 0; n < RelationColumns.Length; n++)
{
selectSQL += RelationColumns[n] + "= '" + row[RelationColumns[n]].ToString() + "' and ";
}
selectSQL = selectSQL.Substring(0, selectSQL.Length - 5);
DataRow[] foundRow = this.DataSourceForTreeExcel.Tables[1].Select(selectSQL);
if (foundRow.Length > 0)
{
workSheet.Rows[rowIndex - 1].OutlineLevel = 0;
for (int j = 0; j < this.ColumnDetailNames.Length; j++)
{
workSheet.Rows[rowIndex].Cells[j].Value = this.ColumnDetailNames[j];
workSheet.Rows[rowIndex].Cells[j].CellFormat.Alignment = HorizontalCellAlignment.Center;
workSheet.Rows[rowIndex].Cells[j].CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
workSheet.Rows[rowIndex].OutlineLevel = 1;
workSheet.Rows[rowIndex].Hidden = true;
}
rowIndex++;
foreach (DataRow dataRow in foundRow)
{
ExcelRow excelChildRow = new ExcelRow();
excelChildRow.Cells = new List <ExcelCell>();
for (int i = 0; i < this.ColumnDetailFields.Length; i++)
{
if (i < RelationColumns.Length)
{
ExcelCell excelCell = new ExcelCell();
excelCell.Value = string.Empty;
excelCell.DataType = ExcelCellType.String;
excelChildRow.Cells.Add(excelCell);
}
else
{
ExcelCell excelCell = new ExcelCell();
excelCell.Value = dataRow[i].ToString();
excelCell.DataType = ExcelCellType.String;
excelChildRow.Cells.Add(excelCell);
}
}
DataRowEventArgs drChildEventArgs = new DataRowEventArgs();
drChildEventArgs.ExcelRow = excelChildRow;
drChildEventArgs.DataRow = dataRow;
//触发事件
this.OnDataRowBind(this, drChildEventArgs);
for (int i = 0; i < this.ColumnDetailFields.Length; i++)
{
if (((ExcelCell)excelRow.Cells[i]).DataType == ExcelCellType.String)
{
workSheet.Rows[rowIndex].Cells[i].Value = excelChildRow.Cells[i].Value;
workSheet.Rows[rowIndex].Cells[i].CellFormat.Alignment = HorizontalCellAlignment.Left;
workSheet.Rows[rowIndex].Cells[i].CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
}
else if (((ExcelCell)excelRow.Cells[i]).DataType == ExcelCellType.Int)
{
workSheet.Rows[rowIndex].Cells[i].Value = excelChildRow.Cells[i].Value;
workSheet.Rows[rowIndex].Cells[i].CellFormat.Alignment = HorizontalCellAlignment.Right;
workSheet.Rows[rowIndex].Cells[i].CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
}
else
{
}
}
workSheet.Rows[rowIndex].OutlineLevel = 1;
workSheet.Rows[rowIndex].Hidden = true;
rowIndex++;
}
}
}
#endregion
workBook.Save(filePath);
workSheet = null;
workBook = null;
}
/// <summary>
/// Save DataSource To Excel
/// </summary>
/// <param name="filePath">File Path(Include file name)</param>
public void SaveAs(string filePath)
{
if (this.ColumnNames == null || this.ColumnNames.Length == 0)
{
throw new Exception("Please Set Property: ColumnsName!");
}
if (this.ColumnFields == null || this.ColumnFields.Length == 0)
{
throw new Exception("Please Set Property: ColumnFields!");
}
if (this.DataSource == null)
{
throw new Exception("Please Set Property: DataSource");
}
if (filePath.Equals(string.Empty))
{
throw new Exception("Please input file path");
}
Workbook workBook = new Workbook();
Worksheet workSheet = null;
workSheet = workBook.Worksheets.Add(this.SheetName);
workSheet.DefaultColumnWidth = 3000;
int rowIndex = 0;
#region -- 生成表头 --
for (int i = 0; i < this.ColumnNames.Length; i++)
{
workSheet.Rows[rowIndex].Cells[i].Value = this.ColumnNames[i];
workSheet.Rows[rowIndex].Cells[i].CellFormat.Alignment = HorizontalCellAlignment.Center;
workSheet.Rows[rowIndex].Cells[i].CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
}
#endregion
rowIndex++;
#region -- 生成数据行 --
foreach (DataRow row in this.DataSource.Rows)
{
ExcelRow excelRow = new ExcelRow();
excelRow.Cells = new List <ExcelCell>();
for (int i = 0; i < this.ColumnFields.Length; i++)
{
ExcelCell excelCell = new ExcelCell();
excelCell.Value = row[this.ColumnFields[i]].ToString();
excelCell.DataType = ExcelCellType.String;
excelRow.Cells.Add(excelCell);
}
DataRowEventArgs drEventArgs = new DataRowEventArgs();
drEventArgs.ExcelRow = excelRow;
drEventArgs.DataRow = row;
//触发事件
this.OnDataRowBind(this, drEventArgs);
for (int i = 0; i < this.ColumnFields.Length; i++)
{
if (((ExcelCell)excelRow.Cells[i]).DataType == ExcelCellType.String)
{
workSheet.Rows[rowIndex].Cells[i].Value = excelRow.Cells[i].Value;
workSheet.Rows[rowIndex].Cells[i].CellFormat.Alignment = HorizontalCellAlignment.Left;
workSheet.Rows[rowIndex].Cells[i].CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
}
else if (((ExcelCell)excelRow.Cells[i]).DataType == ExcelCellType.Int)
{
workSheet.Rows[rowIndex].Cells[i].Value = excelRow.Cells[i].Value;
workSheet.Rows[rowIndex].Cells[i].CellFormat.Alignment = HorizontalCellAlignment.Right;
workSheet.Rows[rowIndex].Cells[i].CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
}
else
{
}
}
rowIndex++;
}
#endregion
workBook.Save(filePath);
workSheet = null;
workBook = null;
}