108 lines
2.8 KiB
C#
108 lines
2.8 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.IO;
|
|
using System.Linq;
|
|
using System.Runtime.InteropServices;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
using System.Reflection;
|
|
|
|
using OfficeOpenXml;
|
|
using System.Drawing;
|
|
|
|
namespace AutoSellerNS
|
|
{
|
|
public class ExcelHandler
|
|
{
|
|
string m_strFileName = "";
|
|
string m_strToday = DateTime.Now.ToString("yyyy-MM-dd");
|
|
|
|
public ExcelHandler(string strFileName, string[] astrColumns)
|
|
{
|
|
m_strFileName = strFileName;
|
|
if(File.Exists(m_strFileName) == false)
|
|
Create(astrColumns);
|
|
}
|
|
|
|
private void Create(string[] astrColumns)
|
|
{
|
|
lock(this)
|
|
{
|
|
FileInfo newFile = new FileInfo(m_strFileName);
|
|
ExcelPackage package = new ExcelPackage(newFile);
|
|
ExcelWorksheet Sheet = package.Workbook.Worksheets.Add("sheet");
|
|
for(int i = 0; i<astrColumns.Length; i++)
|
|
Sheet.Cells[1, i+1].Value = astrColumns[i];
|
|
package.Save();
|
|
}
|
|
}
|
|
|
|
public bool AddRow(object[] row)
|
|
{
|
|
lock(this)
|
|
{
|
|
FileInfo newFile = new FileInfo(m_strFileName);
|
|
ExcelPackage package = new ExcelPackage(newFile);
|
|
ExcelWorksheet Sheet = package.Workbook.Worksheets["sheet"];
|
|
int iRowNo = Sheet.Dimension.Rows+1;
|
|
for(int i=0; i<row.Length; i++)
|
|
{
|
|
Sheet.Cells[iRowNo, i+1].Value = row[i];
|
|
|
|
if(row[i].GetType() == typeof(int))
|
|
{
|
|
Sheet.Cells[iRowNo, i+1].Style.Numberformat.Format = "###,###,##0";
|
|
}
|
|
else if(row[i].GetType() == typeof(float) || row[i].GetType() == typeof(double))
|
|
{
|
|
Sheet.Cells[iRowNo, i+1].Style.Numberformat.Format = "###,###,##0.00";
|
|
float fValue = (float)row[i];
|
|
Sheet.Cells[iRowNo, i+1].Style.Font.Color.SetColor(fValue>0 ? Color.Red : fValue<0 ? Color.Blue : Color.Black);
|
|
}
|
|
}
|
|
|
|
Sheet.Cells.AutoFitColumns(0);
|
|
package.Save();
|
|
}
|
|
|
|
return true;
|
|
}
|
|
|
|
public bool AddRows(object[][] rows)
|
|
{
|
|
lock(this)
|
|
{
|
|
FileInfo newFile = new FileInfo(m_strFileName);
|
|
ExcelPackage package = new ExcelPackage(newFile);
|
|
ExcelWorksheet Sheet = package.Workbook.Worksheets["sheet"];
|
|
int iRowNo = Sheet.Dimension.Rows+1;
|
|
foreach(object[] row in rows)
|
|
{
|
|
for(int i = 0; i<row.Length; i++)
|
|
{
|
|
Sheet.Cells[iRowNo, i+1].Value = row[i];
|
|
|
|
if(row[i].GetType() == typeof(int))
|
|
{
|
|
Sheet.Cells[iRowNo, i+1].Style.Numberformat.Format = "###,###,##0";
|
|
}
|
|
else if(row[i].GetType() == typeof(float) || row[i].GetType() == typeof(double))
|
|
{
|
|
Sheet.Cells[iRowNo, i+1].Style.Numberformat.Format = "###,###,##0.00";
|
|
float fValue = (float)row[i];
|
|
Sheet.Cells[iRowNo, i+1].Style.Font.Color.SetColor(fValue>0 ? Color.Red : fValue<0 ? Color.Blue : Color.Black);
|
|
}
|
|
}
|
|
|
|
iRowNo++;
|
|
}
|
|
|
|
Sheet.Cells.AutoFitColumns(0);
|
|
package.Save();
|
|
}
|
|
|
|
return true;
|
|
}
|
|
}
|
|
}
|