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 void SetUpSheetAndWorkbook()
{
workbook = new SXSSFWorkbook();
sheet = workbook.CreateSheet() as SXSSFSheet;
tracker = new AutoSizeColumnTracker(sheet);
}
public void TestBug58175()
{
IWorkbook wb = new SXSSFWorkbook();
try
{
ISheet sheet = wb.CreateSheet();
IRow row = sheet.CreateRow(1);
ICell cell = row.CreateCell(3);
cell.SetCellValue("F4");
ICreationHelper factory = wb.GetCreationHelper();
// When the comment box is visible, have it show in a 1x3 space
IClientAnchor anchor = factory.CreateClientAnchor();
anchor.Col1 = (cell.ColumnIndex);
anchor.Col2 = (cell.ColumnIndex + 1);
anchor.Row1 = (row.RowNum);
anchor.Row2 = (row.RowNum + 3);
XSSFClientAnchor ca = (XSSFClientAnchor)anchor;
// create comments and vmlDrawing parts if they don't exist
CommentsTable comments = (((SXSSFWorkbook)wb).XssfWorkbook
.GetSheetAt(0) as XSSFSheet).GetCommentsTable(true);
XSSFVMLDrawing vml = (((SXSSFWorkbook)wb).XssfWorkbook
.GetSheetAt(0) as XSSFSheet).GetVMLDrawing(true);
CT_Shape vmlShape1 = vml.newCommentShape();
if (ca.IsSet())
{
String position = ca.Col1 + ", 0, " + ca.Row1
+ ", 0, " + ca.Col2 + ", 0, " + ca.Row2
+ ", 0";
vmlShape1.GetClientDataArray(0).SetAnchorArray(0, position);
}
// create the comment in two different ways and verify that there is no difference
XSSFComment shape1 = new XSSFComment(comments, comments.NewComment(CellAddress.A1), vmlShape1);
shape1.Column = (ca.Col1);
shape1.Row = (ca.Row1);
CT_Shape vmlShape2 = vml.newCommentShape();
if (ca.IsSet())
{
String position = ca.Col1 + ", 0, " + ca.Row1
+ ", 0, " + ca.Col2 + ", 0, " + ca.Row2
+ ", 0";
vmlShape2.GetClientDataArray(0).SetAnchorArray(0, position);
}
CellAddress ref1 = new CellAddress(ca.Row1, ca.Col1);
XSSFComment shape2 = new XSSFComment(comments, comments.NewComment(ref1), vmlShape2);
Assert.AreEqual(shape1.Author, shape2.Author);
Assert.AreEqual(shape1.ClientAnchor, shape2.ClientAnchor);
Assert.AreEqual(shape1.Column, shape2.Column);
Assert.AreEqual(shape1.Row, shape2.Row);
Assert.AreEqual(shape1.GetCTComment().ToString(), shape2.GetCTComment().ToString());
Assert.AreEqual(shape1.GetCTComment().@ref, shape2.GetCTComment().@ref);
/*CommentsTable table1 = shape1.CommentsTable;
* CommentsTable table2 = shape2.CommentsTable;
* Assert.AreEqual(table1.CTComments.toString(), table2.CTComments.toString());
* Assert.AreEqual(table1.NumberOfComments, table2.NumberOfComments);
* Assert.AreEqual(table1.Relations, table2.Relations);*/
Assert.AreEqual(vmlShape1.ToString().Replace("_x0000_s\\d+", "_x0000_s0000"),
vmlShape2.ToString().Replace("_x0000_s\\d+", "_x0000_s0000"),
"The vmlShapes should have equal content afterwards");
}
finally
{
wb.Close();
}
}
public override IEnumerable <IFreeDocument> WriteData(IEnumerable <IFreeDocument> datas)
{
var xssfWb = new XSSFWorkbook();
var wb = new SXSSFWorkbook(xssfWb, 1000);
// IWorkbook workbook = new XSSFWorkbook();
var sheet1 = xssfWb.CreateSheet("Sheet1");
var sw = File.Create(FileName);
using (var dis = new DisposeHelper(() =>
{
wb.Write(sw);
sw.Close();
}))
{
var rowIndex = 0;
PropertyNames = datas.GetKeys().ToDictionary(d => d, d => d);
foreach (FreeDocument computeable in datas)
{
IDictionary <string, object> data = computeable.DictSerialize();
int cellIndex;
if (rowIndex == 0)
{
var row1 = sheet1.CreateRow(rowIndex);
cellIndex = 0;
foreach (var o in PropertyNames)
{
row1.CreateCell(cellIndex).SetCellValue(o.Value);
sheet1.AutoSizeColumn(cellIndex, true);
cellIndex++;
}
rowIndex++;
}
cellIndex = 0;
var row = sheet1.CreateRow(rowIndex);
foreach (var value in PropertyNames.Select(name => data[name.Key]))
{
if (value is DateTime)
{
row.CreateCell(cellIndex).SetCellValue((value.ToString()));
}
else if (value is int || value is long)
{
row.CreateCell(cellIndex).SetCellValue(value.ToString());
}
else if (value is double)
{
row.CreateCell(cellIndex).SetCellValue((double)value);
}
else if (value is string)
{
row.CreateCell(cellIndex).SetCellValue((string)value);
}
else
{
row.CreateCell(cellIndex).SetCellValue(value?.ToString() ?? "");
}
cellIndex++;
}
rowIndex++;
yield return(computeable);
}
}
}
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);
}
}
