public DataTable[] GetDataTablesFromExcelStream(Stream ExcelStream)
{
Aspose.Excel.License license = new Aspose.Excel.License();
license.SetLicense("Aspose.Excel.lic");
try
{
Excel excel = new Excel();
excel.Open(ExcelStream);
Worksheets sheets = excel.Worksheets;
DataTable[] tables = new DataTable[sheets.Count];
int index = 0;
foreach (Worksheet sheet in sheets)
{
Cells cells = sheet.Cells;
tables[index] = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1);
tables[index++].TableName = sheet.Name;
}
return tables;
}
catch
{
return null;
}
}
/// <summary>
/// Returns an active instance of Excel or a new one.
/// </summary>
/// <returns></returns>
public static IExcel GetInstance() {
//Set a compatible culture
Thread.CurrentThread.CurrentCulture = CULTURE_US;
//Get the running instance of Excel or create one if there isn't.
bool isCreatedApp = false;
Object app = null;
Guid excelguid = typeof(Excel).GUID;
NativeMethods.GetActiveObject(ref excelguid, IntPtr.Zero, out app);
if (app == null) {
try {
app = new Excel();
isCreatedApp = true;
} catch {
throw new ExcelError("Failed to launch Excel.");
}
}
//Cast to interface
IExcel excel = app as IExcel;
if (excel == null)
throw new ExcelError("Failed to interface with Excel.");
//Adds a worksheet if it's an empty instance of Excel
if (isCreatedApp) {
IWorkbook workbook = excel.Workbooks.Add();
workbook.Worksheets.Add();
excel.Visible = true;
}
return excel;
}
public static Excel Dequeue(string src)
{
Excel app;
if (Cached.TryDequeue(out app))
{
try
{
app.Book = app.App.Workbooks.Open(src, 0, true, 5, "", "",
true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
}
catch (Exception e)
{
Cached.Enqueue(app);
throw e;
}
return app;
}
try
{
//ProcKiller.Shared.Lock();
app = new Excel(new Application());
app.App.Visible = true;
app.Book = app.App.Workbooks.Open(src, 0, true, 5, "", "",
true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
app.Pid = CovProc.GetWindowThreadProcessId(app.App.ActiveWindow.Hwnd);
//ProcKiller.AddRunning(app.Pid);
//ProcKiller.Shared.Unlock();
}
catch (Exception e)
{
//ProcKiller.Shared.Unlock();
throw e;
}
return app;
}
/// <summary>
/// EXCEL画线
/// </summary>
/// <param name="excel"></param>
public static void DrawExcelBorders(Excel.Application excel, object cell1, object cell2)
{
Excel.Range range = excel.get_Range(cell1, cell2);
//range.Select();
Excel.Borders borders = range.Borders;
borders[Excel.XlBordersIndex.xlDiagonalDown].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
borders[Excel.XlBordersIndex.xlDiagonalUp].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin;
borders[Excel.XlBordersIndex.xlEdgeLeft].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;
borders[Excel.XlBordersIndex.xlEdgeTop].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin;
borders[Excel.XlBordersIndex.xlEdgeRight].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin;
borders[Excel.XlBordersIndex.xlEdgeBottom].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
}
public void checkin(Excel.Workbook activeWorkbook)
{
try
{
if (MessageBox.Show(resources.GetString("sure_check_in"), resources.GetString("checkin"), MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation) == DialogResult.OK)
{
object saveChanges = true;
object missing = Type.Missing;
String localFileName = activeWorkbook.FullName;
activeWorkbook.Close(saveChanges, missing, missing); // Always we save document
docXML.refresh(); // Refresh document list
if (docXML.isOpenKMDocument(localFileName))
{
OKMDocument oKMDocument = docXML.getOpenKMDocument(localFileName);
docXML.remove(oKMDocument);
DocumentLogic.checkin(oKMDocument, configXML.getHost(), configXML.getUser(), configXML.getPassword());
if (File.Exists(localFileName))
{
File.Delete(localFileName);
}
}
}
}
catch (Exception e)
{
String errorMsg = "OpenKMExcelAddIn - (checkinButton_Click)\n" + e.Message + "\n\n" + e.StackTrace;
MessageBox.Show(errorMsg, "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
private static void AddExcelDocument(string[] attributes)
{
Dictionary<string, string> attr = getDictAttr(attributes);
string documentName = returnNameCmdValue(attr);
if (documentName != null)
{
Excel newDoc = new Excel(documentName);
documents.Add(newDoc);
Console.WriteLine("Document added: " + documentName);
foreach (var key in attr.Keys)
{
switch (key)
{
case "rows":
newDoc.Rows = attr[key];
break;
case "cols":
newDoc.Cols = attr[key];
break;
case "version":
newDoc.Version = attr[key];
break;
case "size":
newDoc.SizeInBytes = attr[key];
break;
case "content":
newDoc.Content = attr[key];
break;
}
}
}
}
public static Excel LoadExcel(string path)
{
FileInfo file = new FileInfo(path);
ExcelPackage ep = new ExcelPackage(file);
Excel xls = new Excel(ep.Workbook);
return xls;
}
public byte[] GetExcel(Grid grid, string sheetName, string sidx, string sord, string filters)
{
Excel excel = new Excel(grid);
System.Data.DataTable dt = excel.SQL.GetDataTable(sidx, sord, filters);
return excel.GetFile(sheetName, sidx, sord, filters);
}
public static Excel CreateExcel(string path)
{
ExcelPackage ep = new ExcelPackage ();
ep.Workbook.Worksheets.Add ("sheet");
Excel xls = new Excel(ep.Workbook);
SaveExcel (xls, path);
return xls;
}
public void BasicBlue()
{
var excel = new Excel(new BlueStyle());
excel.CreateSheet("Test");
excel.WriteObject(Reports, 0, 0);
excel.SetColumnWidth(0, 0, new[] { 5, 35 });
excel.WriteFile(Path.Combine(AppDomain.CurrentDomain.SetupInformation.ApplicationBase, "aa.xlsx"));
}
static void test()
{
Excel xls = new Excel();
ExcelTable table = new ExcelTable();
table.TableName = "1";
xls.Tables.Add(table);
ExcelHelper.SaveExcel(xls, "/Users/junfeixi/Desktop/test111.xlsx");
}
public void ExportExcel(DataSet dataSet)
{
Excel excel = new Excel();
Workbook workbook = excel.Workbooks.Add();
for (int i = 0; i < dataSet.Tables.Count; i++)
{
DataTable dataTable = dataSet.Tables[i];
Worksheet worksheet = new Worksheet();
worksheet.Name = dataTable.TableName;
for (int j = 0; j < dataTable.Columns.Count; j++)
{
worksheet[1, j + 1] = new Cell(dataTable.Columns[j].ColumnName);
}
for (int k = 0; k < dataTable.Rows.Count; k++)
{
for (int l = 0; l < dataTable.Columns.Count; l++)
{
worksheet[k + 2, l + 1] = new Cell(dataTable.Rows[k][l]);
}
}
worksheet.Tables.Rows[1].Height = 100;
worksheet.Tables.Columns[1].Width = 100;
//workSheet[1, 2].StyleId = workbook.Styles.Add(new Style
// {
// Font = new Font
// {
// Color = "red",
// Size = 16
// }
// ,
// Alignment = new Alignment {Horizontal = "Center"}
// });
//workSheet[1, 1].StyleId = workbook.Styles.Add(new Style
// {
// Font = new Font
// {
// Color = "red",
// Size = 16
// }
// ,
// Alignment = new Alignment {Horizontal = "Center"}
// });
//workbook.WorkSheets.Add(worksheet);
//workbook.Save("file.xml");
//Excel excel = new Excel();
//Workbook workbook = excel.Open("file.xml");
workbook.WorkSheets.Add(worksheet);
workbook.Save(dataTable.TableName + ".xml");
}
}
public static bool SaveExcel(Excel.IWorkbookBase workbook, string fileName)
{
if (File.Exists(fileName))
File.Delete(fileName);
using (var fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
workbook.Write(fs);
return true;
}
}
public void Show(Excel xls)
{
mExcel = xls;
for (int i = 0; i < mExcel.Tables.Count; i++)
{
mExcel.Tables[i].SetCellTypeColumn(1, ExcelTableCellType.Label);
mExcel.Tables[i].SetCellTypeColumn(3, ExcelTableCellType.Popup, new List<string>(){"1","2"});
mExcel.Tables[i].SetCellTypeRow(1, ExcelTableCellType.Label);
mExcel.Tables[i].SetCellTypeRow(2, ExcelTableCellType.Label);
}
}
static void WriteXls()
{
Excel xls = new Excel();
ExcelTable table = new ExcelTable();
table.TableName = "test";
string outputPath = Application.dataPath + "/Test/Test2.xlsx";
xls.Tables.Add(table);
xls.Tables[0].SetValue(1, 1, Random.Range(1000,100000).ToString());
xls.ShowLog();
ExcelHelper.SaveExcel(xls, outputPath);
}
protected void DownloadTemplate_Click(object sender, EventArgs e)
{
//Instantiate an instance of license and set the license file through its path
Aspose.Excel.License license = new Aspose.Excel.License();
license.SetLicense("Aspose.Excel.lic");
Excel excel = new Excel();
string path = MapPath("../..") + @"\templates\";
string designerFile = path + "UserCredentialsTemplate.xls";
excel.Open(designerFile);
excel.Save("User_Credentials.xls", SaveType.OpenInExcel, FileFormatType.Default, this.Response);
}
public static void Enqueue(Excel app)
{
try
{
app.Book.Close(false, null, null);
Cached.Enqueue(app);
}
catch (Exception e)
{
L.E(e, "Close Excel fail with error->", e.Message);
app.Dispose();
}
}
static void test()
{
Excel xls = new Excel();
ExcelTable table = new ExcelTable();
table.TableName = "test";
string outputPath = Application.dataPath + "/Test/Test2.xlsx";
xls.Tables.Add(table);
xls.Tables[0].SetValue(1, 1, "1");
xls.Tables[0].SetValue(1, 2, "2");
xls.Tables[0].SetValue(2, 1, "3");
xls.Tables[0].SetValue(2, 2, "4");
xls.ShowLog();
ExcelHelper.SaveExcel(xls, outputPath);
}
public static void NativeExcel_CreateExcel(
DataTable Dt
, ClsExcel_Columns Columns
, string SaveFileName = ""
, Excel.XlFileFormat FileFormat = Excel.XlFileFormat.xlExcel5
, string Title = "")
{
Excel.Application Obj_Excel = new Excel.Application();
Excel.Workbook owbook = Obj_Excel.Workbooks.Add();
Excel.Worksheet owsheet = owbook.Worksheets.Add();
Int32 RowCt = 2;
Int32 ColCt = 1;
foreach (ClsExcel_Columns.Str_Columns? Obj in Columns.pObj)
{
owsheet.Cells[RowCt, ColCt].Value = Obj.Value.FieldDesc;
owsheet.Cells[RowCt, ColCt].Font.Bold = true;
Excel.Range Inner_ExRange =
owsheet.Range[
Do_Methods.GenerateChr(ColCt)
+ RowCt.ToString()
+ ":"
+ Do_Methods.GenerateChr(ColCt)
+ (RowCt + Dt.Rows.Count).ToString()];
Inner_ExRange.NumberFormat = Obj.Value.NumberFormat;
ColCt++;
}
RowCt++;
ColCt = 1;
Excel.Range ExRange =
owsheet.Range[
Do_Methods.GenerateChr(ColCt)
+ RowCt.ToString()
+ ":"
+ Do_Methods.GenerateChr(ColCt + Columns.pObj.Count)
+ (RowCt + Dt.Rows.Count - 1).ToString()];
ExRange.Value = Do_Methods.ConvertDataTo2DimArray(Dt, Columns.pFieldName);
owsheet.Range["A1;IV65536"].AutoFit();
if (SaveFileName == "")
{ SaveFileName = "Excel_File"; }
owsheet.Range["A1:A1"].Select();
owbook.SaveAs(SaveFileName, FileFormat);
}
public static void SaveExcel(Excel xls, string path)
{
FileInfo output = new FileInfo(path);
ExcelPackage ep = new ExcelPackage();
for (int i = 0; i < xls.Tables.Count; i++)
{
ExcelTable table = xls.Tables[i];
ExcelWorksheet sheet = ep.Workbook.Worksheets.Add(table.TableName);
for (int row = 1; row <= table.NumberOfRows; row++) {
for (int column = 1; column <= table.NumberOfColumns; column++) {
sheet.Cells[row, column].Value = table.GetValue(row, column);
}
}
}
ep.SaveAs(output);
}
public static Regression Run(Excel app, Workbook wkbk, Range xRng, Range yRng)
{
Worksheet outWs = wkbk.GetNewWorksheet();
Range outRng = outWs.GetA1Range();
XL.Workbook atvbaen = app.XlApplication.Workbooks.Open(@"C:\Program Files\Microsoft Office 15\root\office15\Library\Analysis\ATPVBAEN.XLAM");
atvbaen.RunAutoMacros(XL.XlRunAutoMacro.xlAutoOpen);
app.XlApplication.Run(@"Regress", yRng.XlRange, xRng.XlRange, Missing.Value, Missing.Value, Missing.Value, outRng.XlRange);
Range usedRng = outWs.GetUsedRange();
object[,] values = usedRng.Values;
Regression retValue = Regression.ParseValues(values);
return retValue;
}
protected void Page_Load(object sender, EventArgs e)
{
string actionKey = string.Empty;
if (Request.QueryString.AllKeys.Contains("actionKey"))
{
actionKey = Request.QueryString["actionKey"];
}
DataTable dt = new Custom_PerFormsService().GetDataTableAll(actionKey, null);
string fileName = "report.xls";
string mimeType = new MIMEType().GetMIMEType(new MIMEType().GetExtension(fileName));
byte[] buffer = new Excel().GetExcelBufferFromDataTable(dt);
Response.Clear();
Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", fileName));
Response.ContentType = mimeType;
Response.OutputStream.Write(buffer, 0, buffer.Length);
Response.OutputStream.Close();
Response.End();
}
public static bool Render(System.Web.HttpResponseBase Response,
Excel.IWorkbookBase workbook, string fileName)
{
// AiLib.Report.Excel.IWorkbookBase workbook
// http://dotnetslackers.com/articles/aspnet/Create-Excel-Spreadsheets-Using-NPOI.aspx
// Save the Excel spreadsheet to a MemoryStream and return it to the client
using (var exportData = new MemoryStream())
{
workbook.Write(exportData);
string saveAsFileName = fileName;
Response.ContentType = ContentExcel;
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
Response.Clear();
Response.BinaryWrite(exportData.GetBuffer());
Response.End();
}
return true;
}
/// <summary>
/// 合并单元格，并赋值，对指定WorkSheet操作
/// </summary>
/// <param name="beginRowIndex">开始行索引</param>
/// <param name="beginColumnIndex">开始列索引</param>
/// <param name="endRowIndex">结束行索引</param>
/// <param name="endColumnIndex">结束列索引</param>
/// <param name="text">合并后Range的值</param>
private void MergeCells(Excel.Worksheet sheet, int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string text)
{
if (sheet == null)
return;
range = sheet.get_Range(sheet.Cells[beginRowIndex, beginColumnIndex], sheet.Cells[endRowIndex, endColumnIndex]);
range.ClearContents(); //先把Range内容清除，合并才不会出错
range.MergeCells = true;
range.Value = text;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
}
private String GetSheetValue(Excel.Worksheet worksheet, string field)
{
Excel.Range range = worksheet.get_Range(field, field);
if (range == null) return "";
if (range.Cells.Value2 == null) return "";
return range.Cells.Value2.ToString();
}
public Range GetRange(Excel excel, int firstUsedRow, int firstUsedColumn)
{
return(excel.GetRange(firstUsedRow, firstUsedColumn));
}
private static void ReadDataFromExcelFile()
{
Console.OutputEncoding = Encoding.UTF8;
Console.WriteLine("Step three: read data from file");
Excel.ReadFromExcelFile();
}
/// <summary>
/// 根据传入参数，将数据导出到Excel中（日报，月报，年报）
/// </summary>
/// <param name="basePath">App_Data</param>
/// <param name="buildId"></param>
/// <param name="energyCode"></param>
/// <param name="circuits"></param>
/// <param name="type"></param>
/// <param name="date"></param>
/// <returns>Excel实体类，包含Excel文件的byte数组与Excel文件的名称</returns>
public Excel ExportCircuitReportToExcel(string basePath, string buildId, string energyCode, string[] circuits, string type, string date)
{
type = type.ToUpper();
string templatePath = basePath + "/DayReportTemplate.xls";
string reportType = " 日报(" + date + ")";
if (type == "MM")
{
reportType = " 月报(" + date + ")";
date += "-01";
templatePath = basePath + "/MonthReportTemplate.xls";
}
else if (type == "YY")
{
reportType = " 年报(" + date + ")";
date += "-01-01";
templatePath = basePath + "/YearReportTemplate.xls";
}
else if (type == "DD")
{
reportType = " 日报(" + date + ")";
templatePath = basePath + "/DayReportTemplate.xls";
}
//根据模板生成Excel
FileStream file = new FileStream(templatePath, FileMode.Open, FileAccess.Read);
HSSFWorkbook book = new HSSFWorkbook(file);
HSSFSheet sheet = (HSSFSheet)book.GetSheet("Sheet1");
//创建单元格格式：数字型单元格默认取小数点后两位
ICellStyle style = book.CreateCellStyle();
style.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
//获取当前建筑名
IHomeDbContext homeContext = new HomeDbContext();
BuildInfo build = homeContext.GetBuildById(buildId);
//获取能源类型
EnergyItemDict energyItem = context.GetUnitByEnergyCode(energyCode);
DateTime now = Utils.Util.ConvertString2DateTime(date, "yyyy-MM-dd");
//string[] circuitIds = circuits.Split(',');
List <ReportValue> data = context.GetReportValueList(circuits, now.ToShortDateString(), type);
var totalList = data.GroupBy(d => new { d.Time })
.Select(group => new ReportValue
{
Id = "Total",
Name = "总计",
Time = group.Key.Time,
Value = group.Sum(p => p.Value)
}).ToList();
data.AddRange(totalList);
//设置Excel标题
sheet.GetRow(0).GetCell(0).SetCellValue(build.BuildName + reportType);
//设置Excel中报表的能源类别
sheet.GetRow(1).GetCell(1).SetCellValue(energyItem.EnergyItemName);
//设置Excel中报表的能源单位
sheet.GetRow(1).GetCell(5).SetCellValue(energyItem.EnergyItemUnit);
//根据传入circuitIds填充excel
int rowId = 0;
for (int i = 0; i < circuits.Length + 1; i++)
{
//使用lamda表达式筛选List中Id与传入的Id对应的仪表：一次填充一行Excel
List <ReportValue> current;
if (i == circuits.Length)
{
current = data.FindAll(p => p.Id == "Total");
}
else
{
current = data.FindAll(p => p.Id == circuits[i]);
}
if (current.Count > 0)
{
IRow row = sheet.CreateRow(rowId + 3);
row.CreateCell(0).SetCellValue(current[0].Name);
decimal total = 0;
switch (type)
{
case "DD":
foreach (var item in current)
{ //遍历筛选出列表中当前回路中每个小时的数据，填充到Excel中
//当前行的第一列已经填充回路名称，根据时间设置向右偏移一位，将对应数据写入到当前单元格，并设置格式
if (item.Time != null)
{
DateTime time = Convert.ToDateTime(item.Time);
row.CreateCell(time.Hour + 1).SetCellValue((double)item.Value);
row.GetCell(time.Hour + 1).CellStyle = style;
total += Convert.ToDecimal(item.Value);
}
}
row.CreateCell(25).SetCellValue((double)total);
row.GetCell(25).CellStyle = style;
break;
case "MM":
foreach (var item in current)
{
if (item.Time != null)
{
DateTime time = Convert.ToDateTime(item.Time);
row.CreateCell(time.Day).SetCellValue((double)item.Value);
row.GetCell(time.Day).CellStyle = style;
total += Convert.ToDecimal(item.Value);
}
}
row.CreateCell(32).SetCellValue((double)total);
row.GetCell(32).CellStyle = style;
break;
case "YY":
foreach (var item in current)
{
if (item.Time != null)
{
DateTime time = Convert.ToDateTime(item.Time);
row.CreateCell(time.Month).SetCellValue((double)item.Value);
row.GetCell(time.Month).CellStyle = style;
total += Convert.ToDecimal(item.Value);
}
}
row.CreateCell(13).SetCellValue((double)total);
row.GetCell(13).CellStyle = style;
break;
}
rowId++;
}
}
Excel excel = new Excel();
excel.Name = build.BuildName + reportType + "[" + Guid.NewGuid().ToString("N") + "].xls";
using (MemoryStream stream = new MemoryStream())
{
book.Write(stream);
stream.Seek(0, SeekOrigin.Begin);
excel.Data = stream.ToArray();
}
return(excel);
}
public MemoryStream GenerateExcel(DateTime?dateFrom, DateTime?dateTo, string destinationCode, bool status, int transaction, string packingList, int offset, string username)
{
var Query = GetReportQuery(dateFrom, dateTo, destinationCode, status, transaction, packingList, offset, username);
Query = Query.OrderByDescending(b => b.LastModifiedUtc);
DataTable result = new DataTable();
//No Unit Budget Kategori Tanggal PR Nomor PR Kode Barang Nama Barang Jumlah Satuan Tanggal Diminta Datang Status Tanggal Diminta Datang Eksternal
result.Columns.Add(new DataColumn()
{
ColumnName = "Tanggal", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Sumber Penyimpanan", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Tujuan Penyimpanan", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Packing List", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Barcode", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Deskripsi", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Size", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "RO", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Satuan", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Quantity", DataType = typeof(double)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Diterima", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Ekspedisi", DataType = typeof(String)
});
//result.Columns.Add(new DataColumn() { ColumnName = "Jumlah Diminta", DataType = typeof(double) });
//result.Columns.Add(new DataColumn() { ColumnName = "Satuan Diminta", DataType = typeof(String) });
//result.Columns.Add(new DataColumn() { ColumnName = "Tanggal diminta datang", DataType = typeof(String) });
//result.Columns.Add(new DataColumn() { ColumnName = "Tanggal diminta datang PO Eksternal", DataType = typeof(String) });
//result.Columns.Add(new DataColumn() { ColumnName = "Jumlah Deal PO Eksternal", DataType = typeof(double) });
//result.Columns.Add(new DataColumn() { ColumnName = "Satuan Deal PO Eksternal", DataType = typeof(String) });
//result.Columns.Add(new DataColumn() { ColumnName = "Status PR", DataType = typeof(String) });
//result.Columns.Add(new DataColumn() { ColumnName = "Status Barang", DataType = typeof(String) });
if (Query.ToArray().Count() == 0)
{
result.Rows.Add("", "", "", "", "", "", "", "", "", 0, "", ""); // to allow column name to be generated properly for empty data as template
}
else
{
int index = 0;
foreach (var item in Query)
{
index++;
string date = item.date == null ? "-" : item.date.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
//string prDate = item.expectedDeliveryDatePR == new DateTime(1970, 1, 1) ? "-" : item.expectedDeliveryDatePR.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
//string epoDate = item.expectedDeliveryDatePO == new DateTime(1970, 1, 1) ? "-" : item.expectedDeliveryDatePO.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
result.Rows.Add(item.date, item.sourceName, item.destinationName, item.packingList, item.itemCode, item.itemName, item.itemSize, item.itemArticleRealizationOrder, item.itemUom, item.Quantity, item.isReceived, item.isDistributed);
}
}
return(Excel.CreateExcel(new List <KeyValuePair <DataTable, string> >()
{
new KeyValuePair <DataTable, string>(result, "Territory")
}, true));
}
internal static string InTouchDIntErrorNumber = "XlLogErrorNumber"; //An InTouch weiterzugebene Fehlernummer
#endregion
#endregion
static void Main(string[] args) //Fehlernummern siehe Log.cs 0101ZZ
{
#region Vorbereitende Abfragen
try
{
CmdArgs = args;
if (CmdArgs.Length < 1)
{
AppStartedBy = Environment.UserName;
}
else
{
AppStartedBy = CmdArgs[0].Remove(0, 1);
}
Config.LoadConfig();
Log.Write(Log.Cat.OnStart, Log.Prio.LogAlways, 010101, string.Format("Gestartet durch {0}, Debug {1}, V{2}", AppStartedBy, Log.DebugWord, System.Reflection.Assembly.GetExecutingAssembly().GetName().Version));
#region PDF erstellen per Drag&Drop
try
{
if (CmdArgs.Length > 0)
{
if (File.Exists(CmdArgs[0]) && Path.GetExtension(CmdArgs[0]) == ".xlsx")
{
//Wenn der Pfad zu einer Excel-Dateie übergebenen wurde, diese in PDF umwandeln, danach beenden
Console.WriteLine("Wandle Excel-Dateie in PDF " + CmdArgs[0]);
Log.Write(Log.Cat.PdfWrite, Log.Prio.LogAlways, 010100, "Wandle Excel-Datei in PDF " + CmdArgs[0]);
Pdf.CreatePdf(CmdArgs[0]);
Console.WriteLine("Exel-Datei " + CmdArgs[0] + " umgewandelt in PDF.\r\nBeliebige Taste drücken zum Beenden...");
Console.ReadKey();
return;
}
else if (!File.Exists(CmdArgs[0]) && Directory.Exists(CmdArgs[0]))
{
//Alle Excel-Dateien im übergebenen Ordner in PDF umwandeln, danach beenden
Console.WriteLine("Wandle alle Excel-Dateien in PDF im Ordner " + CmdArgs[0]);
Log.Write(Log.Cat.PdfWrite, Log.Prio.LogAlways, 010100, "Wandle alle Excel-Dateien in PDF im Ordner " + CmdArgs[0]);
Pdf.CreatePdf4AllXlsxInDir(CmdArgs[0], false);
Console.WriteLine("Exel-Dateien umgewandelt in " + CmdArgs[0] + "\r\nBeliebige Taste drücken zum Beenden...");
Console.ReadKey();
return;
}
}
}
catch
{
Log.Write(Log.Cat.PdfWrite, Log.Prio.Error, 010118, string.Format("Fehler beim Erstellen von PDF durch Drag'n'Drop. Aufrufargumente prüfen."));
}
#endregion
EmbededDLL.LoadDlls();
bool makerIsRunning = Process.GetProcessesByName("wm").Length != 0;
if (makerIsRunning)
{
Log.Write(Log.Cat.OnStart, Log.Prio.Error, 010102, "Das Programm kann nicht zusammen mit dem InTouch WindowMaker / Manager ausgeführt werden und wird deshalb beendet.");
Console.WriteLine("ACHTUNG: Das Programm kann nicht zusammen mit dem InTouch WindowMaker / Manager ausgeführt werden und wird deshalb beendet.");
Tools.Wait(10);
return;
}
bool viewerIsRunning = Process.GetProcessesByName("view").Length != 0;
if (!viewerIsRunning)
{
Log.Write(Log.Cat.OnStart, Log.Prio.Error, 010103, "Das Programm kann nicht ohne den InTouch Viewer ausgeführt werden und wird deshalb beendet.");
Console.WriteLine("ACHTUNG: Das Programm kann nicht ohne den InTouch Viewer ausgeführt werden und wird deshalb beendet.");
Tools.Wait(10);
return;
}
if (!File.Exists(NativeMethods.PtaccPath))
{
Log.Write(Log.Cat.InTouchDB, Log.Prio.Info, 010104, string.Format("Datei für 64bit-OS nicht gefunden: " + NativeMethods.PtaccPath));
if (!File.Exists(NativeMethods32.PtaccPath))
{
Log.Write(Log.Cat.InTouchDB, Log.Prio.Error, 010104, string.Format("Datei für 64bit oder 32bit-OS nicht gefunden: \r\n" +
NativeMethods.PtaccPath + "\r\n" +
NativeMethods32.PtaccPath + "\r\n"));
Console.WriteLine("ACHTUNG: Das Programm kann nicht ohne die Datei " + Path.GetFileName(NativeMethods32.PtaccPath) + " ausgeführt werden und wird deshalb beendet. Beachte Log-Datei.");
Tools.Wait(10);
return;
}
else
{
InTouch.Is32BitSystem = true;
}
}
else
{
InTouch.Is32BitSystem = false;
}
if (!File.Exists(NativeMethods.WwheapPath))
{
Log.Write(Log.Cat.InTouchDB, Log.Prio.Info, 010104, string.Format("Datei für 64bit-OS nicht gefunden: " + NativeMethods.WwheapPath));
if (!File.Exists(NativeMethods32.WwheapPath))
{
Log.Write(Log.Cat.InTouchDB, Log.Prio.Error, 010104, string.Format("Datei für 64bit oder 32bit-OS nicht gefunden: \r\n" +
NativeMethods.WwheapPath + "\r\n" +
NativeMethods32.WwheapPath + "\r\n"));
Console.WriteLine("ACHTUNG: Das Programm kann nicht ohne die Datei " + Path.GetFileName(NativeMethods32.WwheapPath) + " ausgeführt werden und wird deshalb beendet. Beachte Log-Datei.");
Tools.Wait(10);
return;
}
else
{
InTouch.Is32BitSystem = true;
}
}
else
{
InTouch.Is32BitSystem = false;
}
//if (!File.Exists(NativeMethods.WwheapPath))
//{
// Log.Write(Log.Cat.InTouchDB, Log.Prio.Error, 010107, string.Format("Datei nicht gefunden: " + NativeMethods.WwheapPath));
// Console.WriteLine("ACHTUNG: Das Programm kann nicht ohne die Datei " + NativeMethods.WwheapPath + " ausgeführt werden und wird deshalb beendet. Beachte Log-Datei.");
// if (Path.GetDirectoryName(NativeMethods.WwheapPath).Contains(" (x86)"))
// {
// Log.Write(Log.Cat.InTouchDB, Log.Prio.Error, 010108, string.Format("Dieses Programm ist für ein 64-Bit Betriebssystem ausgelegt."));
// }
// else if (Path.GetDirectoryName(NativeMethods.PtaccPath).StartsWith(@"C:\Program Files\"))
// {
// Log.Write(Log.Cat.InTouchDB, Log.Prio.Error, 010109, string.Format("Dieses Programm ist für ein 32-Bit Betriebssystem ausgelegt."));
// }
// Tools.Wait(10);
// return;
//}
if (!File.Exists(Excel.XlTemplateDayFilePath))
{
Log.Write(Log.Cat.InTouchDB, Log.Prio.Error, 010110, string.Format("Vorlage für Tagesdatei nicht gefunden unter: " + Excel.XlTemplateDayFilePath));
//AppErrorOccured = true;
}
if (!File.Exists(Excel.XlTemplateMonthFilePath))
{
Log.Write(Log.Cat.ExcelRead, Log.Prio.Warning, 010111, string.Format("Keine Vorlage für Monatsdatei gefunden."));
//kein Fehler
}
if ((bool)InTouch.ReadTag(InTouchDiscXlLogFlag) != true)
{
Log.Write(Log.Cat.InTouchVar, Log.Prio.Error, 010112, "Freigabe-Flag >" + InTouchDiscXlLogFlag + "< wurde nicht in InTouch gesetzt. Das Programm wird beendet.");
//AppErrorOccured = true;
return;
}
string Operator = (string)InTouch.ReadTag("$Operator");
Log.Write(Log.Cat.Info, Log.Prio.Info, 010113, "Angemeldet in InTouch: >" + Operator + "<");
Scheduler.CeckOrCreateTaskScheduler();
if (!Directory.Exists(Excel.XlArchiveDir))
{
try
{
Directory.CreateDirectory(Excel.XlArchiveDir);
}
catch (Exception ex)
{
Log.Write(Log.Cat.FileSystem, Log.Prio.Error, 010114, string.Format("Archivordner konnte nicht gefunden oder erstellt werden: {0}\r\n\t\t Typ: {1} \r\n\t\t Fehlertext: {2} \r\n\t\t InnerException: {3}", Excel.XlArchiveDir, ex.GetType().ToString(), ex.Message, ex.InnerException));
return;
}
}
}
catch (Exception ex)
{
Log.Write(Log.Cat.OnStart, Log.Prio.Error, 010115, string.Format("Fehler beim initialisieren der Anwendung: Typ: {0} \r\n\t\t Fehlertext: {1} \r\n\t\t InnerException: {2}", ex.GetType().ToString(), ex.Message, ex.InnerException));
return;
}
#endregion
Excel.XlFillWorkbook();
Print.PrintRoutine();
#region Diese *.exe beenden
InTouch.SetExcelAliveBit(Program.AppErrorOccured);
if (AppErrorOccured)
{
Log.Write(Log.Cat.OnStart, Log.Prio.Error, 010116, "XlLog.exe beendet. Es ist ein Fehler aufgetreten.\r\n\r\n");
}
else
{
Log.Write(Log.Cat.OnStart, Log.Prio.Info, 010117, "XlLog.exe ohne Fehler beendet.\r\n");
}
// Bei manuellem Start Fenster kurz offen halten.
if (AppStartedBy == Environment.UserName)
{
Tools.Wait(Tools.WaitToClose);
}
#endregion
}
public ExcelParameter(D.Point StartCell, D.Point EndCell, string value, params Microsoft.Office.Interop.Excel.XlBordersIndex[] borders)
: this(Excel.ColNumToEx(StartCell.X) + StartCell.Y.ToString(), Excel.ColNumToEx(EndCell.X) + EndCell.Y.ToString(), value, MExcel.XlHAlign.xlHAlignCenter, null, borders)
{
}
public int Execute()
{
string strGUID = Guid.NewGuid().ToString();
int realImportNum = 0;
Excel _excel = new Excel();
DataTable dt = new DataTable();
switch (TextType)
{
case "ASTM":
s_ASTM ASTM = new s_ASTM();
realImportNum = ASTM.Execute(IiSheet, strGUID);
break;
case "Digitimes":
s_Digitimes Digi = new s_Digitimes();
realImportNum = Digi.Execute(IiSheet, strGUID);
break;
case "EV":
s_EV EV = new s_EV();
realImportNum = EV.Execute(IiSheet, strGUID);
break;
case "Goldfire":
s_IHS IHS = new s_IHS();
realImportNum = IHS.Execute(IiSheet, strGUID);
break;
case "JCR":
s_JCR JCR = new s_JCR();
realImportNum = JCR.Execute(IiSheet, strGUID);
break;
case "nature":
s_nature nature = new s_nature();
realImportNum = nature.Execute(IiSheet, strGUID);
break;
case "Oxford":
s_Oxford Oxford = new s_Oxford();
realImportNum = Oxford.Execute(IiSheet, strGUID);
break;
case "Science":
s_Science Science = new s_Science();
realImportNum = Science.Execute(IiSheet, strGUID);
break;
//
//phase II below
//
case "ACM":
s_ACM ACM = new s_ACM();
realImportNum = ACM.Execute(IiSheet, strGUID);
break;
case "ACS":
s_ACS ACS = new s_ACS();
realImportNum = ACS.Execute(IiSheet, strGUID);
break;
case "IEEE":
s_IEEE IEEE = new s_IEEE();
realImportNum = IEEE.Execute(IiSheet, strGUID);
break;
case "SDOL":
s_SDOL SDOL = new s_SDOL();
realImportNum = SDOL.Execute(IiSheet, strGUID);
break;
case "SPIE":
s_SPIE SPIE = new s_SPIE();
realImportNum = SPIE.Execute(IiSheet, strGUID);
break;
case "TI":
s_TI TI = new s_TI();
realImportNum = TI.Execute(IiSheet, strGUID);
break;
case "Wiley":
s_Wiley Wiley = new s_Wiley();
realImportNum = Wiley.Execute(IiSheet, strGUID);
break;
case "天下":
s_TangShang TangShang = new s_TangShang();
realImportNum = TangShang.Execute(IiSheet, strGUID);
break;
case "萬方":
s_WangFang WangFang = new s_WangFang();
realImportNum = WangFang.Execute(IiSheet, strGUID);
break;
case "通用":
dt = _excel.ImportExecl(IiSheet);
s_Utility Utility = new s_Utility();
realImportNum = Utility.Execute(dt, strGUID, Year, Month);
break;
case "工程":
dt = _excel.ImportExecl(IiSheet);
s_Engineering Engineering = new s_Engineering();
realImportNum = Engineering.Execute(dt, strGUID);
break;
case "聯合知識庫":
dt = _excel.ImportExecl(IiSheet);
s_Union Union = new s_Union();
realImportNum = Union.Execute(dt, strGUID);
break;
case "SciFinder":
dt = _excel.ImportExecl(IiSheet);
s_SciFinder SciFinder = new s_SciFinder();
realImportNum = SciFinder.Execute(dt, strGUID);
break;
}
//每一次匯入都要做log,判斷等不等於零是因為沒資料就不要做log
if (TextType.Trim() != "通用" && realImportNum != 0)
{
string yearMonth = Year + Month + "01";
DateTime dTime = DateTime.ParseExact(yearMonth, "yyyyMMdd", CultureInfo.InvariantCulture);
//通用的log自己在裡面做
saveLog(realImportNum, strGUID, TextType, dTime);
}
//做完匯入之後再把工研人塞入
UpdateEmpno(strGUID);
//由於在通用的表單裡面,有些資料會有email,但是沒IP跟工號,
//而也有可能是有工號沒IP沒EMAIL
//所以要再等根據IP更新之後
//2016/02/02 由於Goldfile有可能會用中文姓名+部門+單位來當WHERE條件,所以在這裡多一步去補上
if ((TextType.Trim() == "通用" ||
TextType.Trim() == "Goldfire" ||
TextType.Trim() == "TI" ||
TextType.Trim() == "聯合知識庫" ||
TextType.Trim() == "SciFinder") && realImportNum != 0)
{
UpdateUtility(strGUID);
}
return(realImportNum);
}
/// <summary>
/// конструктор с указанием координта ячеек в цифровом виде
/// (нумерация с 1 начинается)
/// </summary>
/// <param name="f"></param>
/// <param name="Align"></param>
/// <param name="nameOfExcel"></param>
/// <param name="nameOfEndMergeExcel"></param>
/// <param name="value"></param>
public ExcelParameter(MExcel.XlHAlign Align, D.Point nameOfExcel, string value)
: this(Excel.ColNumToEx(nameOfExcel.X) + nameOfExcel.Y.ToString(), Excel.ColNumToEx(nameOfExcel.X) + nameOfExcel.Y.ToString(),
value, Align, null, null)
{
}
private void Button_Click(object sender, System.Windows.RoutedEventArgs e)
{
Excel.ExportToExcelAndCsv(grilla);
}
public MemoryStream GenerateExcel(DateTime?dateFrom, DateTime?dateTo, int offset)
{
var Query = GetReportQuery(dateFrom, dateTo, offset);
Query = Query.OrderByDescending(b => b.ReceiptDate);
var QtyTotal = Query.Sum(x => x.Quantity);
var PriceTotal = Math.Round(Query.Sum(x => x.Price), 2);
DataTable result = new DataTable();
result.Columns.Add(new DataColumn()
{
ColumnName = "No", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "No Bon Terima", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Tanggal Bon", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Asal barang", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "No Bon Pengeluaran Barang", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "RO", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Kode Komoditi", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Komoditi", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Qty", DataType = typeof(double)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Satuan", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Price", DataType = typeof(double)
});
if (Query.ToArray().Count() == 0)
{
result.Rows.Add("", "", "", "", "", "", "", "", 0, "", 0); // to allow column name to be generated properly for empty data as template
}
else
{
int index = 0;
foreach (var item in Query)
{
index++;
//DateTimeOffset date = item.date ?? new DateTime(1970, 1, 1);
//string dateString = date == new DateTime(1970, 1, 1) ? "-" : date.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
result.Rows.Add(index, item.ReceiptNoteNo, item.ReceiptDate.AddHours(offset).ToString("dd MMM yyyy", new CultureInfo("id-ID")), item.UnitFromCode, item.ExpenditureGoodNo, item.RONo, item.ComodityCode, item.ComodityName, item.Quantity, item.UomUnit);
}
result.Rows.Add("", "T O T A L .......", "", "", "", "", "", "", QtyTotal, "", PriceTotal);
}
return(Excel.CreateExcel(new List <KeyValuePair <DataTable, string> >()
{
new KeyValuePair <DataTable, string>(result, "Report Pengeluaran Gudang Sisa Barang Jadi")
}, true));
}
public ColHeaderPanel(Excel p_excel)
: base(p_excel, SheetArea.ColumnHeader)
{
}
public MemoryStream GenerateExcel(string storageCode, string productCode, string type, DateTime?dateFrom, DateTime?dateTo, int offset)
{
var query = GetReportQuery(storageCode, productCode, type, dateFrom, dateTo, offset);
query = query.OrderByDescending(b => b.LastModifiedUtc);
DataTable result = new DataTable();
result.Columns.Add(new DataColumn()
{
ColumnName = "No", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Storage", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Reference number", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "RequestType Reference", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Date", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Item name", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Uom", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Before", DataType = typeof(double)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Quantity", DataType = typeof(double)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "After", DataType = typeof(double)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Type", DataType = typeof(string)
});
if (query.ToArray().Count() == 0)
{
result.Rows.Add(string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, 0, 0, 0, string.Empty); // to allow column name to be generated properly for empty data as template
}
else
{
int index = 0;
foreach (var item in query)
{
index++;
//DateTimeOffset date = item.date ?? new DateTime(1970, 1, 1);
//string dateString = date == new DateTime(1970, 1, 1) ? "-" : date.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
result.Rows.Add(index, item.StorageName, item.ReferenceNo, item.ReferenceType, item.Date.ToString("dd MMM yyyy", new CultureInfo("id-ID")), item.ProductName, item.UomUnit, item.Before,
item.Quantity, item.After, item.Type);
}
}
return(Excel.CreateExcel(new List <KeyValuePair <DataTable, string> >()
{
new KeyValuePair <DataTable, string>(result, "Territory")
}, true));
}
public MemoryStream GenerateExcel(DateTime?dateFrom, DateTime?dateTo, int offset)
{
var Query = GetMachineMutationReport(dateFrom, dateTo, offset);
Query = Query.OrderBy(b => b.KodeBarang);
DataTable result = new DataTable();
result.Columns.Add(new DataColumn()
{
ColumnName = "Kode Barang", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Nama Barang", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Sat", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Saldo Awal", DataType = typeof(Double)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Pemasukan", DataType = typeof(Double)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Pengeluaran", DataType = typeof(Double)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Penyesuaian", DataType = typeof(Double)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Saldo Buku", DataType = typeof(Double)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Stock Opname", DataType = typeof(Double)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Selisih", DataType = typeof(Double)
});
if (Query.ToArray().Count() == 0)
{
result.Rows.Add("", "", "", "", "", "", "", "", "", ""); // to allow column name to be generated properly for empty data as template
}
else
{
foreach (var item in Query)
{
result.Rows.Add((item.KodeBarang), item.NamaBarang, item.UnitQtyName, item.SaldoAwal, item.Pemasukan, item.Pengeluaran, item.Penyesuaian, item.SaldoBuku, item.StockOpname, item.Selisih);
}
}
return(Excel.CreateExcel(new List <KeyValuePair <DataTable, string> >()
{
new KeyValuePair <DataTable, string>(result, "Territory")
}, true));
}
public SetValueCommand(Excel p_excel, DataValidationListButtonInfo info)
{
_info = info;
_excel = p_excel;
}
public MemoryStream GenerateExcelEPODODuration(string unit, string duration, DateTime?dateFrom, DateTime?dateTo, int offset)
{
var Query = GetEPODODurationReportQuery(unit, duration, dateFrom, dateTo, offset);
Query = Query.OrderByDescending(b => b.prCreatedDate);
DataTable result = new DataTable();
//No Unit Budget Kategori Tanggal PR Nomor PR Kode Barang Nama Barang Jumlah Satuan Tanggal Diminta Datang Status Tanggal Diminta Datang Eksternal
result.Columns.Add(new DataColumn()
{
ColumnName = "No", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Tanggal Purchase Request", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Tanggal Buat Purchase Request", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Nomor Purchase Request", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Divisi", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Unit", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Budget", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Kategori", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Kode Barang", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Nama Barang", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Jumlah Barang", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Satuan Barang", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Harga Barang", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Kode Supplier", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Nama Supplier", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Tanggal Terima PO Internal", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Tanggal PO Eksternal", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Tanggal Buat PO Eksternal", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Tanggal Target Datang", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "No PO Eksternal", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Tanggal Surat Jalan", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Tanggal Datang Barang", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "No Surat Jalan", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Selisih Tanggal PO Eksternal - Surat Jalan (hari)", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Nama Staff Pembelian", DataType = typeof(string)
});
if (Query.ToArray().Count() == 0)
{
result.Rows.Add("", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", ""); // to allow column name to be generated properly for empty data as template
}
else
{
int index = 0;
foreach (var item in Query)
{
index++;
string prDate = item.prDate == null ? "-" : item.prDate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
string prCreatedDate = item.prCreatedDate == new DateTime(1970, 1, 1) ? "-" : item.prCreatedDate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
string poDate = item.poDate == new DateTime(1970, 1, 1) ? "-" : item.poDate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
string orderDate = item.orderDate == new DateTime(1970, 1, 1) ? "-" : item.orderDate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
string ePOCreatedDate = item.ePOCreatedDate == new DateTime(1970, 1, 1) ? "-" : item.ePOCreatedDate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
string deliveryDate = item.deliveryDate == new DateTime(1970, 1, 1) ? "-" : item.deliveryDate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
string dODate = item.dODate == new DateTime(1970, 1, 1) ? "-" : item.dODate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
string arrivalDate = item.arrivalDate == new DateTime(1970, 1, 1) ? "-" : item.arrivalDate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
result.Rows.Add(index, prDate, prCreatedDate, item.prNo, item.division, item.unit, item.budget, item.category, item.productCode, item.productName, item.dealQuantity, item.dealUomUnit, item.pricePerDealUnit, item.supplierCode, item.supplierName, poDate, orderDate, ePOCreatedDate, deliveryDate, item.ePONo, dODate, arrivalDate, item.dONo, item.dateDiff, item.staff);
}
}
return(Excel.CreateExcel(new List <KeyValuePair <DataTable, string> >()
{
new KeyValuePair <DataTable, string>(result, "Territory")
}, true));
}
//public Tuple<List<UnitReceiptNoteGenerateDataViewModel>, int> GetDisplayReport(DateTime? dateFrom, DateTime? dateTo, int page, int size, string Order, int offset)
//{
// var Query = GetReportQuery(dateFrom, dateTo, offset);
// Dictionary<string, string> OrderDictionary = JsonConvert.DeserializeObject<Dictionary<string, string>>(Order);
// if (OrderDictionary.Count.Equals(0))
// {
// Query = Query.OrderBy(b => b.UnitName).ThenBy(b => b.PRNo);
// }
// else
// {
// string Key = OrderDictionary.Keys.First();
// string OrderType = OrderDictionary[Key];
// }
// var q = Query.ToList();
// var index = 0;
// foreach (UnitReceiptNoteGenerateDataViewModel a in q)
// {
// index++;
// }
// Pageable<UnitReceiptNoteGenerateDataViewModel> pageable = new Pageable<UnitReceiptNoteGenerateDataViewModel>(Query, page - 1, size);
// List<UnitReceiptNoteGenerateDataViewModel> Data = pageable.Data.ToList<UnitReceiptNoteGenerateDataViewModel>();
// int TotalData = pageable.TotalCount;
// return Tuple.Create(Data, TotalData);
//}
public MemoryStream GenerateExcel(DateTime?dateFrom, DateTime?dateTo, int offset)
{
var Query = GetReportQuery(dateFrom, dateTo, offset);
Query = Query.OrderBy(b => b.URNNo);
DataTable result = new DataTable();
result.Columns.Add(new DataColumn()
{
ColumnName = "NOMOR BON TERIMA UNIT", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "TANGGAL BON TERIMA UNIT", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "BAGIAN", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "KODE SUPPLIER", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "NAMA SUPPLIER", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "NOMOR SURAT JALAN", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "KETERANGAN", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "KODE BARANG", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "NAMA BARANG", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "JUMLAH TERIMA", DataType = typeof(double)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "SATUAN BARANG", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "KETERANGAN BARANG", DataType = typeof(String)
});
if (Query.ToArray().Count() == 0)
{
result.Rows.Add("", "", "", "", "", "", "", "", "", 0, "", ""); // to allow column name to be generated properly for empty data as template
}
else
{
var index = 0;
foreach (var item in Query)
{
index++;
string URNDate = item.URNDate == new DateTime(1970, 1, 1) ? "-" : item.URNDate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd/MM/yyyy", new CultureInfo("id-ID"));
result.Rows.Add(item.URNNo, URNDate, item.UnitName, item.SupplierCode, item.SupplierName, item.DONo, item.URNRemark, item.ProductCode, item.ProductName, item.Quantity, item.UOMUnit, item.Remark);
}
}
return(Excel.CreateExcel(new List <KeyValuePair <DataTable, string> >()
{
new KeyValuePair <DataTable, string>(result, "Sheet1")
}, true));
}
public MemoryStream GenerateExcelMutation(DateTime dateFrom, DateTime dateTo)
{
var Query = GetReportQuery(dateFrom, dateTo);
DataTable Result = new DataTable();
Result.Columns.Add(new DataColumn()
{
ColumnName = "Kode Barang", DataType = typeof(String)
});
Result.Columns.Add(new DataColumn()
{
ColumnName = "Nama Barang", DataType = typeof(String)
});
Result.Columns.Add(new DataColumn()
{
ColumnName = "Satuan", DataType = typeof(String)
});
Result.Columns.Add(new DataColumn()
{
ColumnName = "Saldo Awal", DataType = typeof(Double)
});
Result.Columns.Add(new DataColumn()
{
ColumnName = "Pemasukan", DataType = typeof(Double)
});
Result.Columns.Add(new DataColumn()
{
ColumnName = "Pengeluaran", DataType = typeof(Double)
});
Result.Columns.Add(new DataColumn()
{
ColumnName = "Penyesuaian", DataType = typeof(Double)
});
Result.Columns.Add(new DataColumn()
{
ColumnName = "Saldo Akhir", DataType = typeof(Double)
});
Result.Columns.Add(new DataColumn()
{
ColumnName = "Stock Opname", DataType = typeof(Double)
});
Result.Columns.Add(new DataColumn()
{
ColumnName = "Selisih", DataType = typeof(Double)
});
if (Query.ToArray().Count() == 0)
{
Result.Rows.Add("", "", "", 0, 0, 0, 0, 0, 0, 0);
}
else
{
foreach (var item in Query)
{
Result.Rows.Add(item.ClassificationCode, item.ClassificationName, item.UnitQtyName, item.SaldoAwal, item.Pemasukan, item.Pengeluaran, item.Penyesuaian, item.SaldoAkhir, item.StockOpname, item.Selisih);
}
}
return(Excel.CreateExcel(new List <KeyValuePair <DataTable, string> >()
{
new KeyValuePair <DataTable, string>(Result, "ScrapReject")
}, true));
}
protected void imgExportToExcel_Click(object sender, ImageClickEventArgs e)
{
try
{
Utility objUtil = new Utility();
DataTable dt = new DataTable();
dt = Session[clsConstant.SESS_TABLE] as DataTable;
Excel excel = new Excel();
ArrayList list = new ArrayList();
clsExcelObject objExcel = null;
List <Flags> flag = new List <Flags>();
Flags testFlag = null;
//if (txtTopic.Text != "")
//{
// testFlag = new Flags();
// testFlag.key = "Topic";
// testFlag.value = txtTopic.Text;
// flag.Add(testFlag);
//}
//if (drpSector.SelectedIndex != 0)
//{
// testFlag = new Flags();
// testFlag.key = "Sector";
// testFlag.value = drpSector.SelectedItem.Text;
// flag.Add(testFlag);
//}
//if (txtFromDate.Text != "")
//{
// testFlag = new Flags();
// testFlag.key = "From Date";
// testFlag.value = txtFromDate.Text;
// flag.Add(testFlag);
//}
//if (txtToDate.Text != "")
//{
// testFlag = new Flags();
// testFlag.key = "From Date";
// testFlag.value = txtFromDate.Text;
// flag.Add(testFlag);
//}
//if (txtVenue.Text != "")
//{
// testFlag = new Flags();
// testFlag.key = "Venue";
// testFlag.value = txtVenue.Text;
// flag.Add(testFlag);
//}
//if (drpTargetAudience.SelectedIndex != 0)
//{
// testFlag = new Flags();
// testFlag.key = "Target Audience";
// testFlag.value = drpTargetAudience.SelectedItem.Text;
// flag.Add(testFlag);
//}
//if (drpInvite.SelectedIndex != 0)
//{
// testFlag = new Flags();
// testFlag.key = "Invite By";
// testFlag.value = drpInvite.SelectedItem.Text;
// flag.Add(testFlag);
//}
//if (drpNationalInternational.SelectedIndex != 0)
//{
// testFlag = new Flags();
// testFlag.key = "National / International";
// testFlag.value = drpNationalInternational.SelectedItem.Text;
// flag.Add(testFlag);
//}
//if (txtLstDate.Text != "")
//{
// testFlag = new Flags();
// testFlag.key = "Last Date of Nomination";
// testFlag.value = txtLstDate.Text;
// flag.Add(testFlag);
//}
string[] header = new string[]
{
"vsTopic",
"Sector",
"dtFromDate",
"dtToDate",
"vsVenue",
"iTargetID",
"iInvite",
"iSeats",
"CreatedFrom",
"dtLastDate",
"vsFileName"
};
string[] datacolumn = new string[grdWorkShopDetailsMDB.Columns.Count - 4];
int counter = 0;
for (int i = 1; i <= header.Length + 1; i++)
{
if (grdWorkShopDetailsMDB.Columns[i].Visible != false)
{
objExcel = new clsExcelObject(header[counter], grdWorkShopDetailsMDB.Columns[i].HeaderText.Replace("<br />", ""));
list.Add(objExcel);
counter++;
}
}
//excel.FlagValues = flag;
//excel.TableHeader = "WorkShop Details";
excel.ExportToExcel(dt, "WorkShop Details MDB", list);
}
catch (Exception ex)
{
throw ex;
}
}
public MemoryStream GenerateExcel(DateTime?dateFrom, DateTime?dateTo, int offset)
{
var Query = GetData(dateFrom, dateTo, offset);
var data = Query.ToList();
DataTable result = new DataTable();
result.Columns.Add(new DataColumn()
{
ColumnName = "NO", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "KOMODITI", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "NAMA BUYER", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "U N I T", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "NO INVOICE", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "T R U C K I N G", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "R/O", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "I T E M", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "STYLE ORD / ART NO.", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "QUANTITY", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "SATUAN", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "AMOUNT", DataType = typeof(string)
});
List <(string, Enum, Enum)> mergeCells = new List <(string, Enum, Enum)>()
{
};
int rowPosition = 2;
if (data != null && data.Count > 0)
{
var grandTotalByUom = new List <TotalByUom>();
foreach (var d in data)
{
var ucFirstMergedRowPosition = rowPosition;
var ucLastMergedRowPosition = rowPosition;
foreach (var buyer in d.Buyers)
{
var buyerFirstMergedRowPosition = rowPosition;
var buyerLastMergedRowPosition = rowPosition;
var index = 0;
foreach (var detail in buyer.Details)
{
index++;
result.Rows.Add(index, d.ComodityName, buyer.Buyer, detail.UnitCode, detail.InvoiceNo, detail.TruckingDate.ToString("dd MMMM yyyy", new CultureInfo("id-ID")), detail.RO_Number, detail.ComodityName, detail.ComodityDesc, detail.Quantity, detail.UOMUnit, detail.Amount);
buyerLastMergedRowPosition = rowPosition++;
var currentUom = grandTotalByUom.FirstOrDefault(c => c.uom == detail.UOMUnit);
if (currentUom == null)
{
grandTotalByUom.Add(new TotalByUom
{
uom = detail.UOMUnit,
quantity = detail.Quantity,
amount = detail.Amount
});
}
else
{
currentUom.quantity += detail.Quantity;
currentUom.amount += detail.Amount;
}
}
result.Rows.Add(null, null, "SUB TOTAL", null, null, null, null, null, null, buyer.Quantities, null, buyer.Amounts);
mergeCells.Add(($"B{rowPosition}:G{rowPosition}", ExcelHorizontalAlignment.Right, ExcelVerticalAlignment.Bottom));
ucLastMergedRowPosition = rowPosition++;
}
if (ucFirstMergedRowPosition != ucLastMergedRowPosition)
{
mergeCells.Add(($"A{ucFirstMergedRowPosition}:A{ucLastMergedRowPosition}", ExcelHorizontalAlignment.Left, ExcelVerticalAlignment.Top));
}
}
result.Rows.Add(null, null, null, null, null, null, null, null, null, null, null, null);
result.Rows.Add(null, null, null, null, null, null, null, null, null, null, null, null);
rowPosition++;
foreach (var i in Enumerable.Range(0, grandTotalByUom.Count))
{
if (i == 0)
{
result.Rows.Add(null, null, "GRAND TOTAL", grandTotalByUom[i].quantity, grandTotalByUom[i].uom, null, grandTotalByUom[i].amount, null, null, "GRAND TOTAL", data.Sum(d => d.Buyers.Sum(b => b.Details.Sum(dtl => dtl.Amount))), null);
}
else
{
result.Rows.Add(null, null, null, grandTotalByUom[i].quantity, grandTotalByUom[i].uom, null, grandTotalByUom[i].amount, null, null, null, null, null);
}
mergeCells.Add(($"D{++rowPosition}:D{rowPosition}", ExcelHorizontalAlignment.Right, ExcelVerticalAlignment.Bottom));
}
}
else
{
result.Rows.Add(null, null, null, null, null, null, null, null, null, null, null, null);
}
var excel = Excel.CreateExcel(new List <KeyValuePair <DataTable, string> >()
{
new KeyValuePair <DataTable, string>(result, "GarmentOmzet")
}, true);
return(excel);
}
public MemoryStream GenerateExcel(int unitId, long vbRequestId, string applicantName, string clearanceStatus, DateTimeOffset?requestDateFrom, DateTimeOffset?requestDateTo, DateTimeOffset?realizeDateFrom, DateTimeOffset?realizeDateTo, DateTimeOffset?clearanceDateFrom, DateTimeOffset?clearanceDateTo, int offSet)
{
var data = NewGetReportQuery(unitId, vbRequestId, applicantName, clearanceStatus, requestDateFrom, requestDateTo, realizeDateFrom, realizeDateTo, clearanceDateFrom, clearanceDateTo, offSet);
var currencyGroup = data
.GroupBy(element => element.CurrencyCode)
.Select(group => new VBStatusByCurrencyReportViewModel()
{
CurrencyCode = group.Key,
Total = group.Sum(element => element.Amount)
}).ToList();
var dt = new DataTable();
dt.Columns.Add(new DataColumn()
{
ColumnName = "No VB", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Tanggal VB", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Estimasi Tgl Realisasi", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Unit", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Pemohon VB", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Tanggal Approval", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "No Realisasi", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Tgl Realisasi", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Keperluan VB", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Aging (Hari)", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Mata Uang", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Jumlah VB", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Realisasi", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Sisa (Kurang/Lebih)", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Tanggal Clearance", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Status", DataType = typeof(string)
});
var dtCurrency = new DataTable();
dtCurrency.Columns.Add(new DataColumn()
{
ColumnName = "Mata Uang", DataType = typeof(string)
});
dtCurrency.Columns.Add(new DataColumn()
{
ColumnName = "Total", DataType = typeof(string)
});
var requestTotal = 0.0;
var realizationTotal = 0.0;
if (data.Count == 0)
{
dt.Rows.Add("", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "");
}
else
{
data = data.OrderByDescending(s => s.LastModifiedUtc).ToList();
foreach (var item in data)
{
dt.Rows.Add(item.VBNo, item.Date, item.DateEstimate, item.Unit.Name, item.CreateBy, item.ApprovalDate, item.RealizationNo, item.RealizationDate, item.Usage, item.Aging, item.CurrencyCode,
item.Amount.ToString("#,##0.###0"), item.RealizationAmount.ToString("#,##0.###0"), item.Difference.ToString("#,##0.###0"), item.ClearenceDate, item.Status);
}
requestTotal = (double)data.Sum(element => element.Amount);
realizationTotal = (double)data.Sum(element => element.RealizationAmount);
}
if (currencyGroup.Count == 0)
{
dtCurrency.Rows.Add("", "");
}
else
{
currencyGroup = currencyGroup.OrderBy(element => element.CurrencyCode).ToList();
foreach (var item in currencyGroup)
{
dtCurrency.Rows.Add(item.CurrencyCode, item.Total.ToString("#,##0.###0"));
}
}
return(Excel.CreateExcelVBStatusReport(new KeyValuePair <DataTable, string>(dt, "Status VB"), new KeyValuePair <DataTable, string>(dtCurrency, "MataUang"), requestDateFrom.GetValueOrDefault(), requestDateTo.GetValueOrDefault(), true, requestTotal, realizationTotal));
}
public string ExportExcelForStudents(Board board)
{
var fileName = board.Group.GroupName + "_" + board.BoardId + "_resultForStudents" + @".xlsx";
var formFolderPath = Path.Combine(host.ContentRootPath, "forms");
if (!System.IO.Directory.Exists(formFolderPath))
{
System.IO.Directory.CreateDirectory(formFolderPath);
}
var formFilePath = Path.Combine(formFolderPath, @"result_form.xlsx");
// FileInfo file = new FileInfo(Path.Combine(rootFolder, fileName));
var uploadFolderPath = Path.Combine(host.ContentRootPath, "exports/excel");
if (!System.IO.Directory.Exists(uploadFolderPath))
{
System.IO.Directory.CreateDirectory(uploadFolderPath);
}
var filePath = Path.Combine(uploadFolderPath, fileName);
//copy file from formfolder to export folder
System.IO.File.Copy(formFilePath, filePath, true);
FileInfo file = new FileInfo(Path.Combine(uploadFolderPath, fileName));
using (ExcelPackage package = new ExcelPackage(file))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
int studentRows = board.Group.Enrollments.Count();
worksheet.Cells[8, 3].Value = board.Group.Project.Title;
int row = 12;
var studentNames = board.Group.Enrollments.Select(e => e.Student.Name).ToList();
foreach (var studentName in studentNames)
{
worksheet.Cells[row, 3].Value = studentName;
row++;
}
row = 21;
var lecturerInformations = board.BoardEnrollments.ToList();
foreach (var lecturerInformation in lecturerInformations)
{
worksheet.Cells[row, 2].Value = lecturerInformation.Lecturer.Name;
worksheet.Cells[row, 3].Value = lecturerInformation.BoardRole.BoardRoleName;
worksheet.Cells[row, 4].Value = lecturerInformation.Percentage;
worksheet.Cells[row, 5].Value = lecturerInformation.Comment;
worksheet.Cells[row, 6].Value = lecturerInformation.Score;
row++;
}
if (board.ResultScore != null)
{
worksheet.Cells[25, 6].Value = board.ResultScore;
worksheet.Cells[26, 6].Value = board.ResultGrade;
}
worksheet.PrinterSettings.FitToPage = true;
worksheet.PrinterSettings.FitToWidth = 1;
worksheet.PrinterSettings.FitToHeight = 0;
package.Save();
//add to db
var excel = new Excel {
FileName = fileName
};
excelRepository.AddExcel(excel);
//send mail
//SendMail(board, filePath);
return(filePath);
}
}
public GcRangeGroup(Excel p_excel)
: base(p_excel)
{
_groupLineInfos = new List <GroupLineInfo>();
_groupDotInfos = new List <GroupDotInfo>();
}
partial void UpdateExcel(Excel instance);
public string ExportExcelForLecturers(Board board, BoardEnrollment boardEnrollment)
{
var fileName = board.Group.GroupName + "_" + board.BoardId + "_resultForLecturers" + @".xlsx";
var formFolderPath = Path.Combine(host.ContentRootPath, "forms");
if (!System.IO.Directory.Exists(formFolderPath))
{
System.IO.Directory.CreateDirectory(formFolderPath);
}
if (!board.Group.Project.Type.Equals("Final Project"))
{
var formFilePath = Path.Combine(formFolderPath, @"lecturer_form.xlsx");
// FileInfo file = new FileInfo(Path.Combine(rootFolder, fileName));
var uploadFolderPath = Path.Combine(host.ContentRootPath, "exports/excel");
if (!System.IO.Directory.Exists(uploadFolderPath))
{
System.IO.Directory.CreateDirectory(uploadFolderPath);
}
var filePath = Path.Combine(uploadFolderPath, fileName);
//copy file from formfolder to export folder
System.IO.File.Copy(formFilePath, filePath, true);
FileInfo file = new FileInfo(Path.Combine(uploadFolderPath, fileName));
using (ExcelPackage package = new ExcelPackage(file))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
int studentRows = board.Group.Enrollments.Count();
worksheet.Cells[8, 3].Value = board.Group.Project.Title;
int row = 12;
var studentNames = board.Group.Enrollments.Select(e => e.Student.Name).ToList();
foreach (var studentName in studentNames)
{
worksheet.Cells[row, 3].Value = studentName;
row++;
}
worksheet.Cells[16, 3].Value = boardEnrollment.Lecturer.Name;
//add grade
var firstGrade = boardEnrollment.Grades.FirstOrDefault(g => g.GradeDescription.Equals("Trình bày tốt (Chuẩn bị slide tốt, trình bày rõ ràng đúng thời hạn)"));
worksheet.Cells[23, 4].Value = firstGrade.Score;
worksheet.Cells[23, 5].Value = firstGrade.Comment;
var secondGrade = boardEnrollment.Grades.FirstOrDefault(g => g.GradeDescription.Equals("Nội dung đề tai đạt yêu cầu đặt ra, có tính khoa học"));
worksheet.Cells[24, 4].Value = secondGrade.Score;
worksheet.Cells[24, 5].Value = secondGrade.Comment;
var thirdGrade = boardEnrollment.Grades.FirstOrDefault(g => g.GradeDescription.Equals("Phương pháp thực hiện tốt"));
worksheet.Cells[25, 4].Value = thirdGrade.Score;
worksheet.Cells[25, 5].Value = thirdGrade.Comment;
var fourthGrade = boardEnrollment.Grades.FirstOrDefault(g => g.GradeDescription.Equals("Kết quả đề tài có áp dụng thực tế"));
worksheet.Cells[26, 4].Value = fourthGrade.Score;
worksheet.Cells[26, 5].Value = fourthGrade.Comment;
var fifthGrade = boardEnrollment.Grades.FirstOrDefault(g => g.GradeDescription.Equals("Đề tài mới hoặc phương pháp thực hiện có tính sáng tạo"));
worksheet.Cells[27, 4].Value = fifthGrade.Score;
worksheet.Cells[27, 5].Value = fifthGrade.Comment;
var sixthGrade = boardEnrollment.Grades.FirstOrDefault(g => g.GradeDescription.Equals("Trả lời tập trung vào đề tài, trả lời tốt câu hỏi"));
worksheet.Cells[29, 4].Value = sixthGrade.Score;
worksheet.Cells[29, 5].Value = sixthGrade.Comment;
worksheet.Cells[30, 4].Value = firstGrade.Score.Value + secondGrade.Score.Value + thirdGrade.Score.Value + fourthGrade.Score.Value
+ fifthGrade.Score.Value + sixthGrade.Score.Value;
// worksheet.Cells[22, 2].Value = boardEnrollment.Score;
// worksheet.Cells[22, 3].Value = boardEnrollment.Comment;
// row = 22;
// foreach (var recommendation in boardEnrollment.Recommendations)
// {
// worksheet.Cells[row, 5].Value = recommendation.Description;
// row++;
// }
worksheet.Cells[32, 5].Value = "Ngày " + DateTime.Now.Day.ToString()
+ " Tháng " + DateTime.Now.Month.ToString() + " Năm " + DateTime.Now.Year.ToString();
worksheet.PrinterSettings.FitToPage = true;
worksheet.PrinterSettings.FitToWidth = 1;
worksheet.PrinterSettings.FitToHeight = 0;
package.Save();
//add to db
var excel = new Excel {
FileName = fileName
};
excelRepository.AddExcel(excel);
//send mail
//SendMail(board, filePath);
return(filePath);
}
}
else
{
var formFilePath = Path.Combine(formFolderPath, @"lecturer_final_form.xlsx");
// FileInfo file = new FileInfo(Path.Combine(rootFolder, fileName));
var uploadFolderPath = Path.Combine(host.ContentRootPath, "exports/excel");
if (!System.IO.Directory.Exists(uploadFolderPath))
{
System.IO.Directory.CreateDirectory(uploadFolderPath);
}
var filePath = Path.Combine(uploadFolderPath, fileName);
//copy file from formfolder to export folder
System.IO.File.Copy(formFilePath, filePath, true);
FileInfo file = new FileInfo(Path.Combine(uploadFolderPath, fileName));
using (ExcelPackage package = new ExcelPackage(file))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
int studentRows = board.Group.Enrollments.Count();
worksheet.Cells[8, 3].Value = board.Group.Project.Title;
int row = 12;
var studentNames = board.Group.Enrollments.Select(e => e.Student.Name).ToList();
foreach (var studentName in studentNames)
{
worksheet.Cells[row, 3].Value = studentName;
row++;
}
worksheet.Cells[16, 3].Value = boardEnrollment.Lecturer.Name;
//add grade
var firstGrade = boardEnrollment.Grades.FirstOrDefault(g => g.GradeDescription.Equals("Introduction"));
worksheet.Cells[23, FindNumberOfCell(firstGrade.Score.Value)].Value = "X";
var a = FindNumberOfCell(firstGrade.Score.Value);
worksheet.Cells[23, 15].Value = firstGrade.Comment;
var secondGrade = boardEnrollment.Grades.FirstOrDefault(g => g.GradeDescription.Equals("Content (methods)"));
worksheet.Cells[24, FindNumberOfCell(secondGrade.Score.Value)].Value = "X";
worksheet.Cells[24, 15].Value = secondGrade.Comment;
var thirdGrade = boardEnrollment.Grades.FirstOrDefault(g => g.GradeDescription.Equals("Content (analysis)"));
worksheet.Cells[25, FindNumberOfCell(thirdGrade.Score.Value)].Value = "X";
worksheet.Cells[25, 15].Value = thirdGrade.Comment;
var fourthGrade = boardEnrollment.Grades.FirstOrDefault(g => g.GradeDescription.Equals("Media and Visual aids"));
worksheet.Cells[26, FindNumberOfCell(fourthGrade.Score.Value)].Value = "X";
worksheet.Cells[26, 15].Value = fourthGrade.Comment;
var fifthGrade = boardEnrollment.Grades.FirstOrDefault(g => g.GradeDescription.Equals("Presentation style (delivery)"));
worksheet.Cells[27, FindNumberOfCell(fifthGrade.Score.Value)].Value = "X";
worksheet.Cells[27, 15].Value = fifthGrade.Comment;
var sixthGrade = boardEnrollment.Grades.FirstOrDefault(g => g.GradeDescription.Equals("Presentation style (preparation)"));
worksheet.Cells[28, FindNumberOfCell(sixthGrade.Score.Value)].Value = "X";
worksheet.Cells[28, 15].Value = sixthGrade.Comment;
var seventhGrade = boardEnrollment.Grades.FirstOrDefault(g => g.GradeDescription.Equals("Presentation style (style)"));
worksheet.Cells[29, FindNumberOfCell(seventhGrade.Score.Value)].Value = "X";
worksheet.Cells[29, 15].Value = sixthGrade.Comment;
var eighthGrade = boardEnrollment.Grades.FirstOrDefault(g => g.GradeDescription.Equals("Audience connection"));
worksheet.Cells[30, FindNumberOfCell(eighthGrade.Score.Value)].Value = "X";
worksheet.Cells[30, 15].Value = sixthGrade.Comment;
var ninethGrade = boardEnrollment.Grades.FirstOrDefault(g => g.GradeDescription.Equals("Content (conclusion)"));
worksheet.Cells[31, FindNumberOfCell(ninethGrade.Score.Value)].Value = "X";
worksheet.Cells[31, 15].Value = sixthGrade.Comment;
var tenthGrade = boardEnrollment.Grades.FirstOrDefault(g => g.GradeDescription.Equals("Questions and Answers"));
worksheet.Cells[32, FindNumberOfCell(tenthGrade.Score.Value)].Value = "X";
worksheet.Cells[32, 15].Value = sixthGrade.Comment;
worksheet.Cells[34, 4].Value = firstGrade.Score.Value + secondGrade.Score.Value + thirdGrade.Score.Value + fourthGrade.Score.Value
+ fifthGrade.Score.Value + sixthGrade.Score.Value + seventhGrade.Score.Value + eighthGrade.Score.Value
+ ninethGrade.Score.Value + tenthGrade.Score.Value;
// worksheet.Cells[22, 2].Value = boardEnrollment.Score;
// worksheet.Cells[22, 3].Value = boardEnrollment.Comment;
// row = 22;
// foreach (var recommendation in boardEnrollment.Recommendations)
// {
// worksheet.Cells[row, 5].Value = recommendation.Description;
// row++;
// }
worksheet.Cells[36, 12].Value = "Ngày " + DateTime.Now.Day.ToString()
+ " Tháng " + DateTime.Now.Month.ToString() + " Năm " + DateTime.Now.Year.ToString();
worksheet.PrinterSettings.FitToPage = true;
worksheet.PrinterSettings.FitToWidth = 1;
worksheet.PrinterSettings.FitToHeight = 0;
package.Save();
//add to db
var excel = new Excel {
FileName = fileName
};
excelRepository.AddExcel(excel);
//send mail
//SendMail(board, filePath);
return(filePath);
}
}
}
private void IDListView_SelectChange(object sender, SelectionChangedEventArgs e)
{
IDListItem item = (sender as ListView).SelectedItem as IDListItem;
if (item == null)
{
return;
}
Excel excel = GlobalCfg.Instance.GetParsedExcel(_listItemChoosed.FilePath);
List <PropertyInfo> propertyList = excel.Properties;
ObservableCollection <PropertyListItem> fieldList = new ObservableCollection <PropertyListItem>();
_IDItemSelected = item;
List <lparser.config> configs = GlobalCfg.Instance.GetTableRow(item.ID);
string ename = string.Empty;
for (int i = 0; i < propertyList.Count; i++)
{
ename = propertyList[i].ename;
fieldList.Add(new PropertyListItem()
{
PropertyName = propertyList[i].cname,
EnName = propertyList[i].ename,
Context = configs[0] != null && configs[0].propertiesDic.ContainsKey(ename) ? configs[0].propertiesDic[ename].value : null,
Trunk = configs[1] != null && configs[1].propertiesDic.ContainsKey(ename) ? configs[1].propertiesDic[ename].value : null,
Studio = configs[2] != null && configs[2].propertiesDic.ContainsKey(ename) ? configs[2].propertiesDic[ename].value : null,
TF = configs[3] != null && configs[3].propertiesDic.ContainsKey(ename) ? configs[3].propertiesDic[ename].value : null,
Release = configs[4] != null && configs[4].propertiesDic.ContainsKey(ename) ? configs[4].propertiesDic[ename].value : null
});
}
propertyDataGrid.ItemsSource = fieldList;
ResetGenBtnState();
//刷新单元格颜色
for (int j = 0; j < GlobalCfg.BranchCount; j++)
{
tablerowdiff trd = GlobalCfg.Instance.GetCellAllStatus(item.ID, j);
if (trd == null)
{
continue;
}
for (int a = 0; a < fieldList.Count; a++)
{
if (trd.modifiedcells != null && trd.modifiedcells.ContainsKey(fieldList[a].EnName))
{
DataGridCell dataGridCell = GetCell(propertyDataGrid, a, j + 3);
dataGridCell.Background = Brushes.LightBlue;
}
if (trd.modifiedcells != null && trd.addedcells.ContainsKey(fieldList[a].EnName))
{
DataGridCell dataGridCell = GetCell(propertyDataGrid, a, j + 3);
dataGridCell.Background = Brushes.LightPink;
}
if (trd.modifiedcells != null && trd.deletedcells.ContainsKey(fieldList[a].EnName))
{
DataGridCell dataGridCell = GetCell(propertyDataGrid, a, j + 3);
dataGridCell.Background = Brushes.LightBlue;
}
}
}
}
partial void InsertExcel(Excel instance);
public MemoryStream GenerateExcel(int year, int month, int accountingBookId, string accountingBookType, bool valas)
{
string title = "LAPORAN DATA MEMORIAL",
date = new DateTime(year, month, 1).ToString("MMMM yyyy", new CultureInfo("id-ID"));
var query = GetReportXlsData(year, month, accountingBookId, accountingBookType, valas);
DataTable result = new DataTable();
result.Columns.Add(new DataColumn()
{
ColumnName = "Nomor Memo", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Tanggal Memo", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "No. Akun", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Nama Perkiraan", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Keterangan", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Debit", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Kredit", DataType = typeof(String)
});
int index = 0;
if (query.Data.ToArray().Count() == 0)
{
result.Rows.Add("", "", "", "", 0.ToString("#,##0.#0"), 0.ToString("#,##0.#0")); // to allow column name to be generated properly for empty data as template
index++;
}
else
{
foreach (var item in query.Data)
{
index++;
result.Rows.Add(item.MemoGarmentPurchasing.MemoNo, item.MemoGarmentPurchasing.MemoDate.ToString("dd-MMM-yyyy", new CultureInfo("id-ID")), item.COANo, item.COAName, item.MemoGarmentPurchasing.Remarks, item.DebitNominal.ToString("#,##0.#0"), item.CreditNominal.ToString("#,##0.#0"));
}
}
if (string.IsNullOrEmpty(accountingBookType))
{
return(Excel.CreateExcelWithTitleNonDateFilter(new List <KeyValuePair <DataTable, string> >()
{
new KeyValuePair <DataTable, string>(result, "Memorial")
}, title, date, true, 10));
}
else
{
return(Excel.CreateExcelWithTitleNonDateFilterMemoReport(new List <KeyValuePair <DataTable, string> >()
{
new KeyValuePair <DataTable, string>(result, "Memorial")
}, title, date, accountingBookType, true, 10));
}
}
partial void DeleteExcel(Excel instance);
public MemoryStream GenerateExcelTotalGarmentPurchaseBySupplier(string unit, bool jnsSpl, string payMtd, string category, DateTime?dateFrom, DateTime?dateTo, int offset)
{
var Query = GetTotalGarmentPurchaseBySupplierReportQuery(unit, jnsSpl, payMtd, category, dateFrom, dateTo, offset);
DataTable result = new DataTable();
result.Columns.Add(new DataColumn()
{
ColumnName = "Nomor", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Supplier", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Unit", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Kategori", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Metode Bayar", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Jumlah", DataType = typeof(Decimal)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Satuan", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Jumlah Konversi", DataType = typeof(Decimal)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Satuan Kecil", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Nominal(Rp)", DataType = typeof(Decimal)
});
if (Query.ToArray().Count() == 0)
{
result.Rows.Add("", "", "", "", "", 0, "", 0, "", 0); // to allow column name to be generated properly for empty data as template
}
else
{
int index = 0;
foreach (var item in Query)
{
index++;
result.Rows.Add(index, item.SupplierName, item.UnitName, item.CategoryName, item.PaymentMethod, item.Quantity, item.UOMUnit, item.SmallQty, item.SmallUom, (Decimal)Math.Round((item.AmountIDR), 2));
}
}
return(Excel.CreateExcel(new List <KeyValuePair <DataTable, string> >()
{
new KeyValuePair <DataTable, string>(result, "Sheet1")
}, true));
}
private bool HasSheetJudoka(int rowNumber, Excel.Worksheet worksheet)
{
String cell = GetJudokaCell(rowNumber, "B");
String name = GetSheetValue(worksheet, cell);
return name.CompareTo("") != 0;
}
public MemoryStream GenerateExcel(DateTimeOffset?dateFrom, DateTimeOffset?dateTo, int offSet)
{
//var query = _repository.ReadAll()
// .Where(s => s.Area == INSPECTIONMATERIAL && s.DyeingPrintingAreaInputProductionOrders.Any(d => !d.HasOutputDocument));
var query = _repository.ReadAll().Where(s => s.Area == DyeingPrintingArea.INSPECTIONMATERIAL);
if (dateFrom.HasValue && dateTo.HasValue)
{
query = query.Where(s => dateFrom.Value.Date <= s.Date.ToOffset(new TimeSpan(offSet, 0, 0)).Date&&
s.Date.ToOffset(new TimeSpan(offSet, 0, 0)).Date <= dateTo.Value.Date);
}
else if (!dateFrom.HasValue && dateTo.HasValue)
{
query = query.Where(s => s.Date.ToOffset(new TimeSpan(offSet, 0, 0)).Date <= dateTo.Value.Date);
}
else if (dateFrom.HasValue && !dateTo.HasValue)
{
query = query.Where(s => dateFrom.Value.Date <= s.Date.ToOffset(new TimeSpan(offSet, 0, 0)).Date);
}
query = query.OrderBy(s => s.BonNo);
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn()
{
ColumnName = "No. Bon", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "No. SPP", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Tanggal Masuk", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Qty Order", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "No. Kereta", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Material", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Unit", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Buyer", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Warna", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Motif", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Satuan", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Qty Terima", DataType = typeof(string)
});
if (query.Count() == 0)
{
dt.Rows.Add("", "", "", "", "", "", "", "", "", "", "", "");
}
else
{
foreach (var model in query)
{
//foreach (var item in model.DyeingPrintingAreaInputProductionOrders.Where(d => !d.HasOutputDocument).OrderBy(s => s.ProductionOrderNo))
foreach (var item in model.DyeingPrintingAreaInputProductionOrders.OrderBy(s => s.ProductionOrderNo))
{
var dateIn = item.DateIn.Equals(DateTimeOffset.MinValue) ? "" : item.DateIn.ToOffset(new TimeSpan(offSet, 0, 0)).Date.ToString("d");
dt.Rows.Add(model.BonNo, item.ProductionOrderNo, dateIn, item.ProductionOrderOrderQuantity.ToString("N2", CultureInfo.InvariantCulture),
item.CartNo, item.Construction, item.Unit, item.Buyer, item.Color, item.Motif, item.UomUnit, item.InputQuantity.ToString("N2", CultureInfo.InvariantCulture));
}
}
}
return(Excel.CreateExcel(new List <KeyValuePair <DataTable, string> >()
{
new KeyValuePair <DataTable, string>(dt, "Inspection Material")
}, true));
}
/// <summary>
/// 将指定索引列的数据相同的行合并，对指定WorkSheet操作
/// </summary>
/// <param name="columnIndex">要合并的列索引</param>
/// <param name="beginRowIndex">合并开始行索引</param>
/// <param name="rows">要合并的行数</param>
private void MergeRows(Excel.Worksheet sheet, int columnIndex, int beginRowIndex, int rows)
{
int beginIndex = beginRowIndex;
int count = 0;
string text1;
string text2;
if (sheet == null)
return;
for (int j = beginRowIndex; j < beginRowIndex + rows; j++)
{
range1 = (Excel.Range)sheet.Cells[j, columnIndex];
range2 = (Excel.Range)sheet.Cells[j + 1, columnIndex];
text1 = range1.Text.ToString();
text2 = range2.Text.ToString();
if (text1 == text2)
{
++count;
}
else
{
if (count > 0)
{
this.MergeCells(sheet, beginIndex, columnIndex, beginIndex + count, columnIndex, text1);
}
beginIndex = j + 1; //设置开始合并行索引
count = 0; //计数器清0
}
}
}
public MemoryStream GenerateExcel(string buyerAgent, string invoiceNo, string paymentTerm, DateTime?dateFrom, DateTime?dateTo, int offset)
{
var Query = GetData(buyerAgent, invoiceNo, paymentTerm, dateFrom, dateTo, offset);
DataTable result = new DataTable();
result.Columns.Add(new DataColumn()
{
ColumnName = "No", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "No Invoice", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Tgl Invoice", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Tgl Payment", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Tgl Kirim Dokumen", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Payment Term", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Buyer Name", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Buyer Address", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Bank Name", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Amount", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Amount To Be Paid", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Paid Amount", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Balance Amount", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Nett Nego | TT", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Bank Charges | TT", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Other Charges | TT", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "No LC | LC", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "No SR | LC", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Tanggal SR | LC", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Nett Nego | LC", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Bank Comission | LC", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Discreapancy Fee | LC", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Credit Interest | LC", DataType = typeof(string)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Bank Charges | LC", DataType = typeof(string)
});
if (Query.ToArray().Count() == 0)
{
result.Rows.Add("", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "");
}
else
{
Dictionary <string, List <GarmentCreditAdviceMonitoringViewModel> > dataByBrand = new Dictionary <string, List <GarmentCreditAdviceMonitoringViewModel> >();
Dictionary <string, double> subTotalAMT = new Dictionary <string, double>();
Dictionary <string, double> subTotalTBP = new Dictionary <string, double>();
Dictionary <string, double> subTotalNett = new Dictionary <string, double>();
Dictionary <string, double> subTotalNettTT = new Dictionary <string, double>();
Dictionary <string, double> subTotalNettLC = new Dictionary <string, double>();
Dictionary <string, double> outStanding = new Dictionary <string, double>();
foreach (GarmentCreditAdviceMonitoringViewModel item in Query.ToList())
{
string BrandName = item.InvoiceNo;
if (!dataByBrand.ContainsKey(BrandName))
{
dataByBrand.Add(BrandName, new List <GarmentCreditAdviceMonitoringViewModel> {
});
}
dataByBrand[BrandName].Add(new GarmentCreditAdviceMonitoringViewModel
{
InvoiceNo = item.InvoiceNo,
InvoiceDate = item.InvoiceDate,
PaymentDate = item.PaymentDate,
DocUploadDate = item.DocUploadDate,
PaymentTerm = item.PaymentTerm,
Amount = item.Amount,
ToBePaid = item.ToBePaid,
PaidAmount = item.PaidAmount,
BalanceAmount = item.BalanceAmount,
BuyerName = item.BuyerName,
BuyerAddress = item.BuyerAddress,
BankName = item.BankName,
NettNego = item.NettNego,
NettNegoTT = item.NettNegoTT,
BankChargeTT = item.BankChargeTT,
OtherChargeTT = item.OtherChargeTT,
SRNo = item.SRNo,
SRDate = item.SRDate,
LCNo = item.LCNo,
NettNegoLC = item.NettNegoLC,
BankChargeLC = item.BankChargeLC,
BankComissionLC = item.BankComissionLC,
DiscreapancyFeeLC = item.DiscreapancyFeeLC,
CreditInterestLC = item.CreditInterestLC,
});
if (!subTotalNett.ContainsKey(BrandName))
{
subTotalNett.Add(BrandName, 0);
}
;
if (!subTotalNettTT.ContainsKey(BrandName))
{
subTotalNettTT.Add(BrandName, 0);
}
;
if (!subTotalNettLC.ContainsKey(BrandName))
{
subTotalNettLC.Add(BrandName, 0);
}
;
subTotalAMT[BrandName] = item.Amount;
subTotalTBP[BrandName] = item.ToBePaid;
subTotalNett[BrandName] += item.NettNego;
subTotalNettTT[BrandName] += item.NettNegoTT;
subTotalNettLC[BrandName] += item.NettNegoLC;
outStanding[BrandName] = subTotalTBP[BrandName] - subTotalNett[BrandName];
}
int rowPosition = 1;
foreach (KeyValuePair <string, List <GarmentCreditAdviceMonitoringViewModel> > BuyerBrand in dataByBrand)
{
string BrandCode = "";
int index = 0;
foreach (GarmentCreditAdviceMonitoringViewModel item in BuyerBrand.Value)
{
index++;
string InvDate = item.InvoiceDate == new DateTime(1970, 1, 1) ? "-" : item.InvoiceDate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("MM/dd/yyyy", new CultureInfo("us-US"));
string DocDate = item.DocUploadDate == new DateTime(1970, 1, 1) ? "-" : item.DocUploadDate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("MM/dd/yyyy", new CultureInfo("us-US"));
string SRDate = item.SRDate == new DateTime(1970, 1, 1) ? "-" : item.SRDate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("MM/dd/yyyy", new CultureInfo("us-US"));
string PayDate = item.PaymentDate == new DateTime(1970, 1, 1) ? "-" : item.PaymentDate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("MM/dd/yyyy", new CultureInfo("us-US"));
string AmtFOB = string.Format("{0:N2}", item.Amount);
string AmtPaid = string.Format("{0:N2}", item.ToBePaid);
string PaidAmt = string.Format("{0:N2}", item.PaidAmount);
string BlncAmt = string.Format("{0:N2}", item.BalanceAmount);
string NettTT = string.Format("{0:N2}", item.Amount);
string BChrgTT = string.Format("{0:N2}", item.ToBePaid);
string OChrgTT = string.Format("{0:N2}", item.Amount);
string CommLC = string.Format("{0:N2}", item.ToBePaid);
string FeeLC = string.Format("{0:N2}", item.Amount);
string NettLC = string.Format("{0:N2}", item.ToBePaid);
string IntLC = string.Format("{0:N2}", item.Amount);
string BChrgLC = string.Format("{0:N2}", item.ToBePaid);
result.Rows.Add(index, item.InvoiceNo, InvDate, PayDate, DocDate, item.PaymentTerm, item.BuyerName, item.BuyerAddress, item.BankName,
AmtFOB, AmtPaid, PaidAmt, BlncAmt, NettTT, BChrgTT, OChrgTT, item.LCNo, item.SRNo, SRDate, CommLC, FeeLC, NettLC, IntLC, BChrgLC);
rowPosition += 1;
BrandCode = item.InvoiceNo;
}
result.Rows.Add("", "", "INVOICE NO :", BrandCode, "", "", "AMOUNT :", Math.Round(subTotalAMT[BuyerBrand.Key], 2), "", "", "AMOUNT TO BE PAID :", Math.Round(subTotalTBP[BuyerBrand.Key], 2), "", "", "PAID AMOUNT :", Math.Round(subTotalNett[BuyerBrand.Key], 2), "", "", "OUTSTANDING AMOUNT :", Math.Round(outStanding[BuyerBrand.Key], 2), "");
rowPosition += 1;
}
}
return(Excel.CreateExcel(new List <KeyValuePair <DataTable, string> >()
{
new KeyValuePair <DataTable, string>(result, "Sheet1")
}, true));
}
public ExcelParameter(System.Drawing.Font f, MExcel.XlHAlign Align, D.Point StartCell, D.Point EndCell, string value)
: this(Excel.ColNumToEx(StartCell.X) + StartCell.Y.ToString(), Excel.ColNumToEx(EndCell.X) + EndCell.Y.ToString(), value, Align, f, null)
{
}
public MemoryStream GenerateExcel(int kanbanID, int machineID, DateTime?dateFrom, DateTime?dateTo, int offSet)
{
var data = GetReport(kanbanID, machineID, dateFrom, dateTo, offSet);
data = data.OrderByDescending(x => x.LastModifiedUtc).ToList();
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn()
{
ColumnName = "No", DataType = typeof(String)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "No Order", DataType = typeof(String)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "No Kereta", DataType = typeof(String)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Reproses", DataType = typeof(String)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Mesin", DataType = typeof(String)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Step Proses", DataType = typeof(String)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Material", DataType = typeof(String)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Warna", DataType = typeof(String)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Lebar Kain (inch)", DataType = typeof(String)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Jenis Proses", DataType = typeof(String)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Tgl Input", DataType = typeof(String)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Jam Input", DataType = typeof(String)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Input", DataType = typeof(Double)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Tgl Output", DataType = typeof(String)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Jam Output", DataType = typeof(String)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "BQ", DataType = typeof(Double)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "BS", DataType = typeof(Double)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Keterangan BQ", DataType = typeof(String)
});
if (data.Count == 0)
{
dt.Rows.Add("", "", "", "", "", "", "", "", "", "", "", "", 0, "", "", 0, 0, "");
}
else
{
int index = 1;
foreach (var item in data)
{
dt.Rows.Add(index++, item.Kanban.ProductionOrder.OrderNo, item.Kanban.Cart.CartNumber, item.Kanban.IsReprocess.ToString(), item.Machine.Name,
item.Step.Process, item.Kanban.ProductionOrder.Material.Name, item.Kanban.SelectedProductionOrderDetail.ColorRequest, item.Kanban.FinishWidth,
item.Kanban.ProductionOrder.ProcessType.Name, item.DateInput == null ? "" : item.DateInput.GetValueOrDefault().AddHours(offSet).ToString("dd/MM/yyyy"),
item.TimeInput == null ? "" : item.TimeInput.GetValueOrDefault().ToString(), item.Input.GetValueOrDefault(),
item.DateOutput == null ? "" : item.DateOutput.GetValueOrDefault().AddHours(offSet).ToString("dd/MM/yyyy"), item.TimeOutput == null ? "" : item.TimeOutput.GetValueOrDefault().ToString(),
item.GoodOutput.GetValueOrDefault(), item.BadOutput.GetValueOrDefault(), "");
}
}
return(Excel.CreateExcel(new List <KeyValuePair <DataTable, string> >()
{
new KeyValuePair <DataTable, string>(dt, "Daily Operation")
}, true));
}