private static void SetExcelTableColumnWidth(this Excel.Worksheet workSheet, int row, int column, List <ExcelTableColumnHeader> columnHeaderList)
{
int rangeStartRow = row;
int rangeEndRow = row;
int rangeStartColumn = column;
int rangeEndColumn = column;
for (int j = 0; j < columnHeaderList.Count; j++)
{
ExcelTableColumnHeader columnHeader = columnHeaderList[j];
rangeEndColumn += columnHeader.UseExcelColumnCount - 1;
Excel.Range columnRange = workSheet.GetRange(rangeStartRow, rangeStartColumn, rangeEndRow,
rangeEndColumn);
//设置宽度
if (columnHeader.Width != null)
{
double columnWidth = (double)columnHeader.Width / columnHeader.UseExcelColumnCount;
workSheet.SetColumnWidth(columnRange, columnWidth);
}
rangeStartColumn = rangeEndColumn + 1;
rangeEndColumn = rangeStartColumn;
}
}
public ExcelTableColumnHeader this[string key]
{
get
{
for (int i = 0; i < _ColumnList.Count; i++)
{
ExcelTableColumnHeader columnHeader = _ColumnList[i];
if (key == columnHeader.ColumnName)
{
return(columnHeader);
}
}
return(null);
}
}
public string[,] GetHeaderStringArray()
{
List <ExcelTableColumnHeader> columnHeaderList = TableHeader.Columns.GetSortedColumnList();
int excelColumnCount = TableHeader.Columns.ExcelColumnCount;
string[,] array = new string[1, excelColumnCount];
int column = 0;
for (int j = 0; j < columnHeaderList.Count; j++)
{
ExcelTableColumnHeader columnHeader = columnHeaderList[j];
array[0, column] = columnHeader.DisplayName;
column += columnHeader.UseExcelColumnCount;
}
return(array);
}
public List <ExcelTableColumnHeader> GetSortedColumnList()
{
List <ExcelTableColumnHeader> columnList = new List <ExcelTableColumnHeader>();
for (int i = 0; i < _ColumnList.Count; i++)
{
ExcelTableColumnHeader header = _ColumnList[i];
if (header.Visible)
{
columnList.Add(header);
}
}
columnList.Sort((p1, p2) =>
{
return(p1.ColumnNo - p2.ColumnNo);
});
return(columnList);
}
public string[,] GetContentStringArray()
{
List <ExcelTableColumnHeader> columnHeaderList = TableHeader.Columns.GetSortedColumnList();
int excelColumnCount = TableHeader.Columns.ExcelColumnCount;
string[,] array = new string[TableContent.TableData.Rows.Count, excelColumnCount];
for (int i = 0; i < TableContent.TableData.Rows.Count; i++)
{
DataRow dataRow = TableContent.TableData.Rows[i];
int column = 0;
for (int j = 0; j < columnHeaderList.Count; j++)
{
ExcelTableColumnHeader columnHeader = columnHeaderList[j];
if (columnHeader.ColumnType == ExcelColumnType.text)
{
array[i, column] = dataRow[columnHeader.ColumnName].ToString();
}
else if (columnHeader.ColumnType == ExcelColumnType.picture)
{
array[i, column] = "";
}
else if (columnHeader.ColumnType == ExcelColumnType.rowNo)
{
array[i, column] = (i + 1).ToString();
}
else if (columnHeader.ColumnType == ExcelColumnType.empty)
{
array[i, column] = "";
}
column += columnHeader.UseExcelColumnCount;
}
}
return(array);
}
private static void SetExcelTableColumnStyle(this Excel.Worksheet workSheet, int startRow, int endRow, int column, List <ExcelTableColumnHeader> columnHeaderList)
{
int rangeStartRow = startRow;
int rangeEndRow = endRow;
int rangeStartColumn = column;
int rangeEndColumn = column;
for (int j = 0; j < columnHeaderList.Count; j++)
{
ExcelTableColumnHeader columnHeader = columnHeaderList[j];
rangeEndColumn += columnHeader.UseExcelColumnCount - 1;
Excel.Range columnRange = workSheet.GetRange(rangeStartRow, rangeStartColumn, rangeEndRow,
rangeEndColumn);
//设置对齐方式
workSheet.SetHorizontalAlignment(columnRange, columnHeader.GetHorizontalAlignment());
workSheet.SetVerticalAlignment(columnRange, columnHeader.GetVerticalAlignment());
rangeStartColumn = rangeEndColumn + 1;
rangeEndColumn = rangeStartColumn;
}
}
private static void MergeExcelTableCells(this Excel.Worksheet workSheet,
int startRow, int endRow, int startColumn, List <ExcelTableColumnHeader> columnHeaderList)
{
for (int i = startRow; i <= endRow; i++)
{
int rangeStartColumn = startColumn;
int rangeEndColumn = startColumn;
for (int j = 0; j < columnHeaderList.Count; j++)
{
ExcelTableColumnHeader columnHeader = columnHeaderList[j];
rangeEndColumn += columnHeader.UseExcelColumnCount - 1;
if (columnHeader.UseExcelColumnCount > 1)
{
Excel.Range columnRange = workSheet.GetRange(i, rangeStartColumn, i, rangeEndColumn);
workSheet.MergeCells(columnRange);
}
rangeEndColumn += 1;
rangeStartColumn = rangeEndColumn;
}
}
}
public void Add(ExcelTableColumnHeader value)
{
_ColumnList.Add(value);
}
public static void InsertExcelTable(this Excel.Worksheet workSheet, int row, int column, ExcelTable excelTable, Action <int, int> callback, out Action stopFunc)
{
bool stopFlag = false;
#region Chn 停止方法
stopFunc = delegate()
{
stopFlag = true;
};
#endregion
#region Chn 判断停止
if (stopFlag == true)
{
return;
}
#endregion
ExcelTableHeader tableHeader = excelTable.TableHeader;
ExcelTableContent tableContent = excelTable.TableContent;
Excel.Range tableHeaderRange = null;
Excel.Range tableContentRange = null;
List <ExcelTableColumnHeader> columnHeaderList = tableHeader.Columns.GetSortedColumnList();
int excelColumnCount = tableHeader.Columns.ExcelColumnCount;
//Chn 设置列样式
workSheet.SetExcelTableColumnWidth(row, column, columnHeaderList);
//Chn 合并表头单元格
workSheet.MergeExcelTableCells(row, row, column, columnHeaderList);
int headerStartRow = row;
int headerEndRow = row;
int headerStartColumn = column;
int headerEndColumn = column + excelColumnCount - 1;
//Chn 设置表头(内容，字体...)
if (tableHeader.Visible)
{
tableHeaderRange = workSheet.GetRange(headerStartRow, headerStartColumn, headerEndRow, headerEndColumn);
string[,] headerStringArray = excelTable.GetHeaderStringArray();
workSheet.SetBackgroundColor(tableHeaderRange, tableHeader.BackgroundColor);
workSheet.SetFont(tableHeaderRange, tableHeader.Font);
workSheet.SetBorder(tableHeaderRange, tableHeader.Border);
if (tableHeader.RowHeight != null)
{
workSheet.SetRowHeight(tableHeaderRange, (double)tableHeader.RowHeight);
}
workSheet.SetRangeValue(tableHeaderRange, headerStringArray);
}
#region Chn 判断停止
if (stopFlag == true)
{
return;
}
#endregion
if (tableContent.TableData == null)
{
return;
}
int contentRowCount = excelTable.TableContent.TableData.Rows.Count;
if (tableHeader.Visible)
{
//Chn 设置列样式
workSheet.SetExcelTableColumnStyle(row + 1, row + contentRowCount, column, columnHeaderList);
//Chn 合并表体单元格
workSheet.MergeExcelTableCells(row + 1, row + contentRowCount, column, columnHeaderList);
}
else
{
//Chn 设置列样式
workSheet.SetExcelTableColumnStyle(row, row + contentRowCount - 1, column, columnHeaderList);
//Chn 合并表体单元格
workSheet.MergeExcelTableCells(row, row + contentRowCount - 1, column, columnHeaderList);
}
int contentStartRow;
int contentEndRow;
int contentStartColumn;
int contentEndColumn;
if (tableHeader.Visible)
{
contentStartRow = row + 1;
contentEndRow = row + contentRowCount;
contentStartColumn = column;
contentEndColumn = column + excelColumnCount - 1;
}
else
{
contentStartRow = row;
contentEndRow = row + contentRowCount - 1;
contentStartColumn = column;
contentEndColumn = column + excelColumnCount - 1;
}
tableContentRange = workSheet.GetRange(contentStartRow, contentStartColumn, contentEndRow, contentEndColumn);
//Chn 设置表体(内容，字体...)
string[,] contentStringArray = excelTable.GetContentStringArray();
workSheet.SetBackgroundColor(tableContentRange, tableContent.BackgroundColor);
workSheet.SetFont(tableContentRange, tableContent.Font);
workSheet.SetBorder(tableContentRange, tableContent.Border);
if (tableContent.RowHeight != null)
{
workSheet.SetRowHeight(tableContentRange, (double)tableContent.RowHeight);
}
workSheet.SetRangeValue(tableContentRange, contentStringArray);
#region Chn 获取图片插入位置方法
CellRectangle columnFirstCellRactangle = null;
CellRectangle ret = new CellRectangle();
Func <int, int, CellRectangle> getColumnCellRectangle = delegate(int tableRow, int tableColumn)
{
if (columnFirstCellRactangle == null)
{
int cellRow = row + tableRow - 1;
if (tableHeader.Visible)
{
cellRow = cellRow + 1;
}
int cellStartColumn = column;
int cellEndColumn = column;
for (int i = 0; i < tableColumn; i++)
{
ExcelTableColumnHeader columnHeader = columnHeaderList[i];
cellStartColumn = cellEndColumn;
cellEndColumn += columnHeader.UseExcelColumnCount;
}
cellEndColumn--;
Excel.Range rng = workSheet.GetRange(cellRow, cellStartColumn, cellRow, cellEndColumn);
double top = Convert.ToSingle(rng.Top);
double left = Convert.ToSingle(rng.Left);
double width = Convert.ToSingle(rng.Width);
double height = Convert.ToSingle(rng.Height);
columnFirstCellRactangle = new CellRectangle(left, top, width, height);
}
double retTop = columnFirstCellRactangle.Top + (tableRow - 1) * columnFirstCellRactangle.Height;
double retLeft = columnFirstCellRactangle.Left;
double retWidth = columnFirstCellRactangle.Width;
double retHeight = columnFirstCellRactangle.Height;
ret.Top = retTop;
ret.Left = retLeft;
ret.Width = retWidth;
ret.Height = retHeight;
return(ret);
};
#endregion
#region Chn 判断停止
if (stopFlag == true)
{
return;
}
#endregion
#region Chn 插入图片
for (int j = 0; j < columnHeaderList.Count; j++)
{
ExcelTableColumnHeader columnHeader = columnHeaderList[j];
if (columnHeader.ColumnType == ExcelColumnType.picture)
{
DataTable dt = excelTable.TableContent.TableData;
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dataRow = dt.Rows[i];
string picPath = dataRow[columnHeader.ColumnName].ToString();
CellRectangle rect = getColumnCellRectangle(i + 1, j + 1);
if (File.Exists(picPath))
{
Excel.Shape pic = workSheet.InsertPictureByShape(picPath, (float)rect.Left,
(float)rect.Top);
//Chn 缩放
if (pic.Width > rect.Width || pic.Height > rect.Height)
{
double widthK = pic.Width / rect.Width;
double heightK = pic.Height / rect.Height;
if (widthK > heightK)
{
pic.Width = (float)(pic.Width / widthK);
pic.Height = (float)(pic.Height / widthK);
}
else
{
pic.Width = (float)(pic.Width / heightK);
pic.Height = (float)(pic.Height / heightK);
}
}
//Chn 居中
pic.Left += (float)((rect.Width - pic.Width) / 2);
pic.Top += (float)((rect.Height - pic.Height) / 2);
}
if (callback != null)
{
callback(i + 1, dt.Rows.Count);
}
#region Chn 判断停止
if (stopFlag == true)
{
return;
}
#endregion
}
columnFirstCellRactangle = null;
}
}
#endregion
}
