public void AddSheets()
{
ExcelWorksheet one = doc.Add("one");
ExcelWorksheet two = doc.Add("two");
Assert.AreEqual(one, doc[1]);
Assert.AreEqual(two, doc[2]);
Assert.AreEqual(one, doc["one"]);
Assert.AreEqual(two, doc["two"]);
}
private async Task AppendSheetForEachSchool(ExcelWorksheets worksheets, School school)
{
var worksheet = worksheets.Add(school.Name);
worksheet.AppendHeader(typeof(ActivityExportHeader), 1);
IEnumerable <Entities.Class> efClasses = await this._unitOfWork.Classes.GetWithSchoolId(school.Id);
List <Guid> classIds = efClasses.Select(c => c.Id).ToList();
IEnumerable <Entities.Track> efTracks = await this._unitOfWork.Tracks.GetFullInformationByIds(classIds);
IEnumerable <Track> tracks = _mapper.Map <IEnumerable <Entities.Track>, IEnumerable <Track> >(efTracks);
int rowIndex = 1;
foreach (var track in tracks)
{
rowIndex++;
worksheet.Cells[rowIndex, ActivityExportHeader.ChildName.ColumnIndex].Value = track.ChildName;
worksheet.Cells[rowIndex, ActivityExportHeader.TimeCheckIn.ColumnIndex].Value = track.TimeCheckIn.ToLocalTime();
worksheet.Cells[rowIndex, ActivityExportHeader.TimeCheckOut.ColumnIndex].Value = track.TimeCheckOut.ToLocalTime();
worksheet.Cells[rowIndex, ActivityExportHeader.ClassName.ColumnIndex].Value = track.Class.Name;
worksheet.Cells[rowIndex, ActivityExportHeader.TeacherName.ColumnIndex].Value = track.Class.Teacher.Name;
}
worksheet.AutoFixColumns(typeof(ActivityExportHeader));
}
public Excel工作表 Create(string Name)
{
ExcelWorksheet sheet = Worksheets.Add(Name);
Excel工作表 工作表 = new Excel工作表(sheet);
return(工作表);
}
public virtual ExcelWorksheet AddAsExcelSheet(ExcelWorksheets excelWorksheets, TableStyles tableStyle, string headerStyle)
{
var spreadSheet = excelWorksheets.Add(SpreadsheetName);
var dataTable = ConvertToDataTables().First();
foreach (var column in dataTable.Columns)
{
var columnName = column.ToString();
var friendlyName = column.ToString()
.ToFriendlyString(" ", a => a == '_', b => b.CapitalizeFirstLetter(), true);
dataTable.Columns[column.ToString()].ColumnName = friendlyName;
}
spreadSheet.Cells[1, 1].LoadFromDataTable(dataTable, true, tableStyle);
spreadSheet.Cells.AutoFitColumns();
//No rows of close approach data were added and the spreadsheet is empty, no need to include it.
if (!spreadSheet.Cells.Any())
{
excelWorksheets.Delete(spreadSheet);
return(null);
}
return(spreadSheet);
}
/// <summary>
/// 新しい空のワークシートを追加します。
/// </summary>
/// <param name="worksheets">ワークシートコレクション</param>
/// <param name="sheetName">ワークシートの名前</param>
/// <param name="fontName">フォント名</param>
/// <returns>ワークシート</returns>
public static ExcelWorksheet Add(this ExcelWorksheets worksheets, string sheetName, string fontName)
{
var worksheet = worksheets.Add(sheetName);
worksheet.Cells.Style.Font.Name = fontName;
return(worksheet);
}
private void createUnloadActivityWorksheets(KrogerUnloadingInvoiceModel invoice, ExcelWorksheets sheets, ExcelWorksheet blankUnloadActivitySheet)
{
// Create and populate "All Loads" worksheet:
var allLoadsSheet = sheets.Add("All Loads", blankUnloadActivitySheet);
populateUnloadActivitySheet(allLoadsSheet, invoice.AllWorkOrders);
// Create and populate "Street" worksheet:
var streetSheet = sheets.Add("Street", blankUnloadActivitySheet);
populateUnloadActivitySheet(streetSheet, invoice.PrepaidWorkOrders);
// Create and populate "Controlled" worksheet:
var controlledSheet = sheets.Add("Controlled", blankUnloadActivitySheet);
populateUnloadActivitySheet(controlledSheet, invoice.ControlledWorkOrders);
// Get the names of all unique department #'s in the collection of work orders:
var controlledWorkOrdersByRefNo = invoice.ControlledWorkOrdersByReferenceCode;
var allRefNames = controlledWorkOrdersByRefNo
.Select(g => g[0].ReferenceNumber)
.Where(s => !String.IsNullOrEmpty(s))
.ToList();
// Create workSheets for each unique Ref in the collection of invoices:
allRefNames.ForEach(n =>
{
var sheet = sheets.Add(String.Format("Controlled ({0})", n), blankUnloadActivitySheet);
var workOrdersFromThisRef = controlledWorkOrdersByRefNo.Find(l => l[0].ReferenceNumber == n);
populateUnloadActivitySheet(sheet, workOrdersFromThisRef);
});
if (invoice.BreakdownByDepartment)
{
// Create workSheets for each department
foreach (var gr in invoice.AllWorkOrders.GroupBy(x => new { x.LocationDepartmentId, x.DepartmentName }))
{
var departmentSheet = sheets.Add(gr.Key.DepartmentName, blankUnloadActivitySheet);
populateUnloadActivitySheet(departmentSheet, gr.ToList());
}
}
sheets.Delete(blankUnloadActivitySheet);
}
public static void GenerateWorksheet(ExcelWorksheets worksheets, DataTable dataTable, string strWorksheetName)
{
try
{
ExcelWorksheet ws;
if (worksheets.Count(w => w.Name == strWorksheetName) == 0)
{
ws = worksheets.Add(strWorksheetName);
Log.LogMessage("Adding new Worksheet: " + strWorksheetName);
}
else
{
ws = worksheets[strWorksheetName];
ws.Cells.Clear();
Log.LogMessage("Clearing Values for Worksheet: " + strWorksheetName);
}
int cols = ws.Dimension?.Columns ?? dataTable.Columns.Count;
object[] columns = dataTable.Rows[0].ItemArray;
ws.Cells["A1"].LoadFromDataTable(dataTable, true);
for (int x = 1; x <= columns.Count(); x++)
{
if (Config.Dates.Contains(ws.Cells[1, x].Value))
{
ws.Column(x).Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern;
}
}
Log.LogMessage("Added data to Worksheet: " + strWorksheetName);
// format header cells
using (var range = ws.Cells[1, 1, 1, cols])
{
Log.LogMessage("Formating Header Cells for Worksheet: " + strWorksheetName);
range.Style.Font.Bold = true;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);
range.Style.Font.Color.SetColor(Color.White);
}
// Create autofilter for the range of cells
ws.Cells[ws.Dimension?.Address].AutoFilter = true;
// Autofit columns for all cells
ws.Cells[ws.Dimension?.Address].AutoFitColumns();
Log.LogMessage("Created " + strWorksheetName + " Worksheet");
}
catch (Exception ex)
{
Log.LogMessage("Error Creating " + strWorksheetName + ", Error: " + ex.Message);
}
}
/// <summary>
/// Add a new sheet to the collection. Replace if already exists.
/// </summary>
/// <param name="sheets"></param>
/// <param name="name"></param>
/// <returns></returns>
public static ExcelWorksheet AddOrReplace(this ExcelWorksheets sheets, string name)
{
if (sheets.Any(x => StringComparer.OrdinalIgnoreCase.Equals(x.Name, name)))
{
sheets.Delete(name);
}
return(sheets.Add(name));
}
/// <summary>
/// 添加数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="worksheets"></param>
/// <param name="name"></param>
/// <param name="list"></param>
/// <param name="tableStyles"></param>
public static void AddDataToWorkSheet <T>(this ExcelWorksheets worksheets, String name, IList <T> list, OfficeOpenXml.Table.TableStyles tableStyles = OfficeOpenXml.Table.TableStyles.Light8)
{
ExcelWorksheet worksheet = worksheets.Add(name);
var dataRange = worksheet.Cells["A1"].LoadFromCollection(
list,
true, tableStyles);
dataRange.AutoFitColumns();
return;
}
private static void CreateExcelFile(string excelFile, string sheetName, string csvFile, string columnName)
{
File.Delete(excelFile);
FileInfo excelFileInfo = new FileInfo(excelFile);
using (ExcelPackage package = new ExcelPackage(excelFileInfo))
{
ExcelWorksheets workSheets = package.Workbook.Worksheets;
ExcelWorksheet dataWorkSheet = workSheets.Add(sheetName);
var format = new ExcelTextFormat {
Delimiter = '\t', EOL = "\r"
};
dataWorkSheet.Cells["A1"].LoadFromText(new FileInfo(csvFile), format);
int rowsCount = dataWorkSheet.Dimension.End.Row - 1;
ExcelColumn preColumn = dataWorkSheet.Column(2);
preColumn.Width = 2;
ExcelColumn postColumn = dataWorkSheet.Column(17);
postColumn.Width = 2;
for (int row = 0; row < rowsCount; row++)
{
SetColor(dataWorkSheet.Cells[row + 1, 2], EMPTY_COLUMN_COLOR);
SetColor(dataWorkSheet.Cells[row + 1, 17], EMPTY_COLUMN_COLOR);
}
ExcelWorksheet chartsWorksheet = workSheets.Add("Charts");
ExcelChart chart = chartsWorksheet.Drawings.AddChart("StdDev", eChartType.ColumnClustered);
chart.Title.Text = "StdDev";
chart.SetPosition(1, 0, 1, 0);
chart.SetSize(800, 300);
string yName = String.Format("'" + dataWorkSheet.Name + "'" + "!{0}2:{0}{1}", columnName, rowsCount);
var series = chart.Series.Add(yName, "");
series.Header = "StdDev / Configuration";
package.Save();
}
}
public static void CreateWorksheet(ExcelWorksheets wss)
{
ExcelWorksheet ws = wss.Add("每个所包含的具体资源");
// 标签颜色
ws.TabColor = ColorTranslator.FromHtml("#f9c40f");
AssetBundleReporter.CreateWorksheetBase(ws, "每个 AssetBundle 文件所包含的具体资源", 4);
// 列宽
ws.Column(1).Width = 50;
ws.Column(2).Width = 50;
ws.Column(3).Width = 50;
ws.Column(4).Width = 50;
}
public override ExcelWorksheet AddAsExcelSheet(ExcelWorksheets excelWorksheets, TableStyles tableStyle, string headerStyle)
{
var spreadSheet = excelWorksheets.Add(SpreadsheetName);
var currentRow = 1;
var allDataTables = ConvertToDataTables();
var subTableCollection = SerializationData.Select(a => (CloseApproachInfoSubTable)a).ToList();
var counter = 0;
foreach (var dataTable in allDataTables)
{
if (dataTable.Rows.Count == 0)
{
continue;
}
foreach (var column in dataTable.Columns)
{
var columnName = column.ToString();
var friendlyName = column.ToString()
.ToFriendlyString(" ", a => a == '_', b => b.CapitalizeFirstLetter(), true);
dataTable.Columns[column.ToString()].ColumnName = friendlyName;
}
spreadSheet.Cells[currentRow, 1].Value = subTableCollection[counter].Name;
spreadSheet.Cells[currentRow, 1].StyleName = headerStyle;
spreadSheet.Cells[currentRow + 1, 1].LoadFromDataTable(dataTable, true, tableStyle);
currentRow += dataTable.Rows.Count + 2;
spreadSheet.Cells.AutoFitColumns();
counter++;
}
//No rows of close approach data were added and the spreadsheet is empty, no need to include it.
if (!spreadSheet.Cells.Any())
{
excelWorksheets.Delete(spreadSheet);
return(null);
}
return(spreadSheet);
}
public static void WriteExcel(string filePath, string sheetName, Vector2 pos, string value)
{
FileInfo fileInfo = new FileInfo(filePath);
ExcelPackage excelPackage = new ExcelPackage(fileInfo);
ExcelWorksheets excelWorksheets = excelPackage.Workbook.Worksheets;
if (excelWorksheets[sheetName] == null)
{
//如果没有sheet会创建
excelWorksheets.Add(sheetName);
}
WriteCellValue(excelWorksheets[sheetName], pos, value);
excelPackage.Save();
}
/// <summary>
/// 如果是一个表格添加多条数据建议使用此函数
/// </summary>
/// <param name="filePath"></param>
/// <param name="posToValue"></param>
public static void WriteExcel(string filePath, Dictionary <Vector2, string> posToValue)
{
FileInfo fileInfo = new FileInfo(filePath);
ExcelPackage excelPackage = new ExcelPackage(fileInfo);
ExcelWorksheets excelWorksheets = excelPackage.Workbook.Worksheets;
if (excelWorksheets.Count == 0 || excelWorksheets[1] == null)
{
//如果没有sheet会创建
excelWorksheets.Add("sheet1");
}
WriteCellValue(excelWorksheets[1], posToValue);
excelPackage.Save();
}
public static void CreateWorksheet(ExcelWorksheets wss)
{
ExcelWorksheet ws = wss.Add("AB文件列表");
// 标签颜色
ws.TabColor = ColorTranslator.FromHtml("#32b1fa");
AssetBundleReporter.CreateWorksheetBase(ws, "AssetBundle 文件列表", 11);
// 列头
int colIndex = 1;
ws.Cells[2, colIndex++].Value = "AssetBundle 名称";
ws.Cells[2, colIndex++].Value = "文件大小";
ws.Cells[2, colIndex++].Value = "依赖AB数";
ws.Cells[2, colIndex++].Value = "冗余资源数";
ws.Cells[2, colIndex++].Value = AssetFileInfoType.mesh;
ws.Cells[2, colIndex++].Value = AssetFileInfoType.material;
ws.Cells[2, colIndex++].Value = AssetFileInfoType.texture2D;
ws.Cells[2, colIndex++].Value = AssetFileInfoType.sprite;
ws.Cells[2, colIndex++].Value = AssetFileInfoType.shader;
ws.Cells[2, colIndex++].Value = AssetFileInfoType.animationClip;
ws.Cells[2, colIndex].Value = AssetFileInfoType.audioClip;
using (var range = ws.Cells[2, 1, 2, colIndex])
{
// 字体样式
range.Style.Font.Bold = true;
// 背景颜色
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml("#DDEBF7"));
// 开启自动筛选
range.AutoFilter = true;
}
// 列宽
ws.Column(1).Width = 100;
for (int i = 2; i <= colIndex; i++)
{
ws.Column(i).Width = 15;
ws.Column(i).Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
}
ws.Column(10).Width = 16;
ws.Column(2).Style.Numberformat.Format = "#,##0";
// 冻结前两行
ws.View.FreezePanes(3, 1);
}
/// <summary>
/// 如果是新建一个表格或者要添加多条数据,建议使用这种方法
/// </summary>
/// <param name="excelInfo"></param>
public static void WriteExcel(ExcelInfo excelInfo)
{
FileInfo fileInfo = new FileInfo(excelInfo.filePath);
ExcelPackage excelPackage = new ExcelPackage(fileInfo);
ExcelWorksheets excelWorksheets = excelPackage.Workbook.Worksheets;
Dictionary <string, Dictionary <Vector2, string> > info = excelInfo.excelInfoDic;
List <string> sheetNames = new List <string>(info.Keys);
for (int i = 0; i < sheetNames.Count; i++)
{
if (excelWorksheets[sheetNames[i]] == null)
{
//如果没有sheet会创建
excelWorksheets.Add(sheetNames[i]);
}
WriteCellValue(excelWorksheets[sheetNames[i]], info[sheetNames[i]]);
}
excelPackage.Save();
}
private void AppendSheetForEachClass(ExcelWorksheets worksheets, Class classInfo, IEnumerable <Track> tracks)
{
var worksheet = worksheets.Add(classInfo.Name);
worksheet.AppendHeader(typeof(ActivityExportHeader), 1);
int rowIndex = 1;
foreach (var track in tracks)
{
rowIndex++;
worksheet.Cells[rowIndex, ActivityExportHeader.ChildName.ColumnIndex].Value = track.ChildName;
worksheet.Cells[rowIndex, ActivityExportHeader.TimeCheckIn.ColumnIndex].Value = track.TimeCheckIn.ToLocalTime();
worksheet.Cells[rowIndex, ActivityExportHeader.TimeCheckOut.ColumnIndex].Value = track.TimeCheckOut.ToLocalTime();
worksheet.Cells[rowIndex, ActivityExportHeader.ClassName.ColumnIndex].Value = track.Class.Name;
worksheet.Cells[rowIndex, ActivityExportHeader.TeacherName.ColumnIndex].Value = track.Class.Teacher.Name;
}
worksheet.AutoFixColumns(typeof(ActivityExportHeader));
}
/// <summary>
/// 添加摄像机信息表单
/// </summary>
/// <param name="worksheets">表单列表集合</param>
/// <param name="list">摄像机列表</param>
/// <param name="tableStyles">表单样式</param>
public static void AddDeviceWorkSheet(this ExcelWorksheets worksheets, IList <DeviceExcelData> list, OfficeOpenXml.Table.TableStyles tableStyles = OfficeOpenXml.Table.TableStyles.Light8)
{
ExcelWorksheet worksheet = worksheets.Add("设备信息");
var dataRange = worksheet.Cells["A1"].LoadFromCollection(
list.OrderBy(x => x.Name).OrderBy(x => x.No),
true, tableStyles);
worksheet.DataValidations.AddUniqueValidation("A2:A65536");
worksheet.DataValidations.AddListValidation("C2:C65536", DeviceTypeConverter.GetValues());
worksheet.DataValidations.AddListValidation("K2:K65536", Boolean2ChineseConverter.GetValues());
worksheet.DataValidations.AddUniqueValidation("E2:E65536");
worksheet.DataValidations.AddUniqueValidation("N2:N65536");
//worksheet.Column(0).Style.Locked = true;
//worksheet.Column(13).Style.Locked = true;
//worksheet.Column(14).Style.Locked = true;
//worksheet.Column(15).Style.Locked = true;
//worksheet.Column(16).Style.Locked = true;
//worksheet.Column(17).Style.Locked = true;
//worksheet.Protection.IsProtected = true;
dataRange.AutoFitColumns();
}
public void ConditionalFormating()
{
string assemblyDirectory = AssemblyDirectory;
string csvFile = Path.Combine(assemblyDirectory, @"..\..\dataFile.csv");
string excelFile = Path.Combine(assemblyDirectory, "ExcelWithConditionalFormating.xlsx");
string sheetName = "formating";
File.Delete(excelFile);
FileInfo excelFileInfo = new FileInfo(excelFile);
using (ExcelPackage package = new ExcelPackage(excelFileInfo))
{
ExcelWorksheets workSheets = package.Workbook.Worksheets;
ExcelWorksheet dataWorkSheet = workSheets.Add(sheetName);
var format = new ExcelTextFormat {
Delimiter = '\t', EOL = "\r"
};
dataWorkSheet.Cells["A1"].LoadFromText(new FileInfo(csvFile), format);
int rowsCount = dataWorkSheet.Dimension.End.Row - 1;
// Add Conditional Formatting for the cells we just filled
AddConditionalFormattingColorsRange(dataWorkSheet, "C1:C10", "FFF8696B", "FF63BE7B");
AddConditionalFormatting_Formula(dataWorkSheet, "A2:A20", 1.2);
AddConditionalFormatting_GreaterThan(dataWorkSheet, "B2:B20", 2.1);
// ExcelColumn columnForColor = dataWorkSheet.Column(1);
// columnForColor.Width = 2;
// columnForColor.Style.Fill.PatternType = ExcelFillStyle.Solid;
// columnForColor.Style.Fill.BackgroundColor.SetColor(EMPTY_COLUMN_COLOR);
//
// columnForColor = dataWorkSheet.Column(3);
// columnForColor.Width = 2;
// columnForColor.Style.Fill.PatternType = ExcelFillStyle.Solid;
// columnForColor.Style.Fill.BackgroundColor.SetColor(EMPTY_COLUMN_COLOR);
package.Save();
}
}
public static void CreateWorksheet(ExcelWorksheets wss)
{
ExcelWorksheet ws = wss.Add("资源列表");
// 标签颜色
ws.TabColor = ColorTranslator.FromHtml("#70ad47");
AssetBundleReporter.CreateWorksheetBase(ws, "全部资源列表", 4);
// 列头
ws.Cells[2, 1].Value = "资源名称";
ws.Cells[2, 2].Value = "资源类型";
ws.Cells[2, 3].Value = "AB文件数量";
ws.Cells[2, 4].Value = "相应的AB文件";
using (var range = ws.Cells[2, 1, 2, 4])
{
// 字体样式
range.Style.Font.Bold = true;
// 背景颜色
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml("#DDEBF7"));
// 开启自动筛选
range.AutoFilter = true;
}
// 列宽
ws.Column(1).Width = 50;
ws.Column(2).Width = 30;
ws.Column(3).Width = 15;
ws.Column(3).Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
// 冻结前两行
ws.View.FreezePanes(3, 1);
}
public IWorksheet Add(string name)
{
return(new EPPlusWorksheet(_worksheets.Add(name)));
}
public static void CreateAndFillWorksheet(ExcelWorksheets wss, string typeName)
{
var dicts = AssetBundleFilesAnalyze.assetFileInfos;
if (dicts == null)
{
return;
}
List <string> columnNames = new List <string>();
foreach (var info in dicts.Values)
{
if (info.type != typeName)
{
continue;
}
foreach (var pair in info.propertys)
{
columnNames.Add(pair.Key);
}
break;
}
if (columnNames.Count == 0)
{
return;
}
string titleName = typeName + " 列表";
ExcelWorksheet ws = wss.Add(titleName);
int abCountCol = columnNames.Count + 2;
int abDetailCol = columnNames.Count + 3;
// 标签颜色
ws.TabColor = ColorTranslator.FromHtml("#b490f5");
AssetBundleReporter.CreateWorksheetBase(ws, titleName, abDetailCol);
// 列头
ws.Cells[2, 1].Value = "资源名称";
for (int i = 0; i < columnNames.Count; i++)
{
ws.Cells[2, i + 2].Value = columnNames[i];
}
ws.Cells[2, abCountCol].Value = "AB文件数量";
ws.Cells[2, abDetailCol].Value = "相应的AB文件";
using (var range = ws.Cells[2, 1, 2, abDetailCol])
{
// 字体样式
range.Style.Font.Bold = true;
// 背景颜色
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml("#DDEBF7"));
// 开启自动筛选
range.AutoFilter = true;
}
// 列宽
ws.Column(1).Width = 50;
for (int i = 0; i < columnNames.Count; i++)
{
ws.Column(2 + i).Width = 15;
ws.Column(2 + i).Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Column(2 + i).Style.VerticalAlignment = ExcelVerticalAlignment.Top;
}
ws.Column(abCountCol).Width = 15;
ws.Column(abCountCol).Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Column(abCountCol).Style.VerticalAlignment = ExcelVerticalAlignment.Top;
// 冻结前两行
ws.View.FreezePanes(3, 1);
int startRow = 3;
int maxCol = abDetailCol;
foreach (var info in dicts.Values)
{
if (info.type != typeName)
{
continue;
}
ws.Cells[startRow, 1].Value = info.name;
info.detailHyperLink.ReferenceAddress = ws.Cells[startRow, 1].FullAddress;
int startCol = 2;
foreach (var property in info.propertys)
{
ws.Cells[startRow, startCol++].Value = property.Value;
}
ws.Cells[startRow, startCol++].Value = info.includedBundles.Count;
foreach (var bundleFileInfo in info.includedBundles)
{
ws.Cells[startRow, startCol].Value = bundleFileInfo.name;
ws.Cells[startRow, startCol++].Hyperlink = bundleFileInfo.detailHyperLink;
}
maxCol = System.Math.Max(--startCol, maxCol);
startRow++;
}
ws.Cells[1, 1].Value = ws.Cells[1, 1].Value + " (" + (startRow - 3) + ")";
// 具体所需要的列
using (var range = ws.Cells[2, abDetailCol, 2, maxCol])
{
range.Merge = true;
}
for (int i = abDetailCol; i <= maxCol; i++)
{
ws.Column(i).Width = 100;
}
// 不同类型不同处理
switch (typeName)
{
case "Texture2D":
ws.Column(7).Style.Numberformat.Format = "#,##0";
break;
case "Material":
ws.Column(2).Width = 40;
ws.Column(3).Width = 50;
break;
case "AnimationClip":
ws.Column(7).Style.Numberformat.Format = "#,##0";
break;
case "AudioClip":
ws.Column(2).Width = 23;
break;
}
}
public void Add(string mainSheetName)
{
_worksheets.Add(mainSheetName);
}
public ExcelWorksheetInstance Add(string name)
{
var result = m_excelWorksheets.Add(name);
return(new ExcelWorksheetInstance(this.Engine.Object.InstancePrototype, result));
}
public static void CreateAndFillWorksheet(ExcelWorksheets wss, List <AnalyzeObjectInfo> list, string typeName, string sortKey)
{
if (list.Count == 0)
{
return;
}
list.SortToDown(sortKey);
List <string> columnNames = new List <string>();
//columnNames.Add("guid");
columnNames.Add("path");
//columnNames.Add("type");
foreach (var property in list[0].propertys)
{
columnNames.Add(property.Key);
}
string titleName = typeName;
ExcelWorksheet ws = wss.Add(titleName);
int colCount = columnNames.Count;
// 标签颜色
ws.TabColor = ColorTranslator.FromHtml("#b490f5");
CreateWorksheetBase(ws, titleName, colCount);
// 列头
for (int i = 0; i < columnNames.Count; i++)
{
ws.Cells[2, i + 1].Value = AnalyzePropertys.GetName(columnNames[i]);
}
using (var range = ws.Cells[2, 1, 2, colCount])
{
// 字体样式
range.Style.Font.Bold = true;
// 背景颜色
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml("#DDEBF7"));
// 开启自动筛选
range.AutoFilter = true;
}
// 冻结前两行
ws.View.FreezePanes(3, 1);
int startRow = 3;
foreach (AnalyzeObjectInfo item in list)
{
//ws.Cells[startRow, 1].Value = item.guid;
ws.Cells[startRow, 1].Value = item.path;
//ws.Cells[startRow, 3].Value = item.type;
for (int i = 0; i < item.propertys.Count; i++)
{
ws.Cells[startRow, 1 + 1 + i].Value = item.propertys[i].Value;
}
startRow++;
}
ws.Cells[1, 1].Value = ws.Cells[1, 1].Value + " (" + (startRow - 3) + ")";
// 列宽
for (int i = 0; i < columnNames.Count; i++)
{
ws.Column(i + 1).Width = AnalyzePropertys.GetWidth(columnNames[i]) / 4;
Type columnType = AnalyzePropertys.GetType(columnNames[i]);
if (columnType == AnalyzePropertys.typeInt || columnType == AnalyzePropertys.typeFloat)
{
ws.Column(i + 1).Style.Numberformat.Format = "#,##0";
}
}
ws.Column(1).Width = 100;
}
public static ExcelWorksheet GetOrAddByName(this ExcelWorksheets worksheets, string sheetName)
{
return
(worksheets.FirstOrDefault(x => x.Name == sheetName)
?? worksheets.Add(sheetName));
}