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 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);
}
static void Run(object param)
{
if (!isRun)
{
isRun = true;
try
{
//получаем настройки приложения
var sett = Properties.Settings.Default;
sett.Reload();
//элементы для сравнения с файла
List <Element> l_elements = new List <Element>();
string new_workbookfile; //куда сохраняем результат
bool iswritefile = false; //нужно ли что-то отправлять
int i_write = 0; //сколько строк было удалено
//формируем новый полный путь для копии файла
int ind = sett.FullNameFileSaveTemp.IndexOf('.');
string name = sett.FullNameFileSaveTemp.Substring(0, ind);
string expansion = sett.FullNameFileSaveTemp.Substring(ind + 1, sett.FullNameFileSaveTemp.Length - ind - 1);
new_workbookfile = name + "_" + DateTime.Now.ToString().Replace('.', '_').Replace(':', '_') + "." + expansion;
using (CExcel excel = new CExcel())
{
using (CWorkbook workbook_read = excel.OpenWorkbook(sett.FullNameFileExcel))
{
//создали копию файла
workbook_read.SaveAs(new_workbookfile);
}
using (CWorkbook workbook_read = excel.OpenWorkbook(sett.FullNameFileExcel))
{
using (CWorkbook workbook_write = excel.OpenWorkbook(new_workbookfile))
{
CWorkbook.ProfiCreditWorksheet worksheet_read = workbook_read.ActiveSheet;
CWorkbook.ProfiCreditWorksheet worksheet_write = workbook_write.ActiveSheet;
object valCounterparty = null; //контрагент что ежит
object valFinishDate = null; //срок до что лежит
string counterparty = null; //контрагент
DateTime?finishdate; //срок до к типу дата и время
int i = 1; //индекс строки в файле
bool iswrite; //нужно ли оставить в копии файла
DateTime tempdt = DateTime.Now.AddDays(sett.OldDay); //колько дней сдвинуть текущую дату
DateTime dt = new DateTime(tempdt.Year, tempdt.Month, tempdt.Day);
int sizecol = 0; //кол-о колонок в файле
object val = null;
//определяем кол-во анализируемых столбцов по содержимому первых ячеек каждого столбца
do
{
val = worksheet_read.GetCells(1, i);
i++;
} while (val != null);
sizecol = i - 2;
i = 2;
do
{
iswrite = false; //каждый раз сбрасываем признак того, что нужно ли сохранить запись в копии файла
valCounterparty = worksheet_read.GetCells(i, sett.ColumnIndexCounterparty);
if (valCounterparty != null)
{
counterparty = valCounterparty.ToString().Trim();
valFinishDate = worksheet_read.GetCells(i, sett.ColumnIndexFinishDate);
finishdate = valFinishDate as DateTime?;
if (finishdate != null)
{
tempdt = finishdate.Value;
tempdt = new DateTime(tempdt.Year, tempdt.Month, tempdt.Day);
if ((tempdt.Date <= dt.Date) && (tempdt.Date >= DateTime.Now))
{
iswrite = true;
}
}
else
{
iswrite = true;
}
l_elements.Add(new Element()
{
Counterparty = counterparty, FinishDate = finishdate, ValFinishDate = valFinishDate
});
if (!iswrite)
{
worksheet_write.RowDelete(i - i_write);
i_write++;
}
else
{
iswritefile = true;
}
i++;
}
else
{
break;
}
} while (valCounterparty != null);
if (iswritefile)
{
workbook_write.Save();
}
}
}
}
//если были записи - то отправляем с вложением файла копии
if (iswritefile)
{
Mail.RunMailMessage(sett.MailServer, sett.MailAddressTo, sett.MailAddressFrom,
sett.MainSendNotEmptyBodyMess, "По документу: было выявлено всего " + (l_elements.Count - i_write).ToString() + " записей, срок жизни которых истекает через " + sett.OldDay.ToString() + " дней и меньше.\r\nПодробности во вложении." +
"\r\n Соощение сформировано: " + DateTime.Now.ToString(), new string[] { new_workbookfile });
}
//иначе-по настройкам либо отправляем, либо нет сообщение о том, что ничего не выявлено
else if (sett.IsSendEmptyBody)
{
Mail.RunMailMessage(sett.MailServer, sett.MailAddressFrom, sett.MailAddressFrom,
sett.MainSendEmptyBodyMess, "По документу: записи, срок жизни которых истекает через " + sett.OldDay.ToString() + " дней и меньше, не обнаружены."
+ "\r\n Соощение сформировано: " + DateTime.Now.ToString());
}
try
{
File.Delete(new_workbookfile);
}
catch (Exception exp)
{
Mail.RunMailMessage(sett.MailServer, sett.MailAddressFrom, sett.MailAddressFrom,
"Ошибка выполнения MailOutExcel по файлу", exp.Message);
}
//[email protected];[email protected];[email protected]"
//RunMailMessage(sett.MailServer, "[email protected];[email protected];", "*****@*****.**", "Тестовое сообщение", "Прошу подтвердить обратным письмом, что сообщения до Вас доходят");
using (Stream stream = new FileStream("log.txt", FileMode.Create, FileAccess.Write))
{
using (TextWriter tw = new StreamWriter(stream, Encoding.Unicode))
{
tw.WriteLine("Все хорошо");
tw.Flush();
}
}
}
catch (Exception exp)
{
using (Stream stream = new FileStream("log.txt", FileMode.Create, FileAccess.Write))
{
using (TextWriter tw = new StreamWriter(stream, Encoding.Unicode))
{
tw.WriteLine(exp.Message);
tw.Flush();
}
}
}
Console.WriteLine("Сообщение отправлено в " + DateTime.Now.ToString());
isRun = false;
}
}