public void IfAFontIsCreatedItShouldBeReturnedAndAddedToTheExistingWorkbook()
{
_objectToTest = new SXSSFWorkbook();
var font = _objectToTest.CreateFont();
Assert.NotNull(font);
}
public void IfGivenZeroBasedIndexShouldReturnExistingCellStyle()
{
_objectToTest = new SXSSFWorkbook();
_objectToTest.CreateFont();
var cellStyle = _objectToTest.GetCellStyleAt(0);
Assert.NotNull(cellStyle);
}
public void IfGivenZeroBasedIndexShouldReturnExistingFont()
{
_objectToTest = new SXSSFWorkbook();
_objectToTest.CreateFont();
var font = _objectToTest.GetFontAt(0);
Assert.NotNull(font);
}
public Result Execute(ExternalCommandData commandData, ref string message, ElementSet elements)
{
Document doc = commandData.Application.ActiveUIDocument.Document;
// Variables to store user input
List <int> listIds;
string desktopPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
// Variables to store log
var schedSuccess = new List <string>();
var schedFail = new List <string>();
// Prompt window to collect user input
using (BrowserCheckboxes customWindow = new BrowserCheckboxes(commandData))
{
customWindow.ShowDialog();
listIds = customWindow.listIds;
}
// Check there are schedules selected
if (listIds != null)
{
using (BrowserWindow browserWindow = new BrowserWindow())
{
browserWindow.ShowDialog();
// Variables
string fullPath = browserWindow.selectedPath;
// CHeck that path is not empty and path is a folder
if (fullPath == null || !Directory.Exists(fullPath))
{
TaskDialog.Show("Warning", "No folder has been selected");
return(Result.Cancelled);
}
else
{
// Loop through each selected schedule
foreach (int id in listIds)
{
// Extract data from schedule
ViewSchedule sched = doc.GetElement(new ElementId(id)) as ViewSchedule;
TableData tD = sched.GetTableData();
TableSectionData sectionData = tD.GetSectionData(SectionType.Body);
int numbRows = sectionData.NumberOfRows;
int numbCols = sectionData.NumberOfColumns;
// Name of the file
string excelPath = fullPath + @"\" + sched.Name + ".xlsx";
// Create excel file
SXSSFWorkbook workbook = new SXSSFWorkbook();
SXSSFSheet excelSheet = (SXSSFSheet)workbook.CreateSheet(sched.Name);
excelSheet.SetRandomAccessWindowSize(100);
//Create a header row
IRow row = excelSheet.CreateRow(0);
// Define format for cells
var fontStyle = workbook.CreateFont();
fontStyle.IsBold = true;
fontStyle.FontHeightInPoints = 12;
var titleStyle = workbook.CreateCellStyle();
titleStyle.SetFont(fontStyle);
// Write to excel
using (var fs = new FileStream(excelPath, FileMode.Create, FileAccess.Write))
{
// Write content
for (int i = 0; i < numbRows; i++)
{
row = excelSheet.CreateRow(i);
for (int j = 0; j < numbCols; j++)
{
string content = sched.GetCellText(SectionType.Body, i, j);
var cell = row.CreateCell(j);
cell.SetCellValue(content);
if (i == 0)
{
cell.CellStyle = titleStyle;
}
}
}
// Size columns
excelSheet.TrackAllColumnsForAutoSizing();
for (int i = 0; i < numbCols; i++)
{
excelSheet.AutoSizeColumn(i);
}
excelSheet.UntrackAllColumnsForAutoSizing();
// Write to file
try
{
workbook.Write(fs);
// Log success export schedule name
schedSuccess.Add(sched.Name);
}
catch
{
schedFail.Add(sched.Name);
}
}
}
TaskDialog.Show("Success", "The following schedules have been exported: " +
string.Join("\n", schedSuccess.ToArray()));
return(Result.Succeeded);
}
}
}
return(Result.Cancelled);
}
public MemoryStream GetExcelPackage()
{
using (var fs = new MemoryStream())
{
var workbook = new SXSSFWorkbook();
workbook.RandomAccessWindowSize = 1000;
var sheet = workbook.CreateSheet(SheetName) as SXSSFSheet;
for (var r = 0; r < 50 + Data.Count; r++)
{
var newrow = sheet.CreateRow(r);
for (var c = 0; c < 40; c++)
{
newrow.CreateCell(c);
}
}
sheet.DefaultColumnWidth = 4;
sheet.DisplayGridlines = false;
//var rang = new CellRangeAddress(0, 1, 1, 10);
//var merI = sheet.AddMergedRegion(rang);
//var row = sheet._rows[0];
//var cell = row.Cells[1];
//var style = workbook.CreateCellStyle();
//var font = workbook.CreateFont();
//font.FontHeightInPoints = 16;
//style.SetFont(font);
//style.BorderTop = BorderStyle.Medium;
//style.BorderLeft = BorderStyle.Medium;
//style.BorderRight = BorderStyle.Medium;
//cell.CellStyle = style;
//cell.SetCellValue(123);
//RegionUtil.SetBorderBottom(2, rang, sheet, workbook);
//RegionUtil.SetBorderLeft(2, rang, sheet, workbook);
//RegionUtil.SetBorderRight(2, rang, sheet, workbook);
//RegionUtil.SetBorderTop(2, rang, sheet, workbook);
SetCell(workbook, sheet, 2, 2, 22, 29, $"発行年月日 {PublishDate.ToString("yyyy年MM月dd日")}", 12, false, BorderStyle.None, false, 700);
SetCell(workbook, sheet, 5, 11, 1, 13, $"{ToCompany} 御中", 10, true, BorderStyle.Medium, true);
SetCell(workbook, sheet, 5, 5, 17, 29, FromCompany, 12, false, BorderStyle.None, false);
SetCell(workbook, sheet, 1, 1, 1, 30, FromDepartment, 12, false, BorderStyle.None, true);
//设置标题
var titleFont = workbook.CreateFont();
titleFont.FontHeightInPoints = 16;
titleFont.Underline = FontUnderlineType.Single;
SetCell(workbook, sheet, 16, 17, 0, 30, "adfdsf", titleFont, false, BorderStyle.None, true);
//设置周期
var periodFont = workbook.CreateFont();
periodFont.FontHeightInPoints = 16;
periodFont.Underline = FontUnderlineType.Single;
SetCell(workbook, sheet, 20, 20, 1, 12, Period.ToString("yyyy年MM月度"), periodFont, false, BorderStyle.None, false);
#region 代理商信息
SetCell(workbook, sheet, 22, 23, 1, 4, "代理店コード", 12, true, BorderStyle.Thin, true);
SetCell(workbook, sheet, 22, 23, 5, 13, AgentMessage.AgentCode, 12, true, BorderStyle.Thin, true);
SetCell(workbook, sheet, 24, 25, 1, 4, "振込日", 12, true, BorderStyle.Thin, true);
SetCell(workbook, sheet, 24, 25, 5, 13, AgentMessage.DemittanceDate?.ToString("yyyy年MM月dd日"), 12, true, BorderStyle.Thin, true);
SetCell(workbook, sheet, 26, 27, 1, 4, "振込金額", 12, true, BorderStyle.Thin, true);
SetCell(workbook, sheet, 26, 27, 5, 13, AgentMessage.DemittanceAmount.ToString(), 12, true, BorderStyle.Thin, true);
#endregion
#region 代理商帐户信息
SetCell(workbook, sheet, 22, 23, 17, 20, "金融機関名", 12, true, BorderStyle.Thin, true);
SetCell(workbook, sheet, 22, 23, 21, 29, AgentAccount.BankName, 12, true, BorderStyle.Thin, true);
SetCell(workbook, sheet, 24, 25, 17, 20, "支店名", 12, true, BorderStyle.Thin, true);
SetCell(workbook, sheet, 24, 25, 21, 29, AgentAccount.BranchBankName, 12, true, BorderStyle.Thin, true);
SetCell(workbook, sheet, 26, 27, 17, 20, "口座番号", 12, true, BorderStyle.Thin, true);
SetCell(workbook, sheet, 26, 27, 21, 22, AgentAccount.AccountMode, 12, true, BorderStyle.Thin, true);
SetCell(workbook, sheet, 26, 27, 23, 29, AgentAccount.Account, 12, true, BorderStyle.Thin, true);
SetCell(workbook, sheet, 28, 29, 17, 20, "口座名義", 12, true, BorderStyle.Thin, true);
SetCell(workbook, sheet, 28, 29, 21, 29, AgentAccount.AccountOwner, 12, true, BorderStyle.Thin, true);
#endregion
#region 列表
SetCell(workbook, sheet, 32, 32, 1, 1, null, 12, true, BorderStyle.Thin, true, null, HSSFColor.Rose.Index);
SetCell(workbook, sheet, 32, 32, 2, 4, "コード", 12, true, BorderStyle.Thin, true, null, HSSFColor.Rose.Index);
SetCell(workbook, sheet, 32, 32, 5, 9, "加盟店", 12, true, BorderStyle.Thin, true, null, HSSFColor.Rose.Index);
SetCell(workbook, sheet, 32, 32, 10, 14, "対象期間", 12, true, BorderStyle.Thin, true, null, HSSFColor.Rose.Index);
SetCell(workbook, sheet, 32, 32, 15, 18, "決済利用額", 12, true, BorderStyle.Thin, true, null, HSSFColor.Rose.Index);
SetCell(workbook, sheet, 32, 32, 19, 21, "手数料率", 12, true, BorderStyle.Thin, true, null, HSSFColor.Rose.Index);
SetCell(workbook, sheet, 32, 32, 22, 25, "手数料額", 12, true, BorderStyle.Thin, true, null, HSSFColor.Rose.Index);
SetCell(workbook, sheet, 32, 32, 26, 29, "備考", 12, true, BorderStyle.Thin, true, null, HSSFColor.Rose.Index);
var rowIndex = 33;
var sn = 1;
var total = 0m;
foreach (var item in Data)
{
total += item.RateAmount;
SetCell(workbook, sheet, rowIndex, rowIndex, 1, 1, sn++.ToString(), 12, true, BorderStyle.Thin, true);
SetCell(workbook, sheet, rowIndex, rowIndex, 2, 4, item.Code, 12, true, BorderStyle.Thin, true);
SetCell(workbook, sheet, rowIndex, rowIndex, 5, 9, item.Name, 12, true, BorderStyle.Thin, true);
SetCell(workbook, sheet, rowIndex, rowIndex, 10, 14, item.TimeSection, 12, true, BorderStyle.Thin, true);
SetCell(workbook, sheet, rowIndex, rowIndex, 15, 18, item.Amount.ToString(), 12, true, BorderStyle.Thin, true);
SetCell(workbook, sheet, rowIndex, rowIndex, 19, 21, item.Rate.ToString(), 12, true, BorderStyle.Thin, true);
SetCell(workbook, sheet, rowIndex, rowIndex, 22, 25, item.RateAmount.ToString(), 12, true, BorderStyle.Thin, true);
SetCell(workbook, sheet, rowIndex, rowIndex, 26, 29, item.Memo, 12, true, BorderStyle.Thin, true);
rowIndex++;
}
SetCell(workbook, sheet, rowIndex, rowIndex, 1, 1, "計", 12, true, BorderStyle.Thin, true);
SetCell(workbook, sheet, rowIndex, rowIndex, 2, 4, "", 12, true, BorderStyle.Thin, true);
SetCell(workbook, sheet, rowIndex, rowIndex, 5, 9, "", 12, true, BorderStyle.Thin, true);
SetCell(workbook, sheet, rowIndex, rowIndex, 10, 14, "税抜き", 12, true, BorderStyle.Thin, true);
SetCell(workbook, sheet, rowIndex, rowIndex, 15, 18, "", 12, true, BorderStyle.Thin, true);
SetCell(workbook, sheet, rowIndex, rowIndex, 19, 21, "", 12, true, BorderStyle.Thin, true);
SetCell(workbook, sheet, rowIndex, rowIndex, 22, 25, total.ToString(), 12, true, BorderStyle.Thin, true);
SetCell(workbook, sheet, rowIndex, rowIndex, 26, 29, "", 12, true, BorderStyle.Thin, true);
#endregion
workbook.Write(fs);
return(fs);
}
}
public Result Execute(ExternalCommandData commandData, ref string message, ElementSet elements)
{
Document doc = commandData.Application.ActiveUIDocument.Document;
// Check if the open document is a Family document
try
{
var check = doc.FamilyManager;
TaskDialog.Show("Warning", "Family document opened, please open a project");
return(Result.Cancelled);
}
catch
{
// TODO: refactor Family document check
}
// Retrieve current date
string currentDate = DateTime.Today.ToString("dd/MM/yyyy");
string[] columnNames = { "Priority", "Warning", "Element Ids", "Date Detected", "Date Solved", "Fixed by" };
string warningJSONPath = @"C:\ProgramData\Autodesk\Revit\Addins\BIMicon\WarningsReport\RevitWarningsClassified.json";
string warningsJsonString = Helpers.Helpers.WriteSafeReadAllLines(warningJSONPath);
var warningsJObject = JObject.Parse(warningsJsonString);
string critical = string.Join("", warningsJObject.Value <JArray>("Critical").ToObject <string[]>());
string high = string.Join("", warningsJObject.Value <JArray>("High").ToObject <string[]>());
string medium = string.Join("", warningsJObject.Value <JArray>("Medium").ToObject <string[]>());
string low = string.Join("", warningsJObject.Value <JArray>("Low").ToObject <string[]>());
IList <FailureMessage> docWarnings = doc.GetWarnings();
// Check if there is any warning in the document
if (docWarnings.Count == 0)
{
TaskDialog.Show("Warning", "This project doesn't contain any warnings. Congratulations!");
return(Result.Succeeded);
}
// Store data to transfer to database
List <string[]> dataTransfer = new List <string[]>();
foreach (FailureMessage failMessage in docWarnings)
{
string failDescription = failMessage.GetDescriptionText();
ICollection <ElementId> failWarningElementIds = failMessage.GetFailingElements();
string failElementIds = string.Join(", ", failWarningElementIds);
string priorityCat = "";
if (critical.Contains(failDescription))
{
priorityCat = "Critical";
}
else if (high.Contains(failDescription))
{
priorityCat = "High";
}
else if (low.Contains(failDescription))
{
priorityCat = "Low";
}
else
{
priorityCat = "Medium";
}
dataTransfer.Add(new string[] {
priorityCat,
failDescription,
failElementIds,
currentDate
});
}
// Path to output data
string desktopPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
string excelPath = desktopPath + @"\Warnings Report.xlsx";
// Create excel file
SXSSFWorkbook workbook = new SXSSFWorkbook();
SXSSFSheet excelSheet = (SXSSFSheet)workbook.CreateSheet("Sheet1");
excelSheet.SetRandomAccessWindowSize(100);
//Create a header row
IRow row = excelSheet.CreateRow(0);
// Style for header
var titleHeader = workbook.CreateFont();
titleHeader.FontHeightInPoints = 12;
titleHeader.IsBold = true;
ICellStyle boldStyle = workbook.CreateCellStyle();
boldStyle.SetFont(titleHeader);
// Write to excel
using (var fs = new FileStream(excelPath, FileMode.Create, FileAccess.Write))
{
// Write header
for (int i = 0; i < columnNames.Count(); i++)
{
var cell = row.CreateCell(i);
cell.SetCellValue(columnNames[i]);
cell.CellStyle = boldStyle;
}
// Write content
for (int i = 0; i < dataTransfer.Count; i++)
{
int numberElements = dataTransfer[i].Count();
row = excelSheet.CreateRow(i + 1);
for (int j = 0; j < numberElements; j++)
{
row.CreateCell(j).SetCellValue(dataTransfer[i][j]);
}
}
// Size columns
excelSheet.TrackAllColumnsForAutoSizing();
for (int i = 0; i < columnNames.Count(); i++)
{
if (i == 1)
{
excelSheet.SetColumnWidth(i, 3800);
}
// Autosize needs to be after column has some data
excelSheet.AutoSizeColumn(i);
}
excelSheet.UntrackAllColumnsForAutoSizing();
// Write to file
workbook.Write(fs);
TaskDialog.Show("Success", "Warnings report created in: " + excelPath);
}
return(Result.Succeeded);
}
public MemoryStream GetExcelPackage()
{
using (var fs = new MemoryStream())
{
var workbook = new SXSSFWorkbook();
var sheet = workbook.CreateSheet(SheetName) as SXSSFSheet;
workbook.RandomAccessWindowSize = 5000;
sheet.DefaultColumnWidth = 4;
sheet.DisplayGridlines = false;
var rang = new CellRangeAddress(0, 1, 1, 10);
var merI = sheet.AddMergedRegion(rang);
var row = sheet.CreateRow(sheet.GetMergedRegion(merI - 1).LastRow);
var cell = row.CreateCell(sheet.GetMergedRegion(merI - 1).LastColumn);
cell.SetCellValue("test");
var style = workbook.CreateCellStyle();
var font = workbook.CreateFont();
font.FontHeightInPoints = 16;
style.SetFont(font);
cell.CellStyle = style;
//SetCell(workbook, sheet, 2, 2, 22, 29, $"発行年月日 {PublishDate.ToString("yyyy年MM月dd日")}", 12, false, BorderStyle.None, false, 700);
//SetCell(workbook, sheet, 5, 11, 1, 13, $"{ToCompany} 御中", 10, true, BorderStyle.Medium, true);
//SetCell(workbook, sheet, 5, 5, 17, 29, FromCompany, 12, false, BorderStyle.None, false);
//SetCell(workbook, sheet, 1, 1, 1, 30, FromDepartment, 12, false, BorderStyle.None, true);
////设置标题
//var titleFont = workbook.CreateFont();
//titleFont.FontHeightInPoints = 16;
//titleFont.Underline = FontUnderlineType.Single;
//SetCell(workbook, sheet, 16, 17, 0, 30, "adfdsf", titleFont, false, BorderStyle.None, true);
////设置周期
//var periodFont = workbook.CreateFont();
//periodFont.FontHeightInPoints = 16;
//periodFont.Underline = FontUnderlineType.Single;
//SetCell(workbook, sheet, 20, 20, 1, 12, Period.ToString("yyyy年MM月度"), periodFont, false, BorderStyle.None, true);
//#region 代理商信息
//SetCell(workbook, sheet, 22, 23, 1, 4, "代理店コード", 12, true, BorderStyle.Thin, true);
//SetCell(workbook, sheet, 22, 23, 5, 13, AgentMessage.AgentCode, 12, true, BorderStyle.Thin, true);
//SetCell(workbook, sheet, 24, 25, 1, 4, "振込日", 12, true, BorderStyle.Thin, true);
//SetCell(workbook, sheet, 24, 25, 5, 13, AgentMessage.DemittanceDate?.ToString("yyyy年MM月dd日"), 12, true, BorderStyle.Thin, true);
//SetCell(workbook, sheet, 26, 27, 1, 4, "振込金額", 12, true, BorderStyle.Thin, true);
//SetCell(workbook, sheet, 26, 27, 5, 13, AgentMessage.DemittanceAmount.ToString(), 12, true, BorderStyle.Thin, true);
////SetBoard(sheet, 22, 27, 1, 13, BorderStyle.Medium);
//#endregion
//#region 代理商帐户信息
//SetCell(workbook, sheet, 22, 23, 17, 20, "金融機関名", 12, true, BorderStyle.Thin, true);
//SetCell(workbook, sheet, 22, 23, 21, 29, AgentAccount.BankName, 12, true, BorderStyle.Thin, true);
//SetCell(workbook, sheet, 24, 25, 17, 20, "支店名", 12, true, BorderStyle.Thin, true);
//SetCell(workbook, sheet, 24, 25, 21, 29, AgentAccount.BranchBankName, 12, true, BorderStyle.Thin, true);
//SetCell(workbook, sheet, 26, 27, 17, 20, "口座番号", 12, true, BorderStyle.Thin, true);
//SetCell(workbook, sheet, 26, 27, 21, 22, AgentAccount.AccountMode, 12, true, BorderStyle.Thin, true);
//SetCell(workbook, sheet, 26, 27, 23, 29, AgentAccount.Account, 12, true, BorderStyle.Thin, true);
//SetCell(workbook, sheet, 28, 29, 17, 20, "口座名義", 12, true, BorderStyle.Thin, true);
//SetCell(workbook, sheet, 28, 29, 21, 29, AgentAccount.AccountOwner, 12, true, BorderStyle.Thin, true);
//#endregion
//#region 列表
//SetCell(workbook, sheet, 32, 32, 1, 1, null, 12, true, BorderStyle.Thin, true, null, HSSFColor.Rose.Index);
//SetCell(workbook, sheet, 32, 32, 2, 4, "コード", 12, true, BorderStyle.Thin, true, null, HSSFColor.Rose.Index);
//SetCell(workbook, sheet, 32, 32, 5, 9, "加盟店", 12, true, BorderStyle.Thin, true, null, HSSFColor.Rose.Index);
//SetCell(workbook, sheet, 32, 32, 10, 14, "対象期間", 12, true, BorderStyle.Thin, true, null, HSSFColor.Rose.Index);
//SetCell(workbook, sheet, 32, 32, 15, 18, "決済利用額", 12, true, BorderStyle.Thin, true, null, HSSFColor.Rose.Index);
//SetCell(workbook, sheet, 32, 32, 19, 21, "手数料率", 12, true, BorderStyle.Thin, true, null, HSSFColor.Rose.Index);
//SetCell(workbook, sheet, 32, 32, 22, 25, "手数料額", 12, true, BorderStyle.Thin, true, null, HSSFColor.Rose.Index);
//SetCell(workbook, sheet, 32, 32, 26, 29, "備考", 12, true, BorderStyle.Thin, true, null, HSSFColor.Rose.Index);
//var rowIndex = 33;
//var sn = 1;
//var total = 0m;
//foreach (var item in Data)
//{
// total += item.RateAmount;
// SetCell(workbook, sheet, rowIndex, rowIndex, 1, 1, sn++.ToString(), 12, true, BorderStyle.Thin, true);
// SetCell(workbook, sheet, rowIndex, rowIndex, 2, 4, item.Code, 12, true, BorderStyle.Thin, true);
// SetCell(workbook, sheet, rowIndex, rowIndex, 5, 9, item.Name, 12, true, BorderStyle.Thin, true);
// SetCell(workbook, sheet, rowIndex, rowIndex, 10, 14, item.TimeSection, 12, true, BorderStyle.Thin, true);
// SetCell(workbook, sheet, rowIndex, rowIndex, 15, 18, item.Amount.ToString(), 12, true, BorderStyle.Thin, true);
// SetCell(workbook, sheet, rowIndex, rowIndex, 19, 21, item.Rate.ToString(), 12, true, BorderStyle.Thin, true);
// SetCell(workbook, sheet, rowIndex, rowIndex, 22, 25, item.RateAmount.ToString(), 12, true, BorderStyle.Thin, true);
// SetCell(workbook, sheet, rowIndex, rowIndex, 26, 29, item.Memo, 12, true, BorderStyle.Thin, true);
// rowIndex++;
//}
//SetCell(workbook, sheet, rowIndex, rowIndex, 1, 1, "計", 12, true, BorderStyle.Thin, true);
//SetCell(workbook, sheet, rowIndex, rowIndex, 2, 4, "", 12, true, BorderStyle.Thin, true);
//SetCell(workbook, sheet, rowIndex, rowIndex, 5, 9, "", 12, true, BorderStyle.Thin, true);
//SetCell(workbook, sheet, rowIndex, rowIndex, 10, 14, "税抜き", 12, true, BorderStyle.Thin, true);
//SetCell(workbook, sheet, rowIndex, rowIndex, 15, 18, "", 12, true, BorderStyle.Thin, true);
//SetCell(workbook, sheet, rowIndex, rowIndex, 19, 21, "", 12, true, BorderStyle.Thin, true);
//SetCell(workbook, sheet, rowIndex, rowIndex, 22, 25, total.ToString(), 12, true, BorderStyle.Thin, true);
//SetCell(workbook, sheet, rowIndex, rowIndex, 26, 29, "", 12, true, BorderStyle.Thin, true);
//#endregion
workbook.Write(fs);
return(fs);
}
}