public void CreateExcelDoc(string fileName)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Export"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Constructing header
Row row = new Row();
row.Append(
ConstructCell("Заявка", CellValues.String),
ConstructCell("Статус", CellValues.String),
ConstructCell("Дата Создания", CellValues.String),
ConstructCell("Создатель", CellValues.String),
ConstructCell("Улица", CellValues.String),
ConstructCell("Дом", CellValues.String),
ConstructCell("Корпус", CellValues.String),
ConstructCell("Квартира", CellValues.String),
ConstructCell("Телефоны", CellValues.String),
ConstructCell("Услуга", CellValues.String),
ConstructCell("Причина", CellValues.String),
ConstructCell("Примечание", CellValues.String),
ConstructCell("Дата", CellValues.String),
ConstructCell("Время", CellValues.String),
ConstructCell("Мастер", CellValues.String),
ConstructCell("Исполнитель", CellValues.String),
ConstructCell("Выполнение С", CellValues.String),
ConstructCell("Выполнение По", CellValues.String),
ConstructCell("Потрачено Времени", CellValues.String),
ConstructCell("Гарантийная", CellValues.String),
ConstructCell("Оценка", CellValues.String),
ConstructCell("Комментарий К Оценке", CellValues.String),
ConstructCell("Повторная", CellValues.String),
ConstructCell("Аварийная", CellValues.String)
);
// Insert the header row to the Sheet Data
sheetData.AppendChild(row);
// Inserting each employee
foreach (var request in RequestList)
{
{
row = new Row();
row.Append(
ConstructCell(request.Id.ToString(), CellValues.Number),
ConstructCell(request.Status, CellValues.String),
ConstructCell(request.CreateTime.ToString("dd.MM.yyyy HH:mm"), CellValues.String),
ConstructCell(request.CreateUser.ShortName, CellValues.String),
ConstructCell(request.StreetName, CellValues.String),
ConstructCell(request.Building, CellValues.String),
ConstructCell(request.Corpus, CellValues.String),
ConstructCell(request.Flat, CellValues.String),
ConstructCell(request.ContactPhones, CellValues.String),
ConstructCell(request.ParentService, CellValues.String),
ConstructCell(request.Service, CellValues.String),
ConstructCell(request.Description, CellValues.String),
ConstructCell(request.ExecuteTime?.Date.ToString("dd.MM.yyyy") ?? "", CellValues.String),
ConstructCell(request.ExecutePeriod, CellValues.String),
ConstructCell(request.Master?.ShortName, CellValues.String),
ConstructCell(request.Executer?.ShortName, CellValues.String),
ConstructCell(request.FromTime?.ToString("HH:mm:ss") ?? "", CellValues.String),
ConstructCell(request.ToTime?.ToString("HH:mm:ss") ?? "", CellValues.String),
ConstructCell(request.SpendTime, CellValues.String),
ConstructCell(request.GarantyTest, CellValues.String),
ConstructCell(request.Rating, CellValues.String),
ConstructCell(request.RatingDescription, CellValues.String),
ConstructCell(request.IsRetry ? "Да" : "", CellValues.String),
ConstructCell(request.ImmediateText, CellValues.String));
sheetData.AppendChild(row);
}
worksheetPart.Worksheet.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 = "Е1"
});
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 = "С",
RowIndex = 2,
Text = "Цена",
StyleIndex = 0U
});
uint i = 1;
foreach (var route in info.Routes)
{
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 = route.RouteName,
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "C",
RowIndex = i + 2,
Text = route.Cost.ToString(),
StyleIndex = 0U
});
i++;
}
workbookpart.Workbook.Save();
}
}
public string ImportData(string Identifier, string type, string name)
{
try
{
string File = "";
//name=name.Split()
File = HttpRuntime.AppDomainAppPath + ConfigurationManager.AppSettings["FileUploadSection"].Replace('/', '\\') + name;
DataTable dt = new DataTable();
using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(File, false))
{
WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
IEnumerable <Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>();
string relationshipId = sheets.First().Id.Value;
dt.Columns.Clear();
dt.Rows.Clear();
WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
Worksheet workSheet = worksheetPart.Worksheet;
SheetData sheetData = workSheet.GetFirstChild <SheetData>();
IEnumerable <Row> rows = sheetData.Descendants <Row>();
foreach (Cell cell in rows.ElementAt(0))
{
dt.Columns.Add(Utilities.GetCellValue(spreadSheetDocument, cell));
}
foreach (Row row in rows) //this will also include your header row...
{
DataRow tempRow = dt.NewRow();
int columnIndex = 0;
foreach (Cell cell in row.Descendants <Cell>())
{
// Gets the column index of the cell with data
int cellColumnIndex = (int)Utilities.GetColumnIndexFromName(Utilities.GetColumnName(cell.CellReference));
cellColumnIndex--; //zero based index
if (columnIndex < cellColumnIndex)
{
do
{
tempRow[columnIndex] = ""; //Insert blank data here;
columnIndex++;
}while (columnIndex < cellColumnIndex);
}
tempRow[columnIndex] = Utilities.GetCellValue(spreadSheetDocument, cell);
columnIndex++;
}
dt.Rows.Add(tempRow);
}
dt.Rows.RemoveAt(0); // Remove oth element.
foreach (DataRow row in dt.Rows)
{
if (type == "post_requirement")
{
var data = new PostRequirementImportViewModel
{
client_name = row["Client Name"].ToString(),
contact_details = row["contact details"].ToString(),
email_id = row["email id"].ToString(),
engagement_model = row["Engagement Model"].ToString(),
location = row["Location"].ToString(),
requirement_description = row["Requirement description"].ToString(),
requirement_title = row["Requirement Title"].ToString(),
};
if (data != null)
{
UploadPostRequirement(data);
}
}
else
{
var data = new ImportVendorCandidateViewModel
{
vendor_name = row["Vendor name"].ToString(),
vendor_user_name = row["Vendor user name"].ToString(),
vendor_password = row["Vendor Password"].ToString(),
candidate_firstname = row["Candidate name"].ToString(),
candidate_one_liner_headline = row["one liner headline"].ToString(),
candidate_technology = row["Technology"].ToString(),
country = row["Country"].ToString(),
state = row["State"].ToString(),
city = row["City"].ToString(),
availability = row["Availability (within days)"].ToString(),
experience_level = row["Experience level"].ToString(),
};
UploadVendorCandidate(data);
}
//AddDistributor(Identifier, null, row);
}
}
return("Success");
}
catch (DbEntityValidationException e)
{
foreach (var eve in e.EntityValidationErrors)
{
Console.WriteLine("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:",
eve.Entry.Entity.GetType().Name, eve.Entry.State);
foreach (var ve in eve.ValidationErrors)
{
Console.WriteLine("- Property: \"{0}\", Error: \"{1}\"",
ve.PropertyName, ve.ErrorMessage);
}
}
throw;
}
catch (Exception ex)
{
throw ex;
}
}
static void Main(string[] args)
{
IEnumerable <Customer> reportData = Report.GetCustomers();
using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Create("CustomersReport_Styled.xlsx", SpreadsheetDocumentType.Workbook))
{
WorkbookPart wBookPart = spreadsheetDoc.AddWorkbookPart();
wBookPart.Workbook = new Workbook();
//Creamos nuevo objeto de hojas
spreadsheetDoc.WorkbookPart.Workbook.Sheets = new Sheets();
//para facilitar la manipulación del objeto sheets
Sheets sheets = spreadsheetDoc.WorkbookPart.Workbook.GetFirstChild <Sheets>();
//para facilitar el manejo del worksheetpart
WorksheetPart wSheetPart = wBookPart.AddNewPart <WorksheetPart>();
//crea parte para estilos
WorkbookStylesPart stylesPart = spreadsheetDoc.WorkbookPart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = StylesheetGenerator.GenerateStyleSheet(); //método para hoja de estilos
stylesPart.Stylesheet.Save();
Columns columns = new Columns();
columns.Append(new Column {
Width = 30, Min = 1, Max = 8
});
Sheet sheet = new Sheet()
{
Id = spreadsheetDoc.WorkbookPart.GetIdOfPart(wSheetPart),
SheetId = 1,
Name = "Hoja_Con_Estilos",
};
sheets.Append(sheet);
SheetData sheetData = new SheetData();
wSheetPart.Worksheet = new Worksheet(columns, sheetData);
Row headerRow = new Row();
headerRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = "Name"
}
});
headerRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = "Register Date"
}
});
headerRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = "Last Buy"
}
});
headerRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = "Product"
}
});
headerRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = "Cost"
}
});
headerRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = "Quantity"
}
});
headerRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = "Total"
}
});
sheetData.AppendChild(headerRow);
foreach (Customer data in reportData)
{
Row contentRow = new Row();
contentRow.Append(new Cell {
StyleIndex = 3, DataType = CellValues.String, CellValue = new CellValue {
Text = data.Name
}
});
contentRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = data.RegisterDate
}
});
contentRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = data.LastBuy
}
});
contentRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = data.Item
}
});
contentRow.Append(new Cell {
DataType = CellValues.Number, CellValue = new CellValue {
Text = data.Quantity.ToString()
}
});
contentRow.Append(new Cell {
DataType = CellValues.Number, CellValue = new CellValue {
Text = data.ItemCost.ToString()
}
});
contentRow.Append(new Cell {
DataType = CellValues.Number, CellValue = new CellValue {
Text = string.Format("{0}", data.Quantity * data.ItemCost)
}
});
sheetData.AppendChild(contentRow);
}
}
}
public void loadData()
{
if (thisYearFileName == null || lastYearFileName == null ||
thisYearFileNameQ == null || lastYearFileNameQ == null)
{
throw new MyException("文件未选择");
}
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(thisYearFileName, false))
{
WorkbookPart workbook = doc.WorkbookPart;
WorkbookPart wbPart = doc.WorkbookPart;
List <Sheet> sheets = wbPart.Workbook.Descendants <Sheet>().ToList();
WorksheetPart worksheetPart = (WorksheetPart)doc.WorkbookPart.GetPartById(sheets[0].Id);
Worksheet sheet = worksheetPart.Worksheet;
List <Row> rows = sheet.Descendants <Row>().ToList();
if (rows.Count < 2)
{
throw new MyException("表格数据不对");
}
List <Cell> firstRow = rows.FirstOrDefault().Descendants <Cell>().ToList();
string aTitle = LYJUtil.GetValue(firstRow[0], workbook.SharedStringTablePart);
if (aTitle.Trim() != "交易代码")
{
throw new MyException("表格列不对");
}
string fTitle = LYJUtil.GetValue(firstRow[4], workbook.SharedStringTablePart);
if (fTitle.Trim() != "计划发行规模(亿)")
{
throw new MyException("表格列不对");
}
string hTitle = LYJUtil.GetValue(firstRow[6], workbook.SharedStringTablePart);
if (hTitle.Trim() != "发行规模(亿)")
{
throw new MyException("表格列不对");
}
thisYearData = new List <CompDebitEntity>();
for (int i = 1; i < rows.Count; i++)
{
var data = CompDebitEntity.getFromCell(rows[i], workbook.SharedStringTablePart, false, "新发行债券今年");
if (data != null)
{
thisYearData.Add(data);
}
else
{
break;
}
}
}
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(lastYearFileName, false))
{
WorkbookPart workbook = doc.WorkbookPart;
WorkbookPart wbPart = doc.WorkbookPart;
List <Sheet> sheets = wbPart.Workbook.Descendants <Sheet>().ToList();
WorksheetPart worksheetPart = (WorksheetPart)doc.WorkbookPart.GetPartById(sheets[0].Id);
Worksheet sheet = worksheetPart.Worksheet;
List <Row> rows = sheet.Descendants <Row>().ToList();
if (rows.Count < 2)
{
throw new MyException("表格数据不对");
}
List <Cell> firstRow = rows.FirstOrDefault().Descendants <Cell>().ToList();
string aTitle = LYJUtil.GetValue(firstRow[0], workbook.SharedStringTablePart);
if (aTitle.Trim() != "交易代码")
{
throw new MyException("表格列不对");
}
string fTitle = LYJUtil.GetValue(firstRow[4], workbook.SharedStringTablePart);
if (fTitle.Trim() != "计划发行规模(亿)")
{
throw new MyException("表格列不对");
}
string hTitle = LYJUtil.GetValue(firstRow[6], workbook.SharedStringTablePart);
if (hTitle.Trim() != "发行规模(亿)")
{
throw new MyException("表格列不对");
}
lastYearData = new List <CompDebitEntity>();
for (int i = 1; i < rows.Count; i++)
{
var data = CompDebitEntity.getFromCell(rows[i], workbook.SharedStringTablePart, false, "新发行债券去年");
if (data != null)
{
lastYearData.Add(data);
}
else
{
break;
}
}
}
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(thisYearFileNameQ, false))
{
WorkbookPart workbook = doc.WorkbookPart;
WorkbookPart wbPart = doc.WorkbookPart;
List <Sheet> sheets = wbPart.Workbook.Descendants <Sheet>().ToList();
WorksheetPart worksheetPart = (WorksheetPart)doc.WorkbookPart.GetPartById(sheets[0].Id);
Worksheet sheet = worksheetPart.Worksheet;
List <Row> rows = sheet.Descendants <Row>().ToList();
if (rows.Count < 2)
{
throw new MyException("表格数据不对");
}
List <Cell> firstRow = rows.FirstOrDefault().Descendants <Cell>().ToList();
string aTitle = LYJUtil.GetValue(firstRow[0], workbook.SharedStringTablePart);
if (aTitle.Trim() != "交易代码")
{
throw new MyException("表格列不对");
}
string fTitle = LYJUtil.GetValue(firstRow[4], workbook.SharedStringTablePart);
if (fTitle.Trim() != "计划发行规模(亿)")
{
throw new MyException("表格列不对");
}
string hTitle = LYJUtil.GetValue(firstRow[6], workbook.SharedStringTablePart);
if (hTitle.Trim() != "发行规模(亿)")
{
throw new MyException("表格列不对");
}
thisYearDataQ = new List <CompDebitEntity>();
for (int i = 1; i < rows.Count; i++)
{
var data = CompDebitEntity.getFromCell(rows[i], workbook.SharedStringTablePart, true, "新发行债券今年企");
if (data != null)
{
thisYearDataQ.Add(data);
}
else
{
break;
}
}
}
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(lastYearFileNameQ, false))
{
WorkbookPart workbook = doc.WorkbookPart;
WorkbookPart wbPart = doc.WorkbookPart;
List <Sheet> sheets = wbPart.Workbook.Descendants <Sheet>().ToList();
WorksheetPart worksheetPart = (WorksheetPart)doc.WorkbookPart.GetPartById(sheets[0].Id);
Worksheet sheet = worksheetPart.Worksheet;
List <Row> rows = sheet.Descendants <Row>().ToList();
if (rows.Count < 2)
{
throw new MyException("表格数据不对");
}
List <Cell> firstRow = rows.FirstOrDefault().Descendants <Cell>().ToList();
string aTitle = LYJUtil.GetValue(firstRow[0], workbook.SharedStringTablePart);
if (aTitle.Trim() != "交易代码")
{
throw new MyException("表格列不对");
}
string fTitle = LYJUtil.GetValue(firstRow[4], workbook.SharedStringTablePart);
if (fTitle.Trim() != "计划发行规模(亿)")
{
throw new MyException("表格列不对");
}
string hTitle = LYJUtil.GetValue(firstRow[6], workbook.SharedStringTablePart);
if (hTitle.Trim() != "发行规模(亿)")
{
throw new MyException("表格列不对");
}
lastYearDataQ = new List <CompDebitEntity>();
for (int i = 1; i < rows.Count; i++)
{
var data = CompDebitEntity.getFromCell(rows[i], workbook.SharedStringTablePart, true, "新发行债券去年企");
if (data != null)
{
lastYearDataQ.Add(data);
}
else
{
break;
}
}
}
}
public static List <SetupExcelModel> GetDataTableFromSpreadsheet(Stream MyExcelStream, bool ReadOnly, BHSingleSetupModel bHSingleSetupModel, HRProfile hRProfile)
{
List <SetupExcelModel> dt = new List <SetupExcelModel>();
using (SpreadsheetDocument sDoc = SpreadsheetDocument.Open(MyExcelStream, ReadOnly)) {
WorkbookPart workbookPart = sDoc.WorkbookPart;
IEnumerable <Sheet> sheets = sDoc.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>();
string relationshipId = sheets.First().Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)sDoc.WorkbookPart.GetPartById(relationshipId);
Worksheet workSheet = worksheetPart.Worksheet;
SheetData sheetData = workSheet.GetFirstChild <SheetData>();
IEnumerable <Row> rows = sheetData.Descendants <Row>();
foreach (Cell cell in rows.ElementAt(0))
{
//dt.Add(GetCellValue(sDoc, cell));
}
SetupExcelModel setupExcelModel;
Debug.WriteLine("rows length = " + rows.Count());
foreach (Row row in rows)
{
setupExcelModel = new SetupExcelModel();
setupExcelModel.Id = (int)row.RowIndex.Value;
for (int i = 0; i < row.Descendants <Cell>().Count(); i++)
{
Debug.WriteLine("i = " + i);
switch (i)
{
case 0: //StaffBranch
setupExcelModel.StaffBranch = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
break;
case 1: //StaffBranchCode
setupExcelModel.StaffBranchCode = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
break;
case 2: //StaffNumber
setupExcelModel.StaffNumber = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
break;
case 3: //StaffName
setupExcelModel.StaffName = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
break;
case 4: //StaffRole--SelectedAppraisalPeriod--SetupAppPeriod--HRProfile--Comments
setupExcelModel.StaffRole = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
setupExcelModel.SelectedAppraisalPeriod = bHSingleSetupModel.SelectedAppraisalPeriod;
setupExcelModel.SetupAppPeriod = bHSingleSetupModel.SetupAppPeriod;
setupExcelModel.HRProfile = hRProfile;
setupExcelModel.Comments = bHSingleSetupModel.Comments;
dt.Add(setupExcelModel);
break;
}
}
}
}
return(dt);
}
public byte[] CaricoLavoroExcel(ReportDS ds)
{
byte[] content;
MemoryStream ms = new MemoryStream();
using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
// Adding style
WorkbookStylesPart stylePart = workbookPart.AddNewPart <WorkbookStylesPart>();
stylePart.Stylesheet = GenerateStylesheet();
stylePart.Stylesheet.Save();
int numeroColonne = 38;
Columns columns = new Columns();
for (int i = 0; i < numeroColonne; i++)
{
Column c = new Column();
UInt32Value u = new UInt32Value((uint)(i + 1));
c.Min = u;
c.Max = u;
c.Width = 15;
columns.Append(c);
}
worksheetPart.Worksheet.AppendChild(columns);
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Carico lavoro"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Constructing header
Row row = new Row();
row.Append(ConstructCell("Azienda", CellValues.String, 2));
row.Append(ConstructCell("Tipo Lancio", CellValues.String, 2));
row.Append(ConstructCell("Codice", CellValues.String, 2));
row.Append(ConstructCell("Codiceclifo", CellValues.String, 2));
row.Append(ConstructCell("Ragione Soc", CellValues.String, 2));
row.Append(ConstructCell("nummovfase", CellValues.String, 2));
row.Append(ConstructCell("pianificato_sn", CellValues.String, 2));
row.Append(ConstructCell("NomeCommessa", CellValues.String, 2));
row.Append(ConstructCell("segnalatore", CellValues.String, 2));
row.Append(ConstructCell("Codtipomovfase", CellValues.String, 2));
row.Append(ConstructCell("destipomovfase", CellValues.String, 2));
row.Append(ConstructCell("Modello_lancio", CellValues.String, 2));
row.Append(ConstructCell("Desmodello_lancio", CellValues.String, 2));
row.Append(ConstructCell("Idmagazz", CellValues.String, 2));
row.Append(ConstructCell("Modello_wip", CellValues.String, 2));
row.Append(ConstructCell("Desmodello_wip", CellValues.String, 2));
row.Append(ConstructCell("Elencofasi", CellValues.String, 2));
row.Append(ConstructCell("datamovfase", CellValues.String, 2));
row.Append(ConstructCell("Datainizio_odl", CellValues.String, 2));
row.Append(ConstructCell("Dataprimoinvio_odl", CellValues.String, 2));
row.Append(ConstructCell("Documenti_invio", CellValues.String, 2));
row.Append(ConstructCell("Datafine_odl_e_multipla", CellValues.String, 2));
row.Append(ConstructCell("Datafine_fasecommessa", CellValues.String, 2));
row.Append(ConstructCell("Conta_multiple", CellValues.String, 2));
row.Append(ConstructCell("Codiceunimi", CellValues.String, 2));
row.Append(ConstructCell("Qta", CellValues.String, 2));
row.Append(ConstructCell("qtadater", CellValues.String, 2));
row.Append(ConstructCell("Priorita", CellValues.String, 2));
row.Append(ConstructCell("Noteparticolarifase", CellValues.String, 2));
row.Append(ConstructCell("Notaparticolareodl", CellValues.String, 2));
row.Append(ConstructCell("Modello_lancio_mp", CellValues.String, 2));
row.Append(ConstructCell("Desmodello-lancio_mp", CellValues.String, 2));
row.Append(ConstructCell("Impegnatoareparto", CellValues.String, 2));
row.Append(ConstructCell("Internoesterno", CellValues.String, 2));
row.Append(ConstructCell("Fermounasettimana", CellValues.String, 2));
row.Append(ConstructCell("Scaduto", CellValues.String, 2));
row.Append(ConstructCell("Annocarico", CellValues.String, 2));
row.Append(ConstructCell("Settimanacarico", CellValues.String, 2));
row.Append(ConstructCell("Apertodaduegiorni", CellValues.String, 2));
row.Append(ConstructCell("Nota", CellValues.String, 2));
row.Append(ConstructCell("Appoggio", CellValues.String, 2));
sheetData.AppendChild(row);
foreach (ReportDS.ODL_APERTIRow odl_aperto in ds.ODL_APERTI)
{
Row rowDati = new Row();
rowDati.Append(ConstructCell(odl_aperto.IsAZIENDANull() ? string.Empty : odl_aperto.AZIENDA, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsDESTIPOLANCIONull() ? string.Empty : odl_aperto.DESTIPOLANCIO, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsCODICETIPOONull() ? string.Empty : odl_aperto.CODICETIPOO, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsCODICECLIFONull() ? string.Empty : odl_aperto.CODICECLIFO, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsRAGIONESOCNull() ? string.Empty : odl_aperto.RAGIONESOC, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsNUMMOVFASENull() ? string.Empty : odl_aperto.NUMMOVFASE, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsPIANIFICATO_SNNull() ? string.Empty : odl_aperto.PIANIFICATO_SN, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsNOMECOMMESSANull() ? string.Empty : odl_aperto.NOMECOMMESSA, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsSEGNALATORENull() ? string.Empty : odl_aperto.SEGNALATORE, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsCODTIPOMOVFASENull() ? string.Empty : odl_aperto.CODTIPOMOVFASE, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsDESTIPOMOVFASENull() ? string.Empty : odl_aperto.DESTIPOMOVFASE, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsMODELLO_LANCIONull() ? string.Empty : odl_aperto.MODELLO_LANCIO, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsDESMODELLO_LANCIONull() ? string.Empty : odl_aperto.DESMODELLO_LANCIO, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsIDMAGAZZ_WIPNull() ? string.Empty : odl_aperto.IDMAGAZZ_WIP, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsMODELLO_WIPNull() ? string.Empty : odl_aperto.MODELLO_WIP, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsDESMODELLO_WIPNull() ? string.Empty : odl_aperto.DESMODELLO_WIP, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsELENCOFASINull() ? string.Empty : odl_aperto.ELENCOFASI, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsDATAMOVFASENull() ? string.Empty : odl_aperto.DATAMOVFASE.ToShortDateString(), CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsDATAINIZIO_ODLNull() ? string.Empty : odl_aperto.DATAINIZIO_ODL.ToShortDateString(), CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsDATAPRIMOINVIO_ODLNull() ? string.Empty : odl_aperto.DATAPRIMOINVIO_ODL.ToShortDateString(), CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsDOCUMENTI_INVIONull() ? string.Empty : odl_aperto.DOCUMENTI_INVIO, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsDATAFINE_ODL_E_MULTIPLANull() ? string.Empty : odl_aperto.DATAFINE_ODL_E_MULTIPLA.ToShortDateString(), CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsDATAFINE_FASECOMMESSANull() ? string.Empty : odl_aperto.DATAFINE_FASECOMMESSA.ToShortDateString(), CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsCONTA_MULTIPLENull() ? string.Empty : odl_aperto.CONTA_MULTIPLE, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsCODICEUNIMINull() ? string.Empty : odl_aperto.CODICEUNIMI, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsQTANull() ? string.Empty : odl_aperto.QTA.ToString(), CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsQTADATERNull() ? string.Empty : odl_aperto.QTADATER.ToString(), CellValues.Number, 1));
rowDati.Append(ConstructCell(odl_aperto.IsPRIORITANull() ? string.Empty : odl_aperto.PRIORITA.ToString(), CellValues.Number, 1));
rowDati.Append(ConstructCell(odl_aperto.IsNOTEPARTICOLARIFASENull() ? string.Empty : odl_aperto.NOTEPARTICOLARIFASE, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsNOTAPARTICOLAREODLNull() ? string.Empty : odl_aperto.NOTAPARTICOLAREODL, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsMODELLO_LANCIO_MPNull() ? string.Empty : odl_aperto.MODELLO_LANCIO_MP, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsDESMODELLO_LANCIO_MPNull() ? string.Empty : odl_aperto.DESMODELLO_LANCIO_MP, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsIMPEGNATOAREPARTONull() ? string.Empty : odl_aperto.IMPEGNATOAREPARTO, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsINTERNOESTERNONull() ? string.Empty : odl_aperto.INTERNOESTERNO, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsFERMOUNASETTIMANANull() ? string.Empty : odl_aperto.FERMOUNASETTIMANA, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsSCADUTONull() ? string.Empty : odl_aperto.SCADUTO, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsANNOCARICONull() ? string.Empty : odl_aperto.ANNOCARICO.ToString(), CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsSETTIMANACARICONull() ? string.Empty : odl_aperto.SETTIMANACARICO.ToString(), CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsAPERTODADUEGIORNINull() ? string.Empty : odl_aperto.APERTODADUEGIORNI, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsNOTANull() ? string.Empty : odl_aperto.NOTA, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsAPPOGGIONull() ? string.Empty : odl_aperto.APPOGGIO, CellValues.String, 1));
sheetData.AppendChild(rowDati);
}
workbookPart.Workbook.Save();
document.Save();
document.Close();
ms.Seek(0, SeekOrigin.Begin);
content = ms.ToArray();
}
return(content);
}
private void btn_Excel_Click(object sender, EventArgs e)
{
string targetPathFormato = "C:\\BaseTLD\\formato\\" + "FormatoTLD.xlsx";
string targetPathFormatoInfome = "C:\\BaseTLD\\formato\\" + "FORMULARIO DESPACHO.xlsx";
grdDatos.Sort(grdDatos.Columns[1], ListSortDirection.Ascending);
string targetPathConf = "C:\\BaseTLD\\Cliente";
string targetPathFormatoFormulario = "C:\\BaseTLD\\Cliente";
if (!System.IO.Directory.Exists(targetPathConf))
{
System.IO.Directory.CreateDirectory(targetPathConf);
}
targetPathConf = "C:\\BaseTLD\\Cliente\\Cliente" + lbl_id_cliente.Text;
if (!System.IO.Directory.Exists(targetPathConf))
{
System.IO.Directory.CreateDirectory(targetPathConf);
}
targetPathConf = "C:\\BaseTLD\\Cliente\\Cliente" + lbl_id_cliente.Text;
if (!System.IO.Directory.Exists(targetPathConf))
{
System.IO.Directory.CreateDirectory(targetPathConf);
}
string strDirCliente = @targetPathConf;
targetPathConf = "C:\\BaseTLD\\Cliente\\Cliente" + lbl_id_cliente.Text + "\\CodigoBarra";
if (!System.IO.Directory.Exists(targetPathConf))
{
System.IO.Directory.CreateDirectory(targetPathConf);
}
targetPathFormatoFormulario = "C:\\BaseTLD\\Cliente\\Cliente" + lbl_id_cliente.Text + "\\Formulario";
if (!System.IO.Directory.Exists(targetPathFormatoFormulario))
{
System.IO.Directory.CreateDirectory(targetPathFormatoFormulario);
}
DataSet dtPeriodo;
// SqlCommand cmdPeriodo = new SqlCommand();
SqlCommand cmdPeriodo = new SqlCommand();
cmdPeriodo.CommandText = "SELECT fecha_inicio,fecha_termino, " +
"substring([fecha_inicio],1,2)+' de '+ dbo.getmonth(cast(substring( [fecha_inicio],4,2)as int))+ ' de '+ substring([fecha_inicio],7,4) as Finicio," +
"substring([fecha_termino],1,2)+' de '+dbo.getmonth(cast(substring( [fecha_termino],4,2)as int))+ ' de '+ substring([fecha_termino],7,4) as FTermino" +
" FROM conf_periodo " +
//"where mes =3 and anno=" + cbx_anno.Text;
"WHERE Id_Periodo= " + cbx_id_periodo.SelectedValue;
cmdPeriodo.CommandType = CommandType.Text;
dtPeriodo = Conectar.Listar(Clases.clsBD.BD, cmdPeriodo);
string strfecha_inicio = "From " + dtPeriodo.Tables[0].Rows[0]["fecha_inicio"].ToString().Substring(0, 6) + dtPeriodo.Tables[0].Rows[0]["fecha_inicio"].ToString().Substring(8, 2);
strfecha_inicio = strfecha_inicio + " to " + dtPeriodo.Tables[0].Rows[0]["fecha_termino"].ToString().Substring(0, 6) + dtPeriodo.Tables[0].Rows[0]["fecha_termino"].ToString().Substring(8, 2);
string strfecha_Per = dtPeriodo.Tables[0].Rows[0]["Finicio"].ToString() + " al " + dtPeriodo.Tables[0].Rows[0]["FTermino"].ToString();
string strfecha_Fin = dtPeriodo.Tables[0].Rows[0]["FTermino"].ToString();
string strTri = cbx_id_periodo.Text.ToString().Substring(0, 1) + "er Trim " + cbx_anno.Text;
// From 01/01/19 to 31/03/19
//*****************
DataGridViewCheckBoxCell checkGenerar;
DataGridViewCheckBoxCell checkCell;
//DataGridViewTextBoxCell txtvalor;
DataGridViewTextBoxCell txtndocumento;
DataGridViewTextBoxCell txtnpelicula;
DataGridViewTextBoxCell txtid_estadodosimetro;
// DataGridViewTextBoxCell N_Cliente;
DataGridViewTextBoxCell Rut;
DataGridViewTextBoxCell Paterno;
DataGridViewTextBoxCell Nombres;
DataGridViewTextBoxCell Maternos;
DataGridViewTextBoxCell id_sucursal;
DataGridViewTextBoxCell id_dosimetro;
DataGridViewTextBoxCell Id_Personal;
//*******************
// string strArchivo = "";// dtformato.Tables[0].Rows[0]["Glosa"].ToString() + "Plantillaword.docx";
// int i;
int intExcel = 1;
File.Copy(targetPathFormato, targetPathConf + "\\ET_Cliente" + lbl_id_cliente.Text + "_" + cbx_id_seccion.Text + "_" + cbx_anno.Text.ToString() + "_" + cbx_id_periodo.Text.ToString().Substring(0, 1) + "Tri.xlsx", true);
File.Copy(targetPathFormatoInfome, targetPathFormatoFormulario + "\\Formulario Cliente" + lbl_id_cliente.Text + "_" + cbx_id_seccion.Text + "_" + cbx_anno.Text.ToString() + "_" + cbx_id_periodo.Text.ToString().Substring(0, 1) + "Tri_" + intExcel.ToString() + ".xlsx", true);
string strpathcopiar = targetPathConf + "\\ET_Cliente" + lbl_id_cliente.Text + "_" + cbx_id_seccion.Text + "_" + cbx_anno.Text.ToString() + "_" + cbx_id_periodo.Text.ToString().Substring(0, 1) + "Tri.xlsx";
int intFila = 2;
int intHojaExcel = 19;
string strpathcopiarInforme = targetPathFormatoFormulario + "\\Formulario Cliente" + lbl_id_cliente.Text + "_" + cbx_id_seccion.Text + "_" + cbx_anno.Text.ToString() + "_" + cbx_id_periodo.Text.ToString().Substring(0, 1) + "Tri_" + intExcel.ToString() + ".xlsx";
string strUsados = "Estos dosimetros deben ser usados entre el " + strfecha_Per + " al " + strfecha_Fin;
string fmt = "00000000";
for (int idatos = 0; idatos <= grdDatos.Rows.Count - 1; idatos++)
{
checkGenerar = (DataGridViewCheckBoxCell)grdDatos.Rows[idatos].Cells["Generar"];
checkCell = (DataGridViewCheckBoxCell)grdDatos.Rows[idatos].Cells["chkGenerado"];
txtndocumento = (DataGridViewTextBoxCell)grdDatos.Rows[idatos].Cells["NDocumento"];
txtnpelicula = (DataGridViewTextBoxCell)grdDatos.Rows[idatos].Cells["N_pelicula"];
txtid_estadodosimetro = (DataGridViewTextBoxCell)grdDatos.Rows[idatos].Cells["id_estadodosimetro"];
Rut = (DataGridViewTextBoxCell)grdDatos.Rows[idatos].Cells["Rut"];
id_dosimetro = (DataGridViewTextBoxCell)grdDatos.Rows[idatos].Cells["id_dosimetro"];
Paterno = (DataGridViewTextBoxCell)grdDatos.Rows[idatos].Cells["Paterno"];
Maternos = (DataGridViewTextBoxCell)grdDatos.Rows[idatos].Cells["Maternos"];
Nombres = (DataGridViewTextBoxCell)grdDatos.Rows[idatos].Cells["Nombres"];
id_sucursal = (DataGridViewTextBoxCell)grdDatos.Rows[idatos].Cells["id_sucursal"];
Id_Personal = (DataGridViewTextBoxCell)grdDatos.Rows[idatos].Cells["Id_Personal"];
string wsName = "Sheet1";
if ((checkCell.Value.ToString() == "1")) //(checkGenerar.Value.ToString() == "1") &&&& (txtid_estadodosimetro.Value.ToString() == "-1")
{
if (intHojaExcel == 34)
{
intHojaExcel = 19;
intExcel = intExcel + 1;
strpathcopiarInforme = targetPathFormatoFormulario + "\\Formulario Cliente" + lbl_id_cliente.Text + "_" + cbx_id_seccion.Text + "_" + cbx_anno.Text.ToString() + "_" + cbx_id_periodo.Text.ToString().Substring(0, 1) + "Tri_" + (intExcel - 1).ToString() + ".xlsx";
File.Copy(strpathcopiarInforme, targetPathFormatoFormulario + "\\Formulario Cliente" + lbl_id_cliente.Text + "_" + cbx_id_seccion.Text + "_" + cbx_anno.Text.ToString() + "_" + cbx_id_periodo.Text.ToString().Substring(0, 1) + "Tri_" + (intExcel).ToString() + ".xlsx", true);
strpathcopiarInforme = targetPathFormatoFormulario + "\\Formulario Cliente" + lbl_id_cliente.Text + "_" + cbx_id_seccion.Text + "_" + cbx_anno.Text.ToString() + "_" + cbx_id_periodo.Text.ToString().Substring(0, 1) + "Tri_" + intExcel.ToString() + ".xlsx";
for (int intFilalimpiar = intHojaExcel; intFilalimpiar <= 33; intFilalimpiar++)
{
wsName = "Informe";
document = SpreadsheetDocument.Open(strpathcopiarInforme, true);
wbPart = document.WorkbookPart;
UpdateValue(wsName, "B" + (intFilalimpiar).ToString(), "", 0, true);
UpdateValue(wsName, "C" + (intFilalimpiar).ToString(), "", 0, true);
UpdateValue(wsName, "D" + (intFilalimpiar).ToString(), "", 0, true);
UpdateValue(wsName, "E" + (intFilalimpiar).ToString(), "", 0, true);
UpdateValue(wsName, "F" + (intFilalimpiar).ToString(), "", 0, true);
UpdateValue(wsName, "G" + (intFilalimpiar).ToString(), "", 0, true);
UpdateValue(wsName, "B15", strUsados, 0, true);
// UpdateValue(wsName, "D18", strfecha_Fin, 0, true);
// UpdateValue(wsName, "J4", strServicio, 0, true);
UpdateValue(wsName, "C12", strDireccion.ToUpper(), 0, true);
UpdateValue(wsName, "C14", lbl_rut.Text, 0, true);
// UpdateValue(wsName, "M4", strRegion, 0, true);
UpdateValue(wsName, "C13", strComuna.ToUpper() + ", " + strRegion.ToUpper(), 0, true);
UpdateValue(wsName, "C11", lbl_nombreCliente.Text.ToUpper(), 0, true);
UpdateValue(wsName, "F11", lbl_id_cliente.Text, 0, true);
//lbl_id_cliente
document.Close();
}
}
document = SpreadsheetDocument.Open(strpathcopiar, true);
wbPart = document.WorkbookPart;
wsName = "Sheet1";
UpdateValue(wsName, "A" + (intFila).ToString(), int.Parse(txtnpelicula.Value.ToString()).ToString(fmt), 0, true);
UpdateValue(wsName, "B" + (intFila).ToString(), Paterno.Value.ToString().ToUpper(), 0, true);
UpdateValue(wsName, "C" + (intFila).ToString(), Maternos.Value.ToString().ToUpper(), 0, true);
UpdateValue(wsName, "D" + (intFila).ToString(), Nombres.Value.ToString().Substring(0, 1).ToUpper() + Nombres.Value.ToString().Substring(1, Nombres.Value.ToString().Length - 1).ToLower(), 0, true);
UpdateValue(wsName, "E" + (intFila).ToString(), Rut.Value.ToString().ToUpperInvariant(), 0, true);
UpdateValue(wsName, "F" + (intFila).ToString(), strTri, 0, true);
UpdateValue(wsName, "G" + (intFila).ToString(), strfecha_inicio, 0, true);
document.Close();
wsName = "Informe";
document = SpreadsheetDocument.Open(strpathcopiarInforme, true);
wbPart = document.WorkbookPart;
UpdateValue(wsName, "B" + (intHojaExcel).ToString(), int.Parse(txtnpelicula.Value.ToString()).ToString(fmt), 0, true);
UpdateValue(wsName, "C" + (intHojaExcel).ToString(), Paterno.Value.ToString().ToUpper(), 0, true);
UpdateValue(wsName, "D" + (intHojaExcel).ToString(), Maternos.Value.ToString().ToUpper(), 0, true);
UpdateValue(wsName, "E" + (intHojaExcel).ToString(), Nombres.Value.ToString().ToUpper(), 0, true);
UpdateValue(wsName, "F" + (intHojaExcel).ToString(), Rut.Value.ToString().ToUpperInvariant(), 0, true);
//UpdateValue(wsName, "D2" , strfecha_Per, 0, true);
//UpdateValue(wsName, "D18", strfecha_Fin, 0, true);
UpdateValue(wsName, "B15", strUsados, 0, true);
UpdateValue(wsName, "C12", strDireccion, 0, true);
UpdateValue(wsName, "C14", lbl_rut.Text, 0, true);
// UpdateValue(wsName, "M4", strRegion, 0, true);
UpdateValue(wsName, "C13", strComuna.ToUpper() + ", " + strRegion.ToUpper(), 0, true);
UpdateValue(wsName, "C11", lbl_nombreCliente.Text.ToUpper(), 0, true);
UpdateValue(wsName, "F11", lbl_id_cliente.Text, 0, true);
document.Close();
intFila = intFila + 1;
intHojaExcel = intHojaExcel + 1;
//
}
}
Process.Start("explorer.exe", strDirCliente);
MessageBox.Show("El archivo fue generado");
}
public static string GetCellValue(this SpreadsheetDocument document,
string sheetName,
string addressName)
{
string value = "";
// Open the spreadsheet document for read-only access.
// Retrieve a reference to the workbook part.
WorkbookPart wbPart = document.WorkbookPart;
// Find the sheet with the supplied name, and then use that
// Sheet object to retrieve a reference to the first worksheet.
Sheet theSheet = wbPart.Workbook.Descendants <Sheet>().
Where(s => s.Name == sheetName).FirstOrDefault();
// Throw an exception if there is no sheet.
if (theSheet == null)
{
throw new ArgumentException("sheetName");
}
// Retrieve a reference to the worksheet part.
WorksheetPart wsPart =
(WorksheetPart)(wbPart.GetPartById(theSheet.Id));
// Use its Worksheet property to get a reference to the cell
// whose address matches the address you supplied.
Cell theCell = wsPart.Worksheet.Descendants <Cell>().
Where(c => c.CellReference == addressName).FirstOrDefault();
// If the cell does not exist, return an empty string.
if (theCell != null)
{
value = theCell.InnerText;
// If the cell represents an integer number, you are done.
// For dates, this code returns the serialized value that
// represents the date. The code handles strings and
// Booleans individually. For shared strings, the code
// looks up the corresponding value in the shared string
// table. For Booleans, the code converts the value into
// the words TRUE or FALSE.
if (theCell.DataType != null)
{
switch (theCell.DataType.Value)
{
case CellValues.SharedString:
// For shared strings, look up the value in the
// shared strings table.
var stringTable =
wbPart.GetPartsOfType <SharedStringTablePart>()
.FirstOrDefault();
// If the shared string table is missing, something
// is wrong. Return the index that is in
// the cell. Otherwise, look up the correct text in
// the table.
if (stringTable != null)
{
value =
stringTable.SharedStringTable
.ElementAt(int.Parse(value)).InnerText;
}
break;
case CellValues.Boolean:
switch (value)
{
case "0":
value = "FALSE";
break;
default:
value = "TRUE";
break;
}
break;
}
if (theCell.CellFormula != null)
{
value = theCell.CellValue.InnerText;
}
}
else
{
if (theCell.CellFormula != null)
{
value = theCell.CellValue.InnerText;
}
}
}
return(value);
}
public static string GetCellValue(this WorksheetPart wsPart, WorkbookPart wbPart, string addressName)
{
string value = null;
// Use its Worksheet property to get a reference to the cell
// whose address matches the address you supplied.
Cell theCell = wsPart.Worksheet.Descendants <Cell>().
Where(c => c.CellReference == addressName).FirstOrDefault();
// If the cell does not exist, return an empty string.
if (theCell != null)
{
value = theCell.InnerText;
// If the cell represents an integer number, you are done.
// For dates, this code returns the serialized value that
// represents the date. The code handles strings and
// Booleans individually. For shared strings, the code
// looks up the corresponding value in the shared string
// table. For Booleans, the code converts the value into
// the words TRUE or FALSE.
if (theCell.DataType != null)
{
switch (theCell.DataType.Value)
{
case CellValues.SharedString:
// For shared strings, look up the value in the
// shared strings table.
var stringTable =
wbPart.GetPartsOfType <SharedStringTablePart>()
.FirstOrDefault();
// If the shared string table is missing, something
// is wrong. Return the index that is in
// the cell. Otherwise, look up the correct text in
// the table.
if (stringTable != null)
{
value =
stringTable.SharedStringTable
.ElementAt(int.Parse(value)).InnerText;
}
break;
case CellValues.Boolean:
switch (value)
{
case "0":
value = "FALSE";
break;
default:
value = "TRUE";
break;
}
break;
}
if (theCell.CellFormula != null)
{
value = theCell.CellValue.InnerText;
}
}
else
{
if (theCell.CellFormula != null)
{
value = theCell.CellValue.InnerText;
}
}
}
return(value);
}
public FileResult ExportMetalsReport(int companyId, string sCurrDate)
{
byte[] b;
DateTime curr;
sCurrDate = sCurrDate.Replace("'", "");
if (!DateTime.TryParse(sCurrDate, out curr))
{
curr = DateTime.Now.ToLocalTime();
}
string currDate = $"{curr.ToShortDateString()} {curr.ToShortTimeString()}";
DCTSOpenXML oxl = new DCTSOpenXML();
using (MemoryStream memStream = new MemoryStream())
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(memStream, SpreadsheetDocumentType.Workbook))
{
// Build Excel File
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = document.WorkbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets());
// declare locals
Row row;
Cell cell;
string loc;
int rr;
// Build sheet
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Metals"
};
sheets.Append(sheet);
Worksheet worksheet = new Worksheet();
SheetData sd = new SheetData();
// Title
row = new Row();
cell = oxl.SetCellVal("A1", $"Export - Metal Codes {currDate}");
row.Append(cell);
sd.Append(row);
row = new Row();
cell = oxl.SetCellVal("A2", "");
row.Append(cell);
sd.Append(row);
// Headers
row = new Row();
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 1, Max = 1, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("A3", "Code"); row.Append(cell);
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 2, Max = 2, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("B3", "Desc"); row.Append(cell);
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 3, Max = 3, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("C3", "Market"); row.Append(cell);
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 4, Max = 4, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("D3", "Multiplier"); row.Append(cell);
worksheet.Append(oxl.columns);
sd.Append(row);
List <MetalCode> Metals = db.MetalCodes.Where(m => m.CompanyId == companyId).OrderBy(m => m.Code).ToList();
// Content
for (int i = 0; i < Metals.Count(); i++)
{
row = new Row();
rr = 4 + i;
loc = "A" + rr; cell = oxl.SetCellVal(loc, Metals[i].Code); row.Append(cell);
loc = "B" + rr; cell = oxl.SetCellVal(loc, Metals[i].Desc); row.Append(cell);
loc = "C" + rr; cell = oxl.SetCellVal(loc, Metals[i].Market); row.Append(cell);
loc = "D" + rr; cell = oxl.SetCellVal(loc, Metals[i].Multiplier); row.Append(cell);
sd.Append(row);
}
worksheet.Append(sd);
// Autofit columns - ss:AutoFitWidth="1"
worksheetPart.Worksheet = worksheet;
workbookPart.Workbook.Save();
document.Close();
b = memStream.ToArray();
return(File(b, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
"Metals as of " + $"{currDate}" + ".xlsx"));
}
}
}
static DataTable ReadExcelasJSON(string fileName)
{
DataTable dtTable = new DataTable();
try
{
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fileName, false))
{
WorkbookPart workbookPart = doc.WorkbookPart;
Sheets thesheetcollection = workbookPart.Workbook.GetFirstChild <Sheets>();
string sheetId = "";
foreach (Sheet thesheet in thesheetcollection)
{
if (thesheet.Name == "Исходник")
{
sheetId = thesheet.Id;
break;
}
}
//using for each loop to get the sheet from the sheetcollection
//foreach (Sheet thesheet in thesheetcollection.OfType<Sheet>())
//{
//statement to get the worksheet object by using the sheet id
//Worksheet theWorksheet = ((WorksheetPart)workbookPart.GetPartById(thesheet.Id)).Worksheet;
Worksheet theWorksheet = ((WorksheetPart)workbookPart.GetPartById(sheetId)).Worksheet;
SheetData thesheetdata = theWorksheet.GetFirstChild <SheetData>();
IEnumerable <Row> rows = thesheetdata.Descendants <Row>();
//Выделяет все символы из строки
Regex regex = new Regex(@"\D+");
//Выделяет все цифры из строки
Regex regex1 = new Regex(@"\d+");
//Поиск колонки с признаком классификатора ОС
string[] rowHeaders = new string[] { "Власник/Орендодавець" };
int?startRowIndex = null;
int ozColumnIndex = 0;
//string[] vechicleType = new string[] {
// "АТП-2004 ТОВ".Replace(" ",""),
// "ТОВ Кернел-Трейд".Replace(" ", ""),
// "ПРАТ Кропивницький ОЕЗ".Replace(" ", ""),
// "СТОВ Дружба - Нова".Replace(" ", ""),
// "СТОВ Придніпровський край".Replace(" ", ""),
// "Коритня-Агро КМ ТОВ".Replace(" ", ""),
// "Приколотнянский МЭЗ ТОВ".Replace(" ", ""),
// "Агро Інвест Україна, ТОВ".Replace(" ", ""),
// "СТОВ Дружба-Нова".Replace(" ", ""),
// "Вовчанський ОЕЗ ПРАТ".Replace(" ", ""),
// "Юнігрейн-Агро ТОВ, Семенівка".Replace(" ", ""),
// "Вовчанський ОЕЗ ПРАТ".Replace(" ", "")
//};
List <string> passengerCellValue = new List <string>();
List <int> rowsToProc = new List <int>();
//Поиск первой строки с данными
for (int rCnt = 0; rCnt < thesheetdata.ChildElements.Count(); rCnt++)
{
for (int rCnt1 = 0; rCnt1 < thesheetdata.ElementAt(rCnt).ChildElements.Count(); rCnt1++)
{
Cell thecurrentcell = (Cell)thesheetdata.ElementAt(rCnt).ChildElements.ElementAt(rCnt1);
int id;
if (Int32.TryParse(thecurrentcell.InnerText, out id))
{
SharedStringItem item = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(id);
if (item.Text != null)
{
if (Array.IndexOf(rowHeaders, item.Text.Text) > -1)
{
startRowIndex = rCnt;
ozColumnIndex = rCnt1;
//rowsToProc.Add(rCnt);
//passengerCellValue = thecurrentcell.InnerText;
break;
}
}
}
}
if (startRowIndex != null)
{
break;
}
}
//rowHeaders = new string[] { "Кластер", "Назва підприємства", "Назва основного засобу", "Класифікатор ОЗ", "Дата випуску", "Державний номер", "Стан ОЗ", "Модель" };
rowHeaders = new string[] { "Власник/Орендодавець" };
SharedStringTablePart stringTablePart = workbookPart.SharedStringTablePart;
List <int> columns = new List <int>();
//Поиск номеров колонок для получения данных
int idx = 0;
foreach (Cell c in rows.ElementAt((int)startRowIndex).Elements <Cell>())
{
if (c.CellValue != null)
{
if (c.DataType != null && c.DataType.Value == CellValues.SharedString)
{
if (Array.IndexOf(rowHeaders, stringTablePart.SharedStringTable.ChildElements[Int32.Parse(c.CellValue.InnerXml)].InnerText) > -1)
{
columns.Add(idx);// regex.Match(c.CellReference).Value);
}
}
}
idx++;
}
for (int rCnt = 0; rCnt < thesheetdata.ChildElements.Count(); rCnt++)
{
try
{
Cell thecurrentcell = (Cell)thesheetdata.ElementAt(rCnt).ChildElements.ElementAt(ozColumnIndex);
int id;
if (Int32.TryParse(thecurrentcell.InnerText, out id))
{
SharedStringItem item = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(id);
//if (Array.IndexOf(vechicleType.ToArray(), item.Text.Text.Replace("\"", "").Replace(" ", "")) > -1 && item.Text.Text.IndexOf("Дружба")>-1)
//{
// int fkjgk = 0;
// //rowsToProc.Add(rCnt);
// //passengerCellValue.Add(thecurrentcell.InnerText);
//}
//if (Array.IndexOf(vechicleType.ToArray(), item.Text.Text.Replace("\"","").Replace(" ", "")) < 0)
//{
rowsToProc.Add(rCnt);
passengerCellValue.Add(thecurrentcell.InnerText);
//}
}
}
catch (Exception)
{
//throw;
}
}
for (int rCnt = 0; rCnt < rowsToProc.Count(); rCnt++)
{
List <string> rowList = new List <string>();
for (int c = 0; c < columns.Count(); c++)
{
Cell thecurrentcell = (Cell)thesheetdata.ElementAt(rowsToProc[rCnt]).ChildElements.ElementAt(columns[c]);
//statement to take the integer value
string currentcellvalue = string.Empty;
if (thecurrentcell.DataType != null)
{
if (thecurrentcell.DataType == CellValues.SharedString)
{
int id;
if (Int32.TryParse(thecurrentcell.InnerText, out id))
{
SharedStringItem item = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(id);
if (item.Text != null)
{
//first row will provide the column name.
if (rCnt == 0)
{
dtTable.Columns.Add(item.Text.Text);
}
else
{
rowList.Add(item.Text.Text);
}
}
else
{
rowList.Add("");
}
}
}
}
else
{
if (rCnt != 0)//reserved for column values
{
rowList.Add(thecurrentcell.InnerText);
}
}
}
if (rCnt != 0)//reserved for column values
{
dtTable.Rows.Add(rowList.ToArray());
}
}
}
}
catch (Exception)
{
throw;
}
return(dtTable);
}
private static void ProcessExcelDocument(
string outputDocumentFullName,
string sourceLanguage,
string targetLanguage,
bool ignoreHidden = false)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Open(outputDocumentFullName, true))
{
//document.WorkbookPart.SharedStringTablePart.PutXDocument();
List <DocumentFormat.OpenXml.Spreadsheet.Text> lstTexts = new List <DocumentFormat.OpenXml.Spreadsheet.Text>();
foreach (SharedStringItem si in document.WorkbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>())
{
if (si != null && si.Text != null && !String.IsNullOrEmpty(si.Text.Text))
{
lstTexts.Add(si.Text);
}
else if (si != null)
{
lstTexts.AddRange(si.Elements <DocumentFormat.OpenXml.Spreadsheet.Run>().Where(item => (item != null && item.Text != null && !String.IsNullOrEmpty(item.Text.Text))).Select(item => item.Text));
}
}
var batch = lstTexts.Select(item => item.Text);
IEnumerable <string> values = batch as string[] ?? batch.ToArray();
var batches = SplitList(values, TranslationServiceFacade.Maxelements, TranslationServiceFacade.Maxrequestsize);
string[] translated = new string[values.Count()];
var exceptions = new ConcurrentQueue <Exception>();
Parallel.For(
0,
batches.Count(),
new ParallelOptions {
MaxDegreeOfParallelism = 1
},
l =>
{
try
{
var translationOutput = TranslationServiceFacade.TranslateArray(
batches[l].ToArray(),
sourceLanguage,
targetLanguage);
int batchStartIndexInDocument = 0;
for (int i = 0; i < l; i++)
{
batchStartIndexInDocument = batchStartIndexInDocument + batches[i].Count();
}
// Apply translated batch to document
for (int j = 0; j < translationOutput.Length; j++)
{
int indexInDocument = j + batchStartIndexInDocument + 1;
var newValue = translationOutput[j];
translated[indexInDocument - 1] = newValue;
lstTexts[indexInDocument - 1].Text = newValue;
}
}
catch (Exception ex)
{
exceptions.Enqueue(ex);
}
});
if (exceptions.Count > 0)
{
throw new AggregateException(exceptions);
}
// Refresh all the shared string references.
foreach (var table in document.WorkbookPart.GetPartsOfType <WorksheetPart>().Select(part => part.TableDefinitionParts).SelectMany(tables => tables))
{
foreach (TableColumn col in table.Table.TableColumns)
{
col.Name = translated[int.Parse(col.Id) - 1];
}
table.Table.Save();
}
// Update comments
WorkbookPart workBookPart = document.WorkbookPart;
List <DocumentFormat.OpenXml.Spreadsheet.Comment> lstComments = new List <DocumentFormat.OpenXml.Spreadsheet.Comment>();
foreach (WorksheetCommentsPart commentsPart in workBookPart.WorksheetParts.SelectMany(sheet => sheet.GetPartsOfType <WorksheetCommentsPart>()))
{
lstComments.AddRange(commentsPart.Comments.CommentList.Cast <Comment>());
}
var batchComments = lstComments.Select(item => item.InnerText);
var batchesComments = SplitList(batchComments, TranslationServiceFacade.Maxelements, TranslationServiceFacade.Maxrequestsize);
string[] translatedComments = new string[batchesComments.Count()];
Parallel.For(
0,
batchesComments.Count(),
new ParallelOptions {
MaxDegreeOfParallelism = 1
},
l =>
{
try
{
var translationOutput =
TranslationServiceFacade.TranslateArray(
batchesComments[l].ToArray(),
sourceLanguage,
targetLanguage);
int batchStartIndexInDocument = 0;
for (int i = 0; i < l; i++)
{
batchStartIndexInDocument = batchStartIndexInDocument + batches[i].Count();
}
for (int j = 0; j < translationOutput.Length; j++)
{
int indexInDocument = j + batchStartIndexInDocument + 1;
var currentSharedStringItem = lstComments.Take(indexInDocument).Last();
var newValue = translationOutput[j];
if (translatedComments.Count() > indexInDocument - 1)
{
translatedComments[indexInDocument - 1] = newValue;
}
currentSharedStringItem.CommentText = new CommentText
{
Text =
new DocumentFormat.
OpenXml.Spreadsheet.
Text
{
Text = newValue
}
};
}
}
catch (Exception ex)
{
exceptions.Enqueue(ex);
}
});
// Throw the exceptions here after the loop completes.
if (exceptions.Count > 0)
{
throw new AggregateException(exceptions);
}
}
}
static void Test_1()
{
string path = @"C:\Users\Miras Talaspayev\Downloads\Telegram Desktop\Test_OpenXml.xlsx";
List <string> company_names = new List <string>();
int[] company_index = new int[5] {
2, 1, 0, 4, 3
};
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(path, false))
{
WorkbookPart wbPart = spreadsheetDocument.WorkbookPart;
Sheets sheets = wbPart.Workbook.Sheets; // It is necessary only for getting names of company.
foreach (Sheet sheet in sheets)
{
company_names.Add(sheet.Name);
}
int comp = 0;
foreach (WorksheetPart worksheetPart in wbPart.WorksheetParts)
{
Console.WriteLine("Sheet: {0}. ", company_names[company_index[comp]]);
SheetData sheetData = worksheetPart.Worksheet.Elements <SheetData>().First();
int i = 0;
List <string> Positions = new List <string>();
foreach (Row rows in sheetData.Elements <Row>())
{
if (i == 0)
{
i++;
continue;
}
if (i == 1)
{
foreach (Cell cell in rows.Elements <Cell>())
{
Positions.Add(ExcelDocument.CellString(cell, wbPart));
}
i++;
continue;
}
int j = 0;
string docs = null;
foreach (Cell cell in rows.Elements <Cell>())
{
if (j == 0)
{
docs = (ExcelDocument.CellString(cell, wbPart));
break;
}
}
Console.WriteLine("\t" + docs);
foreach (Cell cell in rows.Elements <Cell>())
{
if (ExcelDocument.CellString(cell, wbPart) == "+")
{
Console.WriteLine("\t\t" + Positions[j] + " " + cell.CellReference);
}
j++;
}
i++;
}
Console.WriteLine();
comp++;
}
}
}
public byte[] BolleVenditaExcel(ReportDS ds)
{
byte[] content;
MemoryStream ms = new MemoryStream();
using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
// Adding style
WorkbookStylesPart stylePart = workbookPart.AddNewPart <WorkbookStylesPart>();
stylePart.Stylesheet = GenerateStylesheet();
stylePart.Stylesheet.Save();
int numeroColonne = 18;
Columns columns = new Columns();
for (int i = 0; i < numeroColonne; i++)
{
Column c = new Column();
UInt32Value u = new UInt32Value((uint)(i + 1));
c.Min = u;
c.Max = u;
c.Width = 15;
columns.Append(c);
}
worksheetPart.Worksheet.AppendChild(columns);
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Bolle vendita"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Constructing header
Row row = new Row();
row.Append(ConstructCell("Azienda", CellValues.String, 2));
row.Append(ConstructCell("Tipo", CellValues.String, 2));
row.Append(ConstructCell("Causale", CellValues.String, 2));
row.Append(ConstructCell("Documento", CellValues.String, 2));
row.Append(ConstructCell("Data documento", CellValues.String, 2));
row.Append(ConstructCell("Numero documento", CellValues.String, 2));
row.Append(ConstructCell("Segnalatore", CellValues.String, 2));
row.Append(ConstructCell("Cliente", CellValues.String, 2));
row.Append(ConstructCell("Riga", CellValues.String, 2));
row.Append(ConstructCell("Modello", CellValues.String, 2));
row.Append(ConstructCell("Quantità", CellValues.String, 2));
row.Append(ConstructCell("Prezzo", CellValues.String, 2));
row.Append(ConstructCell("Valore", CellValues.String, 2));
row.Append(ConstructCell("Ordine cliente", CellValues.String, 2));
row.Append(ConstructCell("Data ordine", CellValues.String, 2));
row.Append(ConstructCell("Data richiesta", CellValues.String, 2));
row.Append(ConstructCell("Data conferma", CellValues.String, 2));
row.Append(ConstructCell("Riferimento", CellValues.String, 2));
sheetData.AppendChild(row);
foreach (ReportDS.BOLLE_VENDITARow bolla in ds.BOLLE_VENDITA)
{
Row rowDati = new Row();
rowDati.Append(ConstructCell(bolla.IsAZIENDANull() ? string.Empty : bolla.AZIENDA, CellValues.String, 1));
rowDati.Append(ConstructCell(string.Format("{0}-{1}", bolla.CODICETIPOO, bolla.DESTABTIPOO), CellValues.String, 1));
rowDati.Append(ConstructCell(string.Format("{0}-{1}", bolla.CODICECAUTR, bolla.DESTABCAUTR), CellValues.String, 1));
rowDati.Append(ConstructCell(bolla.FULLNUMDOC, CellValues.String, 1));
rowDati.Append(ConstructCell(bolla.DATDOC.ToString("dd/MM/yyyy"), CellValues.String, 1));
rowDati.Append(ConstructCell(bolla.NUMDOC, CellValues.String, 1));
rowDati.Append(ConstructCell(bolla.IsSEGNALATORE_RSNull() ? string.Empty : bolla.SEGNALATORE_RS.Trim(), CellValues.String, 1));
rowDati.Append(ConstructCell(bolla.RAGIONESOC.Trim(), CellValues.String, 1));
rowDati.Append(ConstructCell(bolla.NRRIGA, CellValues.String, 1));
rowDati.Append(ConstructCell(bolla.MODELLO, CellValues.String, 1));
rowDati.Append(ConstructCell(bolla.QTATOT.ToString().Replace(',', '.'), CellValues.Number, 1));
rowDati.Append(ConstructCell(bolla.PREZZOTOT.ToString().Replace(',', '.'), CellValues.Number, 1));
rowDati.Append(ConstructCell(bolla.VALORE.ToString().Replace(',', '.'), CellValues.Number, 1));
rowDati.Append(ConstructCell(bolla.IsFULLNUMDOC_OCNull() ? string.Empty : bolla.FULLNUMDOC_OC, CellValues.String, 1));
rowDati.Append(ConstructCell(bolla.IsDATDOC_OCNull() ? string.Empty : bolla.DATDOC_OC.ToString("dd/MM/yyyy"), CellValues.String, 1));
rowDati.Append(ConstructCell(bolla.IsDATA_RICHIESTANull() ? string.Empty : bolla.DATA_RICHIESTA.ToString("dd/MM/yyyy"), CellValues.String, 1));
rowDati.Append(ConstructCell(bolla.IsDATA_CONFERMANull() ? string.Empty : bolla.DATA_CONFERMA.ToString("dd/MM/yyyy"), CellValues.String, 1));
rowDati.Append(ConstructCell(bolla.IsRIFERIMENTONull() ? string.Empty : bolla.RIFERIMENTO, CellValues.String, 1));
sheetData.AppendChild(rowDati);
}
workbookPart.Workbook.Save();
document.Save();
document.Close();
ms.Seek(0, SeekOrigin.Begin);
content = ms.ToArray();
}
return(content);
}
public void Main()
{
string user, password, path;
// クラウドサービスのリストを取得する
List <string> listCloudService = new List <string>();
int index = -1;
string systemPath = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location).ToString();
if (!(File.Exists(systemPath + "\\CloudServices_Information\\CloudServices.xlsx")))
{
//log.WriteLog("ファイルエクセルクラウドサービス情報が存在しません", null);
Console.WriteLine("ファイルエクセルクラウドサービス情報が存在しません");
return;
}
// Excelファイル情報クラウドサービスへのパス
path = systemPath + "\\CloudServices_Information\\CloudServices.xlsx";
SpreadsheetDocument doc = SpreadsheetDocument.Open(path, false);
WorkbookPart workbookPart = doc.WorkbookPart;
Worksheet theWorksheet = ((WorksheetPart)workbookPart.GetPartById("rId1")).Worksheet;
SheetData thesheetdata = theWorksheet.GetFirstChild <SheetData>();
var lastRow = thesheetdata.Descendants <Row>().LastOrDefault();
var lastRowToInt = Int32.Parse(lastRow.RowIndex);
Cell theCellUser = (Cell)thesheetdata.ElementAt(1).ChildElements.ElementAt(4);
SharedStringItem textUser = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(Int32.Parse(theCellUser.InnerText));
user = textUser.Text.Text;
Cell theCellPassword = (Cell)thesheetdata.ElementAt(1).ChildElements.ElementAt(5);
SharedStringItem textPassword = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(Int32.Parse(theCellPassword.InnerText));
password = textPassword.Text.Text;
// クラウドサービスのステータスのリストを取得する
for (int i = 1; i < lastRowToInt - 1; i++)
{
Cell theCellCheck = (Cell)thesheetdata.ElementAt(i).ChildElements.ElementAt(1);
Int32.TryParse(theCellCheck.InnerText, out index);
string textCheck = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(index).InnerText;
if ((String.Compare("〇", textCheck)) == 0)
{
Cell theCellCloudService = (Cell)thesheetdata.ElementAt(i).ChildElements.ElementAt(2);
SharedStringItem textCloudService = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(Int32.Parse(theCellCloudService.InnerText));
listCloudService.Add(textCloudService.Text.Text);
}
}
var stateStoping = "stop";
MyTestCaseTest test_case = new MyTestCaseTest();
test_case.SetUp();
test_case.StartAzure(user, password);
for (int i = 0; i < listCloudService.Count; i++)
{
if (test_case.TestCases(listCloudService[i], stateStoping) == true)
{
//log.WriteLog(stateStoping + " " + listCloudService[i], "OK");
Console.WriteLine(stateStoping + " " + listCloudService[i] + " " + "OK");
}
else
{
//log.WriteLog(stateStoping + " " + listCloudService[i], "ERROR");
Console.WriteLine(stateStoping + " " + listCloudService[i] + " " + "ERROR");
}
}
test_case.TearDown();
}
public byte[] ReportQuantitaExcel(ReportDS ds)
{
byte[] content;
MemoryStream ms = new MemoryStream();
using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
// Adding style
WorkbookStylesPart stylePart = workbookPart.AddNewPart <WorkbookStylesPart>();
stylePart.Stylesheet = GenerateStylesheet();
stylePart.Stylesheet.Save();
int numeroColonne = 4;
Columns columns = new Columns();
for (int i = 0; i < numeroColonne; i++)
{
Column c = new Column();
UInt32Value u = new UInt32Value((uint)(i + 1));
c.Min = u;
c.Max = u;
c.Width = 15;
columns.Append(c);
}
worksheetPart.Worksheet.AppendChild(columns);
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Report"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Constructing header
Row row = new Row();
row.Append(ConstructCell("Codice", CellValues.String, 2));
row.Append(ConstructCell("Ragione Soc", CellValues.String, 2));
row.Append(ConstructCell("Somma", CellValues.String, 2));
row.Append(ConstructCell("Percentuale", CellValues.String, 2));
sheetData.AppendChild(row);
foreach (ReportDS.REPORTQUANTITARow reportquantita in ds.REPORTQUANTITA)
{
Row rowDati = new Row();
rowDati.Append(ConstructCell(reportquantita.CODICECLIFO, CellValues.String, 1));
rowDati.Append(ConstructCell(reportquantita.RAGIONESOC, CellValues.String, 1));
rowDati.Append(ConstructCell(reportquantita.SOMMA.ToString(), CellValues.String, 1));
rowDati.Append(ConstructCell(reportquantita.PERC.ToString(), CellValues.String, 1));
sheetData.AppendChild(rowDati);
}
workbookPart.Workbook.Save();
document.Save();
document.Close();
ms.Seek(0, SeekOrigin.Begin);
content = ms.ToArray();
}
return(content);
}
private string FailedDataToExcel(DataTable dt, List <string> errMsgs)
{
// set file name to include the current date and time
var currentTime = DateTime.Now.ToString("dd-MM-yyyy_HH-mm-ss");
var fileName = "failed_" + currentTime + ".xlsx";
var filePath = Path.Combine(_hostingEnvironment.WebRootPath, "failed_" + currentTime + ".xlsx");
using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
{
// create woorkbookpart
WorkbookPart workbookPart = spreadSheetDocument.AddWorkbookPart();
//workbookPart.Workbook = new Workbook();
spreadSheetDocument.WorkbookPart.Workbook = new Workbook();
spreadSheetDocument.WorkbookPart.Workbook.Sheets = new Sheets();
//create worksheetPart
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
SheetData sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
// create sheet
Sheets sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild <Sheets>();
Sheet sheet = new Sheet()
{
Id = spreadSheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Failed entries"
};
sheets.Append(sheet);
Row headers = new Row();
// first get headers and store in a row
foreach (DataColumn col in dt.Columns)
{
Cell cell = new Cell();
cell.DataType = CellValues.String; // just store as string
cell.CellValue = new CellValue(col.ColumnName); // get column headers for each column in datatable
headers.Append(cell); // add the cell to headers
}
// add error messages header
Cell errorCell = new Cell();
errorCell.DataType = CellValues.String;
errorCell.CellValue = new CellValue("Error message");
headers.Append(errorCell);
sheetData.AppendChild(headers); // add the headers to the sheet
// enumerator for error messages
IEnumerator <string> errMsgEnumerator = errMsgs.GetEnumerator();
foreach (DataRow dtRow in dt.Rows)
{
Row row = new Row(); // this is the spreadsheet row that will be stored in sheetData
foreach (var item in dtRow.ItemArray)
{
Cell cell = new Cell();
cell.DataType = CellValues.String; // just storing as string
cell.CellValue = new CellValue(item.ToString());
row.Append(cell); // add the cell to the row
}
// set errMsg enumerator to next and then get current value and add to row.
errMsgEnumerator.MoveNext();
Cell errCell = new Cell();
errCell.DataType = CellValues.String;
errCell.CellValue = new CellValue(errMsgEnumerator.Current);
row.Append(errCell);
sheetData.AppendChild(row); // add row to the sheet data
}
}
// return the filename so user can download
return(fileName);
}
public byte[] ReportOrdiniAttiviExcel(List <OrdiniAttiviModel> lista)
{
byte[] content;
MemoryStream ms = new MemoryStream();
using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
// Adding style
WorkbookStylesPart stylePart = workbookPart.AddNewPart <WorkbookStylesPart>();
stylePart.Stylesheet = GenerateStylesheet();
stylePart.Stylesheet.Save();
int numeroColonne = 12;
Columns columns = new Columns();
for (int i = 0; i < numeroColonne; i++)
{
Column c = new Column();
UInt32Value u = new UInt32Value((uint)(i + 1));
c.Min = u;
c.Max = u;
c.Width = 15;
columns.Append(c);
}
worksheetPart.Worksheet.AppendChild(columns);
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Report"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Constructing header
Row row = new Row();
row.Append(ConstructCell("Segnalatore", CellValues.String, 2));
row.Append(ConstructCell("Quantità", CellValues.String, 2));
row.Append(ConstructCell("Quantità non spedita", CellValues.String, 2));
row.Append(ConstructCell("Quantità scaduta", CellValues.String, 2));
row.Append(ConstructCell("Quantità annullata", CellValues.String, 2));
row.Append(ConstructCell("Valore", CellValues.String, 2));
row.Append(ConstructCell("Valore non spedito", CellValues.String, 2));
row.Append(ConstructCell("Valore scaduto", CellValues.String, 2));
row.Append(ConstructCell("Valore annullato", CellValues.String, 2));
row.Append(ConstructCell("% scaduto su cliente", CellValues.String, 2));
row.Append(ConstructCell("% scaduto su totale", CellValues.String, 2));
sheetData.AppendChild(row);
foreach (OrdiniAttiviModel reportquantita in lista)
{
Row rowDati = new Row();
rowDati.Append(ConstructCell(reportquantita.Cliente, CellValues.String, 1));
rowDati.Append(ConstructCell(reportquantita.Quantita.ToString(), CellValues.String, 1));
rowDati.Append(ConstructCell(reportquantita.QuantitaNonSpedita.ToString(), CellValues.String, 1));
rowDati.Append(ConstructCell(reportquantita.QuantitaScaduta.ToString(), CellValues.String, 1));
rowDati.Append(ConstructCell(reportquantita.QuantitaAnnullata.ToString(), CellValues.String, 1));
rowDati.Append(ConstructCell(reportquantita.Valore.ToString(), CellValues.String, 1));
rowDati.Append(ConstructCell(reportquantita.ValoreNonSpedito.ToString(), CellValues.String, 1));
rowDati.Append(ConstructCell(reportquantita.ValoreScaduto.ToString(), CellValues.String, 1));
rowDati.Append(ConstructCell(reportquantita.ValoreAnnullato.ToString(), CellValues.String, 1));
rowDati.Append(ConstructCell(reportquantita.PercScadutoCliente.ToString(), CellValues.String, 1));
rowDati.Append(ConstructCell(reportquantita.PercScadutoSulTotale.ToString(), CellValues.String, 1));
sheetData.AppendChild(rowDati);
}
workbookPart.Workbook.Save();
document.Save();
document.Close();
ms.Seek(0, SeekOrigin.Begin);
content = ms.ToArray();
}
return(content);
}
static void WritePlainExcel(ResultTable results, Stream stream, string title)
{
stream.WriteAllBytes(Template);
if (results == null)
{
throw new ApplicationException(ExcelMessage.ThereAreNoResultsToWrite.NiceToString());
}
using (SpreadsheetDocument document = SpreadsheetDocument.Open(stream, true))
{
document.PackageProperties.Creator = "";
document.PackageProperties.LastModifiedBy = "";
WorkbookPart workbookPart = document.WorkbookPart;
WorksheetPart worksheetPart = document.GetWorksheetPartById("rId1");
worksheetPart.Worksheet = new Worksheet();
worksheetPart.Worksheet.Append(new Columns(results.Columns.Select((c, i) => new spreadsheet.Column()
{
Min = (uint)i + 1,
Max = (uint)i + 1,
Width = GetColumnWidth(c.Column.Type),
BestFit = true,
CustomWidth = true
}).ToArray()));
Dictionary <ResultColumn, (DefaultStyle defaultStyle, UInt32Value styleIndex)> indexes =
results.Columns.ToDictionary(c => c, c => CellBuilder.GetDefaultStyleAndIndex(c));
var ss = document.WorkbookPart.WorkbookStylesPart.Stylesheet;
{
var maxIndex = ss.NumberingFormats.ChildElements.Cast <NumberingFormat>()
.Max(f => (uint)f.NumberFormatId) + 1;
var decimalCellFormat = ss.CellFormats.ElementAt((int)(uint)CellBuilder.DefaultStyles[DefaultStyle.Decimal]);
foreach (var kvp in CellBuilder.CustomDecimalStyles)
{
var numberingFormat = new NumberingFormat
{
NumberFormatId = maxIndex++,
FormatCode = kvp.Key
};
ss.NumberingFormats.AppendChild(numberingFormat);
var cellFormat = (CellFormat)decimalCellFormat.CloneNode(false);
cellFormat.NumberFormatId = numberingFormat.NumberFormatId;
ss.CellFormats.AppendChild(cellFormat);
ss.CellFormats.Count = (uint)ss.CellFormats.ChildElements.Count;
if (ss.CellFormats.Count != kvp.Value + 1)
{
throw new InvalidOperationException("Unexpected CellFormats count");
}
}
}
worksheetPart.Worksheet.Append(new Sequence <Row>()
{
new [] { CellBuilder.Cell(title, DefaultStyle.Title) }.ToRow(),
(from c in results.Columns
select CellBuilder.Cell(c.Column.DisplayName, DefaultStyle.Header)).ToRow(),
from r in results.Rows
select(from c in results.Columns
let t = indexes.GetOrThrow(c)
select CellBuilder.Cell(r[c], t.defaultStyle, t.styleIndex)).ToRow()
}.ToSheetData());
workbookPart.Workbook.Save();
document.Close();
}
}
/// <summary>
/// Ctor
/// </summary>
/// <param name="xlsContext">The excel context object</param>
public WorkbookMapping(ExcelContext xlsContext, WorkbookPart workbookPart)
: base(workbookPart.XmlWriter)
{
this._xlsContext = xlsContext;
this._workbookPart = workbookPart;
}
public void Save(string path, string testName, List <ExtendedResultSheetItem> results)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
FileVersion fv = new FileVersion();
fv.ApplicationName = "Microsoft Office Excel";
worksheetPart.Worksheet = new Worksheet(new SheetData());
WorkbookStylesPart wbsp = workbookPart.AddNewPart <WorkbookStylesPart>();
//Styles
wbsp.Stylesheet = GenerateStyleSheet();
wbsp.Stylesheet.Save();
// Задаем колонки и их ширину
Columns lstColumns = worksheetPart.Worksheet.GetFirstChild <Columns>();
Boolean needToInsertColumns = false;
if (lstColumns == null)
{
lstColumns = new Columns();
needToInsertColumns = true;
}
lstColumns.Append(new Column()
{
Min = 1, Max = 10, Width = 20, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 2, Max = 10, Width = 20, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 3, Max = 10, Width = 20, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 4, Max = 10, Width = 20, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 5, Max = 10, Width = 20, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 6, Max = 10, Width = 20, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 7, Max = 10, Width = 20, CustomWidth = true
});
if (needToInsertColumns)
{
worksheetPart.Worksheet.InsertAt(lstColumns, 0);
}
//Создаем лист в книге
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = testName
};
sheets.Append(sheet);
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
//Question headers
Row row = new Row()
{
RowIndex = 1
};
sheetData.Append(row);
InsertCell(row, 1, "Номер", CellValues.String, 0);
InsertCell(row, 2, "Имя/Фамилия", CellValues.String, 0);
InsertCell(row, 3, "Дата", CellValues.String, 0);
InsertCell(row, 4, "Время выполнения", CellValues.String, 0);
InsertCell(row, 5, "Оценка", CellValues.String, 0);
int colIndex = 6;
Dictionary <string, int> questionTitles = new Dictionary <string, int>();
foreach (var result in results)
{
foreach (var extResult in result.ExtendedResult)
{
if (!questionTitles.ContainsKey(extResult.QuestionName))
{
questionTitles.Add(extResult.QuestionName, colIndex);
InsertCell(row, colIndex++, ReplaceHexadecimalSymbols(extResult.QuestionName), CellValues.String, 0);
}
}
}
//Results
uint rowIndex = 2;
foreach (var result in results)
{
row = new Row()
{
RowIndex = rowIndex++
};
sheetData.Append(row);
InsertCell(row, 1, ReplaceHexadecimalSymbols(result.id.ToString()), CellValues.Number, 1);
InsertCell(row, 2, ReplaceHexadecimalSymbols(result.NameSurname), CellValues.String, 0);
InsertCell(row, 3, ReplaceHexadecimalSymbols(result.PassDate.ToString("dd.MM.yyyy")), CellValues.String, 2);
InsertCell(row, 4, ReplaceHexadecimalSymbols(result.PassingTime.ToString("hh:mm:ss")), CellValues.String, 3);
InsertCell(row, 5, ReplaceHexadecimalSymbols(result.Mark.ToString()), CellValues.Number, (uint)(result.Mark < 4 ? 5 : 4));
foreach (var extResult in result.ExtendedResult)
{
InsertCell(row, questionTitles[extResult.QuestionName], extResult.IsRight ? ReplaceHexadecimalSymbols(extResult.Question_score.ToString()) : "0", CellValues.Number, (uint)(extResult.IsRight ? 4 : 5));
}
}
workbookPart.Workbook.Save();
document.Close();
MessageBox.Show("Успешно сохранено", "Вывод в Excel", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
public string AddSheetWithChart(string TemplateSheetName, List <List <object> > ChartData, string[] SeriesLabels, Dictionary <String, String> ReplacementDict)
{
//Assume error
string result = "Error Creating Chart";
WSheetName = TemplateSheetName;
result = CopyFile(templatefilepath + "ExcelTemplate.xlsx", resultfilepath + "CloudReport1.xlsx");
try
{
using (SpreadsheetDocument document = SpreadsheetDocument.Open(resultfilepath + "CloudReport1.xlsx", true))
{
//create clone of template chart sheet as TemplateSheetName
CloneSheet(document, "TemplateChartSheet", WSheetName);
}
using (SpreadsheetDocument document = SpreadsheetDocument.Open(resultfilepath + "CloudReport1.xlsx", true))
{
IEnumerable <Sheet> sheets = document.WorkbookPart.Workbook.Descendants <Sheet>().Where(s => s.Name == WSheetName);
if (sheets.Count() == 0)
{
return(result = "No worksheet found named as " + WSheetName);
}
else
{
WorkbookPart workbookPart = document.WorkbookPart;
int numOfRowsToAdd = ChartData.Count();
//currentRowNum = 3;
//Char currentColumn;
// inserting Chart data in excel sheet rows
//foreach (List<Object> rowitem in ChartData)
//{
// //this is a line
// currentColumn = 'B';
// foreach (var obj in rowitem)
// {
//inserted values are NOT of type string
// UpdateValue(workbookPart, WSheetName, currentColumn + currentRowNum.ToString(), obj.ToString(), 3, false);
// currentColumn++;
// }
// currentRowNum++;
//}
// update chart part to reflect the new data
Sheet sheet = workbookPart.Workbook.Descendants <Sheet>().Where((s) => s.Name == WSheetName).FirstOrDefault();
WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
ChartPart part = worksheetPart.DrawingsPart.ChartParts.First();
XElement XmlChart;
using (XmlReader xmlr = XmlReader.Create(part.GetStream()))
{
XmlChart = XElement.Load(xmlr);
}
XElement barChartData = XmlChart.Descendants(ns_c + "barChart").Single();
List <XElement> elements = new List <XElement>();
elements.AddRange(barChartData.Elements().Take(ChartData.Count()));
// these 2 values are hard coded for now
Char startColChar = 'B';
int currentRowNum = 3;
// Char endColChar = 'B';
int colCount = 0;
int m = 0;
foreach (List <object> rowitem in ChartData)
{
Char endColChar = 'B';
colCount = rowitem.Count();
while (colCount > 1)
{
endColChar++;
colCount--;
}
XElement ser = new XElement(ns_c + "ser");
ser.Add(new XElement(ns_c + "idx", new XAttribute("val", m)));
ser.Add(new XElement(ns_c + "order", new XAttribute("val", m)));
ser.Add(new XElement(ns_c + "tx", new XElement(ns_c + "v", new XText(SeriesLabels[m].ToString()))));
m++;
XElement cat = new XElement(ns_c + "cat");
XElement strRef = new XElement(ns_c + "strRef");
cat.Add(strRef);
strRef.Add(new XElement(ns_c + "f", new XText("'" + WSheetName + "'!$" + "B" + "$2:$" + endColChar + "$2")));
//XElement strCache = new XElement(ns_c + "strCache");
//strRef.Add(strCache);
//strCache.Add(new XElement(ns_c + "ptCount", new XAttribute("val", rowitem.Count())));
//int j = 0;
//foreach (var obj in rowitem)
//{
// strCache.Add(new XElement(ns_c + "pt",
// new XAttribute("idx", j),
// new XElement(ns_s + "v", new XText(obj.ToString()))));
// j++;
//}
XElement val = new XElement(ns_c + "val");
XElement numRef = new XElement(ns_c + "numRef");
val.Add(numRef);
numRef.Add(new XElement(ns_c + "f", new XText("'" + WSheetName + "'!$" + startColChar + "$" + currentRowNum + ":$" + endColChar + "$" + currentRowNum)));
XElement numCache = new XElement(ns_c + "numCache");
numRef.Add(numCache);
//numCache.Add(new XElement(ns_c + "formatCode", new XText(""$"#,##0_);\("$"#,##0\)");
numCache.Add(new XElement(ns_c + "ptCount", new XAttribute("val", rowitem.Count())));
int k = 0;
foreach (var obj in rowitem)
{
numCache.Add(new XElement(ns_c + "pt",
new XAttribute("idx", k),
new XElement(ns_s + "v", new XText(obj.ToString()))));
k++;
}
ser.Add(cat);
ser.Add(val);
elements.Add(ser);
currentRowNum++;
}
//Now we have all elements
barChartData.Elements().Remove();
barChartData.Add(elements);
using (Stream s = part.GetStream(FileMode.Create, FileAccess.Write))
{
using (XmlWriter xmlw = XmlWriter.Create(s))
{
XmlChart.WriteTo(xmlw);
}
}
result = "Chart updated";
////save data in the sheet
//currentRowNum = 3;
//Char currentColumn;
//// inserting Chart data in excel sheet rows
//foreach (List<Object> rowitem in ChartData)
//{
// //this is a line
// currentColumn = 'B';
// foreach (var obj in rowitem)
// {
// // inserted values are NOT of type string
// UpdateValue(workbookPart, WSheetName, currentColumn + currentRowNum.ToString(), obj.ToString(), 3, false);
// currentColumn++;
// }
// currentRowNum++;
//}
using (XmlReader xmlr = XmlReader.Create(worksheetPart.GetStream()))
{
XmlData = XElement.Load(xmlr);
}
XElement sheetData = XmlData.Descendants(ns_s + "sheetData").Single();
//now we have a series of row, first of all build the new data
List <XElement> wselements = new List <XElement>();
wselements.AddRange(sheetData.Elements().Take(ChartData.Count()));
//Int32 currentRowNum = numOfRowsToSkip + 1;
Char currentColumn;
currentRowNum = 1;
foreach (List <Object> rowitem in ChartData)
{
currentColumn = 'B';
XElement row = new XElement(ns_s + "row",
new XAttribute(ns_s + "r", currentRowNum),
new XAttribute(ns_s + "spans", "1:" + rowitem.Count));
int k = 0;
foreach (var obj in rowitem)
{
row.Add(new XElement(ns_s + "c",
new XAttribute(ns_s + "r", currentColumn + currentRowNum),
new XElement(ns_s + "v", new XText(obj.ToString()))));
currentColumn++;
}
wselements.Add(row);
currentRowNum++;
}
//Now we have all elements
sheetData.Elements().Remove();
sheetData.Add(wselements);
using (Stream s = worksheetPart.GetStream(FileMode.Create, FileAccess.Write))
{
using (XmlWriter xmlw = XmlWriter.Create(s))
{
XmlData.WriteTo(xmlw);
}
}
}
//// Replace [Tag Name] by “Tag Value” in a worksheet
foreach (KeyValuePair <string, string> item in ReplacementDict)
{
string tagname = item.Key;
string tagvalue = item.Value;
WorkbookPart workbookPart = document.WorkbookPart;
SharedStringTablePart sharedStringsPart = workbookPart.SharedStringTablePart;
IEnumerable <DocumentFormat.OpenXml.Spreadsheet.Text> sharedStringTextElements = sharedStringsPart.SharedStringTable.Descendants <DocumentFormat.OpenXml.Spreadsheet.Text>();
DoReplace(sharedStringTextElements, tagname, tagvalue);
IEnumerable <WorksheetPart> worksheetParts = workbookPart.GetPartsOfType <WorksheetPart>();
foreach (var worksheet in worksheetParts)
{
var allTextElements = worksheet.Worksheet.Descendants <DocumentFormat.OpenXml.Spreadsheet.Text>();
DoReplace(allTextElements, tagname, tagvalue);
}
}
}
}
catch (Exception ex)
{
result = ex.Message;
}
return(result);
}
public ActionResult ConvertToExcel()
{
MemoryStream ms = new MemoryStream();
SpreadsheetDocument xl = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook);
WorkbookPart wbp = xl.AddWorkbookPart();
WorksheetPart wsp = wbp.AddNewPart <WorksheetPart>();
Workbook wb = new Workbook();
FileVersion fv = new FileVersion();
fv.ApplicationName = "Microsoft Office Excel";
Worksheet ws = new Worksheet();
SheetData sd = new SheetData();
var list = GetPerson();
Row r1 = new Row();
Cell c1 = new Cell();
Cell c2 = new Cell();
Cell c3 = new Cell();
Cell c4 = new Cell();
c1.DataType = CellValues.String;
c2.DataType = CellValues.String;
c3.DataType = CellValues.String;
c4.DataType = CellValues.String;
c1.CellValue = new CellValue("Ad");
c2.CellValue = new CellValue("Soyad");
c3.CellValue = new CellValue("Adres");
c4.CellValue = new CellValue("Email");
r1.Append(c1);
r1.Append(c2);
r1.Append(c3);
r1.Append(c4);
sd.Append(r1);
foreach (var item in list.Persons)
{
Row r2 = new Row();
Cell c5 = new Cell();
Cell c6 = new Cell();
Cell c7 = new Cell();
Cell c8 = new Cell();
c5.DataType = CellValues.String;
c6.DataType = CellValues.String;
c7.DataType = CellValues.String;
c8.DataType = CellValues.String;
c5.CellValue = new CellValue(item.Name);
c6.CellValue = new CellValue(item.Surname);
c7.CellValue = new CellValue(item.Address);
c8.CellValue = new CellValue(item.Email);
r2.Append(c5);
r2.Append(c6);
r2.Append(c7);
r2.Append(c8);
sd.Append(r2);
}
ws.Append(sd);
wsp.Worksheet = ws;
wsp.Worksheet.Save();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet();
sheet.Name = "first sheet";
sheet.SheetId = 1;
sheet.Id = wbp.GetIdOfPart(wsp);
sheets.Append(sheet);
wb.Append(fv);
wb.Append(sheets);
xl.WorkbookPart.Workbook = wb;
xl.WorkbookPart.Workbook.Save();
xl.Close();
string fileName = "testOpenXml.xlsx";
Response.Clear();
byte[] dt = ms.ToArray();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", fileName));
Response.BinaryWrite(dt);
Response.Flush();
Response.End();
return(View());
}
// USed to read the value of cell
public string XLGetCellValue(WorkbookPart wbPart, string sheetName, string addressName)
{
string value = null;
// Find the sheet with the supplied name, and then use that Sheet object
// to retrieve a reference to the appropriate worksheet.
Sheet theSheet = wbPart.Workbook.Descendants <Sheet>().
Where(s => s.Name == sheetName).FirstOrDefault();
if (theSheet == null)
{
throw new ArgumentException("sheetName");
}
// Retrieve a reference to the worksheet part, and then use its Worksheet property to get
// a reference to the cell whose address matches the address you've supplied:
WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
Cell theCell = wsPart.Worksheet.Descendants <Cell>().
Where(c => c.CellReference == addressName).FirstOrDefault();
// If the cell doesn't exist, return an empty string:
if (theCell != null)
{
value = theCell.InnerText;
// If the cell represents an integer number, you're done.
// For dates, this code returns the serialized value that
// represents the date. The code handles strings and booleans
// individually. For shared strings, the code looks up the corresponding
// value in the shared string table. For booleans, the code converts
// the value into the words TRUE or FALSE.
if (theCell.DataType != null)
{
switch (theCell.DataType.Value)
{
case CellValues.SharedString:
// For shared strings, look up the value in the shared strings table.
var stringTable = wbPart.GetPartsOfType <SharedStringTablePart>().FirstOrDefault();
// If the shared string table is missing, something's wrong.
// Just return the index that you found in the cell.
// Otherwise, look up the correct text in the table.
if (stringTable != null)
{
value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;
}
break;
case CellValues.Boolean:
switch (value)
{
case "0":
value = "FALSE";
break;
default:
value = "TRUE";
break;
}
break;
}
}
}
return(value);
}
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();
}
}
/// <summary>
/// Copy sheet to another document
/// </summary>
/// <param name="sourceDoc">Source document</param>
/// <param name="srcSheetName">Name of source sheet</param>
/// <param name="targetDoc">Spreadsheet document to copied</param>
/// <param name="targetIndex">Index of copied sheet in target document</param>
public static void CopyWorksheet(SpreadsheetDocument sourceDoc, String srcSheetName, SpreadsheetDocument targetDoc, uint targetIndex)
{
// Locate the source sheet
if (sourceDoc.WorkbookPart == null)
{
throw new InvalidOperationException("WorkbookPart is not exist in sourceDoc!");
}
if (sourceDoc.WorkbookPart.Workbook.Sheets == null)
{
throw new InvalidOperationException("No sheets exist in sourceDoc!");
}
var srcSheet =
sourceDoc.WorkbookPart.Workbook.Sheets.Descendants <Sheet>()
.FirstOrDefault(a => a.Name == srcSheetName);
if (srcSheet == null)
{
throw new InvalidOperationException(String.Format("No sheet found with name {0}!", srcSheetName));
}
var srcSheetPart = sourceDoc.WorkbookPart.GetPartById(srcSheet.Id) as WorksheetPart;
if (srcSheetPart == null)
{
throw new InvalidOperationException(String.Format("Cannot find worksheet part with Id {0}!", srcSheet.Id));
}
var srcWorkSheet = srcSheetPart.Worksheet;
if (srcWorkSheet == null)
{
throw new InvalidOperationException("Worksheet not exist in source worksheet part!");
}
// Locate the position of target sheet
WorkbookPart tgtWbPart = targetDoc.WorkbookPart ?? targetDoc.AddWorkbookPart();
Sheets tgtSheets = tgtWbPart.Workbook.Sheets ?? tgtWbPart.Workbook.AppendChild <Sheets>(new Sheets());
if (targetIndex > tgtSheets.Count())
{
targetIndex = (uint)tgtSheets.Count();
}
// Create a new worksheet and clone data from original worksheet
var newSheetPart = tgtWbPart.AddNewPart <WorksheetPart>();
newSheetPart.Worksheet = new Worksheet(); //srcWorkSheet.Clone() as Worksheet;
// Create a unique ID for the new worksheet.
uint sheetId = 1;
if (tgtSheets.Elements <Sheet>().Any())
{
sheetId = tgtSheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
// Add cloned worksheet to target workbook
var newSheet = new Sheet()
{
Id = tgtWbPart.GetIdOfPart(newSheetPart),
SheetId = sheetId,
Name = srcSheet.Name
};
tgtSheets.InsertAt(newSheet, (int)targetIndex);
// Import data from source sheet to target sheet
ImportWorksheet(sourceDoc, srcWorkSheet, targetDoc, newSheetPart.Worksheet);
// Import all necessary resources into target document that referenced by cloned sheet
//ImportResources(sourceDoc, newSheetPart.Worksheet, targetDoc);
// Save it
tgtWbPart.Workbook.Save();
}
private static void CreateStyles(WorkbookPart workbookpart)
{
WorkbookStylesPart sp = workbookpart.AddNewPart <WorkbookStylesPart>();
sp.Stylesheet = new Stylesheet();
Fonts fonts = new Fonts()
{
Count = (UInt32Value)2U, KnownFonts = true
};
Font fontUsual = new Font();
fontUsual.Append(new FontSize()
{
Val = 12D
});
fontUsual.Append(new DocumentFormat.OpenXml.Office2010.Excel.Color()
{
Theme = (UInt32Value)1U
});
fontUsual.Append(new FontName()
{
Val = "Times New Roman"
});
fontUsual.Append(new FontFamilyNumbering()
{
Val = 2
});
fontUsual.Append(new FontScheme()
{
Val = FontSchemeValues.Minor
});
Font fontTitle = new Font();
fontTitle.Append(new Bold());
fontTitle.Append(new FontSize()
{
Val = 14D
});
fontTitle.Append(new DocumentFormat.OpenXml.Office2010.Excel.Color()
{
Theme = (UInt32Value)1U
});
fontTitle.Append(new FontName()
{
Val = "Times New Roman"
});
fontTitle.Append(new FontFamilyNumbering()
{
Val = 2
});
fontTitle.Append(new FontScheme()
{
Val = FontSchemeValues.Minor
});
fonts.Append(fontUsual);
fonts.Append(fontTitle);
Fills fills = new Fills()
{
Count = (UInt32Value)2U
};
Fill fill1 = new Fill();
fill1.Append(new PatternFill()
{
PatternType = PatternValues.None
});
Fill fill2 = new Fill();
fill2.Append(new PatternFill()
{
PatternType = PatternValues.Gray125
});
fills.Append(fill1);
fills.Append(fill2);
Borders borders = new Borders()
{
Count = (UInt32Value)2U
};
Border borderNoBorder = new Border();
borderNoBorder.Append(new LeftBorder());
borderNoBorder.Append(new RightBorder());
borderNoBorder.Append(new TopBorder());
borderNoBorder.Append(new BottomBorder());
borderNoBorder.Append(new DiagonalBorder());
Border borderThin = new Border();
LeftBorder leftBorder = new LeftBorder()
{
Style = BorderStyleValues.Thin
};
leftBorder.Append(new DocumentFormat.OpenXml.Office2010.Excel.Color()
{
Indexed = (UInt32Value)64U
});
RightBorder rightBorder = new RightBorder()
{
Style = BorderStyleValues.Thin
};
rightBorder.Append(new DocumentFormat.OpenXml.Office2010.Excel.Color()
{
Indexed = (UInt32Value)64U
});
TopBorder topBorder = new TopBorder()
{
Style = BorderStyleValues.Thin
};
topBorder.Append(new DocumentFormat.OpenXml.Office2010.Excel.Color()
{
Indexed = (UInt32Value)64U
});
BottomBorder bottomBorder = new BottomBorder()
{
Style = BorderStyleValues.Thin
};
bottomBorder.Append(new DocumentFormat.OpenXml.Office2010.Excel.Color()
{
Indexed = (UInt32Value)64U
});
borderThin.Append(leftBorder);
borderThin.Append(rightBorder);
borderThin.Append(topBorder);
borderThin.Append(bottomBorder);
borderThin.Append(new DiagonalBorder());
borders.Append(borderNoBorder);
borders.Append(borderThin);
CellStyleFormats cellStyleFormats = new CellStyleFormats()
{
Count = (UInt32Value)1U
};
CellFormat cellFormatStyle = new CellFormat()
{
NumberFormatId = (UInt32Value)0U,
FontId = (UInt32Value)0U,
FillId = (UInt32Value)0U,
BorderId = (UInt32Value)0U
};
cellStyleFormats.Append(cellFormatStyle);
CellFormats cellFormats = new CellFormats()
{
Count = (UInt32Value)3U
};
CellFormat cellFormatFont = new CellFormat()
{
NumberFormatId = (UInt32Value)0U,
FontId = (UInt32Value)0U,
FillId = (UInt32Value)0U,
BorderId = (UInt32Value)0U,
FormatId = (UInt32Value)0U,
ApplyFont = true
};
CellFormat cellFormatFontAndBorder = new CellFormat()
{
NumberFormatId = (UInt32Value)0U,
FontId = (UInt32Value)0U,
FillId = (UInt32Value)0U,
BorderId = (UInt32Value)1U,
FormatId = (UInt32Value)0U,
ApplyFont = true,
ApplyBorder = true
};
CellFormat cellFormatTitle = new CellFormat()
{
NumberFormatId = (UInt32Value)0U,
FontId = (UInt32Value)1U,
FillId = (UInt32Value)0U,
BorderId = (UInt32Value)0U,
FormatId = (UInt32Value)0U,
Alignment = new Alignment()
{
Vertical = VerticalAlignmentValues.Center,
WrapText = true,
Horizontal = HorizontalAlignmentValues.Center
},
ApplyFont = true
};
cellFormats.Append(cellFormatFont);
cellFormats.Append(cellFormatFontAndBorder);
cellFormats.Append(cellFormatTitle);
CellStyles cellStyles = new CellStyles()
{
Count = (UInt32Value)1U
};
cellStyles.Append(new CellStyle()
{
Name = "Normal",
FormatId = (UInt32Value)0U,
BuiltinId = (UInt32Value)0U
});
DocumentFormat.OpenXml.Office2013.Excel.DifferentialFormats
differentialFormats = new DocumentFormat.OpenXml.Office2013.Excel.DifferentialFormats()
{
Count = (UInt32Value)0U
};
TableStyles tableStyles = new TableStyles()
{
Count = (UInt32Value)0U,
DefaultTableStyle = "TableStyleMedium2",
DefaultPivotStyle = "PivotStyleLight16"
};
StylesheetExtensionList stylesheetExtensionList = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension()
{
Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"
};
stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
stylesheetExtension1.Append(new SlicerStyles()
{
DefaultSlicerStyle = "SlicerStyleLight1"
});
StylesheetExtension stylesheetExtension2 = new StylesheetExtension()
{
Uri = "{9260A510-F301-46a8-8635-F512D64BE5F5}"
};
stylesheetExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
stylesheetExtension2.Append(new TimelineStyles()
{
DefaultTimelineStyle = "TimeSlicerStyleLight1"
});
stylesheetExtensionList.Append(stylesheetExtension1);
stylesheetExtensionList.Append(stylesheetExtension2);
sp.Stylesheet.Append(fonts);
sp.Stylesheet.Append(fills);
sp.Stylesheet.Append(borders);
sp.Stylesheet.Append(cellStyleFormats);
sp.Stylesheet.Append(cellFormats);
sp.Stylesheet.Append(cellStyles);
sp.Stylesheet.Append(differentialFormats);
sp.Stylesheet.Append(tableStyles);
sp.Stylesheet.Append(stylesheetExtensionList);
}
/// <summary>
/// Delete a worksheet from excel doc
/// </summary>
/// <param name="doc">Spreadsheet document</param>
/// <param name="sheetToDelete">Name of sheet to be deleted</param>
public static void DeleteWorksheet(SpreadsheetDocument doc, string sheetToDelete)
{
WorkbookPart wbPart = doc.WorkbookPart;
// Get the pivot Table Parts
IEnumerable <PivotTableCacheDefinitionPart> pvtTableCacheParts = wbPart.PivotTableCacheDefinitionParts;
var pvtTableCacheDefinationPart = new Dictionary <PivotTableCacheDefinitionPart, string>();
foreach (PivotTableCacheDefinitionPart item in pvtTableCacheParts)
{
PivotCacheDefinition pvtCacheDef = item.PivotCacheDefinition;
//Check if this CacheSource is linked to SheetToDelete
var pvtCahce = pvtCacheDef.Descendants <CacheSource>().Where(s => s.WorksheetSource.Sheet == sheetToDelete);
if (pvtCahce.Count() > 0)
{
pvtTableCacheDefinationPart.Add(item, item.ToString());
}
}
foreach (var Item in pvtTableCacheDefinationPart)
{
wbPart.DeletePart(Item.Key);
}
//Get the SheetToDelete from workbook.xml
Sheet theSheet = wbPart.Workbook.Descendants <Sheet>().FirstOrDefault(s => s.Name == sheetToDelete);
if (theSheet == null)
{
return;
}
//Store the SheetID for the reference
var sheetid = theSheet.SheetId;
// Remove the sheet reference from the workbook.
var worksheetPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
theSheet.Remove();
// Delete the worksheet part.
wbPart.DeletePart(worksheetPart);
//Get the DefinedNames
var definedNames = wbPart.Workbook.Descendants <DefinedNames>().FirstOrDefault();
if (definedNames != null)
{
var defNamesToDelete = new List <DefinedName>();
foreach (DefinedName Item in definedNames)
{
// This condition checks to delete only those names which are part of Sheet in question
if (Item.Text.Contains(sheetToDelete + "!"))
{
defNamesToDelete.Add(Item);
}
}
foreach (DefinedName Item in defNamesToDelete)
{
Item.Remove();
}
}
// Get the CalculationChainPart
//Note: An instance of this part type contains an ordered set of references to all cells in all worksheets in the
//workbook whose value is calculated from any formula
CalculationChainPart calChainPart;
calChainPart = wbPart.CalculationChainPart;
if (calChainPart != null)
{
var iSheetId = new Int32Value((int)sheetid.Value);
var calChainEntries = calChainPart.CalculationChain.Descendants <CalculationCell>().Where(c => c.SheetId == iSheetId);
var calcsToDelete = new List <CalculationCell>();
foreach (CalculationCell Item in calChainEntries)
{
calcsToDelete.Add(Item);
}
foreach (CalculationCell Item in calcsToDelete)
{
Item.Remove();
}
if (calChainPart.CalculationChain.Count() == 0)
{
wbPart.DeletePart(calChainPart);
}
}
// Save the workbook.
wbPart.Workbook.Save();
}
public byte[] GenerateExcel(List <System.Data.DataTable> _DataSet, string Classification)
{
var stream = new MemoryStream();
SpreadsheetDocument document = SpreadsheetDocument.Create(stream, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook);
WorkbookPart wbp = document.AddWorkbookPart();
wbp.Workbook = new Workbook();
Sheets sheets = new Sheets();
FileVersion fv = new FileVersion();
fv.ApplicationName = "Microsoft Office Excel";
UInt32 TotalSheets = 1;
WorksheetPart[] wsp = new WorksheetPart[TotalSheets];
//Worksheet[] ws = new Worksheet[TotalSheets];
SheetData[] sd = new SheetData[TotalSheets];
Sheet[] sheet = new Sheet[TotalSheets];
Columns thisColumn = new Columns();
for (int i = 0; i < TotalSheets; i++)
{
wsp[i] = wbp.AddNewPart <WorksheetPart>();
sd[i] = new SheetData();
wsp[i].Worksheet = new Worksheet();
wsp[i].Worksheet.Append(thisColumn);
wsp[i].Worksheet.Append(sd[i]);
sheet[i] = new Sheet();
}
WorkbookStylesPart wbsp = wbp.AddNewPart <WorkbookStylesPart>();
wbsp.Stylesheet = CreateStylesheet();
wbsp.Stylesheet.Save();
_GetExcelInfo _ge = new _GetExcelInfo();
UInt32 HeaderRow = 1;
for (int i = 0; i < TotalSheets; i++)
{
DataColumnCollection _dccColumnID = _DataSet[i].Columns;
if (_dccColumnID.Contains("rownumb"))
{
_DataSet[i].Columns.Remove("rownumb");
}
if (_dccColumnID.Contains("SSN"))
{
_DataSet[i].Columns.Remove("SSN");
}
if (_dccColumnID.Contains("DOC_MAP")) //Catch all to rename this column whenever present.
{
_DataSet[i].Columns["DOC_MAP"].ColumnName = "ASG";
}
CreateColumnHeader(_DataSet[i], sd[i], _ge, HeaderRow);
CreateHeaderFooter(Classification, wsp, sd, i);
CreateContent(_DataSet[i], sd[i], HeaderRow, _ge);
AutoSizeColumns(wsp, sd, thisColumn, i);
}
for (UInt32 i = 0; i < TotalSheets; i++)
{
//wsp[i].Worksheet.Append(sd[i]);
wsp[i].Worksheet.Save();
sheet[i].SheetId = i + 1;
sheet[i].Name = "Sheet " + (i + 1);
sheet[i].Id = wbp.GetIdOfPart(wsp[i]);
sheets.Append(sheet[i]);
}
wbp.Workbook.Append(fv);
wbp.Workbook.Append(sheets);
document.WorkbookPart.Workbook.Save();
document.Close();
return(stream.ToArray());
}
private string ReadExcelCell(Cell cell, WorkbookPart workbookPart)
{
var cellValue = cell.CellValue;
var text = (cellValue == null) ? cell.InnerText : cellValue.Text;
if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
{
text = workbookPart.SharedStringTablePart.SharedStringTable
.Elements<SharedStringItem>().ElementAt(
Convert.ToInt32(cell.CellValue.Text)).InnerText;
}
return (text ?? string.Empty).Trim();
}
