/// <summary>
/// Метод создающий новый файл
/// </summary>
/// <param name="filename">Имя и путь файла</param>
/// <param name="background">Создать в бэкграунде</param>
/// <param name="overwrite">Флаг перезаписи файле, если таковой уже существует</param>
public static void CreateNewFile(string filename, bool background, bool overwrite)
{
if (!background)
excelApp.Visible = true;
else
excelApp.Visible = false;
excelApp.Workbooks.Add();
workSheet = excelApp.ActiveSheet;
if (File.Exists(filename))
if (overwrite)
File.Delete(filename);
workSheet.SaveAs(filename);
}
public static bool ExcelExport <T>(List <T> dataSource)
{
Thread t = new Thread(new ThreadStart(LoadingScreen));
t.Start();
//convert từ List sang DataTable
//http://www.c-sharpcorner.com/UploadFile/deveshomar/exporting-generic-listt-to-excel-in-C-Sharp-using-interop/
PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
System.Data.DataTable dataTable = new System.Data.DataTable();
foreach (PropertyDescriptor prop in properties)
{
dataTable.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
}
foreach (T item in dataSource)
{
DataRow row = dataTable.NewRow();
foreach (PropertyDescriptor prop in properties)
{
row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
}
dataTable.Rows.Add(row);
}
//xuất ra file excel
try
{
if (dataTable == null || dataTable.Columns.Count == 0)
{
throw new Exception("ExportToExcel: Bảng trống hoặc không tồn tại!\n");
}
// load excel, and create a new workbook
Excel.Application excelApp = new Excel.Application();
excelApp.Workbooks.Add();
// single worksheet
Excel._Worksheet workSheet = excelApp.ActiveSheet;
workSheet.Name = "ExportSheet";
// column headings
for (int i = 0; i < dataTable.Columns.Count; i++)
{
workSheet.Cells[1, (i + 1)] = dataTable.Columns[i].ColumnName;
}
// rows
for (int i = 0; i < dataTable.Rows.Count; i++)
{
// to do: format datetime values before printing
for (int j = 0; j < dataTable.Columns.Count; j++)
{
workSheet.Cells[(i + 2), (j + 1)] = dataTable.Rows[i][j];
}
}
string ExcelFilePath = null;
if (ExcelFilePath != null && ExcelFilePath != "")
{
try
{
workSheet.SaveAs(ExcelFilePath);
excelApp.Quit();
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: Không thể lưu file Excel, kiểm tra đường dẫn.\n" + ex.Message);
}
}
else // no filepath is given
{
excelApp.Visible = true;
}
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: \n" + ex.Message);
}
t.Abort();
return(true);
}
// Source sample - https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/interop/how-to-access-office-onterop-objects
public static void SaveAsExcel(IList <Commit> commits, string path)
{
if (File.Exists(path))
{
File.Delete(path);
}
var excelApp = new Excel.Application
{
// Dont need to see the app.
Visible = false
};
// Create a new, empty workbook and add it to the collection returned
// by property Workbooks. The new workbook becomes the active workbook.
// Add has an optional parameter for specifying a praticular template.
// Because no argument is sent in this example, Add creates a new workbook.
excelApp.Workbooks.Add();
// This example uses a single workSheet.
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
workSheet.Cells[1, "A"] = "Area";
workSheet.Cells[1, "B"] = "PR";
workSheet.Cells[1, "C"] = "Issues";
workSheet.Cells[1, "D"] = "Commit";
workSheet.Cells[1, "E"] = "Author";
workSheet.Cells[1, "F"] = "Commit Message";
var row = 1;
var area = "";
foreach (var commit in commits)
{
row++;
var prString = string.Empty;
var issueStringBuilder = new StringBuilder();
var commitString = $"= HYPERLINK(\"{commit.Link}\", \"Commit\")";
if (commit.PR != null)
{
prString = $"= HYPERLINK(\"{commit.PR.Item2}\", \"{commit.PR.Item1}\")";
}
if (commit.Issues != null)
{
if (commit.Issues.Count > 1)
{
foreach (var issue in commit.Issues)
{
issueStringBuilder.Append(issue.Item2 + "\r\n");
}
}
else
{
foreach (var issue in commit.Issues)
{
issueStringBuilder.Append($"= HYPERLINK(\"{issue.Item2}\", \"{issue.Item1}\")");
}
}
}
workSheet.Cells[row, "A"] = area;
workSheet.Cells[row, "B"] = prString;
workSheet.Cells[row, "C"] = issueStringBuilder.ToString();
workSheet.Cells[row, "D"] = commitString;
workSheet.Cells[row, "E"] = commit.Author;
workSheet.Cells[row, "F"] = commit.SanitizedMessage;
}
workSheet.Range["A1", $"E1"].AutoFormat(
Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
workSheet.Range["A1", $"E1"].WrapText = true;
workSheet.SaveAs(path);
excelApp.ActiveWorkbook.Close();
excelApp.Quit();
Console.WriteLine($"Saving results file: {path}");
}
private void btnExcel_Click(object sender, EventArgs e)
{//导至excel
if (dt.Rows.Count == 0)
{
return;
}
string localFilePath = String.Empty;
//设置文件类型
//saveFileDialog1.Filter = " xls files(*.xls)|*.txt|All files(*.*)|*.*";
//设置文件名称：
saveFileDialog1.FileName = this.textBox1.Text.Trim() + "-" + DateTime.Now.ToString("yyyyMMdd") + "-" + "白条批发销售明细表.xls";
//点了保存按钮进入
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
WaitFormService.CreateWaitForm();
WaitFormService.SetWaitFormCaption(" 正在导出，请稍候......");
try
{
//获得文件路径
localFilePath = saveFileDialog1.FileName.ToString();
string wordPath = Application.StartupPath + "\\btsk.xls"; //定义模板的路径
Excel.Application app = new Excel.Application(); //添加一个 Excle应用对象
//打开工作簿，可见很多参数，第一个就是我们模板的路径
Excel._Workbook wbook = app.Workbooks.Open(wordPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
Excel._Worksheet oSheet = (Excel._Worksheet)wbook.Worksheets[1];//创建一张sheet表
int excel_cur = 2;
oSheet.Cells[excel_cur, 2] = dt.Rows[0]["收银员"].ToString();
oSheet.Cells[excel_cur, 4] = dt.Rows[0]["fdate"].ToString();
excel_cur = 6;
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
oSheet.Cells[excel_cur, 1] = dt.Rows[i]["市场"].ToString();
oSheet.Cells[excel_cur, 2] = dt.Rows[i]["K3代码"].ToString();
oSheet.Cells[excel_cur, 3] = dt.Rows[i]["客户名称"].ToString();
oSheet.Cells[excel_cur, 4] = dt.Rows[i]["前日累欠"].ToString();
oSheet.Cells[excel_cur, 5] = dt.Rows[i]["礼券"].ToString();
oSheet.Cells[excel_cur, 6] = dt.Rows[i]["现金收款"].ToString();
oSheet.Cells[excel_cur, 7] = dt.Rows[i]["银行存款"].ToString();
oSheet.Cells[excel_cur, 8] = dt.Rows[i]["余额(不含当天销售)"].ToString();
oSheet.Cells[excel_cur, 9] = dt.Rows[i]["重量1"].ToString();
oSheet.Cells[excel_cur, 10] = dt.Rows[i]["金额1"].ToString();
oSheet.Cells[excel_cur, 11] = dt.Rows[i]["重量2"].ToString();
oSheet.Cells[excel_cur, 12] = dt.Rows[i]["金额2"].ToString();
oSheet.Cells[excel_cur, 13] = dt.Rows[i]["重量3"].ToString();
oSheet.Cells[excel_cur, 14] = dt.Rows[i]["金额3"].ToString();
oSheet.Cells[excel_cur, 15] = dt.Rows[i]["重量4"].ToString();
oSheet.Cells[excel_cur, 16] = dt.Rows[i]["金额4"].ToString();
oSheet.Cells[excel_cur, 17] = dt.Rows[i]["重量5"].ToString();
oSheet.Cells[excel_cur, 18] = dt.Rows[i]["金额5"].ToString();
oSheet.Cells[excel_cur, 19] = dt.Rows[i]["折让金额"].ToString();
oSheet.Cells[excel_cur, 20] = dt.Rows[i]["当日应付"].ToString();
oSheet.Cells[excel_cur, 21] = dt.Rows[i]["次日实收现金"].ToString();
oSheet.Cells[excel_cur, 66] = dt.Rows[i]["累计余额"].ToString();
excel_cur++;
}
}
app.Application.DisplayAlerts = false;
oSheet.SaveAs(localFilePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);//文件保存
//打开后就要关闭。O(∩_∩)O~
app.Workbooks.Close();
//同样不要忘记结束进程
System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
GC.Collect();//强制对所有代进行即时垃圾回收
WaitFormService.CloseWaitForm();
MessageBox.Show("导出完成!", "软件提示");
}
catch (Exception ex)
{
WaitFormService.CloseWaitForm();
MessageBox.Show("导出失败!" + ex.ToString(), "软件提示");
return;
}
}
}
private void save_excel(int mode)
{
if (mode == 1)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Excel files (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = "导出Excel文件到";
DateTime now = DateTime.Now;
saveFileDialog.FileName = now.Year.ToString().PadLeft(2)
+ now.Month.ToString().PadLeft(2, '0')
+ now.Day.ToString().PadLeft(2, '0') + "-"
+ now.Hour.ToString().PadLeft(2, '0')
+ now.Minute.ToString().PadLeft(2, '0')
+ now.Second.ToString().PadLeft(2, '0');
saveFileDialog.ShowDialog();
Stream myStream = saveFileDialog.OpenFile();
StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding("gb2312"));
string str = "";
try
{
//写标题
for (int i = 0; i < this.dataGridView1.ColumnCount; i++)
{
if (i > 0)
{
str += ",";
}
str += this.dataGridView1.Columns[i].HeaderText;
}
str = "\t" + str;
sw.WriteLine(str);
//写内容
for (int j = 0; j < this.dataGridView1.Rows.Count; j++)
{
string tempStr = "";
for (int k = 0; k < this.dataGridView1.Columns.Count; k++)
{
if (k > 0)
{
tempStr += ",";
}
tempStr += this.dataGridView1.Rows[j].Cells[k].Value.ToString();
}
tempStr = (j + 1).ToString() + "," + tempStr;
sw.WriteLine(tempStr);
}
sw.Close();
myStream.Close();
}
catch (Exception ex)
{
//MessageBox.Show(ex.ToString());
}
finally
{
sw.Close();
myStream.Close();
}
}
else if (mode == 0)
{
string[,] data = new string[this.dataGridView1.Rows.Count - 1, this.dataGridView1.Columns.Count];
for (int i = 0; i < this.dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < this.dataGridView1.Columns.Count; j++)
{
data[i, j] = this.dataGridView1.Rows[i].Cells[j].Value.ToString();
}
}
while (data[0, 0] != null)
{
string[,] temp_same_date = new string[data.GetLength(0), data.GetLength(1)];
string[,] temp_diff_date = new string[data.GetLength(0), data.GetLength(1)];
int g = 0;
int h = 0;
for (int k = 0; k < data.GetLength(0); k++)
{
string date_temp = data[0, 1];
if (data[k, 1] != date_temp)
{
temp_diff_date[g, 0] = data[k, 0];
temp_diff_date[g, 1] = data[k, 1];
temp_diff_date[g, 2] = data[k, 2];
g++;
}
else
{
temp_same_date[h, 0] = data[k, 0];
temp_same_date[h, 1] = data[k, 1];
temp_same_date[h, 2] = data[k, 2];
h++;
}
}
data = new string[temp_diff_date.GetLength(0), temp_diff_date.GetLength(1)];
for (int k = 0; k < temp_diff_date.GetLength(0); k++)
{
data[k, 0] = temp_diff_date[k, 0];
data[k, 1] = temp_diff_date[k, 1];
data[k, 2] = temp_diff_date[k, 2];
}
string save_file_name = this.textBox1.Text + "\\" + "report\\" + temp_same_date[0, 1] + ".xls";
string save_file_path = this.textBox1.Text + "\\" + "report\\";
Directory.CreateDirectory(save_file_path);
if (!File.Exists(save_file_name))
{
File.Create(save_file_name).Close();
}
StreamWriter sw = new StreamWriter(save_file_name, false, Encoding.UTF8);
string str = "";
//写标题
str = "," + "序列号" + "," + "日期" + "," + "时间";
sw.WriteLine(str);
//写内容
for (int j = 0; j < temp_same_date.GetLength(0); j++)
{
string tempStr = "";
for (int k = 0; k < temp_same_date.GetLength(1); k++)
{
if (k > 0)
{
tempStr += ",";
}
tempStr += temp_same_date[j, k];
}
if (tempStr != ",,")
{
tempStr = (j + 1).ToString() + "," + tempStr;
sw.WriteLine(tempStr);
}
}
sw.Flush();
sw.Close();
}
}
else if (mode == 2)
{
System.Data.DataTable dt = (System.Data.DataTable)dataGridView1.DataSource;
if (dt == null || dt.Rows.Count == 0)
{
return;
}
// 创建Excel文档，保存格式 Office2007 xlsx.
// 需要引用：Microsoft.Office.Interop.Excel.dll 12.0版本的支持Office2007.
while (dt.Rows[0][0] != "")
{
string[,] temp_same_date = new string[dt.Rows.Count, dt.Columns.Count]; //定义2维数组长度
string[,] temp_diff_date = new string[dt.Rows.Count, dt.Columns.Count]; //定义2维数组长度
int g = 0;
int h = 0;
for (int k = 0; k < dt.Rows.Count; k++)
{
string date_temp = dt.Rows[0][1].ToString();
if (date_temp == "")
{
return;
}
if (dt.Rows[k][1].ToString() != date_temp)
{
temp_diff_date[g, 0] = dt.Rows[k][0].ToString();
temp_diff_date[g, 1] = dt.Rows[k][1].ToString();
temp_diff_date[g, 2] = dt.Rows[k][2].ToString();
g++;
}
else
{
temp_same_date[h, 0] = dt.Rows[k][0].ToString();
temp_same_date[h, 1] = dt.Rows[k][1].ToString();
temp_same_date[h, 2] = dt.Rows[k][2].ToString();
h++;
}
}
//temp_same_date = temp_same_date.Where(s => !string.IsNullOrEmpty(s)).ToArray();
dt.Clear();
for (int k = 0; k < temp_diff_date.GetLength(0); k++) //将不同的数据重新写入表格中
{
DataRow dr = dt.NewRow();
for (int j = 0; j < temp_diff_date.GetLength(1); j++)
{
dr[j] = temp_diff_date[k, j];
}
dt.Rows.Add(dr);
}
/*
* for (int k = 0; k < temp_diff_date.GetLength(0); k++)
* {
* dt.Rows[k][0] = temp_diff_date[k, 0];
* dt.Rows[k][1] = temp_diff_date[k, 1];
* dt.Rows[k][2] = temp_diff_date[k, 2];
* }
*/
string save_file_name = this.textBox1.Text + "\\" + "report\\" + temp_same_date[0, 1] + ".xlsx";
string save_file_path = this.textBox1.Text + "\\" + "report\\";
Directory.CreateDirectory(save_file_path);
/*
* if (!File.Exists(save_file_name))
* File.Create(save_file_name).Close();
* else
* {
* System.IO.File.Delete(save_file_name);
* }
*/
if (File.Exists(save_file_name))
{
System.IO.File.Delete(save_file_name); //如果原始excel文件存在，删除该文件
}
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workBook = excel.Workbooks.Add(); //新建文件
Microsoft.Office.Interop.Excel._Worksheet workSheet = (Microsoft.Office.Interop.Excel._Worksheet)workBook.ActiveSheet; //新建sheet
object misValue = System.Reflection.Missing.Value;
//excel.DisplayAlerts = false; // 不显示告警
int rowIndex;
int colIndex;
int page, page_length = 0;
Range range_1, range_2, range_3;
//取得标题并保存
String Current_date = DateTime.Now.ToString("yyyyMMdd");
String Number = "记录单编码: " + Current_date + "0001";
/*
* workSheet.Cells[1, 1] = "电池序列号记录单";
* Range range_1 = (Range)workSheet.get_Range("A1", "E1");
* range_1.Merge(0);
* range_1.HorizontalAlignment = XlVAlign.xlVAlignCenter;
* workSheet.Cells[1, 6] = "记录单编码: " + Current_date + "0001";
* Range range_2 = (Range)workSheet.get_Range("F1", "I1");
* range_2.Merge(0);
* range_2.HorizontalAlignment = XlVAlign.xlVAlignCenter;
*/
int same_date_length = 0;
for (h = 0; h < temp_same_date.GetLength(0); h++)
{
if (temp_same_date[h, 0] != null)
{
same_date_length++;
}
}
if (same_date_length % 100 == 0)
{
page = same_date_length / 100;
}
else
{
page = same_date_length / 100 + 1;
}
for (int k = 0; k < page; k++)
{
rowIndex = 1;
colIndex = 0;
workSheet.Cells[54 + k * 58, 7] = "签名及日期:";
///合并单元格
workSheet.Cells[1 + k * 58, 1] = "电池序列号记录单";
workSheet.Cells[1 + k * 58, 6] = Number;
range_1 = (Range)workSheet.get_Range("A" + Convert.ToString(1 + k * 58), "E" + Convert.ToString(1 + k * 58));
range_1.Merge(0);
range_1.HorizontalAlignment = XlVAlign.xlVAlignCenter;
range_2 = (Range)workSheet.get_Range("F" + Convert.ToString(1 + k * 58), "I" + Convert.ToString(1 + k * 58));
range_2.Merge(0);
range_2.HorizontalAlignment = XlVAlign.xlVAlignCenter;
for (int i = 0; i < dt.Columns.Count; i++)
{
colIndex++;
workSheet.Cells[2 + k * 58, colIndex + 1] = dt.Columns[i].ColumnName; //保存标题
workSheet.Cells[2 + k * 58, colIndex + 6] = dt.Columns[i].ColumnName; //保存标题
workSheet.Cells[2 + k * 58, 1] = "序号";
workSheet.Cells[2 + k * 58, 6] = "序号";
}
//保存数据到execl
for (int i = k * 100; i < (k + 1) * 100; i++)
{
rowIndex++;
colIndex = 0;
/*
* if (temp_same_date[i, 1] != null)
* {
* workSheet.Cells[rowIndex+1, 1] = i + 1; //保存数量编号到第一列
* }
*
*/
if (i % 100 < 50 && i < same_date_length)
{
workSheet.Cells[rowIndex + 1 + k * 58, 1] = i + 1;
}
else
if (i < same_date_length)
{
workSheet.Cells[rowIndex + 1 + k * 58 - 50, 6] = i + 1;
}
for (int j = 0; j < temp_same_date.GetLength(1); j++)
{
colIndex++;
if (i % 100 < 50)
{
workSheet.Cells[rowIndex + 1 + k * 58, colIndex + 1] = temp_same_date[i, j]; //保存具体数据到excel
}
else
{
workSheet.Cells[rowIndex + 1 + k * 58 - 50, colIndex + 6] = temp_same_date[i, j];
}
//workSheet.Cells.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle =Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
}
}
//画边框，字体居中
range_3 = (Range)workSheet.get_Range("A" + Convert.ToString(1 + k * 58), "I" + Convert.ToString(52 + k * 58));
//横向居中
range_3.HorizontalAlignment = XlVAlign.xlVAlignCenter;
///字体大小
range_3.Font.Size = 10;
///字体
range_3.Font.Name = "黑体";
///行高
//range_3.RowHeight = 24;
//自动调整列宽
range_3.EntireColumn.AutoFit();
//填充颜色
//range_3.Interior.ColorIndex = 20;
//设置单元格边框的粗细
range_3.Cells.Borders.LineStyle = 1;
}
//文件保存
workSheet.SaveAs(save_file_name); //保存文件
workBook.Close(); //关闭引用
excel.Quit(); //退出excel
PublicMethod.Kill(excel); //调用kill当前excel进程
releaseObject(workSheet); //释放COM对象
releaseObject(workBook);
releaseObject(excel);
GC.Collect();
//MessageBox.Show(string.Format("{0} 文档生成成功.", filePath));
}
return;
}
}
public override void Save()
{
try
{
var excelApp = new Excel.Application();
excelApp.Workbooks.Add();
Excel._Worksheet workSheet = excelApp.ActiveSheet;
Excel.Range range = workSheet.get_Range("A1", "F1");
range.Font.Bold = true;
range.Font.Size = 20;
range.Cells.Font.Color = System.Drawing.Color.Maroon;
workSheet.Cells[1, 1] = "Progressive Rewards " + IoFileInfo.PrismNumber + " Financial Report";
workSheet.Range[workSheet.Cells[1, 1], workSheet.Cells[1, 5]].Merge();
workSheet.Cells[1, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
range = workSheet.get_Range("A1", "A3");
workSheet.Rows[2].Cells[1].Style.Font.Size = 12;
workSheet.Rows[2].Cells[1].Style.Font.Color = System.Drawing.Color.Black;
workSheet.Cells[2, 7] = "Total Vouchers Needed";
workSheet.Range[workSheet.Cells[2, 6], workSheet.Cells[2, 10]].Merge();
range = workSheet.get_Range("A2", "E3");
range.Cells.Interior.Color = System.Drawing.Color.FromArgb(255, 255, 204);
range.Font.Bold = true;
range = workSheet.get_Range("A2", "J2");
range.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
range.EntireColumn.ColumnWidth = 17;
//range.Style.
range = workSheet.get_Range("F2", "J3");
range.Cells.Interior.Color = System.Drawing.Color.FromArgb(51, 153, 102);;
range.Font.Color = System.Drawing.Color.White;
range.Font.Bold = true;
for (var i = 0; i < IoFileInfo.OutputDataSource.Columns.Count; i++)
{
workSheet.Cells[3, i + 1] = IoFileInfo.OutputDataSource.Columns[i].ColumnName;
}
for (var i = 0; i < IoFileInfo.OutputDataSource.Rows.Count; i++)
{
// to do: format datetime values before printing
for (var j = 0; j < IoFileInfo.OutputDataSource.Columns.Count; j++)
{
workSheet.Cells[i + 4, j + 1] = IoFileInfo.OutputDataSource.Rows[i][j];
}
}
range = workSheet.get_Range("A" + IoFileInfo.OutputDataSource.Rows.Count + 1, "J" + IoFileInfo.OutputDataSource.Rows.Count + 2);
string r1 = "A" + (IoFileInfo.OutputDataSource.Rows.Count + 2).ToString();
string r2 = "J" + (IoFileInfo.OutputDataSource.Rows.Count + 3).ToString();
range = workSheet.get_Range(r1, r2);
range.Cells.Interior.Color = System.Drawing.Color.Green;
range.Font.Color = System.Drawing.Color.White;
range.Font.Bold = true;
workSheet.SaveAs(IoFileInfo.FolderName + "\\Progessive_" + IoFileInfo.PrismNumber + "_Financial_Reports.xls", Excel.XlFileFormat.xlExcel8);
excelApp.Quit();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
private void btnReport_Click(object sender, EventArgs e)
{
if (String.Compare(btnExport.Text, "Zatvori") == 0)
{
Application.Exit();
}
else
{
loadInfo.Text = "Učitavam podatke";
Cursor = Cursors.WaitCursor;
List <Siebel_export> dataSiebelExport = new List <Siebel_export>();
dataSiebelExport.Clear();
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(excelPath.ToString());
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
Excel.Range a1 = xlWorksheet.Cells[1, 4];
Excel.Range a2 = xlWorksheet.Cells[rowCount, 4];
xlWorksheet.get_Range(a1, a2).NumberFormat = "#";
object[,] _2dArray = new object[rowCount - 1, colCount - 1];
_2dArray = xlRange.Value2;
Siebel_export row_siebel_export = new Siebel_export();
List <Proizvodi> unique_proizvodi = new List <Proizvodi>();
List <string> unique_sims = new List <string>();
List <string> headers = new List <string>();
for (int k = 1; k <= colCount; k++)
{
headers.Add(_2dArray[1, k].ToString());
}
int kor_naplata = headers.IndexOf("Korisnik za naplatu") + 1;
int kor_usluga = headers.IndexOf("Korisnik za uslugu") + 1;
int broj_telefona = headers.IndexOf("Broj telefona") + 1;
int dat_akt = headers.IndexOf("Datum aktivacije") + 1;
int dat_deakt = headers.IndexOf("Datum deaktivacije") + 1;
int status = headers.IndexOf("Status") + 1;
int proizv = headers.IndexOf("Proizvod") + 1;
int prof_napl = headers.IndexOf("Profil naplate") + 1;
int sb_kor = headers.IndexOf("SB korisnik") + 1;
int prof_napl_sb = headers.IndexOf("Profil naplate SB korisnika") + 1;
int ser_sim = headers.IndexOf("Serijski broj SIM-a") + 1;
int dat_poc_uo = headers.IndexOf("Datum početka ugovorne obveze") + 1;
int dat_kraj_uo = headers.IndexOf("Datum isteka ugovorne obveze") + 1;
int pnp = headers.IndexOf("Skraćeni broj (PNP)") + 1;
int odl_prof = headers.IndexOf("VPN odlazni profil") + 1;
int dol_prof = headers.IndexOf("VPN dolazni profil") + 1;
int vpn_budget = headers.IndexOf("Iznos limita - VPN Budget") + 1;
int limit = headers.IndexOf("Iznos limita potrošnje") + 1;
int korp_apn = headers.IndexOf("Korporativni APN") + 1;
int multisim = headers.IndexOf("MultiSIM nominacija") + 1;
int vrsta_usluge = headers.IndexOf("Vrsta usluge") + 1;
int vrsta_proiz = headers.IndexOf("Vrsta proizvoda") + 1;
int klas_proiz = headers.IndexOf("Klasifikacija proizvoda") + 1;
int stat_uo = headers.IndexOf("Status ugovorne obveze") + 1;
int br_dana_uo = headers.IndexOf("Preostalo dana ugovorne obveze") + 1;
int multisimcount = 0;
int korporativniAPN = 0;
int limitPotrosnje = 0;
string temp_broj = _2dArray[2, broj_telefona].ToString();
for (var i = 2; i <= rowCount; i++)
{
if (String.Compare(_2dArray[i, status].ToString(), "Active") == 0 || String.Compare(_2dArray[i, status].ToString(), "Suspended") == 0)
{
if (String.Compare(temp_broj, _2dArray[i, broj_telefona].ToString()) == 0)
{
Proizvodi row_proizvod = new Proizvodi();
Proizvodi unique_proizvod = new Proizvodi();
if (_2dArray[i, kor_naplata] != null && String.Compare(_2dArray[i, kor_naplata].ToString(), "") != 0 && String.Compare(_2dArray[i, kor_naplata].ToString(), "--") != 0)
{
row_siebel_export.KorisnikZaNaplatu = _2dArray[i, kor_naplata].ToString();
}
if (_2dArray[i, kor_usluga] != null && String.Compare(_2dArray[i, kor_usluga].ToString(), "") != 0 && String.Compare(_2dArray[i, kor_usluga].ToString(), "--") != 0)
{
row_siebel_export.KorisnikZaUslugu = _2dArray[i, kor_usluga].ToString();
}
if (_2dArray[i, broj_telefona] != null && String.Compare(_2dArray[i, broj_telefona].ToString(), "") != 0 && String.Compare(_2dArray[i, broj_telefona].ToString(), "--") != 0)
{
row_siebel_export.BrojTelefona = _2dArray[i, broj_telefona].ToString();
}
if (_2dArray[i, status] != null && String.Compare(_2dArray[i, status].ToString(), "") != 0 && String.Compare(_2dArray[i, status].ToString(), "--") != 0)
{
row_siebel_export.Status = _2dArray[i, status].ToString();
}
if (_2dArray[i, prof_napl] != null && String.Compare(_2dArray[i, prof_napl].ToString(), "") != 0 && String.Compare(_2dArray[i, prof_napl].ToString(), "--") != 0)
{
row_siebel_export.ProfilNaplate = _2dArray[i, prof_napl].ToString();
}
if (_2dArray[i, dat_poc_uo] != null && String.Compare(_2dArray[i, dat_poc_uo].ToString(), "") != 0 && String.Compare(_2dArray[i, dat_poc_uo].ToString(), "--") != 0)
{
try
{
if (Convert.ToDateTime(row_siebel_export.PocetakUO) < Convert.ToDateTime(DateTime.FromOADate(Convert.ToDouble(_2dArray[i, dat_poc_uo].ToString())).ToString("dd.MM.yyyy")))
{
row_siebel_export.PocetakUO = DateTime.FromOADate(Convert.ToDouble(_2dArray[i, dat_poc_uo].ToString())).ToString("dd.MM.yyyy");
}
}
catch
{
if (Convert.ToDateTime(row_siebel_export.PocetakUO) < Convert.ToDateTime(_2dArray[i, dat_poc_uo].ToString().Substring(2, _2dArray[i, dat_poc_uo].ToString().Length - 2)))
{
row_siebel_export.PocetakUO = DateTime.ParseExact(_2dArray[i, dat_poc_uo].ToString().Substring(2, _2dArray[i, dat_poc_uo].ToString().Length - 2), "dd-MM-yyyy", CultureInfo.InvariantCulture).ToString("dd.MM.yyyy");
}
}
}
if (_2dArray[i, dat_kraj_uo] != null && String.Compare(_2dArray[i, dat_kraj_uo].ToString(), "") != 0 && String.Compare(_2dArray[i, dat_kraj_uo].ToString(), "--") != 0)
{
try
{
if (Convert.ToDateTime(row_siebel_export.IstekUO) < Convert.ToDateTime(DateTime.FromOADate(Convert.ToDouble(_2dArray[i, dat_kraj_uo].ToString())).ToString("dd.MM.yyyy")))
{
row_siebel_export.IstekUO = DateTime.FromOADate(Convert.ToDouble(_2dArray[i, dat_kraj_uo].ToString())).ToString("dd.MM.yyyy");
}
}
catch
{
if (Convert.ToDateTime(row_siebel_export.IstekUO) < Convert.ToDateTime(_2dArray[i, dat_kraj_uo].ToString().Substring(2, _2dArray[i, dat_kraj_uo].ToString().Length - 2)))
{
row_siebel_export.IstekUO = DateTime.ParseExact(_2dArray[i, dat_kraj_uo].ToString().Substring(2, _2dArray[i, dat_kraj_uo].ToString().Length - 2), "dd-MM-yyyy", CultureInfo.InvariantCulture).ToString("dd.MM.yyyy");
}
}
}
if (_2dArray[i, pnp] != null && String.Compare(_2dArray[i, pnp].ToString(), "") != 0 && String.Compare(_2dArray[i, pnp].ToString(), "--") != 0)
{
row_siebel_export.PNP = _2dArray[i, pnp].ToString();
}
if (_2dArray[i, odl_prof] != null && String.Compare(_2dArray[i, odl_prof].ToString(), "") != 0 && String.Compare(_2dArray[i, odl_prof].ToString(), "--") != 0)
{
row_siebel_export.OdlazniProfil = _2dArray[i, odl_prof].ToString();
}
if (_2dArray[i, dol_prof] != null && String.Compare(_2dArray[i, dol_prof].ToString(), "") != 0 && String.Compare(_2dArray[i, dol_prof].ToString(), "--") != 0)
{
row_siebel_export.DolazniProfil = _2dArray[i, dol_prof].ToString();
}
if (_2dArray[i, stat_uo] != null && String.Compare(_2dArray[i, stat_uo].ToString(), "") != 0 && String.Compare(_2dArray[i, stat_uo].ToString(), "--") != 0)
{
row_siebel_export.StatusUgovorneObveze = _2dArray[i, stat_uo].ToString();
}
if (_2dArray[i, br_dana_uo] != null && String.Compare(_2dArray[i, br_dana_uo].ToString(), "") != 0 && String.Compare(_2dArray[i, br_dana_uo].ToString(), "--") != 0)
{
row_siebel_export.PreostaloDana = _2dArray[i, br_dana_uo].ToString();
}
if (_2dArray[i, multisim] != null && String.Compare(_2dArray[i, multisim].ToString(), "") != 0 && String.Compare(_2dArray[i, multisim].ToString(), "--") != 0)
{
row_siebel_export.MultiSIM_nominacija = _2dArray[i, multisim].ToString();
multisimcount = 1;
}
if (_2dArray[i, korp_apn] != null && String.Compare(_2dArray[i, korp_apn].ToString(), "") != 0 && String.Compare(_2dArray[i, korp_apn].ToString(), "--") != 0)
{
row_siebel_export.KorporativniAPN = _2dArray[i, korp_apn].ToString();
korporativniAPN = 1;
}
if (_2dArray[i, limit] != null && String.Compare(_2dArray[i, limit].ToString(), "") != 0 && String.Compare(_2dArray[i, limit].ToString(), "--") != 0)
{
row_siebel_export.LimitPotrosnje = _2dArray[i, limit].ToString();
limitPotrosnje = 1;
}
if (_2dArray[i, klas_proiz] == null)
{
continue;
}
else if (String.Compare(_2dArray[i, klas_proiz].ToString(), "Root Service") == 0)
{
if (_2dArray[i, dat_akt] != null && String.Compare(_2dArray[i, dat_akt].ToString(), "") != 0 && String.Compare(_2dArray[i, dat_akt].ToString(), "--") != 0)
{
try
{
row_siebel_export.DatumAktivacijeUsluge = DateTime.FromOADate(Convert.ToDouble(_2dArray[i, dat_akt].ToString())).ToString("dd.MM.yyyy");
}
catch
{
row_siebel_export.DatumAktivacijeUsluge = DateTime.ParseExact(_2dArray[i, dat_akt].ToString().Substring(2, _2dArray[i, dat_akt].ToString().Length - 2), "dd-MM-yyyy", CultureInfo.InvariantCulture).ToString("dd.MM.yyyy");
}
}
if (_2dArray[i, proizv] != null && String.Compare(_2dArray[i, proizv].ToString(), "") != 0 && String.Compare(_2dArray[i, proizv].ToString(), "--") != 0)
{
row_siebel_export.Usluga = _2dArray[i, proizv].ToString();
}
}
else if (String.Compare(_2dArray[i, klas_proiz].ToString(), "Tariff") == 0)
{
if (_2dArray[i, dat_akt] != null && String.Compare(_2dArray[i, dat_akt].ToString(), "") != 0 && String.Compare(_2dArray[i, dat_akt].ToString(), "--") != 0)
{
try
{
row_siebel_export.DatumAktivacijeTarife = DateTime.FromOADate(Convert.ToDouble(_2dArray[i, dat_akt].ToString())).ToString("dd.MM.yyyy");
}
catch
{
row_siebel_export.DatumAktivacijeTarife = DateTime.ParseExact(_2dArray[i, dat_akt].ToString().Substring(2, _2dArray[i, dat_akt].ToString().Length - 2), "dd-MM-yyyy", CultureInfo.InvariantCulture).ToString("dd.MM.yyyy");
}
}
if (_2dArray[i, proizv] != null && String.Compare(_2dArray[i, proizv].ToString(), "") != 0 && String.Compare(_2dArray[i, proizv].ToString(), "--") != 0)
{
row_siebel_export.Tarifa = _2dArray[i, proizv].ToString();
}
}
else if (String.Compare(_2dArray[i, klas_proiz].ToString(), "Hardware") == 0)
{
/* simovi lista */
SIM_S sim = new SIM_S();
if (_2dArray[i, proizv] != null && String.Compare(_2dArray[i, proizv].ToString(), "") != 0 && String.Compare(_2dArray[i, proizv].ToString(), "--") != 0)
{
sim.Naziv = _2dArray[i, proizv].ToString();
}
if (_2dArray[i, ser_sim] != null && String.Compare(_2dArray[i, ser_sim].ToString(), "") != 0 && String.Compare(_2dArray[i, ser_sim].ToString(), "--") != 0)
{
sim.Serial = _2dArray[i, ser_sim].ToString();
}
row_siebel_export.Simovi.Add(sim);
if (unique_sims.Find(x => String.Compare(x, sim.Naziv) == 0) == null)
{
unique_sims.Add(sim.Naziv);
}
}
else if (String.Compare(_2dArray[i, klas_proiz].ToString(), "Split Biller") == 0)
{
if (_2dArray[i, sb_kor] != null && String.Compare(_2dArray[i, sb_kor].ToString(), "") != 0 && String.Compare(_2dArray[i, sb_kor].ToString(), "--") != 0)
{
row_siebel_export.SplitBiller = _2dArray[i, sb_kor].ToString();
}
if (_2dArray[i, vpn_budget] != null && String.Compare(_2dArray[i, vpn_budget].ToString(), "") != 0 && String.Compare(_2dArray[i, vpn_budget].ToString(), "--") != 0)
{
row_siebel_export.Vpn_budget = _2dArray[i, vpn_budget].ToString();
}
if (_2dArray[i, prof_napl_sb] != null && String.Compare(_2dArray[i, prof_napl_sb].ToString(), "") != 0 && String.Compare(_2dArray[i, prof_napl_sb].ToString(), "--") != 0)
{
row_siebel_export.ProfilNaplateSB = _2dArray[i, prof_napl_sb].ToString();
}
splitBillerActive = true;
}
else
{
//SVE OSTALO
if (_2dArray[i, dat_akt] != null && String.Compare(_2dArray[i, dat_akt].ToString(), "") != 0 && String.Compare(_2dArray[i, dat_akt].ToString(), "--") != 0)
{
try
{
if (Convert.ToDateTime(row_proizvod.DatumAktivacije) < Convert.ToDateTime(DateTime.FromOADate(Convert.ToDouble(_2dArray[i, dat_akt].ToString())).ToString("dd.MM.yyyy")))
{
row_proizvod.DatumAktivacije = DateTime.FromOADate(Convert.ToDouble(_2dArray[i, dat_akt].ToString())).ToString("dd.MM.yyyy");
}
}
catch
{
if (Convert.ToDateTime(row_proizvod.DatumAktivacije) < Convert.ToDateTime(_2dArray[i, dat_akt].ToString().Substring(2, _2dArray[i, dat_akt].ToString().Length - 2)))
{
row_proizvod.DatumAktivacije = DateTime.ParseExact(_2dArray[i, dat_akt].ToString().Substring(2, _2dArray[i, dat_akt].ToString().Length - 2), "dd-MM-yyyy", CultureInfo.InvariantCulture).ToString("dd.MM.yyyy");
}
}
}
if (_2dArray[i, proizv] != null && String.Compare(_2dArray[i, proizv].ToString(), "") != 0 && String.Compare(_2dArray[i, proizv].ToString(), "--") != 0)
{
row_proizvod.Proizvod = _2dArray[i, proizv].ToString();
unique_proizvod.Proizvod = _2dArray[i, proizv].ToString();
}
if (_2dArray[i, klas_proiz] != null && String.Compare(_2dArray[i, klas_proiz].ToString(), "") != 0 && String.Compare(_2dArray[i, klas_proiz].ToString(), "--") != 0)
{
row_proizvod.KlasifikacijaProizvoda = _2dArray[i, klas_proiz].ToString();
unique_proizvod.KlasifikacijaProizvoda = _2dArray[i, klas_proiz].ToString();
}
row_siebel_export.Proizvodi.Add(row_proizvod);
if (unique_proizvodi.Find(x => String.Compare(x.Proizvod, unique_proizvod.Proizvod) == 0) == null)
{
unique_proizvodi.Add(unique_proizvod);
}
}
if (i != rowCount && String.Compare(temp_broj, _2dArray[i + 1, broj_telefona].ToString()) != 0)
{
row_siebel_export.Simovi = row_siebel_export.Simovi.OrderBy(x => x.Naziv).ToList();
dataSiebelExport.Add(row_siebel_export);
if (i + 1 < rowCount)
{
temp_broj = _2dArray[i + 1, broj_telefona].ToString();
row_siebel_export = new Siebel_export();
}
}
}
}
else if (i + 1 <= rowCount && String.Compare(temp_broj, _2dArray[i + 1, broj_telefona].ToString()) != 0)
{
if (row_siebel_export.Status != null)
{
row_siebel_export.Simovi = row_siebel_export.Simovi.OrderBy(x => x.Naziv).ToList();
dataSiebelExport.Add(row_siebel_export);
}
if (i + 1 < rowCount)
{
temp_broj = _2dArray[i + 1, broj_telefona].ToString();
row_siebel_export = new Siebel_export();
}
}
if (i == rowCount && row_siebel_export.BrojTelefona != null)
{
row_siebel_export.Simovi = row_siebel_export.Simovi.OrderBy(x => x.Naziv).ToList();
dataSiebelExport.Add(row_siebel_export);
}
}
unique_proizvodi = unique_proizvodi.OrderBy(x => x.KlasifikacijaProizvoda).ThenBy(x => x.Proizvod).ToList();
unique_sims = unique_sims.OrderBy(x => x).ToList();
dataSiebelExport = dataSiebelExport.OrderBy(x => x.BrojTelefona).ToList();
xlWorkbook.Close(false, Type.Missing, Type.Missing);
xlApp.Quit();
loadInfo.Text = "Podatci su učitani";
int rowCountExport = dataSiebelExport.Count + 1;
Excel.Application excelApp = new Excel.Application();
Excel.Workbook workBook = excelApp.Workbooks.Add(Type.Missing);
Excel._Worksheet workSheet = workBook.Worksheets[1];
workSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
object[,] _2dData = new object[rowCountExport, 16 + multisimcount + korporativniAPN + limitPotrosnje + 3 + unique_sims.Count + unique_proizvodi.Count];
workSheet.Name = "Export";
_2dData[0, 0] = "Korisnik za naplatu";
_2dData[0, 1] = "Korisnik za uslugu";
_2dData[0, 2] = "Datum aktivacije usluge";
_2dData[0, 3] = "Usluga";
_2dData[0, 4] = "Broj telefona";
_2dData[0, 5] = "Status";
_2dData[0, 6] = "Profil naplate";
if (splitBillerActive)
{
_2dData[0, 7] = "Split Biller";
_2dData[0, 8] = "Iznos limita - VPN Budget";
_2dData[0, 9] = "Profil Naplate SB";
sbBroj = 3;
}
_2dData[0, 7 + sbBroj] = "Datum aktivacije tarife";
_2dData[0, 8 + sbBroj] = "Tarifa";
_2dData[0, 9 + sbBroj] = "Početak ugovorne obveze";
_2dData[0, 10 + sbBroj] = "Istek ugovorne obveze";
_2dData[0, 11 + sbBroj] = "PNP";
_2dData[0, 12 + sbBroj] = "Odlazni profil";
_2dData[0, 13 + sbBroj] = "Dolazni profil";
_2dData[0, 14 + sbBroj] = "Status ugovorne obveze";
_2dData[0, 15 + sbBroj] = "Preostali broj dana ugovorne obveze";
if (limitPotrosnje == 1)
{
_2dData[0, 16 + sbBroj] = "Iznos limita potrošnje";
}
if (korporativniAPN == 1)
{
_2dData[0, 16 + limitPotrosnje + sbBroj] = "Korporativni APN";
}
if (multisimcount == 1)
{
_2dData[0, 16 + limitPotrosnje + korporativniAPN + sbBroj] = "MultiSIM nominacija";
}
string stupac = GetExcelColumnName(16 + multisimcount + limitPotrosnje + korporativniAPN + sbBroj + unique_proizvodi.Count + unique_sims.Count);
workSheet.get_Range("a1", stupac + "1").Cells.Interior.Color = System.Drawing.Color.Orange;
workSheet.get_Range("a1", stupac + "1").Cells.Font.Color = System.Drawing.Color.Black;
workSheet.get_Range("a1", stupac + "1").Cells.Font.Bold = true;
workSheet.get_Range("a1", stupac + "1").Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = 3d;
Excel.Range c1 = workSheet.Cells[1, 17 + multisimcount + limitPotrosnje + korporativniAPN + sbBroj];
Excel.Range c2 = workSheet.Cells[rowCountExport, 16 + multisimcount + limitPotrosnje + korporativniAPN + sbBroj + unique_sims.Count];
Excel.Range rangeNum = workSheet.get_Range(c1, c2);
rangeNum.NumberFormat = "@";
rangeNum.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
for (int k = 1; k <= unique_sims.Count; k++)
{
_2dData[0, 15 + multisimcount + limitPotrosnje + korporativniAPN + sbBroj + k] = unique_sims[k - 1];
}
for (int k = 1; k <= unique_proizvodi.Count; k++)
{
_2dData[0, 15 + multisimcount + limitPotrosnje + korporativniAPN + sbBroj + unique_sims.Count + k] = unique_proizvodi[k - 1].Proizvod;
}
for (int i = 0; i < dataSiebelExport.Count; i++)
{
_2dData[i + 1, 0] = dataSiebelExport[i].KorisnikZaNaplatu;
_2dData[i + 1, 1] = dataSiebelExport[i].KorisnikZaUslugu;
_2dData[i + 1, 2] = Convert.ToDateTime(dataSiebelExport[i].DatumAktivacijeUsluge);
_2dData[i + 1, 3] = dataSiebelExport[i].Usluga;
_2dData[i + 1, 4] = dataSiebelExport[i].BrojTelefona;
_2dData[i + 1, 5] = dataSiebelExport[i].Status;
_2dData[i + 1, 6] = dataSiebelExport[i].ProfilNaplate;
if (splitBillerActive)
{
_2dData[i + 1, 7] = dataSiebelExport[i].SplitBiller;
_2dData[i + 1, 8] = dataSiebelExport[i].Vpn_budget;
_2dData[i + 1, 9] = dataSiebelExport[i].ProfilNaplateSB;
}
_2dData[i + 1, 7 + sbBroj] = Convert.ToDateTime(dataSiebelExport[i].DatumAktivacijeTarife);
_2dData[i + 1, 8 + sbBroj] = dataSiebelExport[i].Tarifa;
_2dData[i + 1, 9 + sbBroj] = Convert.ToDateTime(dataSiebelExport[i].PocetakUO);
_2dData[i + 1, 10 + sbBroj] = Convert.ToDateTime(dataSiebelExport[i].IstekUO);
_2dData[i + 1, 11 + sbBroj] = dataSiebelExport[i].PNP;
_2dData[i + 1, 12 + sbBroj] = dataSiebelExport[i].OdlazniProfil;
_2dData[i + 1, 13 + sbBroj] = dataSiebelExport[i].DolazniProfil;
_2dData[i + 1, 14 + sbBroj] = dataSiebelExport[i].StatusUgovorneObveze;
_2dData[i + 1, 15 + sbBroj] = dataSiebelExport[i].PreostaloDana;
if (limitPotrosnje == 1)
{
_2dData[i + 1, 16 + sbBroj] = dataSiebelExport[i].LimitPotrosnje;
}
if (korporativniAPN == 1)
{
_2dData[i + 1, 16 + limitPotrosnje + sbBroj] = dataSiebelExport[i].KorporativniAPN;
}
if (multisimcount == 1)
{
_2dData[i + 1, 16 + limitPotrosnje + korporativniAPN + sbBroj] = dataSiebelExport[i].MultiSIM_nominacija;
}
foreach (SIM_S temp in dataSiebelExport[i].Simovi)
{
_2dData[i + 1, 16 + multisimcount + limitPotrosnje + korporativniAPN + sbBroj + unique_sims.IndexOf(temp.Naziv)] = temp.Serial;
}
foreach (Proizvodi temp in dataSiebelExport[i].Proizvodi)
{
int index = unique_proizvodi.FindIndex(x => String.Compare(x.Proizvod, temp.Proizvod) == 0);
_2dData[i + 1, 16 + multisimcount + limitPotrosnje + korporativniAPN + sbBroj + unique_sims.Count + index] = "X";
}
}
c1 = workSheet.Cells[1, 1];
c2 = workSheet.Cells[rowCountExport, 16 + multisimcount + limitPotrosnje + korporativniAPN + sbBroj + unique_sims.Count + unique_proizvodi.Count];
Excel.Range range = workSheet.get_Range(c1, c2);
if (chkDatumi.Checked)
{
for (int i = 0; i < dataSiebelExport.Count; i++)
{
foreach (Proizvodi temp in dataSiebelExport[i].Proizvodi)
{
int index = unique_proizvodi.FindIndex(x => String.Compare(x.Proizvod, temp.Proizvod) == 0);
c1 = workSheet.Cells[i + 2, 17 + multisimcount + limitPotrosnje + korporativniAPN + sbBroj + unique_sims.Count + index];
c1.AddComment(temp.DatumAktivacije);
}
}
}
c1 = workSheet.Cells[1, 5];
c2 = workSheet.Cells[rowCountExport, 7];
rangeNum = workSheet.get_Range(c1, c2);
rangeNum.NumberFormat = "#";
rangeNum.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
c1 = workSheet.Cells[1, 12];
c2 = workSheet.Cells[rowCountExport, 12];
workSheet.get_Range(c1, c2).Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
c1 = workSheet.Cells[1, 16];
c2 = workSheet.Cells[rowCountExport, 16 + sbBroj + unique_proizvodi.Count + unique_sims.Count];
workSheet.get_Range(c1, c2).Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.Value = _2dData;
workSheet.Application.ActiveWindow.SplitRow = 1;
workSheet.Application.ActiveWindow.FreezePanes = true;
Excel.Range firstRow = (Excel.Range)workSheet.Rows[1];
firstRow.Activate();
firstRow.Select();
firstRow.AutoFilter(1,
Type.Missing,
Excel.XlAutoFilterOperator.xlAnd,
Type.Missing,
true);
workSheet.get_Range("A:" + stupac, Type.Missing).Columns.AutoFit();
excelApp.DisplayAlerts = true;
excelPath = Path.GetDirectoryName(Environment.GetCommandLineArgs()[0]);
bool tempSave = false;
int saveCounter = 0;
while (!tempSave)
{
try
{
tempSave = true;
if (saveCounter == 0)
{
workSheet.SaveAs(excelPath + "\\" + "export " + dataSiebelExport[0].KorisnikZaNaplatu + " " + DateTime.Now.ToShortDateString() + ".xlsx");
}
else
{
workSheet.SaveAs(excelPath + "\\" + "export " + dataSiebelExport[0].KorisnikZaNaplatu + " " + DateTime.Now.ToShortDateString() + ".xlsx");
}
}
catch
{
tempSave = false;
saveCounter++;
}
}
workBook.Close(true, Type.Missing, Type.Missing);
excelApp.Quit();
Cursor = DefaultCursor;
loadInfo.Text = "Export završen";
loadInfo.ForeColor = Color.Green;
chkDatumi.Enabled = false;
btnExport.Text = "Zatvori";
btnOpenExport.Enabled = false;
}
}
public void WritetoExcel(DataTable DT, string FilePath = null)
{
int iColumnCount;
try
{
if (DT == null || (iColumnCount = DT.Columns.Count) == 0)
{
throw new Exception("Export to Excel filed: The table is either null or empty!\n");
}
//creat an Excel Application instance
var excelApp = new Excel.Application();
//add a workbook to the Excel workbook
excelApp.Workbooks.Add();
//create a single worksheet
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
object[] Header = new object[iColumnCount];
//create column headings and add them to the worksheet
iColumnCount = DT.Columns.Count;
for (int i = 0; i < iColumnCount; i++)
{
Header[i] = DT.Columns[i].ColumnName;
}
Excel.Range HeaderRange = workSheet.get_Range((Excel.Range)(workSheet.Cells[1, 1]), (Excel.Range)(workSheet.Cells[1, iColumnCount]));
HeaderRange.Value = Header;
HeaderRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
HeaderRange.Font.Bold = true;
//Get Data Cells
int iRowCount = DT.Rows.Count;
object[,] Cells = new object[iRowCount, iColumnCount];
for (int i = 0; i < iRowCount; i++)
{
for (int j = 0; j < iColumnCount; j++)
{
Cells[i, j] = DT.Rows[i][j];
}
}
workSheet.get_Range((Excel.Range)(workSheet.Cells[2, 1]), (Excel.Range)(workSheet.Cells[1 + iRowCount, iColumnCount])).Value = Cells;
if (FilePath != null && FilePath != "")
{
try
{
workSheet.SaveAs(FilePath);
excelApp.Quit();
System.Windows.Forms.MessageBox.Show("Report was saved!");
}
catch (Exception ex)
{
throw new Exception("Export to Excel failed: Excel file could not be saved! Check filepath! \n" +
ex.Message);
}//try/catch
}
else
{
excelApp.Visible = true;
}
}
catch (Exception e)
{
throw new Exception("Export to Excel failed: \n" + e.Message);
}//try/catch
}
private void ImportData()
{
string ERPInDirectory = Convert.ToString(ConfigurationManager.AppSettings["ERPIN"].ToString().Trim());
if (!Directory.Exists(ERPInDirectory))
{
log.Info("ERPIN路径不存在。");
return;
}
string ExcelPath = ERPInDirectory;
List <FileInfo> ExcelFiles = getFiles(ExcelPath, ".xls");
if (ExcelFiles.Count == 0)
{
log.Info("没有检测到Excel文件。");
return;
}
foreach (FileInfo fileInfo in ExcelFiles)
{
try
{
string FilePath = fileInfo.FullName;
log.Info("正在处理：" + FilePath);
MSExcel.Application excelApp = new MSExcel.Application
{
Visible = false//是打开可见
};
MSExcel.Workbooks _workbooks = excelApp.Workbooks;
MSExcel._Workbook _workbook = _workbooks.Add(FilePath);
MSExcel._Worksheet whs = _workbook.Sheets[1];//获取第2张工作表 TODO：生产环境改回1
whs.Activate();
JArray ModelArray = new JArray();
JObject ModelJson = new JObject();
JArray FOrderEntrys = new JArray();
List <string> ordenoList = new List <string>();
int excelIndex = 2;
int BillNoLineNumber = 0; //表头单据编号所在行数
bool IsAllSuccess = true; //是否整个文件导入成功
bool HasSuccess = false; //是否整个文件有导入成功的订单
bool HasError = false; //是否整个文件有导入失败的订单
while (true)
{
try
{
MSExcel.Range rang = (MSExcel.Range)whs.Cells[excelIndex, 32];//ERP收料通知单单号
if (rang.Value != null)
{
string ERPBillNo = Convert.ToString(rang.Value);
string PrevFBillNo = Convert.ToString(((MSExcel.Range)whs.Cells[excelIndex - 1, 32]).Value); //上一行的ERP收料通知单单号
string NextFBillNo = Convert.ToString(((MSExcel.Range)whs.Cells[excelIndex + 1, 32]).Value); //下一行的ERP收料通知单单号
string QISBillNo = Convert.ToString(((MSExcel.Range)whs.Cells[excelIndex, 1]).Value); //QIS检验单号
string FDate = Convert.ToString(((MSExcel.Range)whs.Cells[excelIndex, 2]).Value);
string MaterialNumber = Convert.ToString(((MSExcel.Range)whs.Cells[excelIndex, 7]).Value);
string ComputerResult = Convert.ToString(((MSExcel.Range)whs.Cells[excelIndex, 15]).Value); //电脑判定
double CheckQty = Convert.ToDouble(((MSExcel.Range)whs.Cells[excelIndex, 14]).Value); //批量数（报检数量）
double RealQty = Convert.ToDouble(((MSExcel.Range)whs.Cells[excelIndex, 22]).Value); //实收数量（合格数量）
string CheckResult = Convert.ToString(((MSExcel.Range)whs.Cells[excelIndex, 16]).Value); //检验结果
double unqualifiedQty = CheckQty - RealQty; //不合格数量
//ERP收料通知单单号和上一行不同，则是另一张单据
if (!PrevFBillNo.Equals(ERPBillNo))
{
BillNoLineNumber = excelIndex;
log.Info("正在读取：" + ERPBillNo);
}
string NeedPushEntryIds = "";
//excel的一行是相同物料合并数量的，在ERP查源单，查出来可能有多行物料
List <List <object> > Bills = PUR_ReceiveBill.GetAllBill(client, "FBillNo='" + ERPBillNo + "'" + " and FMaterialID.FNumber='" + MaterialNumber + "'");
for (int i = 0; i < Bills.Count; i++)
{
double FQty = 0;
string SrcBillID = Convert.ToString(Bills[i][0]);
string SrcBillNo = Convert.ToString(Bills[i][1]);
string SrcEntryID = Convert.ToString(Bills[i][8]);
double FActReceiveQty = Convert.ToDouble(Bills[i][7]); //交货数量
double FCheckJoinQty = Convert.ToDouble(Bills[i][9]); //检验关联数量
double NoCheckQty = FActReceiveQty - FCheckJoinQty; //剩余未检验数量
string SrcBillTypeID = Convert.ToString(Bills[i][10]);
string SrcSeq = Convert.ToString(Bills[i][11]);
if (NoCheckQty > CheckQty)
{
FQty = CheckQty;
}
else
{
FQty = NoCheckQty;
}
CheckQty = CheckQty - FQty;
if (FQty <= 0)
{
continue;
}
NeedPushEntryIds = NeedPushEntryIds + SrcEntryID + ",";
//一行下推成一单
string NewBillId = PUR_ReceiveBill.PushToInspectBill(client, SrcEntryID);
if (!"".Equals(NewBillId))
{
//修改数量
List <List <object> > InspectBills = QM_InspectBill.GetAllBill(client, "FID=" + NewBillId);
if (InspectBills.Count > 0)
{
string NewBillNo = Convert.ToString(InspectBills[0][1]);
ordenoList.Add(NewBillNo);
JArray MultiLanguageTextArr = new JArray();
JObject MultiLanguageTextJson = new JObject();
MultiLanguageTextJson.Add("Key", 2052);
MultiLanguageTextJson.Add("Value", "QIS导入，单号" + QISBillNo);
JObject Entry = new JObject();
ModelJson.Add("FID", Convert.ToString(InspectBills[0][0]));
ModelJson.Add("FEntity", FOrderEntrys);
ModelJson.Add("FDescription", "QIS导入，单号" + QISBillNo);
FOrderEntrys.Add(Entry);
Entry.Add("FEntryID", Convert.ToString(InspectBills[0][2]));
Entry.Add("FInspectQty", FQty);
Entry.Add("FMemo", "QIS导入，单号" + QISBillNo);
if (!"合格".Equals(ComputerResult))
{
Entry.Add("FInspectResult", "2"); //检验结果=不合格
JArray FPolicyDetailEntrys = new JArray(); //使用决策 子单据体
JObject FPolicyDetailEntry1 = new JObject();
JObject FPolicyDetailEntry2 = new JObject();
switch (CheckResult)
{
case "退货":
break;
case "让步接收":
Entry.Add("FPolicyDetail", FPolicyDetailEntrys);
FPolicyDetailEntrys.Add(FPolicyDetailEntry1);
FPolicyDetailEntry1.Add("FUsePolicy", "A");
FPolicyDetailEntry1.Add("FPolicyQty", RealQty);
FPolicyDetailEntrys.Add(FPolicyDetailEntry2);
FPolicyDetailEntry2.Add("FUsePolicy", "B");
FPolicyDetailEntry2.Add("FPolicyQty", unqualifiedQty);
break;
case "挑选":
Entry.Add("FPolicyDetail", FPolicyDetailEntrys);
FPolicyDetailEntrys.Add(FPolicyDetailEntry1);
FPolicyDetailEntry1.Add("FUsePolicy", "A");
FPolicyDetailEntry1.Add("FPolicyQty", RealQty);
FPolicyDetailEntrys.Add(FPolicyDetailEntry2);
FPolicyDetailEntry2.Add("FUsePolicy", "E");
FPolicyDetailEntry2.Add("FPolicyQty", unqualifiedQty);
break;
}
}
else if ("合格".Equals(ComputerResult) && "内部原因".Equals(CheckResult))
{
JArray FPolicyDetailEntrys = new JArray();//使用决策 子单据体
JObject FPolicyDetailEntry1 = new JObject();
Entry.Add("FPolicyDetail", FPolicyDetailEntrys);
FPolicyDetailEntrys.Add(FPolicyDetailEntry1);
FPolicyDetailEntry1.Add("FDetailID", Convert.ToString(InspectBills[0][3]));
FPolicyDetailEntry1.Add("FMemo1", "内部原因");
}
Dictionary <string, object> ImportResult = QM_InspectBill.SaveBill(client, new StringBuilder(ModelJson.ToString()), new List <string>(ordenoList));
//提交审核检验单
CommonOperate commonOperate = new CommonOperate();
commonOperate.SubmitBill(client, "QM_InspectBill", new StringBuilder("\"" + NewBillNo + "\""));
commonOperate.AuditBill(client, "QM_InspectBill", new StringBuilder("\"" + NewBillNo + "\""));
ModelJson = new JObject();
FOrderEntrys = new JArray();
if (!Convert.ToBoolean(ImportResult["IsSuccess"]))
{
HasError = true;
IsAllSuccess = false;
}
else
{
HasSuccess = true;
}
}
else
{
log.Info("下推的单据已被删除。");
}
}
}
//移除掉最后一个","
if (NeedPushEntryIds.Length > 0)
{
NeedPushEntryIds = NeedPushEntryIds.Remove(NeedPushEntryIds.LastIndexOf(","), 1);
}
}
else
{
break;
}
}
catch (Exception ex)
{
log.Error(ex);
}
finally
{
excelIndex++;
}
}
string ERPBackup = Convert.ToString(ConfigurationManager.AppSettings["ERPBackup"].ToString().Trim());
string ResultDirectory = ERPBackup; //处理完后文件保存到这里
if (!Directory.Exists(ResultDirectory)) //如果不存在就创建文件夹
{
Directory.CreateDirectory(ResultDirectory);
}
whs.SaveAs(Path.Combine(ResultDirectory, fileInfo.Name), 51);
//关闭对象
Marshal.ReleaseComObject(_workbook);
Marshal.ReleaseComObject(whs);
excelApp.Quit();
GC.Collect();
fileInfo.Delete();
}
catch (Exception ex)
{
log.Error(ex);
}
}
Executed = true;
}
private void outPutReciveBill()
{
List <List <object> > Bills = PUR_ReceiveBill.GetAllBill(client, "FDocumentStatus = 'C' and FCheckInComing = 1 and F_PAEZ_Exported=0");//F_PAEZ_Exported=0 未导出
//List<List<object>> Bills = PUR_ReceiveBill.GetAllBill(client, "FBillNo='CGSL000328'");//F_PAEZ_Exported=0 未导出
if (Bills.Count == 0)
{
log.Info("没有可以导出的收料通知单。");
return;
}
MSExcel.Application excelApp = new MSExcel.Application
{
Visible = false//是打开可见
};
MSExcel.Workbooks _workbooks = excelApp.Workbooks;
//MSExcel._Workbook _workbook = _workbooks.Add(AppDomain.CurrentDomain.BaseDirectory+"\\DD_20200901084512--ERP输出模板.XLS");
MSExcel._Workbook _workbook = _workbooks.Add(System.Reflection.Missing.Value);
MSExcel._Worksheet whs = _workbook.Sheets[1];//获取第1张工作表
try
{
whs.Name = "Sheet1";
//headline
whs.Cells[1, 1] = "进货单号";
whs.Cells[1, 2] = "产品编号";
whs.Cells[1, 3] = "物料名称";
whs.Cells[1, 4] = "物料规格";
whs.Cells[1, 5] = "厂商";
whs.Cells[1, 6] = "报检数量";
whs.Cells[1, 7] = "厂商编码";
whs.Cells[1, 8] = "物料分类名称";
whs.Cells[1, 9] = "实收数量";
whs.Cells[1, 10] = "不良数";
whs.Cells[1, 11] = "QIS报检单号";
//同一单据内，相同物料合并数量
Hashtable AllMaterialHT = new Hashtable();
for (int i = 0; i < Bills.Count; i++)
{
string BillNo = Convert.ToString(Bills[i][1]);
string MaterialNumber = Convert.ToString(Bills[i][2]);
double Qty = Convert.ToDouble(Bills[i][7]);
if (AllMaterialHT.ContainsKey(BillNo + MaterialNumber))
{
JObject jObject = AllMaterialHT[BillNo + MaterialNumber] as JObject;
double OldQty = Convert.ToDouble(Convert.ToString(jObject["Qty"]));
jObject["Qty"] = OldQty + Qty;
}
else
{
List <object> OtherInfo = Bills[i];
JObject jObject = new JObject();
jObject.Add("MaterialNumber", MaterialNumber);
jObject.Add("Qty", Qty);
jObject.Add("OtherInfoIndex", i); //原来的List的索引，保存以便在下方获取原来的其他数据
AllMaterialHT.Add(BillNo + MaterialNumber, jObject);
}
}
string DateTimeStr = DateTime.Now.ToString("yyyyMMddHHmmssf");
Dictionary <string, string> BillIDsDic = new Dictionary <string, string>();
StringBuilder sbBillNos = new StringBuilder();
//for (int i = 0; i < Bills.Count; i++)
int ExcelRow = 2;
foreach (string BillAndMaterialNumber in AllMaterialHT.Keys)
{
JObject jObject = AllMaterialHT[BillAndMaterialNumber] as JObject;
string MaterialNumber = Convert.ToString(jObject["MaterialNumber"]);
double Qty = Convert.ToDouble(Convert.ToString(jObject["Qty"]));
int i = Convert.ToInt32(Convert.ToString(jObject["OtherInfoIndex"]));
List <object> BillInfo = Bills[i];
string BillID = Convert.ToString(Bills[i][0]);
string BillNo = Convert.ToString(Bills[i][1]);
string FDetailEntity_FSeq = Convert.ToString(Bills[i][11]);
if (!BillIDsDic.ContainsKey(BillID))
{
BillIDsDic.Add(BillID, BillNo);
sbBillNos.Append("\"").Append(BillNo).Append("\",");
}
whs.Cells[ExcelRow, 1] = BillNo;
whs.Cells[ExcelRow, 2] = Bills[i][2];
whs.Cells[ExcelRow, 3] = Bills[i][3];
whs.Cells[ExcelRow, 4] = Bills[i][4];
whs.Cells[ExcelRow, 5] = Bills[i][6];
whs.Cells[ExcelRow, 6] = Qty;
whs.Cells[ExcelRow, 7] = Bills[i][5];
whs.Cells[ExcelRow, 8] = Bills[i][12];
whs.Cells[ExcelRow, 9] = 0;
whs.Cells[ExcelRow, 10] = 0;
whs.Cells[ExcelRow, 11] = "'" + DateTimeStr + (i + 1).ToString("000");
ExcelRow++;
}
if (sbBillNos.Length > 0)
{
sbBillNos.Remove(sbBillNos.Length - 1, 1);; //移除掉最后一个","
}
string ERPOUTDirectory = Convert.ToString(ConfigurationManager.AppSettings["ERPOUT"].ToString().Trim());
if (!Directory.Exists(ERPOUTDirectory))//如果不存在就创建文件夹
{
Directory.CreateDirectory(ERPOUTDirectory);
}
string fileName = ERPOUTDirectory + "K3Cloud_DD_" + DateTimeStr + ".XLS";
whs.SaveAs(fileName, 51);
log.Info("正在更新该订单的同步状态。");
PUR_ReceiveBill.ExportedToQIS(client, sbBillNos);
//UpdateSyncStatus("PUR_ReceiveBill", BillIDsDic);
}
catch (Exception ex)
{
log.Error(ex);
}
finally {
//关闭对象
Marshal.ReleaseComObject(_workbook);
Marshal.ReleaseComObject(whs);
excelApp.Quit();
GC.Collect();
}
}
static void Main(string[] args)
{
using (var driver = new ChromeDriver())
{
TimeSpan timeout = new TimeSpan(00, 00, 05);
var find = new Finder(driver);
string[][] Urls =
{
new string[] { "https://eplaza.panasonic.ru/products/digital_av/av_accessories/head_phone/RP-TCM105E/", "https://eplaza.panasonic.ru/products/digital_av/av_accessories/head_phone/RP-HT161E-K/" },
new string[] { /*"https://eplaza.panasonic.ru/products/composite_sets/composite_sets/composite_sets/ES-LT2N-S820%20+%20WES9015Y1361/", "https://eplaza.panasonic.ru/products/composite_sets/composite_sets/composite_sets/ES-LV6N-S820%20+%20WES9034Y1361/"*/ }
};
var productsPrise = new List <int>();
var count = -1;
foreach (var q in Urls)
{
if (q == Urls[1])
{
for (int t = 0; t < Urls[1].Length; t++)
{
count++;
driver.Navigate().GoToUrl(Urls[1][t]);
var ProductPrise = driver.FindElementByXPath("/html/body/div[1]/div/div/div/div[4]/div/div[1]/div[1]/span/span[1]").Text;
string[] prPri = ProductPrise.Split(new Char[] { ' ', 'Р' });
foreach (string m in prPri)
{
if (m.Trim() != "")
{
;
}
}
int PrPrise = Convert.ToInt32(prPri[4] + prPri[5]);
productsPrise.Add(PrPrise);
var buy = (new WebDriverWait(driver, timeout)).Until(ExpectedConditions.ElementIsVisible(By.XPath("/html/body/div[1]/div/div/div/div[4]/div/div[1]/div[1]/div[2]/div[1]/a")));
buy.Click();
driver.Manage().Window.Maximize();
}
}
else
{
for (int t = 0; t < Urls[0].Length; t++)
{
count++;
driver.Navigate().GoToUrl(Urls[0][t]);
var ProductPrise = driver.FindElementByXPath("/html/body/div[1]/div/div/div/div[3]/div/div/div[2]/div[1]/div[2]/div[2]/div[1]/span[1]").Text;
int PrPrise = Convert.ToInt32(ProductPrise);
productsPrise.Add(PrPrise);
var buy = (new WebDriverWait(driver, timeout)).Until(ExpectedConditions.ElementIsVisible(By.XPath("/html/body/div[1]/div/div/div/div[3]/div/div/div[2]/div[2]/div[1]/div[2]/table/tbody/tr/td[3]/div/div/span/span/a[1]")));
buy.Click();
driver.Manage().Window.Maximize();
}
}
}
driver.Navigate().GoToUrl("https://eplaza.panasonic.ru/personal/order/make/");
//NewUser user = new NewUser(driver);
//user.NewUsers();
User user = new User(driver);
user.Auth();
user.Action();
//начальная стоимость из оформления
var PriseOrder = driver.FindElementByXPath("//*[@id=\"process_order\"]/div[1]/div/div[5]/table/tbody/tr[1]/td[2]/span[1]").Text;
string[] prOrd = PriseOrder.Split(new Char[] { ' ' });
foreach (string m in prOrd)
{
if (m.Trim() != "")
{
;
}
}
int PrOr = Convert.ToInt32(prOrd[0] + prOrd[1]);
//стоимость доставки из оформления
var delivery = driver.FindElementByXPath("//*[@id=\"process_order\"]/div[1]/div/div[5]/table/tbody/tr[2]/td[2]/span[1]").Text;
int Del = Convert.ToInt32(delivery);
//итоговая стоимость из оформления
var PriseOrderTotal = driver.FindElementByXPath("/html/body/div[1]/div/div/div/div[3]/div/div[2]/form/div[1]/div/div[5]/table/tbody/tr[3]/td[2]/span[1]").Text;
string[] prise = PriseOrderTotal.Split(new Char[] { ' ' });
foreach (string m in prise)
{
if (m.Trim() != "")
{
;
}
}
int PrOrT = Convert.ToInt32(prise[0] + prise[1]);
user.Submit();
//подтверждение совершеннолетия
//_18_ check = new _18_(driver);
//check.Check();
//переход в лк
driver.Navigate().Forward();
driver.Navigate().GoToUrl("https://eplaza.panasonic.ru/personal/orders/");
//номер заказа
string OrdNum = driver.FindElementByXPath("/html/body/div[1]/div/div/div/div[4]/div/div/div/div[2]/div[1]/div[1]/a").Text;
//все действия в админке
Admin adm = new Admin(driver);
adm.Revocation();
//XML
XML xml = new XML(driver, OrdNum);
xml.Info();
int h = 260; //cтоимость доставки
int i = productsPrise.Sum() + h;
var EplazaOrders = new List <Order>
{
new Order
{
OrderId = OrdNum,
ProdName = String.Join(" / ", xml.ProductName),
ProductValue = PrOr,
DeliveryValue = Del,
OrderValue = PrOrT,
AdminOrderValue = adm.AdmOrd,
Sap = xml.Sap,
Uniteller = xml.Unit,
nProductValue = productsPrise.Sum(),
nDeliveryValue = h,
nOrderValue = i,
nAdminOrderValue = i,
nSap = i,
nUniteller = i,
UrlAdress = adm.label
}
};
DisplayInExcel(EplazaOrders);
}
void DisplayInExcel(IEnumerable <Order> orders)
{
var excelApp = new Excel.Application();
Object missing = Type.Missing;
excelApp.Visible = true;
excelApp.Workbooks.Add(missing);
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
int[] Rep = new int[] { 4, 6, 8, 10, 12, 14, 2 };
int[] nRep = new int[] { 3, 5, 7, 9, 11, 13, 15, 1 };
//обЪединение ячеек
for (int i = 0; i < 6; i++)
{
Excel.Range oRange1;
oRange1 = workSheet.Range[workSheet.Cells[1, Rep[i]], workSheet.Cells[1, nRep[i]]];
oRange1.Merge(Type.Missing);
}
string[] Arr = new string[] { "D", "F", "H", "J", "L", "N" };
string[] Arr2 = new string[] { "C", "E", "G", "I", "K", "M", "O", "A", "B" };
string[] Arr3 = new string[] { "Cтоимость товара(ов)", "Стоимость доставки", "Стоимость заказа", "Заказ в админке", "САП", "Uniteller", "Ссылка на заказ", "Номер заказа", "Наименование товара" };
string[] Arr4 = new string[] { "должно быть", " факт " };
for (int i = 0; i < 9; i++)
{
workSheet.Cells[1, Arr2[i]] = Arr3[i];
}
for (int i = 0; i < 6; i++)
{
workSheet.Cells[2, Arr2[i]] = Arr4[0];
}
;
for (int i = 0; i < 6; i++)
{
workSheet.Cells[2, Arr[i]] = Arr4[1];
}
;
var row = 2;
foreach (var ord in orders)
{
row++;
workSheet.Cells[row, "A"] = ord.OrderId;
workSheet.Cells[row, "B"] = ord.ProdName;
workSheet.Cells[row, "D"] = ord.ProductValue;
workSheet.Cells[row, "F"] = ord.DeliveryValue;
workSheet.Cells[row, "H"] = ord.OrderValue;
workSheet.Cells[row, "J"] = ord.AdminOrderValue;
workSheet.Cells[row, "L"] = ord.Sap;
workSheet.Cells[row, "N"] = ord.Uniteller;
workSheet.Cells[row, "C"] = ord.nProductValue;
workSheet.Cells[row, "E"] = ord.nDeliveryValue;
workSheet.Cells[row, "G"] = ord.nOrderValue;
workSheet.Cells[row, "I"] = ord.nAdminOrderValue;
workSheet.Cells[row, "K"] = ord.nSap;
workSheet.Cells[row, "M"] = ord.nUniteller;
workSheet.Cells[row, "O"] = ord.UrlAdress;
workSheet.Cells[row, "P"] = " ";
}
//цвет текста
for (int i = 0; i < 6; i++)
{
if (workSheet.Cells[3, Arr2[i]].FormulaLocal == workSheet.Cells[3, Arr[i]].FormulaLocal)
{
Excel.Range rng2 = workSheet.get_Range(Arr[i] + "3");
rng2.Font.Color = ColorTranslator.ToOle(Color.Green);
}
else
{
Excel.Range rng2 = workSheet.get_Range(Arr[i] + "3");
rng2.Font.Color = ColorTranslator.ToOle(Color.Red);
}
}
//редактирование ячеек
workSheet.Range["A1", "O2"].AutoFormat(Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
workSheet.Range["A3", "O3"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//границы ячеек
for (int i = 1; i < 4; i++)
{
Excel.Range rt = workSheet.get_Range("A" + i, "O" + i);
rt.Borders.ColorIndex = 0;
rt.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
rt.Borders.Weight = Excel.XlBorderWeight.xlThin;
}
//перенос текста
workSheet.Cells[3, "B"].WrapText = true;
//сохранение отчета
excelApp.DisplayAlerts = false;
workSheet.SaveAs(string.Format(@"{0}\Test.xlsx", Environment.CurrentDirectory));
excelApp.Quit();
//отправка отчета
SendMail mail = new SendMail();
mail.Mail();
}
}
public bool run(string orderLoc, string transLoc, string resultLoc)
{
Microsoft.Office.Interop.Excel.Application transXL = null;
Microsoft.Office.Interop.Excel._Workbook transWB = null;
Microsoft.Office.Interop.Excel._Worksheet transSheet = null;
Microsoft.Office.Interop.Excel.Application orderXl = null;
Microsoft.Office.Interop.Excel._Workbook orderBook = null;
Microsoft.Office.Interop.Excel._Worksheet orderSheet = null;
Microsoft.Office.Interop.Excel.Application resultXl = null;
Microsoft.Office.Interop.Excel._Workbook resultBook = null;
Microsoft.Office.Interop.Excel._Worksheet resultSheet = null;
int count = 1;
while (File.Exists(resultLoc + "\\Result" + count + ".xlsx"))
{
count++;
}
try
{
transXL = new Microsoft.Office.Interop.Excel.Application();
transWB = transXL.Workbooks.Open(transLoc);
transSheet = (Microsoft.Office.Interop.Excel._Worksheet)transWB.ActiveSheet;
orderXl = new Microsoft.Office.Interop.Excel.Application();
orderBook = orderXl.Workbooks.Open(orderLoc);
orderSheet = (Microsoft.Office.Interop.Excel._Worksheet)orderBook.ActiveSheet;
resultXl = new Microsoft.Office.Interop.Excel.Application();
//resultBook = resultXl.Workbooks.Open(resultLoc + "\\Result" + count + ".xlsx");
resultBook = resultXl.Workbooks.Add();
resultSheet = (Microsoft.Office.Interop.Excel._Worksheet)resultBook.ActiveSheet;
Dictionary <string, School> schools;
Dictionary <string, int[]> itemTotal;
ReadFiles(transSheet, orderSheet, out schools, out itemTotal);
SchoolInfo(schools, resultSheet);
itemInfo(itemTotal, resultSheet);
resultSheet.SaveAs(resultLoc + "\\Result" + count + ".xlsx");
transWB.Close();
transXL.Quit();
orderBook.Close();
orderXl.Quit();
resultBook.Close();
resultXl.Quit();
return(true);
}
catch (Exception e)
{
System.Windows.Forms.MessageBox.Show("Error with Processing");
transWB.Close();
transXL.Quit();
orderBook.Close();
orderXl.Quit();
resultBook.Close();
resultXl.Quit();
return(false);
}
}
ExcelControll(Schraube[] arr, bool senden, int bestellnummer, string kundennummer)
{
// Erstellen einer Neuen Exelmappe
Excel.Application excelApp = new Excel.Application();
if (senden == true)
{
excelApp.Visible = false;
}
else
{
excelApp.Visible = true;
}
excelApp.Workbooks.Add();
// Hinzufügen einer Seite?
Excel._Worksheet mySheet = (Excel.Worksheet)excelApp.ActiveSheet;
// Kategorien festlegen
mySheet.Cells[2, 1] = "Material";
mySheet.Cells[3, 1] = "Festigkeitsklasse";
mySheet.Cells[4, 1] = "Schraubenkopf";
mySheet.Cells[5, 1] = "Gewinde";
mySheet.Cells[6, 1] = "Gewindetyp";
mySheet.Cells[7, 1] = "Schraubenlänge (mm)";
mySheet.Cells[8, 1] = "Gewindelänge (mm)";
mySheet.Cells[9, 1] = "Menge (St.)";
mySheet.Cells[10, 1] = "";
mySheet.Cells[11, 1] = "Preise (€)";
mySheet.Cells[12, 1] = "Summe (netto)";
mySheet.Cells[13, 1] = "Stückpreis (netto)";
mySheet.Cells[14, 1] = "";
mySheet.Cells[15, 1] = "Summe (brutto)";
mySheet.Cells[16, 1] = "Stückpreis (brutto)";
mySheet.Cells[17, 1] = "";
mySheet.Cells[18, 1] = "Bestellsumme";
mySheet.Cells[19, 1] = "";
mySheet.Cells[20, 1] = "";
mySheet.Cells[21, 1] = "";
mySheet.Cells[22, 1] = "Technische Details";
mySheet.Cells[23, 1] = "Schlüsselweite (mm)";
mySheet.Cells[24, 1] = "Masse pro Stück (g)";
mySheet.Cells[25, 1] = "Gesamtgewicht (g)";
mySheet.Cells[26, 1] = "Gewindesteigung (mm)";
mySheet.Cells[27, 1] = "Gewindetiefe (mm)";
mySheet.Cells[28, 1] = "Rundung (mm)";
mySheet.Cells[29, 1] = "Flankendurchmesser (mm)";
mySheet.Cells[30, 1] = "Flankenwinkel (°)";
mySheet.Cells[31, 1] = "";
mySheet.Cells[32, 1] = "Elastizitätsgrenze (N/mm²)";
mySheet.Cells[33, 1] = "Zugfestigkeit (N/mm²)";
// Listenformat einführen
mySheet.Range["A1", "F19"].AutoFormat(Excel.XlRangeAutoFormat.xlRangeAutoFormatList2);
mySheet.Range["A22", "F34"].AutoFormat(Excel.XlRangeAutoFormat.xlRangeAutoFormatList2);
double summe = 0;
// Werte der Schrauben in Tabelle eingeben
for (int i = 0; i < arr.Length; i++)
{
summe = summe + arr[i].nettopreis_Summe;
mySheet.Cells[1, i + 2] = "Schraube " + (i + 1);
mySheet.Cells[2, i + 2] = arr[i].material;
mySheet.Cells[3, i + 2] = arr[i].festigkeit;
mySheet.Cells[4, i + 2] = arr[i].typ;
mySheet.Cells[5, i + 2] = arr[i].gewinde;
mySheet.Cells[6, i + 2] = arr[i].gewindeart;
mySheet.Cells[7, i + 2] = arr[i].laenge;
mySheet.Cells[8, i + 2] = arr[i].gewindelaenge;
mySheet.Cells[9, i + 2] = arr[i].menge;
mySheet.Cells[10, i + 2] = "";
mySheet.Cells[11, i + 2] = "";
mySheet.Cells[12, i + 2] = Math.Round(arr[i].nettopreis_Summe, 2);
mySheet.Cells[13, i + 2] = Math.Round(arr[i].nettoeinzelpreis, 2);
mySheet.Cells[14, i + 2] = "";
mySheet.Cells[15, i + 2] = Math.Round(arr[i].preis_summe, 2);
mySheet.Cells[16, i + 2] = Math.Round(arr[i].stückpreis, 2);
mySheet.Cells[17, i + 2] = "";
// Summe
mySheet.Cells[19, i + 2].AddComment("Test");
mySheet.Cells[20, i + 2] = "";
mySheet.Cells[21, i + 2] = "";
mySheet.Cells[22, i + 2] = "";
mySheet.Cells[23, i + 2] = arr[i].schluesselbreite;
mySheet.Cells[24, i + 2] = Math.Round(arr[i].masse, 2);
mySheet.Cells[25, i + 2] = Math.Round(arr[i].gesamtgewicht, 2);
mySheet.Cells[26, i + 2] = Math.Round(arr[i].gewindesteigung, 2);
mySheet.Cells[27, i + 2] = Math.Round(arr[i].gewindetiefe, 2);
mySheet.Cells[28, i + 2] = Math.Round(arr[i].gewinderundung, 2);
mySheet.Cells[29, i + 2] = Math.Round(arr[i].flankendurchmesser, 2);
mySheet.Cells[30, i + 2] = Math.Round(arr[i].flankenwinkel, 2);
mySheet.Cells[31, i + 2] = "";
mySheet.Cells[32, i + 2] = Math.Round(arr[i].elastizitätsgrenze, 2);
mySheet.Cells[33, i + 2] = Math.Round(arr[i].Zugfestigkeit, 2);
}
mySheet.Cells[18, 6] = Math.Round(summe, 2);
// Zellenbreite an Text anpassen
for (int i = 1; i < 9; i++)
{
mySheet.Columns[i].AutoFit();
}
if (senden == true)
{
mySheet.SaveAs(@"C:\Windows\Temp\Bestellung " + Convert.ToString(bestellnummer) + "(" + kundennummer + ").xlsx");
excelApp.Workbooks.Close();
Emailsenden(bestellnummer, kundennummer);
}
}
public void ExportToExcel(List <Relatorio> _relatorio)
{
// Carregar o aplicativo Excel
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
// Create empty workbook
excel.Workbooks.Add();
// Criar planilha
Microsoft.Office.Interop.Excel._Worksheet workSheet = excel.ActiveSheet;
try
{
workSheet.Cells[1, "A"] = "Regime";
workSheet.Cells[1, "B"] = "Campus";
workSheet.Cells[1, "C"] = "Turno";
workSheet.Cells[1, "D"] = "Curso";
workSheet.Cells[1, "E"] = "Dia da Semana";
workSheet.Cells[1, "F"] = "Data";
workSheet.Cells[1, "G"] = "Hora";
workSheet.Cells[1, "H"] = "Turma";
workSheet.Cells[1, "I"] = "Disciplina";
workSheet.Cells[1, "J"] = "Professor Responsavel";
workSheet.Cells[1, "K"] = "Professor Aplicador";
workSheet.Cells[1, "L"] = "Divisão";
workSheet.Cells[1, "M"] = "Sala";
workSheet.Cells[1, "N"] = "Calendario";
int row = 2;
String Data_Nova;
foreach (Relatorio r in _relatorio)
{
workSheet.Cells[row, "A"] = r.Regime;
workSheet.Cells[row, "B"] = r.Campus;
workSheet.Cells[row, "C"] = r.Turno;
workSheet.Cells[row, "D"] = r.Curso;
workSheet.Cells[row, "E"] = r.DiaSemana;
Data_Nova = r.Data.Substring(3, 2) + "/";
Data_Nova = Data_Nova + r.Data.Substring(0, 2) + "/";
Data_Nova = Data_Nova + r.Data.Substring(6, 4);
workSheet.Cells[row, "F"] = Data_Nova;
workSheet.Cells[row, "G"] = r.Hora;
workSheet.Cells[row, "H"] = r.Turma;
workSheet.Cells[row, "I"] = r.Disciplina;
workSheet.Cells[row, "J"] = r.ProfResp;
workSheet.Cells[row, "K"] = r.ProfApl;
workSheet.Cells[row, "L"] = r.Divisao;
workSheet.Cells[row, "M"] = r.Sala;
workSheet.Cells[row, "N"] = r.Calendario;
row++;
}
// estilos predefinidos
workSheet.Range["A1"].AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic3);
// Nome do Arquivo
string fileName = string.Format(caminho + "\\Relatorio.xls", Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory));
// Salvando Arquivo
workSheet.SaveAs(fileName);
}
catch (Exception exception)
{
System.Windows.MessageBox.Show("Ocorreu um problema para salvar o arquivo !\n" + exception.Message, "Erro");
}
finally
{
// Sair do Excel
excel.Quit();
if (excel != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
}
if (workSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
}
// Limpando Arquivos
excel = null;
workSheet = null;
// Forçar a limpeza do coletor de lixo
GC.Collect();
}
}
public void ProcesarFondo(Maternal m, string periodo)
{
var excelAppOut = new ExcelX.Application();
var fecha = DateTime.Now.ToString().Replace("/", "").Replace(":", "").Replace(" ", "");
//var periodo = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString().PadLeft(2, '0');
var rutaEntrada = @"C:\Fondos Nacionales\Templates\IF_MATERNAL";
var rutaSalida = @"C:\Fondos Nacionales\out\" + periodo + @"\Maternal\";
Utilidades.AbrirLibro(excelAppOut, rutaEntrada);
//"Feb-17"
ExcelX._Worksheet Salida = (ExcelX.Worksheet)excelAppOut.Sheets["Template"];
Salida.Cells["14", "K"] = m.A1.Replace(".", "").Replace(",", "");
Salida.Cells["15", "K"] = m.A2.Replace(".", "").Replace(",", "");
Salida.Cells["18", "I"] = m.A31.Replace(".", "").Replace(",", "");
Salida.Cells["19", "I"] = m.A32.Replace(".", "").Replace(",", "");
Salida.Cells["22", "I"] = m.A41.Replace(".", "").Replace(",", "");
Salida.Cells["23", "I"] = m.A42.Replace(".", "").Replace(",", "");
Salida.Cells["30", "K"] = m.C1.Replace(".", "").Replace(",", "");
Salida.Cells["31", "K"] = m.C2.Replace(".", "").Replace(",", "");
Salida.Cells["32", "K"] = m.C3.Replace(".", "").Replace(",", "");
Salida.Cells["33", "K"] = m.C4.Replace(".", "").Replace(",", "");
Salida.Cells["34", "K"] = m.C5.Replace(".", "").Replace(",", "");
Salida.Cells["37", "I"] = m.C61.Replace(".", "").Replace(",", "");
Salida.Cells["38", "I"] = m.C62.Replace(".", "").Replace(",", "");
Salida.Cells["39", "I"] = m.C63.Replace(".", "").Replace(",", "");
Salida.Cells["40", "I"] = m.C64.Replace(".", "").Replace(",", "");
Salida.Cells["41", "I"] = m.C65.Replace(".", "").Replace(",", "");
Salida.Cells["44", "I"] = m.C71.Replace(".", "").Replace(",", "");
Salida.Cells["45", "I"] = m.C72.Replace(".", "").Replace(",", "");
Salida.Cells["46", "I"] = m.C73.Replace(".", "").Replace(",", "");
Salida.Cells["47", "I"] = m.C74.Replace(".", "").Replace(",", "");
Salida.Cells["48", "I"] = m.C75.Replace(".", "").Replace(",", "");
Salida.Cells["51", "I"] = m.C81.Replace(".", "").Replace(",", "");
Salida.Cells["52", "I"] = m.C82.Replace(".", "").Replace(",", "");
Salida.Cells["53", "I"] = m.C83.Replace(".", "").Replace(",", "");
Salida.Cells["54", "I"] = m.C84.Replace(".", "").Replace(",", "");
Salida.Cells["55", "I"] = m.C85.Replace(".", "").Replace(",", "");
Salida.Cells["58", "I"] = m.C91.Replace(".", "").Replace(",", "");
Salida.Cells["59", "I"] = m.C92.Replace(".", "").Replace(",", "");
Salida.Cells["60", "I"] = m.C93.Replace(".", "").Replace(",", "");
Salida.Cells["61", "I"] = m.C94.Replace(".", "").Replace(",", "");
Salida.Cells["62", "I"] = m.C95.Replace(".", "").Replace(",", "");
Salida.Cells["68", "K"] = m.E1.Replace(".", "").Replace(",", "");
Salida.Cells["69", "K"] = m.E2.Replace(".", "").Replace(",", "");
Salida.Cells["70", "K"] = m.E3.Replace(".", "").Replace(",", "");
Salida.Cells["71", "K"] = m.E4.Replace(".", "").Replace(",", "");
Salida.Cells["72", "K"] = m.E5.Replace(".", "").Replace(",", "");
Salida.Cells["76", "K"] = m.F1.Replace(".", "").Replace(",", "");
Salida.Cells["77", "K"] = m.F2.Replace(".", "").Replace(",", "");
Salida.Cells["78", "K"] = m.F3.Replace(".", "").Replace(",", "");
Salida.Cells["79", "K"] = m.F4.Replace(".", "").Replace(",", "");
Salida.Cells["80", "K"] = m.F5.Replace(".", "").Replace(",", "");
Salida.Cells["84", "K"] = m.G1.Replace(".", "").Replace(",", "");
Salida.Cells["85", "K"] = m.G2.Replace(".", "").Replace(",", "");
Salida.Cells["86", "K"] = m.G3.Replace(".", "").Replace(",", "");
Salida.Cells["87", "K"] = m.G4.Replace(".", "").Replace(",", "");
System.IO.FileAttributes attr;
try
{
attr = System.IO.File.GetAttributes(rutaSalida);
}
catch (Exception ex)
{
System.IO.Directory.CreateDirectory(rutaSalida);
}
Salida.Name = periodo;
Salida.SaveAs(rutaSalida + "IFMaternal_" + fecha + Utilidades.ExtensionLibro(Salida.Application.ActiveWorkbook));
excelAppOut.Quit();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelAppOut);
}
public static void ExportToExcel()
{
List <Car> cars = new List <Car>()
{
new Car {
Name = "Toyota", Color = "Red", MaximumSpeed = 195
},
new Car {
Name = "Honda", Color = "Blue", MaximumSpeed = 224
},
new Car {
Name = "Mazda", Color = "Green", MaximumSpeed = 205
}
};
// Load Excel application
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
// Create empty workbook
excel.Workbooks.Add();
// Create Worksheet from active sheet
Microsoft.Office.Interop.Excel._Worksheet workSheet = excel.ActiveSheet;
// I created Application and Worksheet objects before try/catch,
// so that i can close them in finnaly block.
// It's IMPORTANT to release these COM objects!!
try
{
// ------------------------------------------------
// Creation of header cells
// ------------------------------------------------
workSheet.Cells[1, "A"] = "Name";
workSheet.Cells[1, "B"] = "Color";
workSheet.Cells[1, "C"] = "Maximum speed";
// ------------------------------------------------
// Populate sheet with some real data from "cars" list
// ------------------------------------------------
int row = 2; // start row (in row 1 are header cells)
foreach (Car car in cars)
{
workSheet.Cells[row, "A"] = car.Name;
workSheet.Cells[row, "B"] = car.Color;
workSheet.Cells[row, "C"] = string.Format("{0} km/h", car.MaximumSpeed);
row++;
}
// Apply some predefined styles for data to look nicely :)
workSheet.Range["A1"].AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic1);
// Define filename
string fileName = string.Format(@"{0}\Currency{1}.xlsx", Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), DateTime.Now.Month);
// Save this data as a file
workSheet.SaveAs(fileName);
// Display SUCCESS message
Console.WriteLine(string.Format("The file '{0}' is saved successfully!", fileName));
}
catch (Exception exception)
{
Console.WriteLine("Exception",
"There was a PROBLEM saving Excel file!\n" + exception.Message);
}
finally
{
// Quit Excel application
excel.Quit();
// Release COM objects (very important!)
if (excel != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
}
if (workSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
}
// Empty variables
excel = null;
workSheet = null;
// Force garbage collector cleaning
GC.Collect();
}
}
private void exportexcel()
{
DateTime dt = DateTime.Now;
string Name = cbthongke.Text + " (" + dt.Hour + "h" + dt.Minute + " " + dt.Day + "." + dt.Month + "." + dt.Year + ")";
string LinkSave = System.Windows.Forms.Application.StartupPath + @"\..\Reports\";
// tạo đối tượng
SaveFileDialog fsave = new SaveFileDialog();
fsave.InitialDirectory = LinkSave;
fsave.Filter = "(tất cả các tệp)|*.*|(các tệp tin)|*.xlsx";
fsave.FileName = Name + ".xlsx";
if (fsave.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
{
}
else
{
if (fsave.FileName != "")
{
//khởi tạo excel
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
//Khởi tại WorkBook
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
//Khởi tạo Worksheet
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
app.Visible = false;
DateTime time1 = datetimebd.Value;
DateTime time2 = datetimekt.Value;
string dt1 = time1.ToString("dd/MM/yyyy");
string dt2 = time2.ToString("dd/MM/yyyy");
//Đổ dữ liệu vào sheet
if (cbthongke.Text == "Báo cáo khách hàng")
{
//worksheet.Range["E4", "E100"].NumberFormat("Text");
worksheet.Cells[1, 1] = "BÁO CÁO KHÁCH HÀNG";
worksheet.Cells[2, 1] = "Nhân Viên: " + frmQLCHG.Ten;
worksheet.Cells[3, 1] = "MãKH";
worksheet.Cells[3, 2] = "Họ Và Tên";
worksheet.Cells[3, 3] = "Năm Sinh";
worksheet.Cells[3, 4] = "Giới Tính";
worksheet.Cells[3, 5] = "Số Điện Thoại";
worksheet.Cells[3, 6] = "Địa Chỉ";
worksheet.Cells[3, 7] = "Loại KH";
worksheet.Cells[3, 8] = "Tiền Tích Lũy";
for (int i = 0; i < dataviewthongke.RowCount; i++)
{
for (int j = 0; j < 8; j++)
{
worksheet.Cells[i + 4, j + 1] = dataviewthongke.Rows[i].Cells[j].Value;
worksheet.Cells[i + 5, 2] = "VIP 0 = " + txtvip0.Text + "";
worksheet.Cells[i + 6, 2] = "VIP 1 = " + txtvip1.Text + "";
worksheet.Cells[i + 7, 2] = "VIP 2 = " + txtvip2.Text + "";
worksheet.Cells[i + 8, 2] = "VIP 3 = " + txtvip3.Text + "";
worksheet.Cells[i + 9, 2] = "Tổng KH = " + txttongkh.Text + "";
//Kẻ Bảng
worksheet.Range["A3", "H" + (i + 4)].Borders.LineStyle = 1;
worksheet.Range["A4", "H" + (i + 4)].Borders.LineStyle = 1;
//Định dạng các dòng text;
worksheet.Range["A4", "A" + (i + 4)].HorizontalAlignment = 3;
worksheet.Range["B4", "B" + (i + 4)].HorizontalAlignment = 3;
worksheet.Range["E4", "E" + (i + 4)].HorizontalAlignment = 3;
worksheet.Range["F4", "F" + (i + 4)].HorizontalAlignment = 3;
worksheet.Range["G4", "G" + (i + 4)].HorizontalAlignment = 3;
worksheet.Range["H4", "H" + (i + 4)].HorizontalAlignment = 3;
}
}
//Định dạng trang:
worksheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait;
worksheet.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4;
worksheet.PageSetup.LeftMargin = 0;
worksheet.PageSetup.RightMargin = 0;
worksheet.PageSetup.TopMargin = 0;
worksheet.PageSetup.BottomMargin = 0;
//Định dạng cột
worksheet.Range["A1"].ColumnWidth = 8.57;
worksheet.Range["B1"].ColumnWidth = 30;
worksheet.Range["C1"].ColumnWidth = 12;
worksheet.Range["D1"].ColumnWidth = 11.86;
worksheet.Range["E1"].ColumnWidth = 17.57;
worksheet.Range["F1"].ColumnWidth = 15;
worksheet.Range["G1"].ColumnWidth = 10.57;
worksheet.Range["H1"].ColumnWidth = 18;
//Định dạng font
worksheet.Range["A1", "H100"].Font.Name = "Times New Roman";
worksheet.Range["A1", "H1"].Font.Size = 20;
worksheet.Range["A2", "H100"].Font.Size = 14;
worksheet.Range["A1", "H1"].MergeCells = true;
worksheet.Range["A1", "H1"].Font.Bold = true;
worksheet.Range["A3", "H3"].Font.Bold = true;
//Định dạng các dòng text:
int abc = dataviewthongke.RowCount + 2;
string def = "H" + abc;
worksheet.Range["A1", "H1"].HorizontalAlignment = 3;
worksheet.Range["A3", def].HorizontalAlignment = 3;
}
if (cbthongke.Text == "Báo cáo doanh thu, lợi nhuận")
{
worksheet.Cells[1, 1] = "BÁO CÁO DOANH THU, LỢI NHUẬN";
worksheet.Cells[2, 1] = "Nhân Viên: " + frmQLCHG.Ten;
worksheet.Cells[2, 6] = "Ngày: " + dt1 + " - " + dt2;
worksheet.Cells[3, 1] = "MãHĐ";
worksheet.Cells[3, 2] = "MãTK";
worksheet.Cells[3, 3] = "MãKH";
worksheet.Cells[3, 4] = "Ngày lập";
worksheet.Cells[3, 5] = "Tổng Tiền";
worksheet.Cells[3, 6] = "Chiết Khấu";
worksheet.Cells[3, 7] = "Thuế";
worksheet.Cells[3, 8] = "Tổng Tiền Gốc";
for (int i = 0; i < dataviewthongke.RowCount; i++)
{
for (int j = 0; j < 8; j++)
{
//worksheet.Cells[i + 8, 1] = i ;
worksheet.Cells[i + 4, j + 1] = dataviewthongke.Rows[i].Cells[j].Value;
worksheet.Cells[i + 5, 1] = "";
worksheet.Cells[i + 6, 1] = "Tổng Doanh Thu : " + txtdoanhthu.Text + " VND";
worksheet.Cells[i + 7, 1] = "Lợi Nhuận : " + txtloinhuan.Text + " VND";
//Kẻ Bảng
worksheet.Range["A3", "H" + (i + 4)].Borders.LineStyle = 1;
worksheet.Range["A4", "H" + (i + 4)].Borders.LineStyle = 1;
//Định dạng các dòng text;
worksheet.Range["A4", "A" + (i + 4)].HorizontalAlignment = 3;
worksheet.Range["B4", "B" + (i + 4)].HorizontalAlignment = 3;
worksheet.Range["E4", "E" + (i + 4)].HorizontalAlignment = 3;
worksheet.Range["F4", "F" + (i + 4)].HorizontalAlignment = 3;
worksheet.Range["G4", "G" + (i + 4)].HorizontalAlignment = 3;
worksheet.Range["H4", "H" + (i + 4)].HorizontalAlignment = 3;
}
}
//Định dạng trang:
worksheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait;
worksheet.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4;
worksheet.PageSetup.LeftMargin = 0;
worksheet.PageSetup.RightMargin = 0;
worksheet.PageSetup.TopMargin = 0;
worksheet.PageSetup.BottomMargin = 0;
//Định dạng cột
worksheet.Range["A1"].ColumnWidth = 8.57;
worksheet.Range["B1"].ColumnWidth = 8.57;
worksheet.Range["C1"].ColumnWidth = 12;
worksheet.Range["D1"].ColumnWidth = 21.29;
worksheet.Range["E1"].ColumnWidth = 17.57;
worksheet.Range["F1"].ColumnWidth = 15;
worksheet.Range["G1"].ColumnWidth = 10.57;
worksheet.Range["H1"].ColumnWidth = 18.86;
//Định dạng font
worksheet.Range["A1", "H100"].Font.Name = "Times New Roman";
worksheet.Range["A1", "H1"].Font.Size = 20;
worksheet.Range["A2", "H100"].Font.Size = 14;
worksheet.Range["A1", "H1"].MergeCells = true;
worksheet.Range["A1", "H1"].Font.Bold = true;
worksheet.Range["A3", "H3"].Font.Bold = true;
//Định dạng các dòng text:
int abc = dataviewthongke.RowCount + 3;
string def = "H" + abc;
worksheet.Range["A1", "G1"].HorizontalAlignment = 3;
worksheet.Range["A3", def].HorizontalAlignment = 3;
}
if (cbthongke.Text == "Báo cáo số lượng tồn")
{
worksheet.Cells[1, 1] = "BÁO CÁO SỐ LƯỢNG TỒN";
worksheet.Cells[2, 1] = "Nhân Viên: " + frmQLCHG.Ten;
worksheet.Cells[3, 1] = "MãG";
worksheet.Cells[3, 2] = "TênG";
worksheet.Cells[3, 3] = "LoạiG";
worksheet.Cells[3, 4] = "TLGao";
worksheet.Cells[3, 5] = "Giá Nhập";
worksheet.Cells[3, 6] = "Giá Bán";
worksheet.Cells[3, 7] = "Xuất Xứ";
for (int i = 0; i < dataviewthongke.RowCount; i++)
{
for (int j = 0; j < 7; j++)
{
//worksheet.Cells[i + 8, 1] = i ;
worksheet.Cells[i + 4, j + 1] = dataviewthongke.Rows[i].Cells[j].Value;
worksheet.Cells[i + 5, 1] = "";
//Kẻ Bảng
worksheet.Range["A3", "G" + (i + 4)].Borders.LineStyle = 1;
worksheet.Range["A4", "G" + (i + 4)].Borders.LineStyle = 1;
//Định dạng các dòng text:
worksheet.Range["A3", "A" + (i + 4)].HorizontalAlignment = 3;
worksheet.Range["B3", "B" + (i + 4)].HorizontalAlignment = 3;
worksheet.Range["E3", "E" + (i + 4)].HorizontalAlignment = 3;
worksheet.Range["F3", "F" + (i + 4)].HorizontalAlignment = 3;
worksheet.Range["G3", "G" + (i + 4)].HorizontalAlignment = 3;
}
}
//Định dạng trang:
worksheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait;
worksheet.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4;
worksheet.PageSetup.LeftMargin = 0;
worksheet.PageSetup.RightMargin = 0;
worksheet.PageSetup.TopMargin = 0;
worksheet.PageSetup.BottomMargin = 0;
//Định dạng cột
worksheet.Range["A1"].ColumnWidth = 5.55;
worksheet.Range["B1"].ColumnWidth = 10;
worksheet.Range["C1"].ColumnWidth = 22;
worksheet.Range["D1"].ColumnWidth = 15;
worksheet.Range["E1"].ColumnWidth = 16;
worksheet.Range["F1"].ColumnWidth = 13.35;
worksheet.Range["G1"].ColumnWidth = 15;
//Định dạng font
worksheet.Range["A1", "G100"].Font.Name = "Times New Roman";
worksheet.Range["A1", "G1"].Font.Size = 20;
worksheet.Range["A2", "G100"].Font.Size = 14;
worksheet.Range["A1", "G1"].MergeCells = true;
worksheet.Range["A1", "G1"].Font.Bold = true;
worksheet.Range["A3", "G3"].Font.Bold = true;
//Định dạng các dòng text:
int abc = dataviewthongke.RowCount + 3;
string def = "G" + abc;
worksheet.Range["A1", "G1"].HorizontalAlignment = 3;
worksheet.Range["A3", def].HorizontalAlignment = 3;
}
if (cbthongke.Text == "Báo cáo gạo sắp hết")
{
worksheet.Cells[1, 1] = "BÁO CÁO SỐ GẠO SẮP HẾT";
worksheet.Cells[2, 1] = "Nhân Viên: " + frmQLCHG.Ten;
worksheet.Cells[3, 1] = "MãG";
worksheet.Cells[3, 2] = "TênG";
worksheet.Cells[3, 3] = "LoạiG";
worksheet.Cells[3, 4] = "TLGạo";
worksheet.Cells[3, 5] = "Giá Nhập";
worksheet.Cells[3, 6] = "Giá Bán";
worksheet.Cells[3, 7] = "Xuất Xứ";
for (int i = 0; i < dataviewthongke.RowCount; i++)
{
for (int j = 0; j < 7; j++)
{
//worksheet.Cells[i + 8, 1] = i ;
worksheet.Cells[i + 4, j + 1] = dataviewthongke.Rows[i].Cells[j].Value;
worksheet.Cells[i + 5, 1] = "";
//Kẻ Bảng
worksheet.Range["A3", "G" + (i + 4)].Borders.LineStyle = 1;
worksheet.Range["A4", "G" + (i + 4)].Borders.LineStyle = 1;
//Định dạng các dòng text:
worksheet.Range["A3", "A" + (i + 4)].HorizontalAlignment = 3;
worksheet.Range["B3", "B" + (i + 4)].HorizontalAlignment = 3;
worksheet.Range["E3", "E" + (i + 4)].HorizontalAlignment = 3;
worksheet.Range["F3", "F" + (i + 4)].HorizontalAlignment = 3;
worksheet.Range["G3", "G" + (i + 4)].HorizontalAlignment = 3;
}
}
//Định dạng trang:
worksheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait;
worksheet.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4;
worksheet.PageSetup.LeftMargin = 0;
worksheet.PageSetup.RightMargin = 0;
worksheet.PageSetup.TopMargin = 0;
worksheet.PageSetup.BottomMargin = 0;
//Định dạng cột
worksheet.Range["A1"].ColumnWidth = 5.55;
worksheet.Range["B1"].ColumnWidth = 10;
worksheet.Range["C1"].ColumnWidth = 22;
worksheet.Range["D1"].ColumnWidth = 15;
worksheet.Range["E1"].ColumnWidth = 16;
worksheet.Range["F1"].ColumnWidth = 13.35;
worksheet.Range["G1"].ColumnWidth = 15;
//Định dạng font
worksheet.Range["A1", "G100"].Font.Name = "Times New Roman";
worksheet.Range["A1", "G1"].Font.Size = 20;
worksheet.Range["A2", "G100"].Font.Size = 14;
worksheet.Range["A1", "G1"].MergeCells = true;
worksheet.Range["A1", "G1"].Font.Bold = true;
worksheet.Range["A3", "G3"].Font.Bold = true;
//Định dạng các dòng text:
int abc = dataviewthongke.RowCount + 3;
string def = "G" + abc;
worksheet.Range["A1", "G1"].HorizontalAlignment = 3;
worksheet.Range["A3", def].HorizontalAlignment = 3;
}
worksheet.SaveAs(fsave.FileName);
String Message = "Xuất " + cbthongke.Text + " thành công";
MessageBox.Show(Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
private void metroTilePrint_Click(object sender, EventArgs e)
{
try
{
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Title = "Сохранение xlsx файла";
saveFileDialog1.Filter = "xlsx файлы (*.xlsx)|*.xlsx|Все файлы (*.*)|*.*";
saveFileDialog1.FilterIndex = 1;
saveFileDialog1.RestoreDirectory = true;
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
Excel.Application excelApp = new Excel.Application();
excelApp.Workbooks.Add();
Excel._Worksheet worksheet = excelApp.ActiveSheet;
worksheet.Cells[1, "A"] = "Номер документа";
worksheet.Cells[1, "B"] = "Название документа";
worksheet.Cells[1, "C"] = "Тип документа";
worksheet.Cells[1, "D"] = "Студент";
worksheet.Cells[1, "E"] = "От кого";
worksheet.Cells[1, "F"] = "Кому";
worksheet.Cells[1, "G"] = "ДАта";
worksheet.Cells[1, "H"] = "Примечание";
using (ApplicationDbContext context = new ApplicationDbContext())
{
int row = 1;
var documents = context.StaffDocuments.Include(t => t.DocumentType).Include(s => s.Staff);
if (StaffId != 0)
{
documents = documents.Where(s => s.StaffId == StaffId);
}
if (metroComboBoxType.Text != "Все")
{
documents = documents.Where(s => s.DocumentType.DocumentTypeName == metroComboBoxType.Text);
}
if (metroCheckBoxDate.Checked)
{
documents = documents.Where(s => s.StaffDocumentDateCreation >= dateTimePickerFirst.Value && s.StaffDocumentDateCreation <= dateTimePickerSecond.Value);
}
foreach (var item in documents)
{
row++;
worksheet.Cells[row, "A"] = item.StaffDocumentId.ToString();
worksheet.Cells[row, "B"] = item.StaffDocumentName.ToString();
worksheet.Cells[row, "C"] = item.DocumentType.DocumentTypeName.ToString();
worksheet.Cells[row, "D"] = String.Format("{0} {1} {2}", item.Staff.StafftSurName.ToString(), item.Staff.StaffFirstName.ToString(), item.Staff.StaffSecondName.ToString());
worksheet.Cells[row, "E"] = item.StaffDoucmentFrom.ToString();
worksheet.Cells[row, "F"] = item.StaffDocumentTo.ToString();
worksheet.Cells[row, "G"] = item.StaffDocumentDateCreation.ToShortDateString();
worksheet.Cells[row, "H"] = item.StaffDocumentDescription.ToString();
}
}
worksheet.Range["A1"].AutoFormat(Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic1);
worksheet.SaveAs(string.Format(saveFileDialog1.FileName));
excelApp.Quit();
MessageBox.Show("Сохранение завершено", "SWA", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception gg)
{
MessageBox.Show(gg.Message, "SWA", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
