/// <summary>
/// Sets a cell-value-based conditional formatting.
/// </summary>
/// <param name="range">The range.</param>
/// <param name="rules">The conditional formatting rules to apply.</param>
/// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception>
public static void SetCellValueConditionalFormatting(
this Range range,
IReadOnlyList <CellValueConditionalFormattingRule> rules)
{
if (range == null)
{
throw new ArgumentNullException(nameof(range));
}
if (rules != null)
{
var formatConditionsIndex = range.Worksheet.ConditionalFormattings.Add();
var formatConditions = range.Worksheet.ConditionalFormattings[formatConditionsIndex];
var cellArea = range.GetCellArea();
formatConditions.AddArea(cellArea);
foreach (var rule in rules)
{
var operatorType = rule.Operator.ToOperatorType();
var conditionIndex = formatConditions.AddCondition(FormatConditionType.CellValue, operatorType, rule.Operand1Formula, rule.Operand2Formula);
var formatCondition = formatConditions[conditionIndex];
formatCondition.StopIfTrue = rule.StopIfTrue;
// need a way to leverage our Set... methods above to inflate the style.
var backgroundColor = rule.RangeStyle?.BackgroundColor;
if (backgroundColor != null)
{
formatCondition.Style.BackgroundColor = (Color)backgroundColor;
}
}
}
}
/// <summary>
/// Autofits rows for a range.
/// </summary>
/// <param name="range">The range.</param>
/// <param name="autofitRows">Use true to autofit rows.</param>
/// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception>
public static void SetAutofitRows(
this Range range,
bool?autofitRows)
{
if (range == null)
{
throw new ArgumentNullException(nameof(range));
}
if ((autofitRows != null) && (bool)autofitRows)
{
var columnNumbers = range.GetColumnNumbers();
var minColumnNumber = columnNumbers.Min();
var maxColumnNumber = columnNumbers.Max();
foreach (var rowNumber in range.GetRowNumbers())
{
var rowRangeCells = range.Worksheet.GetRange(rowNumber, rowNumber, minColumnNumber, maxColumnNumber).GetCells();
if (rowRangeCells.Any(_ => _.IsMerged))
{
var autoFitterOptions = new AutoFitterOptions {
AutoFitMergedCells = true
};
range.Worksheet.AutoFitRow(rowNumber - 1, minColumnNumber - 1, maxColumnNumber - 1, autoFitterOptions);
}
else
{
range.Worksheet.AutoFitRow(rowNumber - 1);
}
}
}
}
private static void SetStyle(
this Range range,
StyleContainer styleContainer,
Action <StyleContainer> configureStyleContainer)
{
if (range == null)
{
throw new ArgumentNullException(nameof(range));
}
if (configureStyleContainer == null)
{
throw new ArgumentNullException(nameof(configureStyleContainer));
}
var applyToRange = styleContainer == null;
if (styleContainer == null)
{
styleContainer = StyleContainer.BuildNew(range.Worksheet.Workbook);
}
configureStyleContainer(styleContainer);
if (applyToRange)
{
styleContainer.ApplyToRange(range);
}
}
static void Main(string[] args)
{
//Instantiate a new Workbook.
Workbook workbook = new Workbook();
//Specify the template excel file path.
string myPath = "Book1.xls";
//Open the excel file.
workbook.Open(myPath);
//Get the range of cells i.e.., A1:C1.
Aspose.Cells.Range rng1 = workbook.Worksheets[0].Cells.CreateRange("A1", "C1");
//Merge the cells.
rng1.Merge();
Cells rng = workbook.Worksheets[0].Cells;
//UnMerge the cell.
rng.UnMerge(0, 0, 1, 3);
//Save the file.
workbook.Save("Book1.xls");
}
private static Excel.Range GetUsedRange(Excel.Worksheet worksheet)
{
const int MAX_COLUMNS = 20;
const int MAX_ROWS = 200;
int maxUsedRow = 0;
int maxUsedColumn = 0;
Excel.Range initialRange = worksheet.Cells.CreateRange(0, 0, MAX_ROWS, MAX_COLUMNS);
for (int counter = 0; counter < MAX_COLUMNS; counter++)
{
maxUsedColumn = counter;
if (worksheet.Cells[0, counter].Value == null || worksheet.Cells[0, counter].Value.ToString().Length == 0)
{
break;
}
}
for (int counter = 0; counter < MAX_ROWS; counter++)
{
maxUsedRow = counter;
if (worksheet.Cells[counter, 0].Value == null || worksheet.Cells[counter, 0].Value.ToString().Length == 0)
{
break;
}
}
Excel.Range result = worksheet.Cells.CreateRange(0, 0, maxUsedRow, maxUsedColumn);
return(result);
}
private void SetStyle(Cells cells, dynamic reportDefinition)
{
object startingRowPosition = reportDefinition.RowPosition.Start;
object endingRowPosition = reportDefinition.RowPosition.End;
string startingColumnPosition = reportDefinition.ColumnPosition.Start;
string endingColumnPoisiton = reportDefinition.ColumnPosition.End;
string headerStart = string.Concat(startingColumnPosition, startingRowPosition);
string headerEnd = string.Concat(endingColumnPoisiton, endingRowPosition);
string TextAlignment = reportDefinition.TextAlign;
cells[headerStart].PutValue(reportDefinition.content);
Style headerStyleDefinition = _workbook.Styles[_workbook.Styles.Add()];
headerStyleDefinition.ForegroundColor = Color.Black;
headerStyleDefinition.Font.Name = reportDefinition.Font.Name;
headerStyleDefinition.Font.Size = reportDefinition.Font.Size;
headerStyleDefinition.Font.Color = Color.FromName(reportDefinition.Font.Color);
headerStyleDefinition.Font.IsBold = true;
headerStyleDefinition.HorizontalAlignment = TextAlignmentType.Center;
cells[headerStart].SetStyle(headerStyleDefinition);
if (reportDefinition.MergeCells.ToLower() == "true")
{
Aspose.Cells.Range rng1 = _workbook.Worksheets[0].Cells.CreateRange(headerStart, headerEnd);
rng1.Merge();
//int rowNmber = int.Parse(startingRowPosition.ToString());
}
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
{
System.IO.Directory.CreateDirectory(dataDir);
}
// Instantiate a new Workbook.
Workbook workbook = new Workbook();
// Get the first worksheet in the book.
Worksheet WS = workbook.Worksheets[0];
// Create a range of cells.
Aspose.Cells.Range range = WS.Cells.CreateRange(1, 1, 1, 18);
// Name the range.
range.Name = "MyRange";
// Declare a style object.
Style stl;
// Create/add the style object.
stl = workbook.CreateStyle();
// Specify some Font settings.
stl.Font.Name = "Arial";
stl.Font.IsBold = true;
// Set the font text color
stl.Font.Color = Color.Red;
// To Set the fill color of the range, you may use ForegroundColor with
// Solid Pattern setting.
stl.ForegroundColor = Color.Yellow;
stl.Pattern = BackgroundType.Solid;
// Create a StyleFlag object.
StyleFlag flg = new StyleFlag();
// Make the corresponding attributes ON.
flg.Font = true;
flg.CellShading = true;
// Apply the style to the range.
range.ApplyStyle(stl, flg);
// Save the excel file.
workbook.Save(dataDir + "rangestyles.out.xls");
// ExEnd:1
}
public object GetNamedRangeValue(string NamedRangeName)
{
Aspose.Cells.Range range = GetNamedRange(NamedRangeName);
if (range == null)
{
return(null);
}
return(range[0, 0].Value);
}
public Aspose.Cells.Range GetNamedRange(string NamedRangeName)
{
Aspose.Cells.Range r = _workbook.Worksheets.GetRangeByName(string.Format("{0}!{1}", ActiveSheet, NamedRangeName));
if (r == null)
{
r = _workbook.Worksheets.GetRangeByName(NamedRangeName);
}
return(r);
}
/// <summary>
/// Creates an auto-filter on the specified range.
/// </summary>
/// <param name="range">The range.</param>
/// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception>
public static void SetAutoFilter(
this Range range)
{
if (range == null)
{
throw new ArgumentNullException(nameof(range));
}
range.Worksheet.AutoFilter.Range = range.GetName();
}
/// <summary>
/// Applies this style container to the specified range.
/// </summary>
/// <param name="range">The range.</param>
/// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception>
public void ApplyToRange(
Range range)
{
if (range == null)
{
throw new ArgumentNullException(nameof(range));
}
range.ApplyStyle(this.Style, this.StyleFlag);
}
public bool SetNamedRangeValue(string NamedRangeName, object value)
{
Aspose.Cells.Range range = GetNamedRange(NamedRangeName);
if (range == null)
{
return(false);
}
range[0, 0].PutValue(value);
return(true);
}
/// <summary>
/// Gets the column numbers in the specified range.
/// </summary>
/// <param name="range">The range.</param>
/// <returns>
/// The column numbers in the range.
/// </returns>
/// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception>
public static IReadOnlyList <int> GetColumnNumbers(
this Range range)
{
if (range == null)
{
throw new ArgumentNullException(nameof(range));
}
var result = Enumerable.Range(range.FirstColumn + 1, range.ColumnCount).ToList();
return(result);
}
/// <summary>
/// Gets the upper-left most cell in the specified range.
/// </summary>
/// <param name="range">The range.</param>
/// <returns>
/// The upper-left most cell in the specified range.
/// </returns>
/// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception>
public static Cell GetUpperLeftmostCell(
this Range range)
{
if (range == null)
{
throw new ArgumentNullException(nameof(range));
}
var result = range.Worksheet.Cells[range.FirstRow, range.FirstColumn];
return(result);
}
/// <summary>
/// Gets the individual cell ranges within a range.
/// </summary>
/// <param name="range">The range.</param>
/// <returns>
/// The individual cell ranges within the specified range.
/// </returns>
/// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception>
public static IReadOnlyCollection <Range> GetCellRanges(
this Range range)
{
if (range == null)
{
throw new ArgumentNullException(nameof(range));
}
var result = range.GetCells().Select(_ => _.GetRange()).ToList();
return(result);
}
//Quang Huy 2014-02-25
/// <summary>
/// tô nền cho range
/// </summary>
/// <param name="range"></param>
/// <param name="color"></param>
private void SetRangeBgColor(Aspose.Cells.Range range, System.Drawing.Color?color = null)
{
System.Drawing.Color c = color ?? System.Drawing.Color.White;
//Aspose.Cells.Style s = range.Style ?? range[0, 0].GetStyle();
Aspose.Cells.Style s = range[0, 0].GetStyle();
s.BackgroundColor = c;
s.Update();
range.ApplyStyle(s, (new StyleFlag()
{
All = true
}));
}
public static DataTable GetTableFromWorkSheet(string fileName, string worksheetName)
{
Excel.Workbook workbook = new Excel.Workbook(fileName);
Excel.Worksheet worksheet = workbook.Worksheets[worksheetName];
Excel.Range range = GetUsedRange(worksheet);
DataTable result = worksheet.Cells.ExportDataTable(0, 0, range.RowCount, range.ColumnCount, true);
return(result);
}
public async Task <IActionResult> ExportExcel(string receive_Date)
{
var data = await _serviceCompare.GetCompare(receive_Date);
var count = data.Count;
var path = Path.Combine(_webHostEnvironment.ContentRootPath, "Resources\\Template\\CompareReport.xlsx");
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(path);
Worksheet worksheet = designer.Workbook.Worksheets[0];
designer.SetDataSource("result", data);
designer.Process();
// ----------------Add style excel------------------------//
Style stl = designer.Workbook.CreateStyle();
stl.ForegroundColor = Color.FromArgb(210, 105, 30);
stl.Pattern = BackgroundType.Solid;
StyleFlag flg = new StyleFlag();
flg.Font = true;
flg.CellShading = true;
flg.Alignments = true;
for (int i = 17; i < count + 17; i++)
{
var checkCell1 = worksheet.Cells["K" + i].Value.ToString();
var checkCell2 = worksheet.Cells["O" + i].Value.ToString();
Aspose.Cells.Range range = worksheet.Cells.CreateRange(i - 1, 0, 1, 15);
if (checkCell1 == "0" || checkCell2 == "0")
{
range.ApplyStyle(stl, flg);
}
}
//--------------------End add Style-------------------------//
// for (int i = 16; i < count + 16; i++)
// {
// worksheet.Cells["H"+ i].PutValue(receive_Date);
// }
worksheet.Cells["E2"].PutValue(data[0].Freeze_Date);
MemoryStream stream = new MemoryStream();
designer.Workbook.Save(stream, SaveFormat.Xlsx);
byte[] result = stream.ToArray();
return(File(result, "application/xlsx", "Excel" + DateTime.Now.ToString("dd_MM_yyyy_HH_mm_ss") + ".xlsx"));
}
/// <summary>
/// Creates an auto-filter on the specified range and
/// freezes the top row of that range.
/// </summary>
/// <param name="range">The range.</param>
/// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception>
public static void SetAutoFilterAndFreezeTopRow(
this Range range)
{
if (range == null)
{
throw new ArgumentNullException(nameof(range));
}
range.SetAutoFilter();
var cellToFreezeAt = range.Worksheet.Cells[range.GetRowNumbers().First(), 0];
cellToFreezeAt.SetFreezePanes(PaneKinds.Row);
}
public void FillAssetData(List <AssetModel> source, Workbook wb, int barcodeId)
{
Style style1 = wb.Styles[wb.Styles.Add()]; //新增样式
style1.IsTextWrapped = false; //单元格内容自动换行
style1.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线
style1.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; //应用边界线 右边界线
style1.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线
style1.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线
wb.Worksheets.RemoveAt(0); //移除第一个空sheet
var groupTypes = source.Select(it => new { Id = it.TypeId, Name = it.TypeName }).Distinct();
foreach (var type in groupTypes)
{
int row = 0;
wb.Worksheets.Add(type.Name);
var ws = wb.Worksheets[type.Name];
var itemsByType = source.Where(it => it.TypeId == type.Id).ToList();
//添加表头
var first = itemsByType.First();
ws.Cells[row, 0].Value = "类别";
ws.Cells[row, 1].Value = "状态";
ws.Cells[row, 2].Value = "用户";
int headerCols = 3;
foreach (var attr in first.AssetInfoes)
{
ws.Cells[0, headerCols].Value = attr.AttributeName;
headerCols += 1;
}
row += 1;
//填充数据
foreach (var item in itemsByType)
{
ws.Cells[row, 0].Value = item.TypeName;
ws.Cells[row, 1].Value = item.StatusName;
ws.Cells[row, 2].Value = item.UserName;
int col = 3;
foreach (var assetInfo in item.AssetInfoes)
{
ws.Cells[row, col].Value = assetInfo.AttributeValue;
col += 1;
}
row += 1;
}
Aspose.Cells.Range r = ws.Cells.CreateRange(0, 0, row, headerCols);
r.SetStyle(style1);
}
}
/// <summary>
/// Sets a data validation.
/// </summary>
/// <param name="range">The range.</param>
/// <param name="dataValidation">The validation styling to apply.</param>
/// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception>
public static void SetDataValidation(
this Range range,
DataValidation dataValidation)
{
if (range == null)
{
throw new ArgumentNullException(nameof(range));
}
if (dataValidation != null)
{
var validations = range.Worksheet.Validations;
var cellArea = range.GetCellArea();
var validation = validations[validations.Add(cellArea)];
validation.Type = dataValidation.Kind.ToValidationType();
validation.Operator = dataValidation.Operator.ToOperatorType();
if (dataValidation.Operand1Value != null)
{
validation.Value1 = dataValidation.Operand1Value;
}
if (dataValidation.Operand2Value != null)
{
validation.Value2 = dataValidation.Operand2Value;
}
if (dataValidation.Operand1Formula != null)
{
validation.Formula1 = dataValidation.Operand1Formula;
}
if (dataValidation.Operand2Formula != null)
{
validation.Formula2 = dataValidation.Operand2Formula;
}
validation.IgnoreBlank = dataValidation.IgnoreBlank;
validation.ShowInput = dataValidation.ShowInputMessage;
validation.InputTitle = dataValidation.InputMessageTitle;
validation.InputMessage = dataValidation.InputMessageBody;
validation.ShowError = dataValidation.ShowErrorAlertAfterInvalidDataIsEntered;
validation.AlertStyle = dataValidation.ErrorAlertStyle.ToValidationAlertType();
validation.ErrorTitle = dataValidation.ErrorAlertTitle;
validation.ErrorMessage = dataValidation.ErrorAlertBody;
validation.InCellDropDown = dataValidation.ShowListDropdown;
}
}
/// <summary>
/// Sets the outside border of a range.
/// </summary>
/// <param name="range">The range.</param>
/// <param name="border">The border.</param>
/// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception>
public static void SetOutsideBorder(
this Range range,
Border border)
{
if (range == null)
{
throw new ArgumentNullException(nameof(range));
}
if (border != null)
{
var borderEdges = border.Edges.ToBorderType();
var borderStyle = border.Style.ToCellBorderType();
range.SetOutlineBorder(borderEdges, borderStyle, border.Color);
}
}
public static void Run()
{
// Instantiate a new Workbook.
Workbook workbook = new Workbook();
// Get the first worksheet in the book.
Worksheet WS = workbook.Worksheets[0];
// Create a range of cells.
Aspose.Cells.Range range = WS.Cells.CreateRange(1, 1, 5, 5);
// Name the range.
range.Name = "MyRange";
// Declare a style object.
Style stl;
// Create/add the style object.
stl = workbook.CreateStyle();
// Specify some Font settings.
stl.Font.Name = "Arial";
stl.Font.IsBold = true;
// Set the font text color
stl.Font.Color = Color.Red;
// To Set the fill color of the range, you may use ForegroundColor with
// Solid Pattern setting.
stl.ForegroundColor = Color.Yellow;
stl.Pattern = BackgroundType.Solid;
// Create a StyleFlag object.
StyleFlag flg = new StyleFlag();
// Make the corresponding attributes ON.
flg.Font = true;
flg.CellShading = true;
// Apply the style to the range.
range.ApplyStyle(stl, flg);
// Save the excel file.
workbook.Save(outputDir + "outputFormatRanges1.xlsx");
Console.WriteLine("FormatRanges1 executed successfully.");
}
/// <summary>
/// Sets the per-row height for a range, in pixels.
/// </summary>
/// <param name="range">The range.</param>
/// <param name="rowHeightInPixels">The row height, in pixels.</param>
/// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception>
public static void SetPerRowHeightInPixels(
this Range range,
int?rowHeightInPixels)
{
if (range == null)
{
throw new ArgumentNullException(nameof(range));
}
if (rowHeightInPixels != null)
{
foreach (var rowNumber in range.GetRowNumbers())
{
range.Worksheet.Cells.SetRowHeightPixel(rowNumber - 1, (int)rowHeightInPixels);
}
}
}
/// <summary>
/// Sets the per-column width for a range, in pixels.
/// </summary>
/// <param name="range">The range.</param>
/// <param name="columnWidthInPixels">The column width, in pixels.</param>
/// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception>
public static void SetPerColumnWidthInPixels(
this Range range,
int?columnWidthInPixels)
{
if (range == null)
{
throw new ArgumentNullException(nameof(range));
}
if (columnWidthInPixels != null)
{
foreach (var columnNumber in range.GetColumnNumbers())
{
range.Worksheet.Cells.SetColumnWidthPixel(columnNumber - 1, (int)columnWidthInPixels);
}
}
}
/// <summary>
/// Groups the rows in the range.
/// </summary>
/// <param name="range">The range.</param>
/// <param name="collapseGroup">Optional value indicating whether to collapse the group. Default is false; the group will be expanded.</param>
/// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception>
public static void SetGroupRows(
this Range range,
bool collapseGroup = false)
{
if (range == null)
{
throw new ArgumentNullException(nameof(range));
}
var rowNumbers = range.GetRowNumbers();
if (rowNumbers.Count == 1)
{
throw new ArgumentOutOfRangeException(nameof(range), "There are too few rows specified in the range. To group rows, at least 2 rows must be specified.");
}
range.Worksheet.Cells.GroupRows(rowNumbers.First() - 1, rowNumbers.Last() - 1 - 1, collapseGroup);
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="dt"></param>
/// <param name="fileName"></param>
/// <returns></returns>
public static bool SaveExcel(DataTable dt, string fileName)
{
Workbook workbooks = new Workbook();
Worksheet worksheet = workbooks.Worksheets[0];
Style style = CreateStyle(workbooks);
try
{
for (int j = 0; j < dt.Columns.Count; j++)
{
worksheet.Cells[0, j].PutValue(dt.Columns[j].ColumnName);
worksheet.Cells[0, j].SetStyle(style);
}
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int c = 0; c < dt.Columns.Count; c++)
{
worksheet.Cells[r + 1, c].PutValue(dt.Rows[r][c].ToString());
worksheet.Cells[r + 1, c].SetStyle(style);
}
}
var s = worksheet.Cells[1, 1].GetStyle();
s.ForegroundColor = System.Drawing.Color.FromArgb(216, 228, 188);
s.Pattern = BackgroundType.Solid;
s.Font.IsBold = true;
Aspose.Cells.Range rang = worksheet.Cells.CreateRange(0, 0, 1, dt.Columns.Count);
rang.SetStyle(s);
//Aspose.Cells.Range rang1 = worksheet.Cells.CreateRange(0, 0, dt.Rows.Count, 1);
//rang1.SetStyle(s);
worksheet.AutoFitColumns();
worksheet.FreezePanes(1, 0, dt.Rows.Count, dt.Columns.Count);
workbooks.Save(fileName);
}
catch (Exception ex)
{
return(false);
}
return(true);
}
/// <summary>
/// Sets the indent level for a range.
/// </summary>
/// <param name="range">The range.</param>
/// <param name="indentLevel">The indent level.</param>
/// <param name="styleContainer">The style container.</param>
/// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception>
public static void SetIndentLevel(
this Range range,
int?indentLevel,
StyleContainer styleContainer = null)
{
if (range == null)
{
throw new ArgumentNullException(nameof(range));
}
if (indentLevel != null)
{
range.SetStyle(styleContainer, _ =>
{
_.Style.IndentLevel = (int)indentLevel;
_.StyleFlag.Indent = true;
});
}
}
/// <summary>
/// Sets the horizontal alignment of a range.
/// </summary>
/// <param name="range">The range.</param>
/// <param name="horizontalAlignment">The horizontal alignment.</param>
/// <param name="styleContainer">The style container.</param>
/// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception>
public static void SetHorizontalAlignment(
this Range range,
HorizontalAlignment?horizontalAlignment,
StyleContainer styleContainer = null)
{
if (range == null)
{
throw new ArgumentNullException(nameof(range));
}
if (horizontalAlignment != null)
{
range.SetStyle(styleContainer, _ =>
{
_.Style.HorizontalAlignment = ((HorizontalAlignment)horizontalAlignment).ToTextAlignmentType();
_.StyleFlag.HorizontalAlignment = true;
});
}
}
/// <summary>
/// Sets the total width for a range, in pixels.
/// </summary>
/// <param name="range">The range.</param>
/// <param name="totalWidthInPixels">The total width, in pixels.</param>
/// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception>
public static void SetTotalColumnWidthInPixels(
this Range range,
int?totalWidthInPixels)
{
if (range == null)
{
throw new ArgumentNullException(nameof(range));
}
if (totalWidthInPixels != null)
{
var columnNumbers = range.GetColumnNumbers();
var pixelsToUse = DivideEvenly((int)totalWidthInPixels, columnNumbers.Count).ToArray();
for (var x = 0; x < columnNumbers.Count; x++)
{
range.Worksheet.Cells.SetColumnWidthPixel(columnNumbers[x] - 1, pixelsToUse[x]);
}
}
}