public Xl.Worksheet WriteInterchanges(Xl.Worksheet worksheet, IEnumerable <Interchange> interchanges)
{
var tableColOffset = 1;
var tableRowOffset = 1;
var titleCell = worksheet.Cells[1, 1];
titleCell.Value = "Interchange";
//titleCell.Style = _Style.GetMasterTableHeaderStyle(worksheet);
titleCell.EntireColumn.ColumnWidth = 1.0;
var header = new List <object> {
"PrefectureCode", "TempInterchangeId", "IC_Kana", "IC_Kanji", "Highway", "Latitude", "Longitude", "Data_Date"
};
var data = new List <List <object> > {
header
}.Concat(interchanges.Select(r =>
new List <object> {
r.PrefectureCode, r.TempInterchangeId, r.IC_Kana, r.IC_Kanji, r.HighwayDisplay, r.Latitude, r.Longitude
, DateTime.SpecifyKind(r.DataDate, DateTimeKind.Utc).ToLocalTime()
})).ToArray().CreateRectangularArray();
var tableTopLeft = worksheet.Cells[tableRowOffset + 1, tableColOffset + 1];
var tableBottomRight = worksheet.Cells[interchanges.Count() + tableRowOffset + 1, header.Count() + tableColOffset];
Xl.Range range = worksheet.Range(tableTopLeft, tableBottomRight);
range.set_Value(Type.Missing, data);
var opList = worksheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange, range, null, XlYesNoGuess.xlYes);
opList.Name = $"Zenrin.Interchange";
opList.TableStyle = "TableStyleLight9";
opList.ListColumns[8].DataBodyRange.NumberFormatLocal = Format_DateTime;
var colorRange = AddinContext.ExcelApp.Union(opList.ListColumns[1].DataBodyRange,
opList.ListColumns[8].DataBodyRange);
colorRange.Interior.ThemeColor = XlThemeColor.xlThemeColorAccent1;
colorRange.Interior.TintAndShade = 0.5;
opList.Range.Columns.AutoFit();
opList.Range.Rows.AutoFit();
return(worksheet);
}
public Xl.Worksheet WriteHighwayInterchanges(Xl.Worksheet worksheet, IEnumerable <HighwayInterchange> highwayInterchanges)
{
var tableColOffset = 1;
var tableRowOffset = 1;
var titleCell = worksheet.Cells[1, 1];
titleCell.Value = "HighwayInterchange";
//titleCell.Style = _Style.GetMasterTableHeaderStyle(worksheet);
titleCell.EntireColumn.ColumnWidth = 1.0;
var header = new List <object> {
"HighwayId", "Highway", "Interchange", "SortOrder", "Latitude", "Longitude"
};
var data = new List <List <object> > {
header
}.Concat(highwayInterchanges.Select(r =>
new List <object> {
r.TempHighwayId, r.HighwayKanji, r.Interchange.IC_Kanji, r.SortOrder, r.Interchange.Latitude, r.Interchange.Longitude
})).ToArray().CreateRectangularArray();
var tableTopLeft = worksheet.Cells[tableRowOffset + 1, tableColOffset + 1];
var tableBottomRight = worksheet.Cells[highwayInterchanges.Count() + tableRowOffset + 1, header.Count() + tableColOffset];
Xl.Range range = worksheet.Range(tableTopLeft, tableBottomRight);
range.set_Value(Type.Missing, data);
var opList = worksheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange, range, null, XlYesNoGuess.xlYes);
opList.Name = $"Zenrin.HighwayInterchange";
opList.TableStyle = "TableStyleLight9";
var colorRange = opList.ListColumns[1].DataBodyRange;
colorRange.Interior.ThemeColor = XlThemeColor.xlThemeColorAccent1;
colorRange.Interior.TintAndShade = 0.5;
opList.Range.Columns.AutoFit();
opList.Range.Rows.AutoFit();
return(worksheet);
}