private void AddSheet_Click(object sender, RoutedEventArgs e)
{
CSheet sheet = new CSheet();
sheet.Dat = DateTime.Today;
NodeWindow nodeWindow = new NodeWindow(sheet);
if (nodeWindow.ShowDialog() == true)
{
Collect coll = new Collect();
coll.Collect_name = nodeWindow._node.Name;
if (treeColl.SelectedItem.GetType() == typeof(CSheet))
{
coll.Parent_id = (treeColl.SelectedItem as CSheet).ParentId;
}
else
{
coll.Parent_id = (treeColl.SelectedItem as CNode).Id;
}
coll.Type = "sheet";
coll.DDate = (nodeWindow._node as CSheet).Dat;
db.Collects.Add(coll);
db.SaveChanges();
GenerateTree();
}
}
// Поиск потомков узла дерева сыслающегося на строку с ключом node_id
private SortableObservableCollection <INode> GetChildren(int node_id, IEnumerable <Collect> list)
{
SortableObservableCollection <INode> tree = new SortableObservableCollection <INode>();
foreach (Collect t in list.Where(n => n.Parent_id == node_id))
{
if (t.Type == "sheet") // конечный узел дерева - "лист"
{
CSheet sheet = new CSheet();
sheet.Id = t.Id;
sheet.ParentId = t.Parent_id;
sheet.Name = t.Collect_name;
sheet.Dat = t.DDate;
tree.Add(sheet);
}
else
{
CNode node = new CNode();
node.Id = t.Id;
node.Name = t.Collect_name;
node.Children = GetChildren(t.Id, list);
tree.Add(node);
}
}
tree.Sort(t => t.Name);
return(tree);
}
private void Delete_Click(object sender, RoutedEventArgs e)
{
if (treeColl.SelectedItem == null)
{
return;
}
if (treeColl.SelectedItem.GetType() == typeof(CSheet))
{
CSheet node = treeColl.SelectedItem as CSheet;
Collect coll = db.Collects.Find(node.Id);
db.Collects.Remove(coll);
db.SaveChanges();
GenerateTree();
}
else
{
CNode node = treeColl.SelectedItem as CNode;
if (node.Children.Count() > 0)
{
MessageBox.Show("А данного узла есть потомки, удалите сначала все дочерние узлы.");
}
else
{
Collect coll = db.Collects.Find(node.Id);
db.Collects.Remove(coll);
db.SaveChanges();
GenerateTree();
}
}
}
public ActionResult export_excel(FormCollection fc)
{
try
{
DT_WebGISEntities MyContext = new DT_WebGISEntities();
CMS_Documents_DAO objDAO = new CMS_Documents_DAO();
int typeID = fc["type_excel"].ToString() != "" ? Convert.ToInt32(fc["type_excel"].ToString()) : 0;
int areaID = fc["area_excel"].ToString() != "" ? Convert.ToInt32(fc["area_excel"].ToString()) : 0;
int orgID = fc["org_excel"].ToString() != "" ? Convert.ToInt32(fc["org_excel"].ToString()) : 0;
string keyWord = fc["key_excel"].ToString();
var data = objDAO.Search(keyWord, typeID, areaID, orgID).Where(x => x.Publish == true).ToList();
MemoryStream stream = new MemoryStream();
using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
{
CWorkbook workbook = new CWorkbook(spreadsheetDoc);
string sheetName = "ThongKe";
CSheet sheetLH = createSheet(spreadsheetDoc, workbook, sheetName, data);
sheetLH._worksheetPart.Worksheet.Save();
}
string fileName = "ThongKeVanBan.xlsx";
var contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.Clear();
Response.ContentType = contentType;
string contentDisposition = "attachment; filename*=UTF-8''" + Uri.EscapeDataString(fileName);
Response.AddHeader("content-disposition", contentDisposition);
Response.AddHeader("Content-Length", stream.Length.ToString());
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.BinaryWrite(stream.ToArray());
Response.Flush();
Response.SuppressContent = true;
System.Web.HttpContext.Current.ApplicationInstance.CompleteRequest();
return(View());
}
catch (Exception ex)
{
SetAlert("Lỗi" + ex.Message.ToString(), "error");
Logs.WriteLog(ex);
return(View());
}
}
private void Edit_Click(object sender, RoutedEventArgs e)
{
if (treeColl.SelectedItem == null)
{
return;
}
Collect coll;
if (treeColl.SelectedItem.GetType() == typeof(CSheet))
{
CSheet node = treeColl.SelectedItem as CSheet;
NodeWindow nodeWindow = new NodeWindow(node);
if (nodeWindow.ShowDialog() == true)
{
coll = db.Collects.Find(nodeWindow._node.Id);
if (coll != null)
{
coll.Collect_name = nodeWindow._node.Name;
coll.Type = "sheet";
coll.DDate = (nodeWindow._node as CSheet).Dat;
db.Entry(coll).State = EntityState.Modified;
db.SaveChanges();
}
}
}
if (treeColl.SelectedItem.GetType() == typeof(CNode))
{
CNode node = treeColl.SelectedItem as CNode;
NodeWindow nodeWindow = new NodeWindow(node);
if (nodeWindow.ShowDialog() == true)
{
coll = db.Collects.Find(nodeWindow._node.Id);
if (coll != null)
{
coll.Collect_name = nodeWindow._node.Name;
db.Entry(coll).State = EntityState.Modified;
db.SaveChanges();
}
}
}
GenerateTree();
}
private CSheet createSheet_bando(SpreadsheetDocument spreadsheetDoc, CWorkbook workbook, string sheetName, List <CMS_Maps_LayTatCa_Result> data)
{
CSheet sheet = new CSheet(spreadsheetDoc, sheetName, workbook._sheets);
List <uint> styleIndex = new List <uint>();
CStyle cStyle = new CStyle();
cStyle.fontBold = true;
cStyle.alignmentHorizontal = HorizontalAlignmentValues.Center;
cStyle.SetBorderStyle(BorderStyleValues.None, BorderStyleValues.None, BorderStyleValues.None, BorderStyleValues.None);
cStyle.fontName = "Times New Roman";
cStyle.fontSize = 12;
cStyle.CreateStyle();
styleIndex.Add(cStyle.GetStyleIndex(workbook._workbookPart));
uint rowIndex = 1;
uint colindex = 1;
#region HEADER
string header = "BỘ TÀI NGUYÊN VÀ MÔI TRƯỜNG";
uint rowHeader = rowIndex + 1;
uint colHeader = colindex + 1;
int textHeader = sheet.InsertSharedStringItem(header, workbook._sharedStringTablePart);
Cell cellHeader = sheet.InsertCellInWorksheet(ExcelUtils.GetColumnNameByIndex(colHeader), rowHeader, 1, textHeader, null);
cellHeader.StyleIndex = styleIndex[styleIndex.Count - 1];
ExcelUtils.MergeTwoCells(sheet._worksheetPart.Worksheet, sheetName, ExcelUtils.GetColumnNameByIndex(colHeader) + rowHeader.ToString(), ExcelUtils.GetColumnNameByIndex(colHeader + 1) + (rowHeader).ToString());
cStyle = new CStyle();
cStyle.fontBold = true;
cStyle.alignmentHorizontal = HorizontalAlignmentValues.Center;
cStyle.SetBorderStyle(BorderStyleValues.None, BorderStyleValues.None, BorderStyleValues.None, BorderStyleValues.None);
cStyle.fontName = "Times New Roman";
cStyle.fontSize = 13;
cStyle.CreateStyle();
styleIndex.Add(cStyle.GetStyleIndex(workbook._workbookPart));
header = "Cục công nghệ thông tin và DLTNMT";
rowHeader = rowIndex + 2;
colHeader = colindex + 1;
textHeader = sheet.InsertSharedStringItem(header, workbook._sharedStringTablePart);
cellHeader = sheet.InsertCellInWorksheet(ExcelUtils.GetColumnNameByIndex(colHeader), rowHeader, 1, textHeader, null);
cellHeader.StyleIndex = styleIndex[styleIndex.Count - 1];
ExcelUtils.MergeTwoCells(sheet._worksheetPart.Worksheet, sheetName, ExcelUtils.GetColumnNameByIndex(colHeader) + rowHeader.ToString(), ExcelUtils.GetColumnNameByIndex(colHeader + 1) + (rowHeader).ToString());
cStyle.fontSize = 17;
header = "Thống kê bản đồ";
rowHeader = rowIndex + 4;
colHeader = colindex + 1;
textHeader = sheet.InsertSharedStringItem(header, workbook._sharedStringTablePart);
cellHeader = sheet.InsertCellInWorksheet(ExcelUtils.GetColumnNameByIndex(colHeader), rowHeader, 1, textHeader, null);
cellHeader.StyleIndex = styleIndex[styleIndex.Count - 1];
ExcelUtils.MergeTwoCells(sheet._worksheetPart.Worksheet, sheetName, ExcelUtils.GetColumnNameByIndex(colHeader) + rowHeader.ToString(), ExcelUtils.GetColumnNameByIndex(colHeader + 6) + (rowHeader).ToString());
//cStyle = new CStyle();
//cStyle.fontBold = true;
//cStyle.fontItalic = true;
//cStyle.alignmentHorizontal = HorizontalAlignmentValues.Center;
//cStyle.SetBorderStyle(BorderStyleValues.None, BorderStyleValues.None, BorderStyleValues.None, BorderStyleValues.None);
//cStyle.fontName = "Times New Roman";
//cStyle.fontSize = 13;
//cStyle.CreateStyle();
//styleIndex.Add(cStyle.GetStyleIndex(workbook._workbookPart));
//header = "(Từ ngày " + listLH[0].ngay + " đến ngày " + listLH[listLH.Count - 1].ngay + ")";
//rowHeader = rowIndex + 5;
//colHeader = colindex + 1;
//textHeader = sheet.InsertSharedStringItem(header, workbook._sharedStringTablePart);
//cellHeader = sheet.InsertCellInWorksheet(ExcelUtils.GetColumnNameByIndex(colHeader), rowHeader, 1, textHeader, null);
//cellHeader.StyleIndex = styleIndex[styleIndex.Count - 1];
//ExcelUtils.MergeTwoCells(sheet._worksheetPart.Worksheet, sheetName, ExcelUtils.GetColumnNameByIndex(colHeader) + rowHeader.ToString(), ExcelUtils.GetColumnNameByIndex(colHeader + 6) + (rowHeader).ToString());
#endregion
#region headerTable
rowIndex = rowIndex + 6;
cStyle = new CStyle();
cStyle.fontBold = true;
cStyle.alignmentHorizontal = HorizontalAlignmentValues.Center;
cStyle.SetBorderStyle(BorderStyleValues.Thin, BorderStyleValues.Thin, BorderStyleValues.Thin, BorderStyleValues.Thin);
cStyle.fontName = "Times New Roman";
cStyle.fontSize = 13;
cStyle.CreateStyle();
styleIndex.Add(cStyle.GetStyleIndex(workbook._workbookPart));
string title = "STT";
uint rowindexHeader = rowIndex + 1;
uint colindexHeader = colindex + 1;
int textIndexHeader = sheet.InsertSharedStringItem(title, workbook._sharedStringTablePart);
Cell cellSTT = sheet.InsertCellInWorksheet(ExcelUtils.GetColumnNameByIndex(colindexHeader), rowindexHeader, 1, textIndexHeader, null);
cellSTT.StyleIndex = styleIndex[styleIndex.Count - 1];
title = "Tên";
rowindexHeader = rowIndex + 1;
colindexHeader = colindex + 2;
textIndexHeader = sheet.InsertSharedStringItem(title, workbook._sharedStringTablePart);
Cell cellLanhDaoVu = sheet.InsertCellInWorksheet(ExcelUtils.GetColumnNameByIndex(colindexHeader), rowindexHeader, 1, textIndexHeader, null);
cellLanhDaoVu.StyleIndex = styleIndex[styleIndex.Count - 1];
title = "Loại bản đồ";
rowindexHeader = rowIndex + 1;
colindexHeader = colindex + 3;
textIndexHeader = sheet.InsertSharedStringItem(title, workbook._sharedStringTablePart);
Cell cellChuyenVien = sheet.InsertCellInWorksheet(ExcelUtils.GetColumnNameByIndex(colindexHeader), rowindexHeader, 1, textIndexHeader, null);
cellChuyenVien.StyleIndex = styleIndex[styleIndex.Count - 1];
title = "Mô tả";
rowindexHeader = rowIndex + 1;
colindexHeader = colindex + 4;
textIndexHeader = sheet.InsertSharedStringItem(title, workbook._sharedStringTablePart);
Cell cellLanhDaoBo = sheet.InsertCellInWorksheet(ExcelUtils.GetColumnNameByIndex(colindexHeader), rowindexHeader, 1, textIndexHeader, null);
cellLanhDaoBo.StyleIndex = styleIndex[styleIndex.Count - 1];
#endregion
rowIndex++;
cStyle = new CStyle();
cStyle.alignmentHorizontal = HorizontalAlignmentValues.Center;
cStyle.SetBorderStyle(BorderStyleValues.Thin, BorderStyleValues.Thin, BorderStyleValues.Thin, BorderStyleValues.Thin);
cStyle.fontName = "Times New Roman";
cStyle.fontBold = false;
cStyle.fontSize = 13;
cStyle.CreateStyle();
styleIndex.Add(cStyle.GetStyleIndex(workbook._workbookPart));
cStyle = new CStyle();
cStyle.alignmentHorizontal = HorizontalAlignmentValues.Left;
cStyle.SetBorderStyle(BorderStyleValues.Thin, BorderStyleValues.Thin, BorderStyleValues.Thin, BorderStyleValues.Thin);
cStyle.fontName = "Times New Roman";
cStyle.fontBold = false;
cStyle.fontSize = 13;
cStyle.CreateStyle();
styleIndex.Add(cStyle.GetStyleIndex(workbook._workbookPart));
uint index = 0;
foreach (var item in data)
{
string titleBody = (index + 1).ToString();
uint rowindexBody = rowIndex + index + 1;
uint colIndexBody = colindex + 1;
int textIndexBody = sheet.InsertSharedStringItem(titleBody, workbook._sharedStringTablePart);
cellSTT = sheet.InsertCellInWorksheet(ExcelUtils.GetColumnNameByIndex(colIndexBody), rowindexBody, 1, textIndexBody, titleBody);
cellSTT.StyleIndex = styleIndex[styleIndex.Count - 2];
//ExcelUtils.MergeTwoCells(sheet._worksheetPart.Worksheet, sheetName, ExcelUtils.GetColumnNameByIndex(colIndexBody) + rowindexBody.ToString(), ExcelUtils.GetColumnNameByIndex(colIndexBody) + (rowindexBody + item.data.Count() - 1).ToString());
titleBody = item.Name;
rowindexBody = rowIndex + index + 1;
colIndexBody = colindex + 2;
textIndexBody = sheet.InsertSharedStringItem(titleBody, workbook._sharedStringTablePart);
cellLanhDaoVu = sheet.InsertCellInWorksheet(ExcelUtils.GetColumnNameByIndex(colIndexBody), rowindexBody, 1, textIndexBody, titleBody);
cellLanhDaoVu.StyleIndex = styleIndex[styleIndex.Count - 1];
titleBody = item.LoaiBanDo;
rowindexBody = rowIndex + index + 1;
colIndexBody = colindex + 3;
textIndexBody = sheet.InsertSharedStringItem(titleBody, workbook._sharedStringTablePart);
cellChuyenVien = sheet.InsertCellInWorksheet(ExcelUtils.GetColumnNameByIndex(colIndexBody), rowindexBody, 1, textIndexBody, titleBody);
cellChuyenVien.StyleIndex = styleIndex[styleIndex.Count - 1];
titleBody = item.Description;
rowindexBody = rowIndex + index + 1;
colIndexBody = colindex + 4;
textIndexBody = sheet.InsertSharedStringItem(titleBody, workbook._sharedStringTablePart);
cellLanhDaoBo = sheet.InsertCellInWorksheet(ExcelUtils.GetColumnNameByIndex(colIndexBody), rowindexBody, 1, textIndexBody, titleBody);
cellLanhDaoBo.StyleIndex = styleIndex[styleIndex.Count - 1];
index++;
}
#region ChinhDoRongChoTungCotSheetThietBi
//Chỉnh độ rộng cho từng cột
ExcelUtils.UpdateColumnWidth(sheet._worksheetPart, 1, 5);
ExcelUtils.UpdateColumnWidth(sheet._worksheetPart, 2, 10); //STT
ExcelUtils.UpdateColumnWidth(sheet._worksheetPart, 3, 30); //Tiêu đề
ExcelUtils.UpdateColumnWidth(sheet._worksheetPart, 4, 20); //Thể loại
ExcelUtils.UpdateColumnWidth(sheet._worksheetPart, 5, 20); //Mô tả
#endregion
//Set page setup cho sheet
//ExcelUtils.PageSetupUpdate(sheet._worksheetPart, OrientationValues.Landscape, 0.590, 0.157, 0.66, 0.49, 0.2, 0.2, true, 0, 1, (UInt32Value)(int)PaperSize.A4, "", "", "", "", "");
return(sheet);
}
private CSheet createSheet(SpreadsheetDocument spreadsheetDoc, CWorkbook workbook, string sheetName, List <CMS_Documents_LayTatCa_Result> data)
{
CSheet sheet = new CSheet(spreadsheetDoc, sheetName, workbook._sheets);
List <uint> styleIndex = new List <uint>();
CStyle cStyle = new CStyle();
cStyle.fontBold = true;
cStyle.alignmentHorizontal = HorizontalAlignmentValues.Center;
cStyle.SetBorderStyle(BorderStyleValues.None, BorderStyleValues.None, BorderStyleValues.None, BorderStyleValues.None);
cStyle.fontName = "Times New Roman";
cStyle.fontSize = 12;
cStyle.CreateStyle();
styleIndex.Add(cStyle.GetStyleIndex(workbook._workbookPart));
uint rowIndex = 1;
uint colindex = 1;
#region HEADER
string header = "BỘ TÀI NGUYÊN VÀ MÔI TRƯỜNG";
uint rowHeader = rowIndex + 1;
uint colHeader = colindex + 1;
int textHeader = sheet.InsertSharedStringItem(header, workbook._sharedStringTablePart);
Cell cellHeader = sheet.InsertCellInWorksheet(ExcelUtils.GetColumnNameByIndex(colHeader), rowHeader, 1, textHeader, null);
cellHeader.StyleIndex = styleIndex[styleIndex.Count - 1];
ExcelUtils.MergeTwoCells(sheet._worksheetPart.Worksheet, sheetName, ExcelUtils.GetColumnNameByIndex(colHeader) + rowHeader.ToString(), ExcelUtils.GetColumnNameByIndex(colHeader + 1) + (rowHeader).ToString());
cStyle = new CStyle();
cStyle.fontBold = true;
cStyle.alignmentHorizontal = HorizontalAlignmentValues.Center;
cStyle.SetBorderStyle(BorderStyleValues.None, BorderStyleValues.None, BorderStyleValues.None, BorderStyleValues.None);
cStyle.fontName = "Times New Roman";
cStyle.fontSize = 13;
cStyle.CreateStyle();
styleIndex.Add(cStyle.GetStyleIndex(workbook._workbookPart));
header = "Cục công nghệ thông tin và DLTNMT";
rowHeader = rowIndex + 2;
colHeader = colindex + 1;
textHeader = sheet.InsertSharedStringItem(header, workbook._sharedStringTablePart);
cellHeader = sheet.InsertCellInWorksheet(ExcelUtils.GetColumnNameByIndex(colHeader), rowHeader, 1, textHeader, null);
cellHeader.StyleIndex = styleIndex[styleIndex.Count - 1];
ExcelUtils.MergeTwoCells(sheet._worksheetPart.Worksheet, sheetName, ExcelUtils.GetColumnNameByIndex(colHeader) + rowHeader.ToString(), ExcelUtils.GetColumnNameByIndex(colHeader + 1) + (rowHeader).ToString());
cStyle.fontSize = 17;
header = "Thống kê văn bản";
rowHeader = rowIndex + 4;
colHeader = colindex + 1;
textHeader = sheet.InsertSharedStringItem(header, workbook._sharedStringTablePart);
cellHeader = sheet.InsertCellInWorksheet(ExcelUtils.GetColumnNameByIndex(colHeader), rowHeader, 1, textHeader, null);
cellHeader.StyleIndex = styleIndex[styleIndex.Count - 1];
ExcelUtils.MergeTwoCells(sheet._worksheetPart.Worksheet, sheetName, ExcelUtils.GetColumnNameByIndex(colHeader) + rowHeader.ToString(), ExcelUtils.GetColumnNameByIndex(colHeader + 6) + (rowHeader).ToString());
//cStyle = new CStyle();
//cStyle.fontBold = true;
//cStyle.fontItalic = true;
//cStyle.alignmentHorizontal = HorizontalAlignmentValues.Center;
//cStyle.SetBorderStyle(BorderStyleValues.None, BorderStyleValues.None, BorderStyleValues.None, BorderStyleValues.None);
//cStyle.fontName = "Times New Roman";
//cStyle.fontSize = 13;
//cStyle.CreateStyle();
//styleIndex.Add(cStyle.GetStyleIndex(workbook._workbookPart));
//header = "(Từ ngày " + listLH[0].ngay + " đến ngày " + listLH[listLH.Count - 1].ngay + ")";
//rowHeader = rowIndex + 5;
//colHeader = colindex + 1;
//textHeader = sheet.InsertSharedStringItem(header, workbook._sharedStringTablePart);
//cellHeader = sheet.InsertCellInWorksheet(ExcelUtils.GetColumnNameByIndex(colHeader), rowHeader, 1, textHeader, null);
//cellHeader.StyleIndex = styleIndex[styleIndex.Count - 1];
//ExcelUtils.MergeTwoCells(sheet._worksheetPart.Worksheet, sheetName, ExcelUtils.GetColumnNameByIndex(colHeader) + rowHeader.ToString(), ExcelUtils.GetColumnNameByIndex(colHeader + 6) + (rowHeader).ToString());
#endregion
#region headerTable
rowIndex = rowIndex + 6;
cStyle = new CStyle();
cStyle.fontBold = true;
cStyle.alignmentHorizontal = HorizontalAlignmentValues.Center;
cStyle.SetBorderStyle(BorderStyleValues.Thin, BorderStyleValues.Thin, BorderStyleValues.Thin, BorderStyleValues.Thin);
cStyle.fontName = "Times New Roman";
cStyle.fontSize = 13;
cStyle.CreateStyle();
styleIndex.Add(cStyle.GetStyleIndex(workbook._workbookPart));
string title = "STT";
uint rowindexHeader = rowIndex + 1;
uint colindexHeader = colindex + 1;
int textIndexHeader = sheet.InsertSharedStringItem(title, workbook._sharedStringTablePart);
Cell cellSTT = sheet.InsertCellInWorksheet(ExcelUtils.GetColumnNameByIndex(colindexHeader), rowindexHeader, 1, textIndexHeader, null);
cellSTT.StyleIndex = styleIndex[styleIndex.Count - 1];
title = "Số hiệu";
rowindexHeader = rowIndex + 1;
colindexHeader = colindex + 2;
textIndexHeader = sheet.InsertSharedStringItem(title, workbook._sharedStringTablePart);
Cell cellLanhDaoVu = sheet.InsertCellInWorksheet(ExcelUtils.GetColumnNameByIndex(colindexHeader), rowindexHeader, 1, textIndexHeader, null);
cellLanhDaoVu.StyleIndex = styleIndex[styleIndex.Count - 1];
title = "Trích yếu";
rowindexHeader = rowIndex + 1;
colindexHeader = colindex + 3;
textIndexHeader = sheet.InsertSharedStringItem(title, workbook._sharedStringTablePart);
Cell cellChuyenVien = sheet.InsertCellInWorksheet(ExcelUtils.GetColumnNameByIndex(colindexHeader), rowindexHeader, 1, textIndexHeader, null);
cellChuyenVien.StyleIndex = styleIndex[styleIndex.Count - 1];
title = "Loại văn bản";
rowindexHeader = rowIndex + 1;
colindexHeader = colindex + 4;
textIndexHeader = sheet.InsertSharedStringItem(title, workbook._sharedStringTablePart);
Cell cellLanhDaoBo = sheet.InsertCellInWorksheet(ExcelUtils.GetColumnNameByIndex(colindexHeader), rowindexHeader, 1, textIndexHeader, null);
cellLanhDaoBo.StyleIndex = styleIndex[styleIndex.Count - 1];
title = "Lĩnh vực";
rowindexHeader = rowIndex + 1;
colindexHeader = colindex + 5;
textIndexHeader = sheet.InsertSharedStringItem(title, workbook._sharedStringTablePart);
Cell cellGio = sheet.InsertCellInWorksheet(ExcelUtils.GetColumnNameByIndex(colindexHeader), rowindexHeader, 1, textIndexHeader, null);
cellGio.StyleIndex = styleIndex[styleIndex.Count - 1];
title = "Ngày ban hành";
rowindexHeader = rowIndex + 1;
colindexHeader = colindex + 6;
textIndexHeader = sheet.InsertSharedStringItem(title, workbook._sharedStringTablePart);
Cell cellNoiDung = sheet.InsertCellInWorksheet(ExcelUtils.GetColumnNameByIndex(colindexHeader), rowindexHeader, 1, textIndexHeader, null);
cellNoiDung.StyleIndex = styleIndex[styleIndex.Count - 1];
title = "Ngày hiệu lực";
rowindexHeader = rowIndex + 1;
colindexHeader = colindex + 7;
textIndexHeader = sheet.InsertSharedStringItem(title, workbook._sharedStringTablePart);
Cell cellDiaDiem = sheet.InsertCellInWorksheet(ExcelUtils.GetColumnNameByIndex(colindexHeader), rowindexHeader, 1, textIndexHeader, null);
cellDiaDiem.StyleIndex = styleIndex[styleIndex.Count - 1];
#endregion
rowIndex++;
cStyle = new CStyle();
cStyle.alignmentHorizontal = HorizontalAlignmentValues.Center;
cStyle.SetBorderStyle(BorderStyleValues.Thin, BorderStyleValues.Thin, BorderStyleValues.Thin, BorderStyleValues.Thin);
cStyle.fontName = "Times New Roman";
cStyle.fontBold = false;
cStyle.fontSize = 13;
cStyle.CreateStyle();
styleIndex.Add(cStyle.GetStyleIndex(workbook._workbookPart));
cStyle = new CStyle();
cStyle.alignmentHorizontal = HorizontalAlignmentValues.Left;
cStyle.SetBorderStyle(BorderStyleValues.Thin, BorderStyleValues.Thin, BorderStyleValues.Thin, BorderStyleValues.Thin);
cStyle.fontName = "Times New Roman";
cStyle.fontBold = false;
cStyle.fontSize = 13;
cStyle.CreateStyle();
styleIndex.Add(cStyle.GetStyleIndex(workbook._workbookPart));
uint index = 0;
foreach (var item in data)
{
string titleBody = (index + 1).ToString();
uint rowindexBody = rowIndex + index + 1;
uint colIndexBody = colindex + 1;
int textIndexBody = sheet.InsertSharedStringItem(titleBody, workbook._sharedStringTablePart);
cellSTT = sheet.InsertCellInWorksheet(ExcelUtils.GetColumnNameByIndex(colIndexBody), rowindexBody, 1, textIndexBody, titleBody);
cellSTT.StyleIndex = styleIndex[styleIndex.Count - 2];
//ExcelUtils.MergeTwoCells(sheet._worksheetPart.Worksheet, sheetName, ExcelUtils.GetColumnNameByIndex(colIndexBody) + rowindexBody.ToString(), ExcelUtils.GetColumnNameByIndex(colIndexBody) + (rowindexBody + item.data.Count() - 1).ToString());
titleBody = item.DocumentNumber;
rowindexBody = rowIndex + index + 1;
colIndexBody = colindex + 2;
textIndexBody = sheet.InsertSharedStringItem(titleBody, workbook._sharedStringTablePart);
cellLanhDaoVu = sheet.InsertCellInWorksheet(ExcelUtils.GetColumnNameByIndex(colIndexBody), rowindexBody, 1, textIndexBody, titleBody);
cellLanhDaoVu.StyleIndex = styleIndex[styleIndex.Count - 1];
titleBody = item.Abstract;
rowindexBody = rowIndex + index + 1;
colIndexBody = colindex + 3;
textIndexBody = sheet.InsertSharedStringItem(titleBody, workbook._sharedStringTablePart);
cellChuyenVien = sheet.InsertCellInWorksheet(ExcelUtils.GetColumnNameByIndex(colIndexBody), rowindexBody, 1, textIndexBody, titleBody);
cellChuyenVien.StyleIndex = styleIndex[styleIndex.Count - 1];
titleBody = item.LoaiVanBan;
rowindexBody = rowIndex + index + 1;
colIndexBody = colindex + 4;
textIndexBody = sheet.InsertSharedStringItem(titleBody, workbook._sharedStringTablePart);
cellLanhDaoBo = sheet.InsertCellInWorksheet(ExcelUtils.GetColumnNameByIndex(colIndexBody), rowindexBody, 1, textIndexBody, titleBody);
cellLanhDaoBo.StyleIndex = styleIndex[styleIndex.Count - 1];
titleBody = item.LinhVuc;
rowindexBody = rowIndex + index + 1;
colIndexBody = colindex + 5;
textIndexBody = sheet.InsertSharedStringItem(titleBody, workbook._sharedStringTablePart);
cellGio = sheet.InsertCellInWorksheet(ExcelUtils.GetColumnNameByIndex(colIndexBody), rowindexBody, 1, textIndexBody, titleBody);
cellGio.StyleIndex = styleIndex[styleIndex.Count - 1];
string content = Convert.ToDateTime(item.IssuedDate).ToString("dd/MM/yyyy");
string html = HttpUtility.HtmlDecode(content);
titleBody = Regex.Replace(html, "<.*?>", String.Empty);
rowindexBody = rowIndex + index + 1;
colIndexBody = colindex + 6;
textIndexBody = sheet.InsertSharedStringItem(titleBody, workbook._sharedStringTablePart);
cellNoiDung = sheet.InsertCellInWorksheet(ExcelUtils.GetColumnNameByIndex(colIndexBody), rowindexBody, 1, textIndexBody, titleBody);
cellNoiDung.StyleIndex = styleIndex[styleIndex.Count - 2];
titleBody = Convert.ToDateTime(item.EffectiveDate).ToString("dd/MM/yyyy");
rowindexBody = rowIndex + index + 1;
colIndexBody = colindex + 7;
textIndexBody = sheet.InsertSharedStringItem(titleBody, workbook._sharedStringTablePart);
cellDiaDiem = sheet.InsertCellInWorksheet(ExcelUtils.GetColumnNameByIndex(colIndexBody), rowindexBody, 1, textIndexBody, titleBody);
cellDiaDiem.StyleIndex = styleIndex[styleIndex.Count - 2];
index++;
}
#region ChinhDoRongChoTungCotSheetThietBi
//Chỉnh độ rộng cho từng cột
ExcelUtils.UpdateColumnWidth(sheet._worksheetPart, 1, 5);
ExcelUtils.UpdateColumnWidth(sheet._worksheetPart, 2, 10); //STT
ExcelUtils.UpdateColumnWidth(sheet._worksheetPart, 3, 30); //Số hiệu
ExcelUtils.UpdateColumnWidth(sheet._worksheetPart, 4, 35); //Trích yêu
ExcelUtils.UpdateColumnWidth(sheet._worksheetPart, 5, 20); //Loại văn bản
ExcelUtils.UpdateColumnWidth(sheet._worksheetPart, 6, 15); //Lĩnh vực
ExcelUtils.UpdateColumnWidth(sheet._worksheetPart, 7, 25); //Ngày ban hành
ExcelUtils.UpdateColumnWidth(sheet._worksheetPart, 8, 25); //Ngày hiệu lực
#endregion
//Set page setup cho sheet
//ExcelUtils.PageSetupUpdate(sheet._worksheetPart, OrientationValues.Landscape, 0.590, 0.157, 0.66, 0.49, 0.2, 0.2, true, 0, 1, (UInt32Value)(int)PaperSize.A4, "", "", "", "", "");
return(sheet);
}
public NodeWindow(CSheet p)
{
InitializeComponent();
_node = p;
this.DataContext = _node;
}
