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 }