public XLStyle(IXLStylized container, IXLStyle initialStyle = null, Boolean useDefaultModify = true)
{
if (initialStyle != null)
{
Font = new XLFont(container, initialStyle.Font, useDefaultModify);
Alignment = new XLAlignment(container, initialStyle.Alignment);
Border = new XLBorder(container, initialStyle.Border, useDefaultModify);
Fill = new XLFill(container, initialStyle.Fill, useDefaultModify);
NumberFormat = new XLNumberFormat(container, initialStyle.NumberFormat);
Protection = new XLProtection(container, initialStyle.Protection);
}
else
{
Font = new XLFont(container, null);
Alignment = new XLAlignment(container);
Border = new XLBorder(container, null);
Fill = new XLFill(container);
NumberFormat = new XLNumberFormat(container, null);
Protection = new XLProtection(container);
}
DateFormat = NumberFormat;
}
/// <summary>
/// Copies the report style to an XL Style.
/// </summary>
/// <param name="reportStyle">The report style.</param>
/// <param name="xlStyle">The xl style.</param>
public static void CopyToXlStyle(this ReportStyle reportStyle, IXLStyle xlStyle)
{
if (reportStyle.BackColor != Color.White)
{
xlStyle.Fill.BackgroundColor = XLColor.FromColor(reportStyle.BackColor);
}
if (reportStyle.ForeColor != Color.Black)
{
xlStyle.Font.SetFontColor(XLColor.FromColor(reportStyle.ForeColor));
}
xlStyle.Font.SetBold(reportStyle.Bold);
xlStyle.Font.SetFontSize(reportStyle.FontSize);
xlStyle.Font.SetItalic(reportStyle.Italic);
xlStyle.Font.SetUnderline(reportStyle.Underline ? XLFontUnderlineValues.Single : XLFontUnderlineValues.None);
xlStyle.Alignment.Horizontal =
reportStyle.HorizontalAlignment == HorizontalAlignment.Center ? XLAlignmentHorizontalValues.Center :
reportStyle.HorizontalAlignment == HorizontalAlignment.Left ? XLAlignmentHorizontalValues.Left :
XLAlignmentHorizontalValues.Right;
xlStyle.Alignment.Vertical =
reportStyle.VerticalAlignment == VerticalAlignment.Bottom ? XLAlignmentVerticalValues.Bottom :
reportStyle.VerticalAlignment == VerticalAlignment.Middle ? XLAlignmentVerticalValues.Center :
XLAlignmentVerticalValues.Top;
xlStyle.Alignment.TextRotation = reportStyle.TextRotation;
xlStyle.Alignment.WrapText = reportStyle.WrapText;
}
/// <summary>
/// Update an Excel cell style with the passed in attributes
/// </summary>
/// <param name="cellStyle">IXLStyle to update</param>
/// <param name="numberFormat">Optional number formatting string for the cell</param>
/// <param name="dateFormat">Optional DateTime formatting string for the cell</param>
/// <param name="fontStyle">Optional font style for the cell</param>
/// <param name="fontSize">Optional font size for the cell</param>
/// <param name="fontName">Optional font name for the cell</param>
private void UpdateStyle(
IXLStyle cellStyle,
string numberFormat = null,
string dateFormat = null,
FontStyle?fontStyle = null,
float?fontSize = null,
string fontName = null)
{
// Set up native formatting if provided
if (!string.IsNullOrEmpty(numberFormat))
{
cellStyle.NumberFormat.SetFormat(numberFormat);
}
if (!string.IsNullOrEmpty(dateFormat))
{
cellStyle.DateFormat.SetFormat(dateFormat);
}
// Apply font styling if defined
if (fontStyle != null || fontSize != null || fontName != null)
{
var defaultFont = _defaultFont;
var style = fontStyle ?? defaultFont.Style;
var font = cellStyle.Font;
font.Bold = (style & FontStyle.Bold) != 0;
font.Italic = (style & FontStyle.Italic) != 0;
font.Underline = (style & FontStyle.Underline) != 0 ? XLFontUnderlineValues.Single : XLFontUnderlineValues.None;
font.Strikethrough = (style & FontStyle.Strikeout) != 0;
font.FontSize = fontSize ?? defaultFont.SizeInPoints;
font.FontName = fontName ?? defaultFont.Name;
}
}
public void CopyFromFileSpecial_Test_CopiesCorrectStyle()
{
_tmpFile = GetNewRandomFilePath(GetOrCreateTmpDirectory());
const string numberFormat = "0%";
using (XLWorkbook workbook = new XLWorkbook()) {
IXLWorksheet sourceSheet = workbook.Worksheets.Add();
IXLStyle style = sourceSheet.Cell(1, 1).Style;
style.Font.Bold = true;
style.Font.Italic = true;
style.NumberFormat.Format = numberFormat;
workbook.SaveAs(_tmpFile);
}
Worksheet targetSheet = _application.Workbooks.Add().Sheets[1];
Range range = targetSheet.Cells[1, 1];
new OpenXmlHelper().CopyFromFileSpecial(_tmpFile, range, XlPasteType.xlPasteAll);
Assert.That(range.Font.Bold, Is.True);
Assert.That(range.Font.Italic, Is.True);
Assert.That(range.NumberFormat, Is.EqualTo(numberFormat));
(targetSheet.Parent as Workbook)?.Close();
}
public XLCells(bool usedCellsOnly, bool includeFormats, Func <IXLCell, Boolean> predicate = null)
{
_style = new XLStyle(this, XLWorkbook.DefaultStyle);
_usedCellsOnly = usedCellsOnly;
_includeFormats = includeFormats;
_predicate = predicate;
}
private void SetStyle(IXLStyle styleToUse)
{
//_style = new XLStyle(this, styleToUse);
_styleCacheId = Range.Worksheet.Workbook.GetStyleId(styleToUse);
_style = null;
StyleChanged = false;
}
public void WriteFormulaR1C1(string formula, IXLStyle cellStyle)
{
var xlCell = _sheet.Cell(_row, _clmn);
xlCell.Style = cellStyle;
xlCell.SetFormulaR1C1(formula);
ChangeAddress(_row, _clmn + 1);
}
public void ApplyTo(IXLStyle TargetObjStyle)
{
TargetObjStyle.Fill.BackgroundColor = this.BackgroundColor;
TargetObjStyle.Alignment.Horizontal = this.HorizintalAlign;
TargetObjStyle.Font.FontColor = this.FontColor;
TargetObjStyle.Font.Bold = this.Bold;
TargetObjStyle.Font.Italic = this.Italic;
TargetObjStyle.Font.FontSize = this.FontSize;
}
private IXLStyle GetStyle()
{
//return _style;
if (_style != null)
{
return(_style);
}
return(_style = new XLStyle(this, Range.Worksheet.Workbook.GetStyleById(_styleCacheId), CopyDefaultModify));
}
public void WriteValue(object value, IXLStyle cellStyle)
{
var xlCell = _sheet.Cell(_row, _clmn);
xlCell.SetValue(value);
xlCell.Style = cellStyle ?? _wb.Style;
_maxClmn = Math.Max(_maxClmn, _clmn);
_maxRow = Math.Max(_maxRow, _row);
ChangeAddress(_row, _clmn + 1);
}
public bool Equals(IXLStyle other)
{
return
Font.Equals(other.Font)
&& Fill.Equals(other.Fill)
&& Border.Equals(other.Border)
&& NumberFormat.Equals(other.NumberFormat)
&& Alignment.Equals(other.Alignment)
&& Protection.Equals(other.Protection)
;
}
public bool Equals(IXLStyle other)
{
return
(Font.Equals(other.Font) &&
Fill.Equals(other.Fill) &&
Border.Equals(other.Border) &&
NumberFormat.Equals(other.NumberFormat) &&
Alignment.Equals(other.Alignment) &&
Protection.Equals(other.Protection)
);
}
public static void ActivateRuledLine(IXLStyle style)
{
style.Border.TopBorder = XLBorderStyleValues.Thin;
style.Border.TopBorderColor = XLColor.Black;
style.Border.BottomBorder = XLBorderStyleValues.Thin;
style.Border.BottomBorderColor = XLColor.Black;
style.Border.RightBorder = XLBorderStyleValues.Thin;
style.Border.RightBorderColor = XLColor.Black;
style.Border.LeftBorder = XLBorderStyleValues.Thin;
style.Border.LeftBorderColor = XLColor.Black;
}
private void SetStyle(IXLStyle style, bool propagate = false)
{
if (style is XLStyle xlStyle)
{
SetStyle(xlStyle.Value, propagate);
}
else
{
SetStyle(XLStyleValue.FromKey(XLStyle.GenerateKey(style)), propagate);
}
}
internal void SetStyleNoColumns(IXLStyle value)
{
InnerStyle = value;
int row = RowNumber();
foreach (XLCell c in Worksheet.Internals.CellsCollection.GetCellsInRow(row))
{
c.InnerStyle = value;
}
}
public bool Equals(IXLStyle other)
{
var otherS = other as XLStyle;
if (otherS == null)
{
return(false);
}
return(Key == otherS.Key &&
_container == otherS._container);
}
public static void AreCellsStyleEquals(IXLStyle expected, IXLStyle actual, string message = null)
{
if (expected.Equals(actual))
{
return;
}
message ??= string.Empty;
Assert.AreEqual(expected.Border, actual.Border, string.Format(message, "Border"));
Assert.AreEqual(expected.Alignment, actual.Alignment, string.Format(message, "Alignment"));
Assert.AreEqual(expected.DateFormat, actual.DateFormat, string.Format(message, "DateFormat"));
Assert.AreEqual(expected.Fill, actual.Fill, string.Format(message, "Fill"));
Assert.AreEqual(expected.Font, actual.Font, string.Format(message, "Font"));
Assert.AreEqual(expected.NumberFormat, actual.NumberFormat, string.Format(message, "NumberFormat"));
Assert.AreEqual(expected.Protection, actual.Protection, string.Format(message, "Protection"));
}
public void WriteValue(object value, IXLStyle cellStyle)
{
var xlCell = _sheet.Cell(_row, _clmn);
try
{
xlCell.SetValue(value);
}
catch (ArgumentException)
{
xlCell.SetValue(value?.ToString());
}
xlCell.Style = cellStyle ?? _wb.Style;
ChangeAddress(_row, _clmn + 1);
}
internal Int32 GetStyleId(IXLStyle style)
{
Int32 cached;
if (_stylesByStyle.TryGetValue(style, out cached))
{
return(cached);
}
var count = _stylesByStyle.Count;
var styleToUse = new XLStyle(null, style);
_stylesByStyle.Add(styleToUse, count);
_stylesById.Add(count, styleToUse);
return(count);
}
internal void SetStyleNoColumns(IXLStyle value)
{
if (IsReference)
{
Worksheet.Internals.RowsCollection[RowNumber()].SetStyleNoColumns(value);
}
else
{
SetStyle(value);
int row = RowNumber();
foreach (XLCell c in Worksheet.Internals.CellsCollection.GetCellsInRow(row))
{
c.Style = value;
}
}
}
public void AplicarEstilo(IXLStyle style)
{
if (Fuente != null)
{
Fuente.AplicarEstilo(style.Font);
}
if (Bordes != null)
{
Bordes.AplicarEstilo(style.Border);
}
if (Relleno != null)
{
Relleno.AplicarEstilo(style.Fill);
}
}
public IXLStyle GetStyle(IXLStyle Style)
{
Style.Alignment.Horizontal = this.Horizontal;
Style.Alignment.Vertical = this.Vertical;
Style.Border.BottomBorder = this.BottomBorder;
Style.Border.TopBorder = this.TopBorder;
Style.Border.LeftBorder = this.LeftBorder;
Style.Border.RightBorder = this.RightBorder;
Style.Fill.BackgroundColor = XLColor.FromColor(this.BackgroundColor);
Style.Font.Bold = this.Bold;
Style.Font.FontColor = XLColor.FromColor(this.FontColor);
Style.Font.FontName = this.FontName;
Style.Font.FontSize = this.FontSize;
Style.Font.Italic = this.Italic;
Style.Font.Strikethrough = this.Strikethrough;
Style.Font.Underline = this.Underline;
//Style.Font.VerticalAlignment = this.VerticalAlignment;
return(Style);
}
public void CopyFromFileSpecial_Test_CopiesStyleOnly()
{
_tmpFile = GetNewRandomFilePath(GetOrCreateTmpDirectory());
const string numberFormat = "0%";
const int expected1 = 1;
const string expected2 = "test";
DateTime expected3 = DateTime.Now;
using (XLWorkbook workbook = new XLWorkbook()) {
IXLWorksheet sourceSheet = workbook.Worksheets.Add();
IXLStyle style = sourceSheet.Cell(1, 1).Style;
style.Font.Bold = true;
style.Font.Italic = true;
style.NumberFormat.Format = numberFormat;
sourceSheet.Cell(1, 1).Value = expected1;
sourceSheet.Cell(2, 2).Value = expected2;
sourceSheet.Cell(1, 2).Value = expected3;
workbook.SaveAs(_tmpFile);
}
Worksheet targetSheet = _application.Workbooks.Add().Sheets[1];
Range range = targetSheet.Cells[1, 1];
new OpenXmlHelper().CopyFromFileSpecial(_tmpFile, range, XlPasteType.xlPasteFormats);
Assert.That(range.Font.Bold, Is.True);
Assert.That(range.Font.Italic, Is.True);
Assert.That(range.NumberFormat, Is.EqualTo(numberFormat));
object result1 = targetSheet.Cells[1, 1].Value;
object result2 = targetSheet.Cells[2, 2].Value;
object result3 = targetSheet.Cells[1, 2].Value;
Assert.That(result1, Is.Null);
Assert.That(result2, Is.Null);
Assert.That(result3, Is.Null);
(targetSheet.Parent as Workbook)?.Close();
}
internal static XLStyleKey GenerateKey(IXLStyle initialStyle)
{
if (initialStyle == null)
{
return(Default.Key);
}
if (initialStyle is XLStyle)
{
return((initialStyle as XLStyle).Key);
}
return(new XLStyleKey
{
Font = XLFont.GenerateKey(initialStyle.Font),
Alignment = XLAlignment.GenerateKey(initialStyle.Alignment),
Border = XLBorder.GenerateKey(initialStyle.Border),
Fill = XLFill.GenerateKey(initialStyle.Fill),
NumberFormat = XLNumberFormat.GenerateKey(initialStyle.NumberFormat),
Protection = XLProtection.GenerateKey(initialStyle.Protection)
});
}
public XLStyle(IXLStylized container, IXLStyle initialStyle = null, Boolean useDefaultModify = true)
{
if (initialStyle != null)
{
Font = new XLFont(container, initialStyle.Font, useDefaultModify);
Alignment = new XLAlignment(container, initialStyle.Alignment);
Border = new XLBorder(container, initialStyle.Border, useDefaultModify);
Fill = new XLFill(container, initialStyle.Fill, useDefaultModify);
NumberFormat = new XLNumberFormat(container, initialStyle.NumberFormat);
Protection = new XLProtection(container, initialStyle.Protection);
}
else
{
Font = new XLFont(container, null);
Alignment = new XLAlignment(container);
Border = new XLBorder(container, null);
Fill = new XLFill(container);
NumberFormat = new XLNumberFormat(container, null);
Protection = new XLProtection(container);
}
DateFormat = NumberFormat;
}
public XLRows(XLWorksheet worksheet)
{
_worksheet = worksheet;
style = new XLStyle(this, XLWorkbook.DefaultStyle);
}
public XLStylizedEmpty(IXLStyle defaultStyle)
{
Style = new XLStyle(this, defaultStyle);
}
public XLRanges()
{
_style = new XLStyle(this, XLWorkbook.DefaultStyle);
}
public XLStylizedEmpty(IXLStyle defaultStyle)
{
Style = new XLStyle(this, defaultStyle);
}
public XLStylizedContainer(IXLStyle style, IXLStylized container)
{
Style = style;
_container = container;
RangesUsed = container.RangesUsed;
}
public XLTableRows(IXLStyle defaultStyle)
{
_style = new XLStyle(this, defaultStyle);
}
public XLRangeRows()
{
_style = new XLStyle(this, XLWorkbook.DefaultStyle);
}
public XLStylizedContainer(IXLStyle style, IXLStylized container)
{
Style = style;
_container = container;
RangesUsed = container.RangesUsed;
}
public XLRangeParameters(XLRangeAddress rangeAddress, IXLStyle defaultStyle)
{
RangeAddress = rangeAddress;
_ignoreEvents = !rangeAddress.Worksheet.EventTrackingEnabled;
DefaultStyle = defaultStyle;
}
private static void Main(string[] args)
{
string scriptDirectoryPath = AppDomain.CurrentDomain.BaseDirectory;
if (!Path.IsPathRooted(scriptDirectoryPath))
{
return;
}
var scriptDirectory = new DirectoryInfo(scriptDirectoryPath);
DirectoryInfo excerptsDirectory = scriptDirectory.Parent;
if (excerptsDirectory == null || !excerptsDirectory.Exists)
{
return;
}
const StringComparison comparison = StringComparison.InvariantCultureIgnoreCase;
var estates = new List <Estate>();
foreach (FileInfo file in excerptsDirectory.EnumerateFiles("*.xml"))
{
var builder = new StringBuilder();
string[] lines = File.ReadAllLines(file.FullName);
foreach (string line in lines)
{
if (!line.StartsWith("<?"))
{
if (!string.IsNullOrEmpty(line))
{
builder.Append(line);
builder.Append(Environment.NewLine);
}
}
}
string text = builder.ToString();
var document = new XmlDocument();
try
{
document.LoadXml(text);
}
catch (Exception e)
{
Console.WriteLine(file.FullName);
Console.WriteLine(e);
continue;
}
foreach (XmlNode kpoks in document.ChildNodes)
{
if (string.Equals(kpoks.Name, "KPOKS", comparison))
{
foreach (XmlNode realty in kpoks.ChildNodes)
{
if (string.Equals(realty.Name, "Realty", comparison))
{
foreach (XmlNode flat in realty.ChildNodes)
{
if (string.Equals(flat.Name, "Flat", comparison))
{
var estate = new Estate();
estates.Add(estate);
if (flat.Attributes != null)
{
foreach (XmlAttribute a in flat.Attributes)
{
if (string.Equals(a.Name, "CadastralNumber", comparison))
{
estate.CadastralNumber = a.Value;
}
else if (string.Equals(a.Name, "DateCreated", comparison))
{
estate.DateCreated = a.Value;
}
}
}
foreach (XmlNode flatChild in flat.ChildNodes)
{
if (string.Equals(flatChild.Name, "CadastralBlock", comparison))
{
estate.CadastralBlock = flatChild.InnerText;
}
else if (string.Equals(flatChild.Name, "ObjectType", comparison))
{
estate.ObjectType = flatChild.InnerText;
}
else if (string.Equals(flatChild.Name, "Area", comparison))
{
estate.Area = flatChild.InnerText;
}
else if (string.Equals(flatChild.Name, "Address", comparison))
{
foreach (XmlNode addressChild in flatChild.ChildNodes)
{
if (string.Equals(addressChild.Name, "adrs:OKATO", comparison))
{
estate.Okato = addressChild.InnerText;
}
else if (string.Equals(addressChild.Name, "adrs:KLADR", comparison))
{
estate.Kladr = addressChild.InnerText;
}
else if (string.Equals(addressChild.Name, "adrs:PostalCode", comparison))
{
estate.PostalCode = addressChild.InnerText;
}
else if (string.Equals(addressChild.Name, "adrs:Region", comparison))
{
estate.Region = addressChild.InnerText;
}
else if (string.Equals(addressChild.Name, "adrs:UrbanDistrict", comparison))
{
string type = null;
string name = null;
if (addressChild.Attributes != null)
{
foreach (XmlAttribute a in addressChild.Attributes)
{
if (string.Equals(a.Name, "Type", comparison))
{
type = a.Value;
}
else if (string.Equals(a.Name, "Name", comparison))
{
name = a.Value;
}
}
}
estate.UrbanDistrict = $"{name} {type}";
}
else if (string.Equals(addressChild.Name, "adrs:Street", comparison))
{
string type = null;
string name = null;
if (addressChild.Attributes != null)
{
foreach (XmlAttribute a in addressChild.Attributes)
{
if (string.Equals(a.Name, "Type", comparison))
{
type = a.Value;
}
else if (string.Equals(a.Name, "Name", comparison))
{
name = a.Value;
}
}
}
estate.Street = $"{name} {type}";
}
else if (string.Equals(addressChild.Name, "adrs:Level1", comparison))
{
if (addressChild.Attributes != null)
{
foreach (XmlAttribute a in addressChild.Attributes)
{
if (string.Equals(a.Name, "Value", comparison))
{
estate.Level1 = a.Value;
}
}
}
}
else if (string.Equals(addressChild.Name, "adrs:Apartment", comparison))
{
if (addressChild.Attributes != null)
{
foreach (XmlAttribute a in addressChild.Attributes)
{
if (string.Equals(a.Name, "Value", comparison))
{
estate.Apartment = a.Value;
}
}
}
}
}
}
else if (string.Equals(flatChild.Name, "CadastralCost", comparison))
{
if (flatChild.Attributes != null)
{
foreach (XmlAttribute a in flatChild.Attributes)
{
if (string.Equals(a.Name, "Value", comparison))
{
estate.CadastralCost = a.Value;
}
else if (string.Equals(a.Name, "Unit", comparison))
{
estate.CadastralCostUnit = a.Value;
}
}
}
}
else if (string.Equals(flatChild.Name, "Rights", comparison))
{
foreach (XmlNode rightNode in flatChild.ChildNodes)
{
if (string.Equals(rightNode.Name, "Right", comparison))
{
var right = new Right();
estate.Rights.Add(right);
foreach (XmlNode rightChild in rightNode.ChildNodes)
{
if (string.Equals(rightChild.Name, "Name", comparison))
{
right.Name = rightChild.InnerText;
}
else if (string.Equals(rightChild.Name, "Type", comparison))
{
right.Type = rightChild.InnerText;
}
else if (string.Equals(rightChild.Name, "Owners", comparison))
{
foreach (XmlNode ownerNode in rightChild.ChildNodes)
{
if (string.Equals(ownerNode.Name, "Owner", comparison))
{
var owner = new Owner();
right.Owners.Add(owner);
foreach (XmlNode person in ownerNode.ChildNodes)
{
if (string.Equals(person.Name, "Person", comparison))
{
foreach (XmlNode child in person.ChildNodes)
{
if (string.Equals(child.Name, "FamilyName", comparison))
{
owner.FamilyName = child.InnerText;
}
else if (string.Equals(child.Name, "FirstName", comparison))
{
owner.FirstName = child.InnerText;
}
else if (string.Equals(child.Name, "Patronymic", comparison))
{
owner.Patronymic = child.InnerText;
}
}
}
}
}
}
}
else if (string.Equals(rightChild.Name, "Registration", comparison))
{
foreach (XmlNode child in rightChild.ChildNodes)
{
if (string.Equals(child.Name, "RegNumber", comparison))
{
right.RegNumber = child.InnerText;
}
else if (string.Equals(child.Name, "RegDate", comparison))
{
right.RegDate = child.InnerText;
}
}
}
}
}
}
}
}
break;
}
}
break;
}
}
break;
}
}
}
var workbook = new XLWorkbook();
IXLWorksheet worksheet = workbook.Worksheets.Add("Flats");
var headers = new[]
{
"Квартира",
"ОКАТО",
"КЛАДР",
"Индекс",
"Регион",
"Район",
"Улица",
"Дом",
"Кадастровый номер",
"Кадастровый блок",
"Тип объекта",
"Площадь",
"Кадастровая стоимость",
"Собственность",
"Тип собственности",
"Регистрационный номер",
"Дата регистрации",
"ФИО"
};
char[] alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".ToCharArray();
for (int i = 0; i < headers.Length; i++)
{
IXLCell a1 = worksheet.Cell($"{alphabet[i]}1");
a1.Value = headers[i];
a1.SetDataType(XLCellValues.Text);
IXLStyle style = a1.Style;
style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
style.Font.Bold = true;
}
int row = 1;
foreach (Estate estate in estates)
{
row++;
IXLCell a = worksheet.Cell($"A{row}");
a.Value = estate.Apartment;
a.SetDataType(XLCellValues.Text);
IXLCell b = worksheet.Cell($"B{row}");
b.Value = estate.Okato;
b.SetDataType(XLCellValues.Text);
IXLCell c = worksheet.Cell($"C{row}");
c.Value = estate.Kladr;
c.SetDataType(XLCellValues.Text);
IXLCell d = worksheet.Cell($"D{row}");
d.Value = estate.PostalCode;
d.SetDataType(XLCellValues.Text);
IXLCell e = worksheet.Cell($"E{row}");
e.Value = estate.Region;
e.SetDataType(XLCellValues.Text);
IXLCell f = worksheet.Cell($"F{row}");
f.Value = estate.UrbanDistrict;
f.SetDataType(XLCellValues.Text);
IXLCell g = worksheet.Cell($"G{row}");
g.Value = estate.Street;
g.SetDataType(XLCellValues.Text);
IXLCell h = worksheet.Cell($"H{row}");
h.Value = estate.Level1;
h.SetDataType(XLCellValues.Text);
IXLCell i = worksheet.Cell($"I{row}");
i.Value = estate.CadastralNumber;
i.SetDataType(XLCellValues.Text);
IXLCell j = worksheet.Cell($"J{row}");
j.Value = estate.CadastralBlock;
j.SetDataType(XLCellValues.Text);
IXLCell k = worksheet.Cell($"K{row}");
k.Value = estate.ObjectType;
k.SetDataType(XLCellValues.Text);
IXLCell l = worksheet.Cell($"L{row}");
l.Value = estate.Area;
IXLCell m = worksheet.Cell($"M{row}");
m.Value = estate.CadastralCost;
foreach (Right right in estate.Rights)
{
row++;
IXLCell n = worksheet.Cell($"N{row}");
n.Value = right.Name;
n.SetDataType(XLCellValues.Text);
IXLCell o = worksheet.Cell($"O{row}");
o.Value = right.Type;
o.SetDataType(XLCellValues.Text);
IXLCell p = worksheet.Cell($"P{row}");
p.Value = right.RegNumber;
p.SetDataType(XLCellValues.Text);
IXLCell q = worksheet.Cell($"Q{row}");
q.Value = right.RegDate;
foreach (Owner owner in right.Owners)
{
row++;
IXLCell r = worksheet.Cell($"R{row}");
r.Value = $"{owner.FamilyName} {owner.FirstName} {owner.Patronymic}";
r.SetDataType(XLCellValues.Text);
}
}
row++;
}
worksheet.Columns(1, 18).AdjustToContents();
string xlsxPath = Path.Combine(excerptsDirectory.FullName, $"{DateTime.Now:yyyy-MM-dd HH-mm-ss}.xlsx");
try
{
workbook.SaveAs(xlsxPath);
Process.Start(xlsxPath);
}
catch (Exception e)
{
Console.WriteLine(e);
}
Console.ReadLine();
}
internal Int32 GetStyleId(IXLStyle style)
{
Int32 cached;
if (_stylesByStyle.TryGetValue(style, out cached))
return cached;
var count = _stylesByStyle.Count;
var styleToUse = new XLStyle(null, style);
_stylesByStyle.Add(styleToUse, count);
_stylesById.Add(count, styleToUse);
return count;
}
public XLRows(XLWorksheet worksheet)
{
_worksheet = worksheet;
style = new XLStyle(this, XLWorkbook.DefaultStyle);
}
public XLTableRows(IXLStyle defaultStyle)
{
_style = new XLStyle(this, defaultStyle);
}
private static void Enbox(IXLStyle style)
{
style.Border.LeftBorder = XLBorderStyleValues.Thin;
style.Border.RightBorder = XLBorderStyleValues.Thin;
style.Border.TopBorder = XLBorderStyleValues.Thin;
style.Border.BottomBorder = XLBorderStyleValues.Thin;
}
internal void SetStyleNoColumns(IXLStyle value)
{
if (IsReference)
Worksheet.Internals.RowsCollection[RowNumber()].SetStyleNoColumns(value);
else
{
SetStyle(value);
int row = RowNumber();
foreach (XLCell c in Worksheet.Internals.CellsCollection.GetCellsInRow(row))
c.Style = value;
}
}
public XLPivotValueStyleFormat(IXLPivotField field = null, IXLStyle style = null)
: base(field, style)
{
}
public XLStylizedEmpty(IXLStyle defaultStyle)
{
Style = defaultStyle;
}
public IActionResult OnGetExcel()
{
using (var workbook = new XLWorkbook())
{
var worksheet = workbook.Worksheets.Add("Sheet1");
IXLStyle style = worksheet.Style;
style.Font.Bold = true;
style.Font.FontColor = XLColor.Black;
style.Font.FontName = "微软雅黑";
style.Font.FontSize = 12;
//style.Font.Italic = true;
style.Font.Shadow = false;
//style.Font.Underline = XLFontUnderlineValues.Double;
for (int i = 0; i < worksheet.ColumnCount(); i++)
{
}
var currentRow = 1;
worksheet.Cell(currentRow, 1).Value = "仓库编码";
worksheet.Cell(currentRow, 2).Value = "仓库名称";
worksheet.Cell(currentRow, 3).Value = "入库日期";
worksheet.Cell(currentRow, 4).Value = "单据编号";
worksheet.Cell(currentRow, 5).Value = "存货名称";
worksheet.Cell(currentRow, 6).Value = "规格型号";
worksheet.Cell(currentRow, 7).Value = "数量";
worksheet.Cell(currentRow, 8).Value = "单价";
worksheet.Cell(currentRow, 9).Value = "价格";
worksheet.Cell(currentRow, 10).Value = "上次购买单据编号";
worksheet.Cell(currentRow, 11).Value = "上次购买单价";
worksheet.Cell(currentRow, 12).Value = "差额";
worksheet.Cell(currentRow, 13).Value = "百分比";
IQueryable <TjgCgjg> tjgCgjgs;
if (!string.IsNullOrEmpty(Warehouse))
{
tjgCgjgs = from t in _context.TjgCgjg
where t.Whcode == Warehouse.Substring(0, 3)
select t;
}
else
{
tjgCgjgs = from t in _context.TjgCgjg
select t;
}
foreach (var item in tjgCgjgs)
{
currentRow++;
worksheet.Cell(currentRow, 1).Value = item.Whcode;
worksheet.Cell(currentRow, 2).Value = item.CWhName;
var rkrq = item.Cgrq.Value.ToString("yyyy-MM-dd");
worksheet.Cell(currentRow, 3).Value = rkrq;
worksheet.Cell(currentRow, 4).Value = item.Ccode;
worksheet.Cell(currentRow, 5).Value = item.CInvName;
worksheet.Cell(currentRow, 6).Value = item.CInvStd;
worksheet.Cell(currentRow, 7).Value = item.Iquantity;
worksheet.Cell(currentRow, 8).Value = item.Iunitcost;
worksheet.Cell(currentRow, 9).Value = item.Iprice;
worksheet.Cell(currentRow, 10).Value = item.Lccode;
worksheet.Cell(currentRow, 11).Value = item.Liunitcost;
//decimal difference = (decimal)((item.Iunitcost - item.Liunitcost) * item.Iquantity);
//worksheet.Cell(currentRow, 12).Value = difference;
worksheet.Cell(currentRow, 12).FormulaA1 = $"=(H{currentRow}-K{currentRow})*G{currentRow}";
// 4 #,##0.00 也可以这样写ws.Cell(ro, co).Style.NumberFormat.Format = "$ #,##0.00";
worksheet.Cell(currentRow, 12).Style.NumberFormat.NumberFormatId = 2;
//var bfb = ((item.Iunitcost - item.Liunitcost) / item.Iunitcost).Value.ToString("P");
//worksheet.Cell(currentRow, 13).Value = bfb;
//worksheet.Cell(currentRow, 13).DataType = XLDataType.Number;
worksheet.Cell(currentRow, 13).FormulaR1C1 = $"=(H{currentRow}-K{currentRow})/H{currentRow}";
worksheet.Cell(currentRow, 13).Style.NumberFormat.Format = "0.00%";
}
worksheet.Columns().AdjustToContents();
worksheet.CellsUsed().Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.CellsUsed().Style.Border.TopBorder = XLBorderStyleValues.Thin;
worksheet.CellsUsed().Style.Border.TopBorderColor = XLColor.Black;
worksheet.CellsUsed().Style.Border.LeftBorder = XLBorderStyleValues.Thin;
worksheet.CellsUsed().Style.Border.LeftBorderColor = XLColor.Black;
worksheet.CellsUsed().Style.Border.RightBorder = XLBorderStyleValues.Thin;
worksheet.CellsUsed().Style.Border.RightBorderColor = XLColor.Black;
worksheet.CellsUsed().Style.Border.BottomBorder = XLBorderStyleValues.Thin;
worksheet.CellsUsed().Style.Border.BottomBorderColor = XLColor.Black;
using (var stream = new MemoryStream())
{
workbook.SaveAs(stream);
var content = stream.ToArray();
return(File(
content,
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
"Export.xlsx"));
}
}
}