public ExcelModule()
: base("Excel")
{
//任务列表
Post["/GridExport"] = r =>
{
string excelParam = Request.Form["excelParam"];
if (string.IsNullOrEmpty(excelParam))
{
return("");
}
ExcelInfo info = JsonConvert.DeserializeObject <ExcelInfo>(excelParam);
if (string.IsNullOrEmpty(info.FileName))
{
info.FileName = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + ".xls";
}
else
{
if (!info.FileName.EndsWith(".xls"))
{
info.FileName = info.FileName + ".xls";
}
}
MemoryStream ms = info.ExportExeclStream();
byte[] msbyte = ms.GetBuffer();
ms.Dispose();
ms = null;
return(new Response()
{
Contents = stream => { stream.Write(msbyte, 0, msbyte.Length); },
ContentType = "application/msexcel",
StatusCode = HttpStatusCode.OK,
Headers = new Dictionary <string, string> {
{ "Content-Disposition", string.Format("attachment;filename={0}", HttpUtility.UrlPathEncode(info.FileName)) },
{ "Content-Length", msbyte.Length.ToString() }
}
});
};
/// <summary>
/// 导出Excel模版
/// </summary>
/// <param name="type">业务类型</param>
/// <param name="FunctionCode">对应功能模块Code</param>
/// <returns></returns>
Get["/DownLoadTemplate"] = r =>
{
if (AllImports == null)
{
AllImports = MefConfig.ResolveMany <ExcelImport>();
}
string strType = Request.Query["type"];
ExcelImportType type = EnumHelper.StringToEnum <ExcelImportType>(strType);
var handler = AllImports.FirstOrDefault(e => e.Type == type);
if (handler == null)
{
throw new Exception("未找到“" + type.ToString() + "”相应处理模块");
}
string path = ExcelImporMapper.GetTemplatePath(type);
if (File.Exists(path))
{
try
{
string FileName = Path.GetFileName(path);
return(new Response()
{
Contents = stream => { handler.GetExportTemplate(path, stream); },
ContentType = MimeHelper.GetMineType(path),
StatusCode = HttpStatusCode.OK,
Headers = new Dictionary <string, string> {
{ "Content-Disposition", string.Format("attachment;filename={0}", HttpUtility.UrlPathEncode(FileName)) }
}
});
}
catch (Exception ex)
{
throw ex;
}
}
else
{
throw new Exception("未找到“" + type.ToString() + "”对应模版文件");
}
};
/// <summary>
/// 导入Excel模版
/// </summary>
/// <returns></returns>
Post["/ImportTemplate"] = r =>
{
ImportResult result = new ImportResult();
try
{
if (AllImports == null)
{
AllImports = MefConfig.ResolveMany <ExcelImport>();
}
string ywType = Request.Query["type"];
if (string.IsNullOrEmpty(ywType))
{
throw new ArgumentNullException("ywType");
}
//业务类型
ExcelImportType type = EnumHelper.StringToEnum <ExcelImportType>(ywType);
//文件
HttpFile file = Request.Files.First();
var handler = AllImports.FirstOrDefault(e => e.Type == type);
if (handler == null)
{
throw new Exception("未找到“" + type.ToString() + "”相应处理模块");
}
result = handler.ImportTemplate(file.Value, file.Name, null);
if (result.IsSuccess)
{
//是否获取详细数据，决定后台是否返回 result.ExtraInfo
string ReturnDetailData = Request.Query["ReturnDetailData"];
if (string.IsNullOrEmpty(ReturnDetailData) || ReturnDetailData != "1")
{
result.ExtraInfo = null;
}
}
else
{
//设置错误模版http路径
result.Message = Request.Url.SiteBase + result.Message;
}
}
catch (Exception ex)
{
result.IsSuccess = false;
result.Message = ex.Message;
LogHelper.Error("Excel导入异常", ex);
}
return(Response.AsJson(result));
};
}
public static void CreateDoc(ExcelInfo info)
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(info.FileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
CreateStyles(workbookpart);
SharedStringTablePart shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0
? spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First()
: spreadsheetDocument.WorkbookPart.AddNewPart <SharedStringTablePart>();
if (shareStringPart.SharedStringTable == null)
{
shareStringPart.SharedStringTable = new SharedStringTable();
}
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Лист"
};
sheets.Append(sheet);
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = 1,
Text = info.Title,
StyleIndex = 2U
});
MergeCells(new ExcelMergeParameters
{
Worksheet = worksheetPart.Worksheet,
CellFromName = "A1",
CellToName = "F1"
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = 2,
Text = "№",
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "B",
RowIndex = 2,
Text = "Название",
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "C",
RowIndex = 2,
Text = "Цена",
StyleIndex = 0U
});
uint i = 1;
foreach (var service in info.Services)
{
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = i + 2,
Text = i.ToString(),
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "B",
RowIndex = i + 2,
Text = service.ServiceName,
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "C",
RowIndex = i + 2,
Text = service.Price.ToString(),
StyleIndex = 0U
});
i++;
}
workbookpart.Workbook.Save();
}
}
private void CreateDocument(object o)
{
ExcelInfo ei = o as ExcelInfo;
dgv_task.Rows[ei._id - 1].Cells[4].Value = "正在准备";
//获得 table
DataTable dt_split = ei._data;
//读取excel，得到行数
/*生成一个新的word*/
Spire.Doc.Document spdoc = new Spire.Doc.Document();
/*循环把list中的mycu内容添加到word文档*/
for (int rowindex = 0; rowindex < dt_split.Rows.Count; rowindex++)
{
dgv_task.Rows[ei._id - 1].Cells[4].Value = (100 * Convert.ToDecimal(rowindex + 1) / Convert.ToDecimal(dt_split.Rows.Count)).ToString("00.00") + "%";
//更新状态栏文字
//string str_statue = "正在生成第{0}个，共{1}个……";
//UpdateStatue(string.Format(str_statue, rowindex - 1, lastrow - 1));
///载入一个空白文档
spdoc.LoadFromFile(Environment.CurrentDirectory + @"\newdoc.docx");
for (int i = Global.list_myuc.Count - 1; i >= 0; i--)
{
//设置文本
string str_n = string.Empty;
//设置空行
for (int ji = 0; ji < Global.list_myuc[i].mysetter.konghang; ji++)
{
str_n += "\n";
}
//添加一个段落
Spire.Doc.Documents.Paragraph parainsert = spdoc.LastSection.AddParagraph();
//Spire.Doc.Documents.Paragraph parainsert = spdoc.CreateParagraph();
TextRange tx = parainsert.AppendText(GetContent(Global.list_myuc[i].mysetter.listcolumn, rowindex, dt_split) + str_n);
//字体名称
tx.CharacterFormat.FontName = Global.list_myuc[i].mysetter.fontname;
//字体大小
tx.CharacterFormat.FontSize = Global.list_myuc[i].mysetter.fontsize;
//设置行距
switch (Global.list_myuc[i].mysetter.hangjustyle)
{
case "单倍行距":
parainsert.Format.LineSpacingRule = Spire.Doc.LineSpacingRule.AtLeast;
break;
case "1.5倍行距":
parainsert.Format.LineSpacingRule = Spire.Doc.LineSpacingRule.Exactly;
break;
case "2倍行距":
parainsert.Format.LineSpacingRule = Spire.Doc.LineSpacingRule.Multiple;
break;
default:
parainsert.Format.LineSpacingRule = Spire.Doc.LineSpacingRule.Exactly;
parainsert.Format.LineSpacing = Global.list_myuc[i].mysetter.hangjuvalue;
break;
}
//首行缩进
parainsert.Format.SetFirstLineIndent(Global.list_myuc[i].mysetter.suojin); //首行缩进
//粗体
tx.CharacterFormat.Bold = Global.list_myuc[i].mysetter.bold == 1 ? true : false;
switch (Global.list_myuc[i].mysetter.juzhong)
{
case "左对齐":
parainsert.Format.HorizontalAlignment = Spire.Doc.Documents.HorizontalAlignment.Left;
break;
case "居中":
parainsert.Format.HorizontalAlignment = Spire.Doc.Documents.HorizontalAlignment.Center;
break;
case "右对齐":
parainsert.Format.HorizontalAlignment = Spire.Doc.Documents.HorizontalAlignment.Right;
break;
}
}
SectionCollection sections = spdoc.Sections;
//创建一个HeaderFooter类实例，添加页脚
for (int i = 0; i < sections.Count; i++)
{
sections[i].PageSetup.Margins.Top = 72f;
sections[i].PageSetup.Margins.Left = 90f;
sections[i].PageSetup.Margins.Bottom = 72f;
sections[i].PageSetup.Margins.Right = 90f;
Spire.Doc.HeaderFooter footer = sections[i].HeadersFooters.Footer;
Spire.Doc.Documents.Paragraph footerPara = footer.AddParagraph();
//添加字段类型为页码，添加当前页、分隔线以及总页数
footerPara.AppendField("页码", FieldType.FieldPage);
footerPara.AppendText(" / ");
footerPara.AppendField("总页数", FieldType.FieldNumPages);
footerPara.Format.HorizontalAlignment = Spire.Doc.Documents.HorizontalAlignment.Center;
}
/*保存文档*/
//组成文件名
List <string> strfilename = new List <string>();
for (int i = 0; i < Global.listfilename.Count; i++)
{
string filename_element = Regex.Replace(dt_split.Rows[rowindex][Global.listfilename[i] + 1].ToString(), @"[\s/\:*?''<>|]", "");
strfilename.Add(filename_element);
}
spdoc.BuiltinDocumentProperties.Author = "潜挖智库";
spdoc.SaveToFile(tbcunfang.Text + @"\" + string.Join("-", strfilename) + @".docx");
spdoc.Close();
}
}
public static void ExportExcel(List <SheetInfo> sheets, ExcelInfo excelinfo, ref string error)
{
HSSFWorkbook workbook = new HSSFWorkbook();
if (sheets.Count <= 0)
{
return;
}
foreach (var item in sheets)
{
var table = item.Data;
var lstCloumInfo = item.CloumnInfos;
if (table.Rows.Count > 65536)
{
error = "数据表超出excel容量";
return;
}
//创建sheet
ISheet sheet = workbook.CreateSheet(item.SheetName);
//开始填充数据
IRow firstRow = sheet.CreateRow(0);
//单元格样式
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
//自提
IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
font.Boldweight = (short)FontBoldWeight.Bold;
font.Color = NPOI.HSSF.Util.HSSFColor.Black.Index;
cellStyle.SetFont(font);
//根据传入各列内容样式初始化表格
for (int i = 0; i < lstCloumInfo.Count; i++)
{
var column = lstCloumInfo[i];
ICell cell = firstRow.CreateCell(i, CellType.String);
cell.SetCellValue(column.Header);
cell.CellStyle = cellStyle;
sheet.SetColumnWidth(i, column.Width * 256);
if (column.AutoFilter)
{
sheet.SetAutoFilter(CellRangeAddress.ValueOf(((char)(65 + i)).ToString() + "1"));
}
}
ICellStyle contentCellStyle = workbook.CreateCellStyle(); // 内容列样式
contentCellStyle.VerticalAlignment = VerticalAlignment.Center;
contentCellStyle.BorderTop = BorderStyle.Thin;
contentCellStyle.BorderRight = BorderStyle.Thin;
contentCellStyle.BorderBottom = BorderStyle.Thin;
contentCellStyle.BorderLeft = BorderStyle.Thin;
ICellStyle numberCellStyle = workbook.CreateCellStyle(); // 数值列样式
numberCellStyle.VerticalAlignment = VerticalAlignment.Center;
numberCellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.000");
numberCellStyle.Alignment = HorizontalAlignment.Right;
numberCellStyle.BorderTop = BorderStyle.Thin;
numberCellStyle.BorderRight = BorderStyle.Thin;
numberCellStyle.BorderBottom = BorderStyle.Thin;
numberCellStyle.BorderLeft = BorderStyle.Thin;
int dataIndex = 1;
foreach (DataRow row in table.Rows)
{
IRow dataRow = sheet.CreateRow(dataIndex);
dataRow.HeightInPoints = 20;
for (int i = 0; i < lstCloumInfo.Count; i++)
{
ICell cell = dataRow.CreateCell(i);
switch (lstCloumInfo[i].CellDataType)
{
case CellDataType.contentCellStyle:
cell.SetCellValue(row[i].ToString());
cell.CellStyle = contentCellStyle;
break;
case CellDataType.numberCellStyle:
double str = (double)decimal.Parse(row[i].ToString(), 0);
cell.SetCellValue(str);
cell.CellStyle = numberCellStyle;
break;
//case CellDataType.combinecontentCellStyle:
// cell.SetCellValue(row[i].ToString() + "-" + row[i].ToString());
// cell.CellStyle = contentCellStyle;
// break;
default: break;
}
}
dataIndex++;
}
sheet.ForceFormulaRecalculation = true;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
byte[] data = ms.ToArray();
HttpResponse response = System.Web.HttpContext.Current.Response;
response.Clear();
response.Charset = "UTF-8";
response.ContentType = "application/vnd-excel";
string filename = HttpUtility.UrlEncode("机会宝" + excelinfo.FileName + "(" + DateTime.Now.ToString("yyyy-MM-dd") + ").xls", System.Text.Encoding.UTF8);
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + filename));
System.Web.HttpContext.Current.Response.BinaryWrite(data);
workbook = null;
}
}
/// <summary>
/// Method to execute seach
///
/// </summary>
/// <param name="stringToSearch">string to search in xlsx files</param>
/// <param name="pathToSearch">path to find xlsx files</param>
public void FindTextInExcel(string stringToSearch, string pathToSearch)
{
// initialize before seaching
infoList.Clear();
// check folder existance first
if (Directory.Exists(pathToSearch) == false)
{
string message = string.Format("Path {0} doesn't exist", pathToSearch);
MessageBox.Show(message);
return;
}
// then enumerate excel(.xlsx) files and search
string[] files = Directory.GetFiles(pathToSearch, "*.xlsx", SearchOption.AllDirectories);
foreach (var file in files)
{
// IO exception occurs when file is already opened by others
Package spreadSheetPackage = null;
try
{
// open file
spreadSheetPackage = Package.Open(file, FileMode.Open, FileAccess.Read, FileShare.Read);
}
catch (Exception e)
{
ExcelInfo err = new ExcelInfo();
err.error = e.Message;
err.fileName = Path.GetFileName(file);
err.path = Path.GetDirectoryName(file);
err.content = e.Message;
infoList.Add(err);
continue;
}
using (SpreadsheetDocument document = SpreadsheetDocument.Open(spreadSheetPackage))
{
var wbPart = document.WorkbookPart;
var stringTable = wbPart.GetPartsOfType <SharedStringTablePart>().FirstOrDefault();
// loop sheets in workbook
// this pyramid code shall be refactored, not nice.
foreach (var sheet in wbPart.Workbook.Descendants <Sheet>())
{
var wsheetPart = wbPart.GetPartById(sheet.Id) as WorksheetPart;
if (wsheetPart == null)
{
Console.WriteLine("WorksheetPart Not Found !!");
return;
}
var ws = wsheetPart.Worksheet;
foreach (var row in ws.Descendants <Row>())
{
var list = new List <string>();
UInt32Value colnum = 0;
foreach (Cell cell in row)
{
colnum++; // count up column number
string value = cell.InnerText;
if (cell.DataType != null)
{
switch (cell.DataType.Value)
{
case CellValues.Boolean:
case CellValues.Date:
case CellValues.Error:
case CellValues.InlineString:
case CellValues.Number:
case CellValues.String:
value = cell.InnerText;
break;
case CellValues.SharedString:
if (stringTable != null)
{
value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;
}
break;
default:
break;
}
}
// check if value contains keyword to seach
if (!value.Contains(stringToSearch))
{
continue;
}
// go on if keyword found
list.Add(value);
// construct data class and store int list
ExcelInfo data = new ExcelInfo();
data.content = value;
data.row = row.RowIndex;
data.column = colnum;
data.sheetName = sheet.Name;
data.fileName = Path.GetFileName(file);
data.content = value;
data.path = Path.GetDirectoryName(file);
infoList.Add(data);
}
}
}
}
}
}
public static void CreateDoc(ExcelInfo info)
{
using (SpreadsheetDocument spreadsheetDocument =
SpreadsheetDocument.Create(info.FileName, SpreadsheetDocumentType.Workbook))
{
// Создаем книгу (в ней хранятся листы)
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
CreateStyles(workbookpart);
// Получаем/создаем хранилище текстов для книги
SharedStringTablePart shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0
? spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First()
: spreadsheetDocument.WorkbookPart.AddNewPart <SharedStringTablePart>();
// Создаем SharedStringTable, если его нет
if (shareStringPart.SharedStringTable == null)
{
shareStringPart.SharedStringTable = new SharedStringTable();
}
// Создаем лист в книгу
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
// Добавляем лист в книгу
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Лист"
};
sheets.Append(sheet);
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = 1,
Text = info.Title,
StyleIndex = 2U
});
MergeCells(new ExcelMergeParameters
{
Worksheet = worksheetPart.Worksheet,
CellFromName = "A1",
CellToName = "D1"
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = 2,
Text = "Продукт",
StyleIndex = 2U
});
MergeCells(new ExcelMergeParameters
{
Worksheet = worksheetPart.Worksheet,
CellFromName = "A2",
CellToName = "B2"
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "C",
RowIndex = 2,
Text = "Вес/количество",
StyleIndex = 2U
});
MergeCells(new ExcelMergeParameters
{
Worksheet = worksheetPart.Worksheet,
CellFromName = "C2",
CellToName = "D2"
});
uint rowIndex = 3;
for (int i = 0; i < info.Request.Count; i++)
{
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = rowIndex,
Text = info.Request[i].ProductName,
StyleIndex = 1U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "B",
RowIndex = rowIndex,
Text = string.Empty,
StyleIndex = 1U
});
MergeCells(new ExcelMergeParameters
{
Worksheet = worksheetPart.Worksheet,
CellFromName = "A" + rowIndex,
CellToName = "B" + rowIndex
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "C",
RowIndex = rowIndex,
Text = info.Request[i].Weight.ToString(),
StyleIndex = 1U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "D",
RowIndex = rowIndex,
Text = string.Empty,
StyleIndex = 1U
});
MergeCells(new ExcelMergeParameters
{
Worksheet = worksheetPart.Worksheet,
CellFromName = "C" + rowIndex,
CellToName = "D" + rowIndex
});
rowIndex++;
}
workbookpart.Workbook.Save();
}
}
public static void CreateDoc(ExcelInfo info)
{
using (SpreadsheetDocument spreadsheetDocument =
SpreadsheetDocument.Create(info.FileName, SpreadsheetDocumentType.Workbook))
{
// Создаем книгу (в ней хранятся листы)
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
CreateStyles(workbookpart);
// Получаем/создаем хранилище текстов для книги
SharedStringTablePart shareStringPart =
spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0
?
spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First() : spreadsheetDocument.WorkbookPart.AddNewPart <SharedStringTablePart>();
// Создаем SharedStringTable, если его нет
if (shareStringPart.SharedStringTable == null)
{
shareStringPart.SharedStringTable = new SharedStringTable();
}
// Создаем лист в книгу
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
// Добавляем лист в книгу
Sheets sheets =
spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Лист"
};
sheets.Append(sheet);
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = 1,
Text = info.Title,
StyleIndex = 2U
});
uint rowIndex = 2;
if (info.Orders != null)
{
MergeCells(new ExcelMergeParameters
{
Worksheet = worksheetPart.Worksheet,
CellFromName = "A1",
CellToName = "F1"
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = rowIndex,
Text = "Название букета",
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "B",
RowIndex = rowIndex,
Text = "Дата оформления",
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "C",
RowIndex = rowIndex,
Text = "Статус заказа",
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "D",
RowIndex = rowIndex,
Text = "Способ доставки",
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "E",
RowIndex = rowIndex,
Text = "Количество",
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "F",
RowIndex = rowIndex,
Text = "Сумма",
StyleIndex = 0U
});
rowIndex++;
foreach (var pc in info.Orders)
{
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = rowIndex,
Text = pc.BouquetName,
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "B",
RowIndex = rowIndex,
Text = pc.DateCreate.ToString(),
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "C",
RowIndex = rowIndex,
Text = pc.Status.ToString(),
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "D",
RowIndex = rowIndex,
Text = pc.Delivery.ToString(),
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "E",
RowIndex = rowIndex,
Text = pc.Count.ToString(),
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "F",
RowIndex = rowIndex,
Text = pc.Sum.ToString(),
StyleIndex = 0U
});
rowIndex++;
}
}
if (info.RequestFlowers != null)
{
MergeCells(new ExcelMergeParameters
{
Worksheet = worksheetPart.Worksheet,
CellFromName = "A1",
CellToName = "C1"
});
foreach (var pc in info.RequestFlowers)
{
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = rowIndex,
Text = pc.RequestName,
StyleIndex = 0U
});
rowIndex++;
foreach (var flower in pc.Flowers)
{
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "B",
RowIndex = rowIndex,
Text = flower.Item1,
StyleIndex = 1U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "C",
RowIndex = rowIndex,
Text = flower.Item2.ToString(),
StyleIndex = 1U
});
rowIndex++;
}
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "B",
RowIndex = rowIndex,
Text = "Итого:",
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "C",
RowIndex = rowIndex,
Text = pc.TotalCount.ToString(),
StyleIndex = 0U
});
rowIndex++;
}
}
workbookpart.Workbook.Save();
}
}
public static void CreateDoc(ExcelInfo info)
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(info.FileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
CreateStyles(workbookpart);
SharedStringTablePart shareStringPart =
spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0
?
spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First()
:
spreadsheetDocument.WorkbookPart.AddNewPart <SharedStringTablePart>();
if (shareStringPart.SharedStringTable == null)
{
shareStringPart.SharedStringTable = new SharedStringTable();
}
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Лист"
};
sheets.Append(sheet);
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = 1,
Text = info.Title,
StyleIndex = 2U
});
MergeCells(new ExcelMergeParameters
{
Worksheet = worksheetPart.Worksheet,
CellFromName = "A1",
CellToName = "C1"
});
uint rowIndex = 2;
foreach (var date in info.Orders)
{
int dateSum = 0;
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = rowIndex,
Text = date.Key.ToString(),
StyleIndex = 0U
});
rowIndex++;
foreach (var order in date)
{
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "B",
RowIndex = rowIndex,
Text = order.KomlectName,
StyleIndex = 1U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "C",
RowIndex = rowIndex,
Text = order.Sum.ToString(),
StyleIndex = 1U
});
dateSum += order.Sum;
rowIndex++;
}
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = rowIndex,
Text = "Итого",
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "C",
RowIndex = rowIndex,
Text = dateSum.ToString(),
StyleIndex = 0U
});
rowIndex++;
}
workbookpart.Workbook.Save();
}
}
public static void CreateDoc(ExcelInfo info)
{
using (SpreadsheetDocument spreadsheetDocument =
SpreadsheetDocument.Create(info.FileName, SpreadsheetDocumentType.Workbook))
{
// Создаем книгу (в ней хранятся листы)
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
CreateStyles(workbookpart);
// Получаем/создаем хранилище текстов для книги
SharedStringTablePart shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0
? spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First()
: spreadsheetDocument.WorkbookPart.AddNewPart <SharedStringTablePart>();
// Создаем SharedStringTable, если его нет
if (shareStringPart.SharedStringTable == null)
{
shareStringPart.SharedStringTable = new SharedStringTable();
}
// Создаем лист в книгу
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
// Добавляем лист в книгу
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Лист"
};
sheets.Append(sheet);
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = 1,
Text = info.Title,
StyleIndex = 2U
});
MergeCells(new ExcelMergeParameters
{
Worksheet = worksheetPart.Worksheet,
CellFromName = "A1",
CellToName = "D1"
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = 2,
Text = "C " + info.DateFrom.ToShortDateString() + " по " + info.DateTo.ToShortDateString(),
StyleIndex = 2U
});
MergeCells(new ExcelMergeParameters
{
Worksheet = worksheetPart.Worksheet,
CellFromName = "A2",
CellToName = "D2"
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = 3,
Text = "Дата заказа",
StyleIndex = 2U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "B",
RowIndex = 3,
Text = "Кондитерское изделие",
StyleIndex = 2U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "C",
RowIndex = 3,
Text = "Сумма",
StyleIndex = 2U
});
uint rowIndex = 4;
foreach (var group in info.Orders)
{
decimal sum = 0;
// вставка первой строки с датой
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = rowIndex,
Text = group.Key.ToShortDateString(),
StyleIndex = 1U
});
// форматируем соседние ячейки
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "B",
RowIndex = rowIndex,
Text = string.Empty,
StyleIndex = 1U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "C",
RowIndex = rowIndex,
Text = string.Empty,
StyleIndex = 1U
});
rowIndex++;
foreach (var order in group)
{
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = rowIndex,
Text = string.Empty,
StyleIndex = 1U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "B",
RowIndex = rowIndex,
Text = order.ProductName,
StyleIndex = 1U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "C",
RowIndex = rowIndex,
Text = order.Sum.ToString(),
StyleIndex = 1U
});
sum += order.Sum;
rowIndex++;
}
// вставка итоговой строки
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = rowIndex,
Text = "Итого:",
StyleIndex = 1U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "B",
RowIndex = rowIndex,
Text = string.Empty,
StyleIndex = 1U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "С",
RowIndex = rowIndex,
Text = sum.ToString(),
StyleIndex = 1U
});
rowIndex++;
}
workbookpart.Workbook.Save();
}
}
public static void CreateDoc(ExcelInfo info)
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(info.FileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
CreateStyles(workbookpart);
SharedStringTablePart shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0
? spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First()
: spreadsheetDocument.WorkbookPart.AddNewPart <SharedStringTablePart>();
if (shareStringPart.SharedStringTable == null)
{
shareStringPart.SharedStringTable = new SharedStringTable();
}
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Лист"
};
sheets.Append(sheet);
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = 1,
Text = info.Title,
StyleIndex = 2U
});
MergeCells(new ExcelMergeParameters
{
Worksheet = worksheetPart.Worksheet,
CellFromName = "A1",
CellToName = "F1"
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = 2,
Text = "№",
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "B",
RowIndex = 2,
Text = "Тип мебели",
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "C",
RowIndex = 2,
Text = "Название модели",
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "D",
RowIndex = 2,
Text = "Габариты",
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "E",
RowIndex = 2,
Text = "Цена",
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "F",
RowIndex = 2,
Text = "Кол-во",
StyleIndex = 0U
});
uint i = 1;
int sum = 0;
foreach (var model in info.Models)
{
sum += model.Key * model.Value.Price;
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = i + 2,
Text = i.ToString(),
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "B",
RowIndex = i + 2,
Text = model.Value.TypeName,
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "C",
RowIndex = i + 2,
Text = model.Value.ModelName,
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "D",
RowIndex = i + 2,
Text = model.Value.Dimensions,
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "E",
RowIndex = i + 2,
Text = model.Value.Price.ToString(),
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "F",
RowIndex = i + 2,
Text = model.Key.ToString(),
StyleIndex = 0U
});
i++;
}
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "D",
RowIndex = i + 2,
Text = "Итого:",
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "E",
RowIndex = i + 2,
Text = sum.ToString(),
StyleIndex = 0U
});
workbookpart.Workbook.Save();
}
}
public static void CreateDoc(ExcelInfo info)
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(info.FileName, SpreadsheetDocumentType.Workbook))
{
// Создаем книгу (в ней хранятся листы)
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
CreateStyles(workbookpart);
// Получаем/создаем хранилище текстов для книги
SharedStringTablePart shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0
? spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First()
: spreadsheetDocument.WorkbookPart.AddNewPart <SharedStringTablePart>();
// Создаем SharedStringTable, если его нет
if (shareStringPart.SharedStringTable == null)
{
shareStringPart.SharedStringTable = new SharedStringTable();
}
// Создаем лист в книгу
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
// Добавляем лист в книгу
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Лист"
};
sheets.Append(sheet);
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = 1,
Text = info.Title,
StyleIndex = 2U
});
MergeCells(new ExcelMergeParameters
{
Worksheet = worksheetPart.Worksheet,
CellFromName = "A1",
CellToName = "C1"
});
uint rowIndex = 2;
foreach (var report in info.Disciplines)
{
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = rowIndex,
Text = report.StudentName,
StyleIndex = 0U
});
rowIndex++;
foreach (var discipline in report.Disciplines)
{
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "B",
RowIndex = rowIndex,
Text = discipline.Name,
StyleIndex = 1U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "C",
RowIndex = rowIndex,
Text = discipline.HoursCount.ToString(),
StyleIndex = 1U
});
rowIndex++;
}
}
workbookpart.Workbook.Save();
}
}
public static void CreateDoc(ExcelInfo info)
{
using (SpreadsheetDocument spreadsheetDocument =
SpreadsheetDocument.Create(info.FileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
CreateStyles(workbookpart);
SharedStringTablePart shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0 ?
spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First() :
spreadsheetDocument.WorkbookPart.AddNewPart <SharedStringTablePart>();
if (shareStringPart.SharedStringTable == null)
{
shareStringPart.SharedStringTable = new SharedStringTable();
}
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets =
spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Лист"
};
sheets.Append(sheet);
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = 1,
Text = info.Title,
StyleIndex = 2U
});
MergeCells(new ExcelMergeParameters
{
Worksheet = worksheetPart.Worksheet,
CellFromName = "A1",
CellToName = "I1"
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = 2,
Text = "ФИО клиента",
StyleIndex = 0U
});
MergeCells(new ExcelMergeParameters
{
Worksheet = worksheetPart.Worksheet,
CellFromName = "A2",
CellToName = "C2"
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "D",
RowIndex = 2,
Text = "Дата заказа",
StyleIndex = 0U
});
MergeCells(new ExcelMergeParameters
{
Worksheet = worksheetPart.Worksheet,
CellFromName = "D2",
CellToName = "E2"
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "F",
RowIndex = 2,
Text = "Сумма к оплате",
StyleIndex = 0U
});
MergeCells(new ExcelMergeParameters
{
Worksheet = worksheetPart.Worksheet,
CellFromName = "F2",
CellToName = "G2"
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "H",
RowIndex = 2,
Text = "Статус",
StyleIndex = 0U
});
MergeCells(new ExcelMergeParameters
{
Worksheet = worksheetPart.Worksheet,
CellFromName = "H2",
CellToName = "I2"
});
uint rowIndex = 1;
foreach (var sr in info.Orders)
{
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = rowIndex + 2,
Text = sr.ClientFIO,
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "D",
RowIndex = rowIndex + 2,
Text = sr.DateCreate.ToString(),
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "F",
RowIndex = rowIndex + 2,
Text = (sr.Price - sr.Sum).ToString(),
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "H",
RowIndex = rowIndex + 2,
Text = sr.Status.ToString(),
StyleIndex = 0U
});
rowIndex++;
}
workbookpart.Workbook.Save();
}
}
public static void CreateDoc(ExcelInfo info)
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(info.FileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
CreateStyles(workbookpart);
SharedStringTablePart shareStringPart =
spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0
?
spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First()
:
spreadsheetDocument.WorkbookPart.AddNewPart <SharedStringTablePart>();
if (shareStringPart.SharedStringTable == null)
{
shareStringPart.SharedStringTable = new SharedStringTable();
}
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Лист"
};
sheets.Append(sheet);
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = 1,
Text = info.Title,
StyleIndex = 2U
});
MergeCells(new ExcelMergeParameters
{
Worksheet = worksheetPart.Worksheet,
CellFromName = "A1",
CellToName = "C1"
});
uint rowIndex = 2;
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = rowIndex,
Text = "Название заявки",
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "B",
RowIndex = rowIndex,
Text = "Дата создания",
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "D",
RowIndex = rowIndex,
Text = "Название детали",
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "E",
RowIndex = rowIndex,
Text = "Количество",
StyleIndex = 0U
});
string name = "";
int i = 0;
rowIndex = 3;
foreach (var req in info.SpareRequests)
{
if (name != req.RequestName)
{
if (i != 0)
{
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "C",
RowIndex = rowIndex,
Text = "Итого деталей:",
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "E",
RowIndex = rowIndex,
Text = i.ToString(),
StyleIndex = 0U
});
i = 0;
rowIndex++;
}
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = rowIndex,
Text = req.RequestName,
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "B",
RowIndex = rowIndex,
Text = req.DateCreate.ToString(),
StyleIndex = 0U
});
rowIndex++;
}
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "D",
RowIndex = rowIndex,
Text = req.SpareName,
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "E",
RowIndex = rowIndex,
Text = req.Count.ToString(),
StyleIndex = 0U
});
i += req.Count;
name = req.RequestName;
rowIndex++;
}
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "C",
RowIndex = rowIndex,
Text = "Итого деталей:",
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "E",
RowIndex = rowIndex,
Text = i.ToString(),
StyleIndex = 0U
});
workbookpart.Workbook.Save();
}
}