//private void bt_Query3_Click(object sender, EventArgs e)
//{
// this.dgvStockReport.DataSource = FrmBLL.ReleaseData.arrByteToDataTable(RefWebService_BLL.refWebtWarehouseWipTracking.Instance.StockQuery(txtMth.Text.Trim(), "NA", "NA", 3, 0));
//}
//private void bt_refresh_Click(object sender, EventArgs e)
//{
// bt_Query3_Click(null, null);
//}
//private void bt_toexcel_Click(object sender, EventArgs e)
//{
// if (this.dgvStockReport.RowCount == 0)
// {
// MessageBox.Show("没有数据可以导出到Excel"); return;
// }
// else
// {
// DataToExcel(dgvStockReport);
// }
//}
#endregion
#region 将datagridview 中的数据导出到excel
private void DataToExcel(DataGridView m_DataView)
{
Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
Excel.Workbooks oBooks = oExcel.Workbooks;
Excel._Workbook oBook = null;
oBook = (Excel._Workbook)(oExcel.Workbooks.Add(true));// 引用excel工作薄
//xSheet.Columns("A:G").Selection.NumberFormatLocal = ":";//设置A-G列为文本格式
for (int i = 0; i < m_DataView.Columns.Count; i++)
{
if (m_DataView.Columns[i].Visible == true)
{
oExcel.Cells[2, i + 1] = m_DataView.Columns[i].HeaderText.ToString();
}
}
for (int i = 0; i < m_DataView.Rows.Count; i++)
{
for (int j = 0; j < m_DataView.Columns.Count; j++)
{
if (m_DataView.Columns[j].Visible == true)
{
oExcel.Cells[i + 3, j + 1] = m_DataView.Rows[i].Cells[j].Value.ToString();
}
}
}
oExcel.Visible = true;
object Missing = System.Reflection.Missing.Value;
// oExcel.Run("Sheet1.printdoc", Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing);
oBook.Application.DisplayAlerts = false;
}
public DataTable LeerSeries(DataTable tabla, string archivo, int hoja, int filaInicio, int colInicio, int filaFin, int colFin)
{
DataTable resultado = tabla;
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Open(archivo, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(hoja);
for (int i = filaInicio; i <= filaFin; i++)
{
DataRow r = resultado.NewRow();
int idx = 2;
for (int j = colInicio; j <= colFin; j++)
{
r[idx] = (xlWorkSheet.Rows.Cells[i, j] as Excel.Range).Value2;
idx++;
}
resultado.Rows.Add(r);
}
ReleaseObject(xlWorkSheet);
xlWorkBook.Close(true, null, null);
xlApp.Quit();
ReleaseObject(xlWorkBook);
ReleaseObject(xlApp);
return(resultado);
}
public List <string> GetNombresHojas(string archivo)
{
List <string> resultado = new List <string>();
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Open(archivo, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true);
for (int i = 1; i <= xlWorkBook.Worksheets.Count; i++)
{
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets[i];
resultado.Add(xlWorkSheet.Name);
ReleaseObject(xlWorkSheet);
}
xlWorkBook.Close(true, null, null);
xlApp.Quit();
ReleaseObject(xlWorkBook);
ReleaseObject(xlApp);
return(resultado);
}
public static void ConvertExcel(string openPath, string savePath, ref string errText)
{
errText = string.Empty;
try
{
//将xml文件转换为标准的Excel格式
Object Nothing = Missing.Value; //由于yongCOM组件很多值需要用Missing.Value代替
Excel.Application ExclApp = new Excel.ApplicationClass(); // 初始化
Excel.Workbook ExclDoc = ExclApp.Workbooks.Open(openPath, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing); //打开Excl工作薄
try
{
Object format = Excel.XlFileFormat.xlWorkbookNormal; //获取Excl 2007文件格式
ExclApp.DisplayAlerts = false;
ExclDoc.SaveAs(savePath, format, Nothing, Nothing, Nothing, Nothing, Excel.XlSaveAsAccessMode.xlExclusive, Nothing, Nothing, Nothing, Nothing, Nothing); //保存为Excl 2007格式
}
catch (Exception ex)
{
errText = ex.Message;
}
finally
{
ExclDoc.Close(Nothing, Nothing, Nothing);
ExclApp.Quit();
}
}
catch (Exception ex)
{
errText = ex.Message;
}
}
internal DataTable LeerSeriesParaModificacionDatos(DataTable tabla, string archivo, int hoja, int filaInicio, int colInicio, int filaFin, int colFin, int anioIncio, int anioFin)
{
DataTable resultado = tabla;
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Open(archivo, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true);
int cantidadFilas = filaInicio + (anioFin - anioIncio);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(hoja);
for (int i = filaInicio; i <= filaFin; i++)
{
int idx = 2;
int indiceTabla = ObtenerNumFilaDataRow(resultado, anioIncio);
if (indiceTabla == -1)
{
break;
}
else
{
DataRow r = resultado.Rows[indiceTabla];
for (int j = colInicio; j <= colFin; j++)
{
if ((xlWorkSheet.Rows.Cells[i, j] as Excel.Range).Value2 == null || !Utiles.EsDecimalPositivoYCero((xlWorkSheet.Rows.Cells[i, j] as Excel.Range).Value2.ToString()))
{
_resultado = false;
break;
}
else
{
r[idx] = (xlWorkSheet.Rows.Cells[i, j] as Excel.Range).Value2;
idx++;
}
}
if (_resultado)
{
anioIncio++;
}
else
{
break;
}
}
}
ReleaseObject(xlWorkSheet);
xlWorkBook.Close(true, null, null);
xlApp.Quit();
ReleaseObject(xlWorkBook);
ReleaseObject(xlApp);
return(resultado);
}
protected void Page_Load(object sender, EventArgs e)
{
if (appOP == null)
{
appOP = new Excel.ApplicationClass();
}
txtfileValue.EnableViewState = true;
}
/// tabla identificadores
public DataTable LeerSeriesIdentificadores(int adicionarInicio, DataTable tabla, string archivo, int hoja, int filaInicio, int colInicio, int filaFin, int colFin)
{
DataTable resultado = tabla;
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
_resultado = true;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Open(archivo, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(hoja);
if (adicionarInicio == 0)
{
for (int i = filaInicio; i <= filaFin; i++)
{
DataRow r = resultado.NewRow();
int idx = 3;
for (int j = colInicio; j <= colFin; j++)
{
if ((xlWorkSheet.Rows.Cells[i, j] as Excel.Range).Value2 == null || !Utiles.EsDecimalPositivoYCero((xlWorkSheet.Rows.Cells[i, j] as Excel.Range).Value2.ToString()))
{
_resultado = false;
break;
}
else
{
r[idx] = (xlWorkSheet.Rows.Cells[i, j] as Excel.Range).Value2;
idx++;
}
}
if (_resultado)
{
resultado.Rows.Add(r);
}
else
{
break;
}
}
}
ReleaseObject(xlWorkSheet);
xlWorkBook.Close(true, null, null);
xlApp.Quit();
ReleaseObject(xlWorkBook);
ReleaseObject(xlApp);
if (_resultado)
{
if (resultado.Rows.Count != 7)
{
_resultado = false;
}
}
return(resultado);
}
public void ExportExcel(string filename, DataTable dt, List<KeyValuePair<string, string>> columns)
{
Excel.Application app = null;
try
{
object missing = Type.Missing;
app = new Excel.ApplicationClass();
app.Visible = false;
app.DisplayAlerts = false;
app.Application.Workbooks.Add(true);
Excel.Worksheet ws = (Excel.Worksheet)app.ActiveSheet;
ws.Name = "data";
int line = 1;
if (columns != null)
{
for (int i = 0; i < columns.Count; i++)
{
app.Cells[line, i + 1] = columns[i].Value;
(app.Cells[line, i + 1] as Excel.Range).Font.Bold = true;
}
line++;
}
else
{
for (int i = 0; i < dt.Columns.Count; i++)
{
app.Cells[line, i + 1] = dt.Columns[i].ColumnName;
(app.Cells[line, i + 1] as Excel.Range).Font.Bold = true;
}
line++;
}
object[,] ss = new object[dt.Rows.Count, columns.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < columns.Count; j++)
{
ss[i, j] = "'" + dt.Rows[i][columns[j].Key];
}
}
Excel.Range r = ws.get_Range(ws.Cells[line, 1], ws.Cells[line + dt.Rows.Count - 1, columns.Count]);
r.Value2 = ss;
app.ActiveWorkbook.SaveAs(filename, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
app.ActiveWorkbook.Save();
app.ActiveWorkbook.Close(missing, missing, missing);
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
}
catch (Exception ex)
{
if (app != null)
app.Quit();
throw ex;
}
app = null;
GC.Collect();
}
/// <summary>
///
/// </summary>
private void InputExcel()
{
// if(m_dt.Rows.Count==0)
// else
// {
// m_exl=new Excel.ApplicationClass();
// m_exl.Workbooks.Add ( true );
// for(int j=0;j<m_dt.Columns.Count;j++)
// {
// for(int z=0;z<m_Title.Length;z++)
// {
// if(m_dt.Columns[j].ColumnName==m_Title[z].name)
// {
// m_exl.Cells[2,j+1]=m_Title[z].title;
// for(int i=0;i<m_dt.Rows.Count;i++)
// {
// m_exl.Cells[i+3,j+1]=m_dt.Rows[i][j];
// }
// break;
// }
//
// }
// continue;
// }
// m_exl.Visible=true;
// }
// else
// {
string str = Path.GetDirectoryName(Application.ExecutablePath) + "\\report\\GRDB.xls";
m_exl = new Excel.ApplicationClass();
// m_exl.Workbooks.Add(true);
Excel.Workbook eWork = m_exl.Workbooks.Add(str); //true)
eWork.SaveCopyAs("ll");
m_exl.Cells[2, 1] = m_time;
for (int i = 1; i < m_Title.Length; i++)
{
for (int j = 0; j < m_dt.Columns.Count; j++)
{
if (m_dt.Columns[j].ColumnName == m_Title[i].name)
{
// m_exl.Cells[2,i]=m_Title[i].title;
for (int z = 0; z < m_dt.Rows.Count; z++)
{
m_exl.Cells[z + 5, i] = m_dt.Rows[z][j];
}
}
}
}
m_exl.Visible = true;
// }
}
protected void BusiIncomeExportToExcel(string tabname, string tabdate, DataTable dtIncome)
{
try
{
Excel.Application xapp = new Excel.ApplicationClass();
Excel.Workbook xbook = xapp.Workbooks.Open(Application.StartupPath + @"\BusiIncomeModel.xls", 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 xSheet = (Excel.Worksheet)xbook.Sheets["业务量"]; //得到Sheet
xSheet.get_Range("A1", Missing.Value).Value2 = tabname;
xSheet.get_Range("A2", Missing.Value).Value2 = tabdate;
for (int i = 1; i < dtIncome.Rows.Count - 2; i++)
{
for (int j = 1; j < 8; j++)
{
xSheet.Cells[i + 3, j + 1] = dtIncome.Rows[i][j].ToString();
}
}
for (int i = 1; i < 8; i++)
{
xSheet.Cells[21, i + 1] = dtIncome.Rows[18][i].ToString();
}
SaveFileDialog SaveFileDialog1 = new SaveFileDialog();
SaveFileDialog1.Filter = "Excel文件（*.xls）|*.xls";
SaveFileDialog1.FileName = SysInitial.CP + "业务量报表" + DateTime.Now.ToShortDateString() + ".xls";
if (SaveFileDialog1.ShowDialog() == DialogResult.OK)
{
xbook.SaveCopyAs(SaveFileDialog1.FileName); //另存
xbook.Close(false, Application.StartupPath + @"\BusiIncomeModel.xls", Missing.Value); //关闭
xSheet = null;
xbook = null;
xapp.Quit();
xapp = null;
}
else
{
xbook.Close(false, Missing.Value, Missing.Value); //关闭
xSheet = null;
xbook = null;
xapp.Quit();
xapp = null;
}
}
catch (Exception err)
{
MessageBox.Show("导出时出错，请重试！", "系统提示", MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
clog.WriteLine(err);
}
finally
{
}
}
public DataTable Leer(string archivo, int hoja, int filaInicio, int colInicio, int filaFin, int colFin)
{
DataTable resultado = new DataTable();
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Open(archivo, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(hoja);
for (int j = colInicio; j <= colFin; j++)
{
Type t = null;
if ((xlWorkSheet.Rows.Cells[filaInicio, j] as Excel.Range).Value2 == null)
{
t = typeof(string);
}
else
{
t = (xlWorkSheet.Rows.Cells[filaInicio, j] as Excel.Range).Value2.GetType();
}
DataColumn c = new DataColumn("col" + j, t);
resultado.Columns.Add(c);
}
for (int i = filaInicio; i <= filaFin; i++)
{
DataRow r = resultado.NewRow();
int idx = 0;
for (int j = colInicio; j <= colFin; j++)
{
r[idx] = (xlWorkSheet.Rows.Cells[i, j] as Excel.Range).Value2 == null ? DBNull.Value : (xlWorkSheet.Rows.Cells[i, j] as Excel.Range).Value2;
idx++;
}
resultado.Rows.Add(r);
}
ReleaseObject(xlWorkSheet);
xlWorkBook.Close(true, null, null);
xlApp.Quit();
ReleaseObject(xlWorkBook);
ReleaseObject(xlApp);
return(resultado);
}
/// <summary>
///
/// </summary>
private void InputGrossExcel()
{
try
{
if (m_dt.Rows.Count == 0)
{
MessageBox.Show(
"没有可导入的数据！"
, "错误",
MessageBoxButtons.OK,
MessageBoxIcon.Error
);
}
else
{
string str = Path.GetDirectoryName(Application.ExecutablePath) + "\\report\\Gross.xls";
m_exl = new Excel.ApplicationClass();
Excel.Workbook eWork = m_exl.Workbooks.Add(str);
// eWork.SaveCopyAs("ll");
m_exl.Cells[1, 1] = "厂统计室数据分析报表";
m_exl.Cells[2, 1] = m_Begin.ToShortDateString() + " 至 " + m_End.ToShortDateString();
for (int i = 0; i < m_dt.Columns.Count; i++)
{
if (m_dt.Columns[i].ColumnName == "time")
{
for (int j = 0; j < m_dt.Rows.Count; j++)
{
m_exl.Cells[j + 4, i + 1] = System.Convert.ToDateTime(m_dt.Rows[j][i]).ToShortDateString();
}
}
else
{
for (int j = 0; j < m_dt.Rows.Count; j++)
{
m_exl.Cells[j + 4, i + 1] = m_dt.Rows[j][i].ToString();
}
}
}
m_exl.Visible = true;
m_exl = null;
eWork = null;
GC.Collect();
}
}
catch (Exception ex)
{
ExceptionHandler.Handle(ex);
return;
}
}
/// <summary>
/// 导出gridview为EXCEL
/// </summary>
/// <param name="GridView1"></param>
public void Export(GridView GridView1)
{
//Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
object oMissing = System.Reflection.Missing.Value;
oExcel.Workbooks.Add(oMissing);
Excel.Workbook oBook = oExcel.Workbooks[1];
Excel.Worksheet oSheet = (Excel.Worksheet)oBook.Sheets[1];
oSheet.Name = "最终名单";//this.Title;
Excel.Range rg;
//String test = GridView1.Rows[0].Cells[0].Text;
for (int j = 0; j < GridView1.HeaderRow.Cells.Count; j++)
{
rg = ((Excel.Range)oSheet.Cells[1, j + 1]);
rg.FormulaR1C1 = GridView1.HeaderRow.Cells[j].Text;
}
for (int i = 0; i < GridView1.Rows.Count; i++)
{
for (int j = 0; j < GridView1.Rows[0].Cells.Count; j++)
{
rg = ((Excel.Range)oSheet.Cells[i + 2, j + 1]);
rg.NumberFormatLocal = "@";//设置单元格格式为文本
rg.FormulaR1C1 = GridView1.Rows[i].Cells[j].Text;
}
}
rg = null;
string VirFileName = Guid.NewGuid().ToString() + ".xls";
oBook.SaveAs(Server.MapPath(VirFileName), Excel.XlFileFormat.xlExcel9795, oMissing, oMissing, oMissing, oMissing, Excel.XlSaveAsAccessMode.xlExclusive,
oMissing, oMissing, oMissing, oMissing, oMissing);
oExcel.Workbooks.Close();
oExcel.Quit();
oSheet = null;
oBook = null;
oExcel = null;
GC.Collect();
Response.Redirect(VirFileName);
}
/// <summary>
/// 导出数据
/// </summary>
/// <param name="selectstring">选择导出数据的SQL选择语句</param>
public static void DataOut(SaveFileDialog dialog, string selectstring)
{
if (dialog.ShowDialog() == DialogResult.OK)
{
Excel.ApplicationClass excel = new Excel.ApplicationClass();
Excel.Workbook workbook = excel.Workbooks.Add(System.Reflection.Missing.Value);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets.Add(System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
worksheet.Cells.NumberFormatLocal = "@";
///////////////////////////////////
SqlConnection connect = InitConnect.GetConnection();
SqlDataReader read = null;
try
{
connect.Open();
SqlCommand cmd = new SqlCommand(selectstring, connect);
read = cmd.ExecuteReader();
for (int i = 0; i < read.FieldCount; i++)
{
worksheet.Cells[1, i + 1] = read.GetName(i).Trim();
}
int row = 2;
int count = 0;
while (read.Read())
{
count++;
for (int i = 0; i < read.FieldCount; i++)
{
worksheet.Cells[row, i + 1] = read[i].ToString().Trim();
}
row++;
}
MessageBox.Show("成功导出" + count.ToString() + "条记录！", "恭喜", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
}
catch (Exception ee)
{
MessageBox.Show("错误：" + ee.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
}
finally
{
read.Close();
connect.Close();
object change = false, filename = dialog.FileName;
workbook.SaveCopyAs(filename);
workbook.Close(change, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
excel.Quit();
}
}
}
/// <summary>
/// builds gamma distribution to specified alpha and beta, and stores in local array
/// </summary>
public void InitGammaDistribution(double alpha, double beta)
{
//GammaDeviate gammaDev = new GammaDeviate(1);
Excel.Application app = new Excel.ApplicationClass();
//compute integral at 0.01 minute interval
this.alpha = alpha;
this.beta = beta;
this.gamma = Math.Exp(app.WorksheetFunction.GammaLn(alpha));
h = 0.01;
f = new double[STEPS];
for (int i = 0; i < STEPS; i++)
{
f[i] = (1 / gamma / Math.Pow(beta, alpha)) * Math.Pow(h, (alpha - 1)) * Math.Exp(-(h) / beta); //equation 1
h += 0.01;
}
fsum = 0;
fsumtotal = 0;
g = new double[MINS5];
//sum first 2.5 minutes and put into first slot of gamma distribution array
// r = 0;
// for(r = 0; r < 250; r++)
// {
// fsumtotal += f[r] + 0.01;
// }
// g[0] = fsumtotal;
int t = 0; //counter 0 thru 288
int s = 0;
for (int r = 0; r < STEPS; r = s)
{
for (s = r; s < 500 + r; s++)
{
fsum += f[s] * 0.01; //getting area under curve
fsumtotal += f[s] * 0.01; //will become very close to 1 when finished
}
g[t] = fsum;
t++;
fsum = 0;
}
//m = t - 1;//length of transfer function array
}
protected override void OnUnload(EventArgs e)
{
try
{
if (appOP != null)
{
appOP.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(appOP);
appOP = null;
}
}
catch (Exception eqq)
{
Response.Write(eqq.ToString());
}
base.OnUnload(e);
}
/// <summary>
/// 将DataTable中的列名及数据导出到Excel表中
/// </summary>
/// <param name="tmpDataTable">要导出的DataTable</param>
/// <param name="strFileName">Excel的保存路径及名称</param>
public void DataTabletoExcelkk(System.Data.DataTable tmpDataTable, string strFileName)
{
if (tmpDataTable == null)
{
return;
}
int rowNum = tmpDataTable.Rows.Count;
int columnNum = tmpDataTable.Columns.Count;
int rowIndex = 1;
int columnIndex = 0;
Excel.Application xlApp = new Excel.ApplicationClass();
xlApp.DefaultFilePath = "";
xlApp.DisplayAlerts = true;
xlApp.SheetsInNewWorkbook = 1;
Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Excel.Worksheet ws = (Excel.Worksheet)xlBook.Worksheets[1];
int colnum = tmpDataTable.Columns.Count;
Excel.Range r = ws.get_Range("A1", NumToExeclRowStr(colnum) + "1");
object[] objHeader = new object[colnum];
//将DataTable的列名导入Excel表第一行
foreach (DataColumn dc in tmpDataTable.Columns)
{
objHeader[columnIndex] = dc.ColumnName;
columnIndex++;
}
r.Value2 = objHeader;
//将DataTable中的数据导入Excel中
for (int i = 0; i < rowNum; i++)
{
rowIndex++;
columnIndex = 0;
for (int j = 0; j < columnNum; j++)
{
objHeader[columnIndex] = tmpDataTable.Rows[i][j].ToString();
columnIndex++;
}
r = ws.get_Range("A" + (i + 2), NumToExeclRowStr(colnum) + (i + 2));
r.Value2 = objHeader;
}
r.EntireColumn.AutoFit();
xlBook.SaveCopyAs(strFileName);
}
public static void ExportToExcel(DataTable dt, string type, string title, string thoiGian)
{
//Export to excel
try
{
Excel.ApplicationClass excel = new Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
System.Data.DataTable table = dt;
excel.Cells[1, 1] = "Print Date :" + DateTime.Now.ToLongDateString();
excel.Cells[3, 1] = title;
excel.Cells[4, 1] = thoiGian;
for (int k = 0; k < table.Columns.Count; k++)
{
excel.Cells[6, k + 1] = table.Columns[k].ColumnName.ToString();
}
int ColumnIndex = 0;
int rowIndex = 5;
foreach (DataRow row in table.Rows)
{
rowIndex++;
ColumnIndex = 0;
foreach (DataColumn col in table.Columns)
{
ColumnIndex++;
((Excel.Range)excel.Cells[rowIndex + 1, ColumnIndex]).NumberFormat = "@"; //Format dạng text
excel.Cells[rowIndex + 1, ColumnIndex] = row[col.ColumnName].ToString().Trim();
//((Excel.Range)excel.Cells[rowIndex + 1, ColumnIndex]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
}
}
excel.Visible = true;
Excel._Worksheet worksheet = (Excel._Worksheet)excel.ActiveSheet;
worksheet.Activate();
}
catch (Exception exml)
{
MessageBox.Show(exml.Message);
}
}
/// <summary>
/// 将DataTable中的列名及数据导出到Excel表中
/// </summary>
/// <param name="tmpDataTable">要导出的DataTable</param>
/// <param name="strFileName">Excel的保存路径及名称</param>
public static void DataTabletoExcel(System.Data.DataTable tmpDataTable, string strFileName)
{
if (tmpDataTable == null)
{
return;
}
int rowNum = tmpDataTable.Rows.Count;
int columnNum = tmpDataTable.Columns.Count;
int rowIndex = 1;
int columnIndex = 0;
Excel.Application xlApp = new Excel.ApplicationClass();
xlApp.DefaultFilePath = "";
xlApp.DisplayAlerts = true;
xlApp.SheetsInNewWorkbook = 1;
Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
//将DataTable的列名导入Excel表第一行
foreach (DataColumn dc in tmpDataTable.Columns)
{
columnIndex++;
xlApp.Cells[rowIndex, columnIndex] = dc.ColumnName;
}
//将DataTable中的数据导入Excel中
for (int i = 0; i < rowNum; i++)
{
rowIndex++;
columnIndex = 0;
for (int j = 0; j < columnNum; j++)
{
columnIndex++;
xlApp.Cells[rowIndex, columnIndex] = tmpDataTable.Rows[i][j].ToString();
}
}
xlBook.SaveCopyAs(strFileName);
}
public static void Export_To_Excel(DataGridView dtGridView, string filename, string sheetName)
{
int i, j;
object missing = Type.Missing;
Excel.ApplicationClass excellApp;
excellApp = new Excel.ApplicationClass();
excellApp.Application.Workbooks.Add(true);
try
{
// Add columns name to excel file
for (i = 0; i < dtGridView.Columns.Count; i++)
{
excellApp.Cells[1, i + 1] = dtGridView.Columns[i].Name;
}
for (i = 0; i < dtGridView.Rows.Count; i++)
{
for (j = 0; j < dtGridView.Columns.Count; j++)
{
excellApp.Cells[i + 2, j + 1] = dtGridView.Rows[i].Cells[j].Value;
}
}
//excellApp.Save(("Loinhuan.xls");
Excel._Worksheet worksheet = (Excel._Worksheet)excellApp.ActiveSheet;
worksheet.Activate();
worksheet.Name = sheetName;
worksheet.SaveAs(filename, missing, missing, missing, missing, missing, missing, missing, missing, missing);
//excellApp.Workbooks[1].SaveCopyAs(@"D:\Project\SVN\Source\Quanlyloinhuan\Loinhuan.xls");
excellApp.Quit();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
}
public static void ExportToExcel(DataTable dt, string type, string title, string thoiGian)
{
//Export to excel
try
{
Excel.ApplicationClass excel = new Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
System.Data.DataTable table = dt;
for (int k = 0; k < table.Columns.Count; k++)
{
excel.Cells[1, k + 1] = table.Columns[k].ColumnName.ToString();
}
int ColumnIndex = 0;
int rowIndex = 0;
foreach (DataRow row in table.Rows)
{
rowIndex++;
ColumnIndex = 0;
foreach (DataColumn col in table.Columns)
{
ColumnIndex++;
excel.Cells[rowIndex + 1, ColumnIndex] = row[col.ColumnName];
}
}
excel.Visible = true;
Excel._Worksheet worksheet = (Excel._Worksheet)excel.ActiveSheet;
worksheet.Activate();
}
catch (Exception exml)
{
MessageBox.Show(exml.Message);
}
}
public static void Export_To_Excel(DataGridView dtGridView, string filename, string sheetName)
{
int i, j;
object missing = Type.Missing;
Excel.ApplicationClass excellApp;
excellApp = new Excel.ApplicationClass();
excellApp.Application.Workbooks.Add(true);
try
{
// Add columns name to excel file
for (i = 0; i < dtGridView.Columns.Count; i++)
{
excellApp.Cells[1, i + 1] = dtGridView.Columns[i].Name;
}
for (i = 0; i < dtGridView.Rows.Count; i++)
{
for (j = 0; j < dtGridView.Columns.Count; j++)
{
excellApp.Cells[i + 2, j + 1] = dtGridView.Rows[i].Cells[j].Value;
}
}
//excellApp.Save(("Loinhuan.xls");
Excel._Worksheet worksheet = (Excel._Worksheet)excellApp.ActiveSheet;
worksheet.Activate();
worksheet.Name = sheetName;
worksheet.SaveAs(filename, missing, missing, missing, missing, missing, missing, missing, missing, missing);
//excellApp.Workbooks[1].SaveCopyAs(@"D:\Project\SVN\Source\Quanlyloinhuan\Loinhuan.xls");
excellApp.Quit();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
}
private void ExportExcel(DataTable dtExport)
{
Excel.ApplicationClass excel = new Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
excel.Cells[1, 1] = "Group";// [Dòng, cột]
excel.Cells[1, 2] = "Account ";
excel.Cells[1, 3] = "Ccy_cd";
excel.Cells[1, 4] = "Amount ";
excel.Cells[1, 5] = "Branch_code";
excel.Cells[1, 6] = "Account_Branch ";
excel.Cells[1, 7] = "Dr_Cr";
excel.Cells[1, 8] = "Ex_rate ";
excel.Cells[1, 9] = "Trn_dt";
excel.Cells[1, 10] = "Description ";
//excel.Cells[1, 1] = "Tại thời điểm " + dtTo.Text;
//((Excel.Range)excel.Cells[1, 1]).Font.Bold = true;
string sql = "", err = "";
if (dtExport.Rows.Count > 0)
{
for (int i = 0; i < dtExport.Rows.Count; i++)
{
string Group = "", Account = "", Ccy_cd = "", Amount = "", Branch_code = "",
Account_Branch = "", Dr_Cr = "", Ex_rate = "", Trn_dt = "", Description = "";
//a.group_id,a.account_number,a.ccy_cd,a.amount_accounting,a.branch_code "+
//" ,a.account_branch,a.dr_cr,a.ex_rate,a.trn_dt,a.description,a.user_create,a.accounting_type "+
Group = dtExport.Rows[i]["group"].ToString();
Account = dtExport.Rows[i]["Account"].ToString();
Ccy_cd = dtExport.Rows[i]["Ccy_cd"].ToString();
Amount = dtExport.Rows[i]["Amount"].ToString();
Branch_code = dtExport.Rows[i]["Branch_code"].ToString().Trim();
Account_Branch = dtExport.Rows[i]["Account_Branch"].ToString().Trim();
Dr_Cr = dtExport.Rows[i]["Dr_Cr"].ToString();
Ex_rate = dtExport.Rows[i]["Ex_rate"].ToString();
Trn_dt = dtExport.Rows[i]["Trn_dt"].ToString();
Description = dtExport.Rows[i]["Description"].ToString();
//Export excel
excel.Cells[i + 2, 1] = Group;// [Dòng, cột]
excel.Cells[i + 2, 2] = Account;
excel.Cells[i + 2, 3] = Ccy_cd;
excel.Cells[i + 2, 4] = Amount;
excel.Cells[i + 2, 5] = Branch_code;
excel.Cells[i + 2, 6] = Account_Branch;
excel.Cells[i + 2, 7] = Dr_Cr;
excel.Cells[i + 2, 8] = Ex_rate;
excel.Cells[i + 2, 9] = Trn_dt;
excel.Cells[i + 2, 10] = Description;
}
}
//----------------
excel.Visible = true;
Excel._Worksheet worksheet = (Excel._Worksheet)excel.ActiveSheet;
worksheet.Activate();
// worksheet.SaveAs("D:\\");
}
private void ExportExcel()
{
try
{
DataTable dt = new DataTable();
DataTable dtTemp = new DataTable();
Excel.ApplicationClass excel = new Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
excel.Cells[1, 1] = "SAO KE TAI KHOAN TIEN MAT TAI MAY ATM";
((Excel.Range)excel.Cells[1, 1]).Font.Bold = true;
excel.Cells[2, 1] = "Ngày: " + dtCurrentF.Text;
((Excel.Range)excel.Cells[2, 1]).Font.Bold = true;
((Excel.Range)excel.Cells[2, 1]).Font.Italic = true;
excel.Cells[4, 1] = "ĐỐI CHIẾU TIỀN MẶT TẠI ATM";
((Excel.Range)excel.Cells[4, 1]).Font.Bold = true;
excel.Cells[5, 1] = "ATM ID";
excel.Cells[6, 1] = "SỐ TK";
excel.Cells[7, 1] = "SỐ MÁY";
excel.Cells[8, 1] = "PHẦN A: ĐỐI CHIẾU GIAO DỊCH";
((Excel.Range)excel.Cells[8, 1]).Font.Bold = true;
excel.Cells[9, 1] = "1. Tổng số tiền chi ra trong ngày trên tài khoản Flexcube (Sổ phụ)";
excel.Cells[10, 1] = "2. Tổng số tiền trên Report đối chiếu";
excel.Cells[11, 1] = " 2.1 Onus Electra";
excel.Cells[12, 1] = " 2.2 Banknet";
excel.Cells[13, 1] = " 2.3 Supect Reversal";
excel.Cells[14, 1] = "3. Chêcn lệch";
excel.Cells[15, 1] = "PHẦN B: ĐỐI CHIẾU SỐ DƯ TK TM ATM TRÊN FLEXCUBE";
((Excel.Range)excel.Cells[15, 1]).Font.Bold = true;
excel.Cells[16, 1] = "1. Số dư cuối ngày - TM TM ATM";
excel.Cells[17, 1] = "2. Số dư đầu kỳ tiếp quỹ";
excel.Cells[18, 1] = "3. Số tiền đã Matched sau tiếp quỹ";
excel.Cells[19, 1] = " 3.1 Onus Electra";
excel.Cells[20, 1] = " 3.2 Banknet";
excel.Cells[21, 1] = "4. Số dư cuối kỳ";
excel.Cells[22, 1] = "5. Số tiền kiểm quỹ thực tế (kết quỹ)";
excel.Cells[23, 1] = "6. Retrack";
excel.Cells[24, 1] = "DƯ";
((Excel.Range)excel.Cells[24, 1]).Font.Bold = true;
excel.Cells[25, 1] = "CHÊNH LỆCH";
((Excel.Range)excel.Cells[25, 1]).Font.Bold = true;
excel.Cells[26, 1] = "Tiếp quỹ";
((Excel.Range)excel.Cells[26, 1]).Font.Italic = true;
excel.Cells[27, 1] = "Kết quỹ";
((Excel.Range)excel.Cells[27, 1]).Font.Italic = true;
excel.Cells[30, 1] = " Kế toán";
((Excel.Range)excel.Cells[30, 1]).Font.Bold = true;
excel.Cells[30, 2] = "Kiểm soát";
((Excel.Range)excel.Cells[30, 2]).Font.Bold = true;
//GET Info
dtATM.Clear();
dtATM = getInfo();
if (tile != "")
return;
int i=0, k=0;
string Temp = "";
for (i = 0; i < dtATM.Rows.Count; i++)
{
excel.Cells[5, i+2] = dtATM.Rows[i][1].ToString();
excel.Cells[6, i + 2] = "101401001/"+dtATM.Rows[i]["branch_code"].ToString().Trim();
excel.Cells[7, i + 2] = dtATM.Rows[i][2].ToString().Trim() + "/" + dtATM.Rows[i][0].ToString().Trim();
}
//Set Info Tiep quỹ, Kết quỹ
for (i = 0; i < dtATM.Rows.Count; i++)
{
excel.Cells[26, i + 2] = "'" + dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim();
excel.Cells[27, i + 2] = "-";
if (dataGridView2[0, i].Value.ToString() == "True")
excel.Cells[27, i + 2] = "'"+dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim();
}
//A GET 1
for (i = 0; i < dtATM.Rows.Count; i++)
{
dt.Clear();
dtFrom.Value = new DateTime(Convert.ToInt32(dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim().Substring(6, 4)), Convert.ToInt32(dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim().Substring(3, 2)), Convert.ToInt32(dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim().Substring(0, 2)));
tbFh.Text = dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim().Substring(11, 2);
tbFm.Text = dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim().Substring(14, 2);
tbFs.Text = dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim().Substring(17, 2);
dtTo.Value = new DateTime(Convert.ToInt32(dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim().Substring(6, 4)), Convert.ToInt32(dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim().Substring(3, 2)), Convert.ToInt32(dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim().Substring(0, 2)));
tbTh.Text = dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim().Substring(11, 2);
tbTm.Text = dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim().Substring(14, 2);
tbTs.Text = dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim().Substring(17, 2);
if (dataGridView2[0, i].Value.ToString() == "True")
checkBox1.Checked = true;
else
checkBox1.Checked = false;
//Get XLKN
dtTemp.Clear();
dtTemp = getXLKN(dtATM.Rows[i]["aut_terminal"].ToString().Trim(), dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim());
//--------
dt = get1(dtATM.Rows[i]["flex_id"].ToString());
if (tile != "")
return;
excel.Cells[9, i + 2] = "0";
if(dt.Rows.Count > 0)
excel.Cells[9, i + 2] = Convert.ToDouble(dt.Rows[0]["Credit"].ToString().Trim()) + Convert.ToDouble(dt.Rows[0]["Debit"].ToString().Trim());
//Set agian for XLKN
if (dtTemp.Rows.Count > 0)
{
for (int p = 0; p < dtTemp.Rows.Count; p++)
{
//if (dtTemp.Rows[p]["ATM"].ToString() == "0" && dtTemp.Rows[p]["ELECTRA"].ToString() == "1" && dtTemp.Rows[p]["CORE"].ToString() == "1")
//{
excel.Cells[9, i + 2] = Convert.ToDouble(dt.Rows[0]["Credit"].ToString().Trim()) + Convert.ToDouble(dt.Rows[0]["Debit"].ToString().Trim()) - Convert.ToDouble(dtTemp.Rows[p]["TXN_AMOUNT"].ToString());
//}
}
}
}
//A GET 2.1
for (i = 0; i < dtATM.Rows.Count; i++)
{
dt.Clear();
dtFrom.Value = new DateTime(Convert.ToInt32(dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim().Substring(6, 4)), Convert.ToInt32(dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim().Substring(3, 2)), Convert.ToInt32(dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim().Substring(0, 2)));
tbFh.Text = dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim().Substring(11, 2);
tbFm.Text = dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim().Substring(14, 2);
tbFs.Text = dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim().Substring(17, 2);
dtTo.Value = new DateTime(Convert.ToInt32(dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim().Substring(6, 4)), Convert.ToInt32(dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim().Substring(3, 2)), Convert.ToInt32(dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim().Substring(0, 2)));
tbTh.Text = dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim().Substring(11, 2);
tbTm.Text = dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim().Substring(14, 2);
tbTs.Text = dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim().Substring(17, 2);
if (dataGridView2[0, i].Value.ToString() == "True")
checkBox1.Checked = true;
else
checkBox1.Checked = false;
dt = get2_1(dtATM.Rows[i]["flex_id"].ToString());
if (tile != "")
return;
excel.Cells[11, i + 2] = "0";
if (dt.Rows.Count > 0)
excel.Cells[11, i + 2] = dt.Rows[0]["transaction_amount"].ToString().Trim();
}
//A GET 2.2
for (i = 0; i < dtATM.Rows.Count; i++)
{
dt.Clear();
dtFrom.Value = new DateTime(Convert.ToInt32(dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim().Substring(6, 4)), Convert.ToInt32(dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim().Substring(3, 2)), Convert.ToInt32(dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim().Substring(0, 2)));
tbFh.Text = dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim().Substring(11, 2);
tbFm.Text = dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim().Substring(14, 2);
tbFs.Text = dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim().Substring(17, 2);
dtTo.Value = new DateTime(Convert.ToInt32(dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim().Substring(6, 4)), Convert.ToInt32(dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim().Substring(3, 2)), Convert.ToInt32(dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim().Substring(0, 2)));
tbTh.Text = dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim().Substring(11, 2);
tbTm.Text = dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim().Substring(14, 2);
tbTs.Text = dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim().Substring(17, 2);
if (dataGridView2[0, i].Value.ToString() == "True")
checkBox1.Checked = true;
else
checkBox1.Checked = false;
dt = get2_2(dtATM.Rows[i]["flex_id"].ToString());
if (tile != "")
return;
excel.Cells[12, i + 2] = "0";
if (dt.Rows.Count > 0)
excel.Cells[12, i + 2] = dt.Rows[0]["transaction_amount"].ToString().Trim();
}
//A GET 2.3
for (i = 0; i < dtATM.Rows.Count; i++)
{
dt.Clear();
dtFrom.Value = new DateTime(Convert.ToInt32(dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim().Substring(6, 4)), Convert.ToInt32(dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim().Substring(3, 2)), Convert.ToInt32(dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim().Substring(0, 2)));
tbFh.Text = dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim().Substring(11, 2);
tbFm.Text = dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim().Substring(14, 2);
tbFs.Text = dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim().Substring(17, 2);
dtTo.Value = new DateTime(Convert.ToInt32(dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim().Substring(6, 4)), Convert.ToInt32(dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim().Substring(3, 2)), Convert.ToInt32(dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim().Substring(0, 2)));
tbTh.Text = dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim().Substring(11, 2);
tbTm.Text = dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim().Substring(14, 2);
tbTs.Text = dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim().Substring(17, 2);
if (dataGridView2[0, i].Value.ToString() == "True")
checkBox1.Checked = true;
else
checkBox1.Checked = false;
dt = get2_3(dtATM.Rows[i]["flex_id"].ToString());
if (tile != "")
return;
excel.Cells[13, i + 2] = "0";
if (dt.Rows.Count > 0)
excel.Cells[13, i + 2] = dt.Rows[0]["transaction_amount"].ToString().Trim();
}
//A GET 2 and 3
for (i = 0; i < dtATM.Rows.Count; i++)
{
excel.Cells[10, i + 2] = Convert.ToDouble(((Excel.Range)excel.Cells[11, i + 2]).Value.ToString()) + Convert.ToDouble(((Excel.Range)excel.Cells[12, i + 2]).Value.ToString()) + Convert.ToDouble(((Excel.Range)excel.Cells[13, i + 2]).Value.ToString());
excel.Cells[14, i + 2] = Convert.ToDouble(((Excel.Range)excel.Cells[9, i + 2]).Value.ToString()) - Convert.ToDouble(((Excel.Range)excel.Cells[10, i + 2]).Value.ToString());
}
//B GET 1
dt.Clear();
dt = getB1();
if (tile != "")
return;
excel.Cells[16, 2] = dt.Rows[k]["SD"].ToString().Trim();
//B GET 2
for (i = 0; i < dtATM.Rows.Count; i++)
{
//excel.Cells[17, i + 2] = tbTiepQuy.Text;
excel.Cells[17, i + 2] = dataGridView2["ST_TIEPQUY", i].Value.ToString().Trim();
}
//B GET 3.1
for (i = 0; i < dtATM.Rows.Count; i++)
{
dt.Clear();
dtFrom.Value = new DateTime(Convert.ToInt32(dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim().Substring(6, 4)), Convert.ToInt32(dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim().Substring(3, 2)), Convert.ToInt32(dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim().Substring(0, 2)));
tbFh.Text = dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim().Substring(11, 2);
tbFm.Text = dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim().Substring(14, 2);
tbFs.Text = dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim().Substring(17, 2);
dtTo.Value = new DateTime(Convert.ToInt32(dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim().Substring(6, 4)), Convert.ToInt32(dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim().Substring(3, 2)), Convert.ToInt32(dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim().Substring(0, 2)));
tbTh.Text = dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim().Substring(11, 2);
tbTm.Text = dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim().Substring(14, 2);
tbTs.Text = dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim().Substring(17, 2);
if (dataGridView2[0, i].Value.ToString() == "True")
checkBox1.Checked = true;
else
checkBox1.Checked = false;
dt = getB3_1(dtATM.Rows[i]["flex_id"].ToString());
if (tile != "")
return;
excel.Cells[19, i + 2] = "0";
if (dt.Rows.Count > 0)
excel.Cells[19, i + 2] = dt.Rows[0]["transaction_amount"].ToString().Trim();
}
//B GET 3.2
for (i = 0; i < dtATM.Rows.Count; i++)
{
dt.Clear();
dtFrom.Value = new DateTime(Convert.ToInt32(dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim().Substring(6, 4)), Convert.ToInt32(dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim().Substring(3, 2)), Convert.ToInt32(dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim().Substring(0, 2)));
tbFh.Text = dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim().Substring(11, 2);
tbFm.Text = dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim().Substring(14, 2);
tbFs.Text = dataGridView2["Tiep_Quy_TIME", i].Value.ToString().Trim().Substring(17, 2);
dtTo.Value = new DateTime(Convert.ToInt32(dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim().Substring(6, 4)), Convert.ToInt32(dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim().Substring(3, 2)), Convert.ToInt32(dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim().Substring(0, 2)));
tbTh.Text = dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim().Substring(11, 2);
tbTm.Text = dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim().Substring(14, 2);
tbTs.Text = dataGridView2["Ket_Quy_TIME", i].Value.ToString().Trim().Substring(17, 2);
if (dataGridView2[0, i].Value.ToString() == "True")
checkBox1.Checked = true;
else
checkBox1.Checked = false;
dt = getB3_2(dtATM.Rows[i]["flex_id"].ToString());
if (tile != "")
return;
excel.Cells[20, i + 2] = "0";
if (dt.Rows.Count > 0)
excel.Cells[20, i + 2] = dt.Rows[0]["transaction_amount"].ToString().Trim();
}
//GET B 3 va 4
for (i = 0; i < dtATM.Rows.Count; i++)
{
excel.Cells[18, i + 2] = Convert.ToDouble(((Excel.Range)excel.Cells[19, i + 2]).Value.ToString()) + Convert.ToDouble(((Excel.Range)excel.Cells[20, i + 2]).Value.ToString());
excel.Cells[21, i + 2] = Convert.ToDouble(((Excel.Range)excel.Cells[17, i + 2]).Value.ToString()) - Convert.ToDouble(((Excel.Range)excel.Cells[18, i + 2]).Value.ToString());
}
//----------------
excel.Visible = true;
Excel._Worksheet worksheet = (Excel._Worksheet)excel.ActiveSheet;
for (i = 0; i < dtATM.Rows.Count; i++)
((Excel.Range)worksheet.Cells[5, i+2]).EntireColumn.NumberFormat = "##,##0.00";
worksheet.Activate();
}
catch(Exception exml)
{
MessageBox.Show(exml.Message);
}
}
private void XuatPhieuNhapRaExcel(DataTable dtChiTiet, string FileExcel)
{
CreateWaitDialog("Đang xuất dữ liệu ra file Excel", "Xin vui lòng chờ!");
this.Cursor = System.Windows.Forms.Cursors.WaitCursor;
int DongBatDau = _DongBatDau, CotBatDau = _CotBatDau, SoCot = ColStart;
Excel.Range cel;
Excel.ApplicationClass excel = new Excel.ApplicationClass();
try
{
excel.Application.Workbooks.Open(FileExcel, true, false, true, "", "", true, true, true, true, true, true, true, true, false);
//// Ten mon
//cel = (Excel.Range)excel.Cells[5, 2];
//excel.Cells[5, 2] = cel.Text + " " + drMonHoc["TenMonHoc"].ToString();
//cel = (Excel.Range)excel.Cells[5, 5];
excel.Cells[3, 1] = GetTenTrinhDo();
if (rdNamHoc.EditValue.ToString() == "0")
{
excel.Cells[4, 1] = "XẾP LOẠI HỌC LỰC HỌC KỲ " + Program.HocKy + " NĂM HỌC " + Program.NamHoc;
}
else if (rdNamHoc.EditValue.ToString() == "1")
{
excel.Cells[4, 1] = "XẾP LOẠI HỌC LỰC NĂM HỌC " + Program.NamHoc;
}
else
{
excel.Cells[4, 1] = "XẾP LOẠI HỌC LỰC TOÀN KHÓA";
}
// Insert columns
if (grbXepLoai.Children.Count > 0)
{
int CotDau = CotBatDau;
for (int i = 0; i < grbXepLoai.Children.Count; i++)
{
foreach (BandedGridColumn bgc in grbXepLoai.Children[i].Columns)
{
cel = (Excel.Range)(excel.Cells[8, CotBatDau]);
cel.EntireColumn.Insert(Excel.XlDirection.xlToRight, null);
SoCot++;
excel.Cells[8, CotBatDau] = bgc.Caption;
CotBatDau++;
}
cel = excel.get_Range(excel.Cells[8, CotDau], excel.Cells[8, CotBatDau - 1]);
cel.Borders.Value = 1;
excel.Cells[7, CotDau] = grbXepLoai.Children[i].Caption;
cel = excel.get_Range(excel.Cells[7, CotDau], excel.Cells[7, CotBatDau - 1]);
cel.Merge(null);
cel.Borders.Value = 1;
CotDau = CotBatDau;
}
excel.Cells[6, 4] = "XẾP LOẠI";
cel = excel.get_Range(excel.Cells[6, 4], excel.Cells[6, CotBatDau - 1]);
cel.Merge(null);
cel.Borders.Value = 1;
}
for (int i = 4; i < CotBatDau; i++)
{
cel = (Excel.Range)(excel.Cells[8, i]);
cel.ColumnWidth = 5.0;
}
for (int i = 0; i < dtChiTiet.Rows.Count; i++)
{
// Insert rows
cel = (Excel.Range)(excel.Cells[DongBatDau + i + 1, 1]);
cel.EntireRow.Insert(Excel.XlDirection.xlUp, null);
for (int l = 1; l <= SoCot; l++)
{
cel = (Excel.Range)(excel.Cells[DongBatDau + i, l]);
cel.Borders.Value = 1;
}
excel.Cells[i + DongBatDau, 1] = i + 1;
excel.Cells[i + DongBatDau, 2] = "" + dtChiTiet.Rows[i]["TenLop"];
excel.Cells[i + DongBatDau, 3] = "" + dtChiTiet.Rows[i]["SoSinhVien"];
for (int k = ColStart; k < dtChiTiet.Columns.Count; k++)
{
excel.Cells[DongBatDau + i, _CotBatDau + k - ColStart] = dtChiTiet.Rows[i][k];
}
}
excel.Visible = true;
}
catch (Exception e)
{
this.Cursor = System.Windows.Forms.Cursors.Default;
ThongBaoLoi("Xuất dữ liệu không thành công! Hãy đóng file Excel Phiếu báo điểm trước khi xuất dữ liệu. Thông báo lỗi: " + e.Message);
return;
}
finally
{
excel.Application.Workbooks[1].Save();
//excel.Application.Workbooks.Close();
//excel.Application.Quit();
//excel.Quit();
CloseWaitDialog();
this.Cursor = System.Windows.Forms.Cursors.Default;
}
}
private void XuatDuLieuRaExcel(string FileExcel)
{
CreateWaitDialog("Đang xuất dữ liệu ra file Excel", "Xin vui lòng chờ!");
this.Cursor = System.Windows.Forms.Cursors.WaitCursor;
int i = 0;
int DongBatDau = 8;
Excel.Range cel;
Excel.ApplicationClass excel = new Excel.ApplicationClass();
try
{
excel.Application.Workbooks.Open(FileExcel, true, false, true, "", "", true, true, true, true, true, true, true, true, false);
// Phần này là thêm Header.
excel.Cells[5, 6] = Program.NamHoc;
cel = (Excel.Range)excel.Cells[5, 6];
// Phần này là thêm Detail.
double SumTongThu = 0;
for (i = 0; i < grvSinhVien.DataRowCount; i++)
{
excel.Cells[i + DongBatDau, 1] = i + 1;
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 1]);
cel.Borders.Value = 1;
excel.Cells[i + DongBatDau, 2] = grvSinhVien.GetDataRow(i)["SoBaoDanhTS"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 2]);
cel.Borders.Value = 1;
excel.Cells[i + DongBatDau, 3] = grvSinhVien.GetDataRow(i)["HoVaTenTS"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 3]);
cel.Borders.Value = 1;
excel.Cells[i + DongBatDau, 4] = grvSinhVien.GetDataRow(i)["NgaySinh"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 4]);
cel.Borders.Value = 1;
excel.Cells[i + DongBatDau, 5] = grvSinhVien.GetDataRow(i)["KhoiThi"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 5]);
cel.Borders.Value = 1;
excel.Cells[i + DongBatDau, 6] = grvSinhVien.GetDataRow(i)["NganhThi"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 6]);
cel.Borders.Value = 1;
excel.Cells[i + DongBatDau, 7] = grvSinhVien.GetDataRow(i)["NoiSinhTS"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 7]);
cel.Borders.Value = 1;
excel.Cells[i + DongBatDau, 8] = grvSinhVien.GetDataRow(i)["ThuongTruTS"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 8]);
cel.Borders.Value = 1;
excel.Cells[i + DongBatDau, 9] = grvSinhVien.GetDataRow(i)["SoTien"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 9]);
cel.Borders.Value = 1;
SumTongThu += double.Parse("0" + grvSinhVien.GetDataRow(i)["SoTien"] + "");
}
for (int j = 0; j < 9; j++)
{
cel = (Excel.Range)(excel.Cells[grvSinhVien.DataRowCount + DongBatDau, j + 1]);
cel.Font.Bold = true;
cel.Borders.Value = 1;
}
excel.Cells[grvSinhVien.DataRowCount + DongBatDau, 2] = "Tổng cộng:";
excel.Cells[grvSinhVien.DataRowCount + DongBatDau, 9] = SumTongThu;
}
catch (Exception e)
{
CloseWaitDialog();
this.Cursor = System.Windows.Forms.Cursors.Default;
ThongBaoLoi("Xuất dữ liệu không thành công! Hãy đóng file Excel Danh sách thu tiền nhập học trước khi xuất dữ liệu. Thông báo lỗi: " + e.Message);
return;
}
finally
{
excel.Application.Workbooks[1].Save();
excel.Application.Workbooks.Close();
excel.Application.Quit();
excel.Quit();
Process.Start(FileExcel);
CloseWaitDialog();
this.Cursor = System.Windows.Forms.Cursors.Default;
}
}
//======================22/08/2011======================
// PhanThanh Dat
// Noi dung: Check accounting
//======================================================
private void button2_Click(object sender, EventArgs e)
{
try
{
string sql = "", err1 = ""; string gio = "";//,phut="",giay="";
DataTable dt;
//Kiem tra gio xuat file
sql = "select substr(to_char(sysdate,'hh24miss'),1,2) as gio from dual";
//,substr(to_char(sysdate,'hh24miss'),3,2) as phut,substr(to_char(sysdate,'hh24miss'),5,2) as giay
dt = Businessbp.executedb.getTable(sql, ref err1);
if (err1 != "")
{
MessageBox.Show(err1);
return;
}
else
{
gio = dt.Rows[0]["gio"].ToString();
//phut = dt.Rows[0]["phut"].ToString();
//giay = dt.Rows[0]["giay"].ToString();
if (Int32.Parse(gio) < 17)
{
MessageBox.Show("Chưa đến thời điểm xuất file!!!");
return;
}
}
int B3 = 0, Y31 = 0, Y32 = 0, E3 = 0, D1 = 0, E5 = 0, D3 = 0, H3 = 0, Y41 = 0, Y42 = 0, Y43 = 0, N41 = 0, N42 = 0, N43 = 0, N44 = 0, H5 = 0, H4 = 0, Result_out = 0,B4 = 0,H6 = 0;
string Emsg_out = "";
int B2 = 0, Y2 = 0, H2 = 0;
if (tbB2.Text.Trim() == "")
B2 = 0;
else
B2 = Int32.Parse(tbB2.Text.Trim());
if (tbY2.Text.Trim() == "")
Y2 = 0;
else
Y2 = Int32.Parse(tbY2.Text.Trim());
if (tbH2.Text.Trim() == "")
H2 = 0;
else
H2 = Int32.Parse(tbH2.Text.Trim());
if (tbH4.Text.Trim() == "")
H4 = 0;
else
H4 = Int32.Parse(tbH2.Text.Trim());
string fromtime = dtFrom.Text;
string totime = dtTo.Text;
string name = " " + Businessbp.executedb.owner + "pro_check_accountonus";
string[] InParaName = new string[2] { "Tn1", "Tn" };
string[] OutParaName = new string[20] { "B3", "Y31", "Y32", "E3", "D1", "E5", "D3", "H3", "Y41", "Y42", "Y43", "N41", "N42", "N43", "N44", "H5", "Result_out", "Emsg_out", "B4" , "H6" };
OleDbType[] InParaType = new OleDbType[2] { OleDbType.VarChar, OleDbType.VarChar };
OleDbType[] OutParaType = new OleDbType[20] { OleDbType.Numeric, OleDbType.Numeric, OleDbType.Numeric, OleDbType.Numeric, OleDbType.Numeric, OleDbType.Numeric,
OleDbType.Numeric, OleDbType.Numeric, OleDbType.Numeric, OleDbType.Numeric, OleDbType.Numeric, OleDbType.Numeric,
OleDbType.Numeric, OleDbType.Numeric, OleDbType.Numeric, OleDbType.Numeric, OleDbType.Numeric, OleDbType.VarChar,
OleDbType.Numeric, OleDbType.Numeric};
string[] InParaValues = new string[2] { fromtime, totime };
string[] OutParaValues = new string[20];
for (int i = 0; i < 20; i++)
OutParaValues[i] = "";
int[] OutParaSize = new int[20] { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 300, 0, 0 };
string err = "";
Businessbp.executedb.ExecuteProcELECTRA(name, InParaName, InParaType, InParaValues, OutParaName, OutParaType, OutParaSize, ref OutParaValues, ref err);
if (err != "")
MessageBox.Show(err);
else
{
B3 = Int32.Parse(OutParaValues[0].ToString());
Y31 = Int32.Parse(OutParaValues[1].ToString());
Y32 = Int32.Parse(OutParaValues[2].ToString());
E3 = Int32.Parse(OutParaValues[3].ToString());
D1 = Int32.Parse(OutParaValues[4].ToString());
E5 = Int32.Parse(OutParaValues[5].ToString());
D3 = Int32.Parse(OutParaValues[6].ToString());
H3 = Int32.Parse(OutParaValues[7].ToString());
Y41 = Int32.Parse(OutParaValues[8].ToString());
Y42 = Int32.Parse(OutParaValues[9].ToString());
Y43 = Int32.Parse(OutParaValues[10].ToString());
N41 = Int32.Parse(OutParaValues[11].ToString());
N42 = Int32.Parse(OutParaValues[12].ToString());
N43 = Int32.Parse(OutParaValues[13].ToString());
N44 = Int32.Parse(OutParaValues[14].ToString());
H5 = Int32.Parse(OutParaValues[15].ToString());
Result_out = Int32.Parse(OutParaValues[16].ToString());
Emsg_out = OutParaValues[17].ToString();
B4 = Int32.Parse(OutParaValues[18].ToString());
H6 = Int32.Parse(OutParaValues[19].ToString());
if (Result_out == 0)
{
MessageBox.Show(Emsg_out);
return;
}
else
{
Excel.ApplicationClass excel = new Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
excel.Cells[1, 1] = "Tại thời điểm " + dtTo.Text;
((Excel.Range)excel.Cells[1, 1]).Font.Bold = true;
excel.Cells[5, 1] = "459918001 - CASA 88";
//excel.Range(eWSheet.Cells(1, 1), eWSheet.Cells(4, 1)).Merge()
// Excel.Range(excel.Cells[6, 1]),excel.Cells[7, 1]).Merge();
excel.Cells[9, 1] = "359010002 - CASA 30";
excel.Cells[10, 1] = "459999002 - CASA 86";
// ((Excel.Range)excel.Cells[9, 1]).Font.Background = Color.Yellow;
excel.Cells[11, 1] = "459999002 - CASA 85";
excel.Cells[12, 1] = "359099004 - CASA 103";
excel.Cells[13, 1] = "454004001 - Treo lệch Acquier";
excel.Cells[16, 1] = "359099002 - Treo lệch Issuer";
excel.Cells[20, 1] = "459918001 - CASA 107";
excel.Cells[21, 1] = "459918001 - CASA 105";
// ((Excel.Range)excel.Cells[20, 1]).Font.Background = Color.Yellow;
excel.Cells[13, 2] = "ST";
// ((Excel.Range)excel.Cells[12, 1]).Font.Color = Color.Red;
excel.Cells[14, 2] = "Phí CASA 85 (lệch)";
// ((Excel.Range)excel.Cells[13, 1]).Font.Color = Color.Red;
excel.Cells[15, 2] = "BNK tự đảo (ISS)";
excel.Cells[16, 2] = "ST";
// ((Excel.Range)excel.Cells[15, 1]).Font.Color = Color.Red;
excel.Cells[17, 2] = "Phí CASA 103 (lệch)";
// ((Excel.Range)excel.Cells[16, 1]).Font.Color = Color.Red;
excel.Cells[18, 2] = "Phí POS lệch Acquier";
excel.Cells[19, 2] = "BNK tự đảo (ACQ)";
excel.Cells[20, 2] = "Payment";
excel.Cells[21, 2] = "Treo TT ĐL ngoài";
//So du dau ky
excel.Cells[3, 3] = "Số dư Tn-1 (Core)";
((Excel.Range)excel.Cells[3, 3]).Font.Bold = true;
excel.Cells[4, 3] = "Nợ";
excel.Cells[4, 4] = "Có";
excel.Cells[5, 4] = B3;
excel.Cells[9, 3] = 0;
excel.Cells[10, 4] = B4;
excel.Cells[20, 4] = B2;
//Phat sinh
excel.Cells[3, 5] = "Phát sinh Tn (Electra)";
((Excel.Range)excel.Cells[3, 5]).Font.Bold = true;
excel.Cells[4, 5] = "Nợ";
excel.Cells[4, 6] = "Có";
excel.Cells[5, 6] = Y31 + Y32;
excel.Cells[6, 6] = H3;
excel.Cells[7, 6] = H4;
excel.Cells[8, 6] = H5;
excel.Cells[10, 6] = H6;
excel.Cells[13, 6] = Y41;
excel.Cells[14, 6] = Y42;
excel.Cells[15, 6] = Y43;
excel.Cells[16, 5] = N41;
excel.Cells[17, 5] = N42;
excel.Cells[18, 5] = N43;
excel.Cells[19, 5] = N44;
excel.Cells[20, 6] = Y2;
//Cuoi ky
excel.Cells[3, 7] = "Số dư Tn (Core)";
((Excel.Range)excel.Cells[3, 7]).Font.Bold = true;
excel.Cells[4, 7] = "Nợ";
excel.Cells[4, 8] = "Có";
excel.Cells[5, 8] = E3;
excel.Cells[9, 7] = D1;
excel.Cells[11, 8] = E5;
excel.Cells[12, 7] = D3;
excel.Cells[13, 8] = Y41 + Y42 + Y43;//E4
excel.Cells[16, 7] = N41 + N42 + N43 + N44;//D2
excel.Cells[20, 8] = Y2 + H2;
//Ket qua
excel.Cells[3, 9] = "Kết quả";
((Excel.Range)excel.Cells[3, 9]).Font.Bold = true;
//----------------
excel.Visible = true;
Excel._Worksheet worksheet = (Excel._Worksheet)excel.ActiveSheet;
worksheet.Activate();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void XuatBangDiemRaExcel(DataTable dtChiTiet, string FileExcel, int SoLanThi)
{
CreateWaitDialog("Đang xuất dữ liệu ra file Excel", "Xin vui lòng chờ!");
this.Cursor = System.Windows.Forms.Cursors.WaitCursor;
int DongBatDau = 11, CotBatDau = _ColStart, SoCot = 0;
Excel.Range cel;
Excel.ApplicationClass excel = new Excel.ApplicationClass();
try
{
excel.Application.Workbooks.Open(FileExcel, true, false, true, "", "", true, true, true, true, true, true, true, true, false);
cel = (Excel.Range)excel.Cells[5, 1];
excel.Cells[5, 1] = cel.Text + " LỚP " + pDM_LopInfo.TenLop.Replace("Lớp: ", "").ToUpper();
excel.Cells[6, 1] = "HỌC KỲ: " + Program.HocKy.ToString() + " - NĂM HỌC: " + Program.NamHoc;
// Them các tiêu đề cột môn học phía sau
if (grbMonHoc.Children.Count > 0)
{
int CotDau = CotBatDau;
foreach (GridBand grbTenMon in grbMonHoc.Children)
{
for (int i = 1; i <= grbTenMon.Children[0].Columns.Count; i++)
{
cel = (Excel.Range)(excel.Cells[DongBatDau - 1, CotBatDau]);
cel.EntireColumn.Insert(Excel.XlDirection.xlToRight, null);
SoCot++;
// Lần thi [DongBatDau - 1]
excel.Cells[DongBatDau - 1, CotBatDau] = "L" + i.ToString();
cel = (Excel.Range)(excel.Cells[DongBatDau - 1, CotBatDau]);
cel.ColumnWidth = 3;
CotBatDau++;
}
// Số học trình [DongBatDau - 2]
excel.Cells[DongBatDau - 2, CotDau] = grbTenMon.Children[0].Caption;
cel = excel.get_Range(excel.Cells[DongBatDau - 2, CotDau], excel.Cells[DongBatDau - 2, CotBatDau - 1]);
cel.Merge(null);
cel.Borders.Value = 1;
// Tên môn học [DongBatDau - 3]
excel.Cells[DongBatDau - 3, CotDau] = grbTenMon.Caption;
cel = excel.get_Range(excel.Cells[DongBatDau - 3, CotDau], excel.Cells[DongBatDau - 3, CotBatDau - 1]);
cel.Font.Size = 10;
cel.Font.Bold = false;
cel.WrapText = true;
cel.Merge(null);
cel.Borders.Value = 1;
CotDau = CotBatDau;
}
// ĐIỂM [DongBatDau - 4]
excel.Cells[DongBatDau - 4, _ColStart] = "ĐIỂM MÔN HỌC";
cel = excel.get_Range(excel.Cells[DongBatDau - 4, _ColStart], excel.Cells[DongBatDau - 4, _ColStart + SoCot - 1]);
cel.Merge(null);
cel.Borders.Value = 1;
int colPhongDaoTao = SoCot / 2 - 5;
//excel.Cells[DongBatDau + 5, _ColStart + colPhongDaoTao] = "Phòng đào tạo";
}
DataRow dr;
int SoSinhVien = bgvDiem.DataRowCount;
for (int j = 0; j < SoSinhVien; j++)
{
dr = bgvDiem.GetDataRow(j);
cel = (Excel.Range)(excel.Cells[j + DongBatDau + 1, 1]);
cel.EntireRow.Insert(Excel.XlDirection.xlUp, null);
excel.Cells[j + DongBatDau, 1] = j + 1;
excel.Cells[j + DongBatDau, 2] = "" + dr["MaSinhVien"];
excel.Cells[j + DongBatDau, 3] = "" + dr["HoVa"];
excel.Cells[j + DongBatDau, 4] = "" + dr["TenSV"];
excel.Cells[j + DongBatDau, 5] = "'" + dr["NgaySinh"];
for (int i = dtColStart; i < dtColEnd; i++)
{
if ("" + dr[i] != "")
{
cel = (Excel.Range)excel.Cells[j + DongBatDau, _ColStart + i - dtColStart];
excel.Cells[j + DongBatDau, _ColStart + i - dtColStart] = dr[i];
cel.NumberFormat = "0.0";
}
}
if ("" + dr["DiemTK1"] != "")
{
cel = (Excel.Range)excel.Cells[j + DongBatDau, _ColStart + SoCot];
excel.Cells[j + DongBatDau, _ColStart + SoCot] = dr["DiemTK1"];
cel.NumberFormat = "0.0";
}
if ("" + dr["DiemTK2"] != "")
{
cel = (Excel.Range)excel.Cells[j + DongBatDau, _ColStart + SoCot + 1];
excel.Cells[j + DongBatDau, _ColStart + SoCot + 1] = dr["DiemTK2"];
cel.NumberFormat = "0.0";
}
}
// Đưa dữ liệu phần tỷ lệ kết quả học tập
// Tỷ lệ giỏi
string Condition = "";
int SoLuong = 0;
DataRow[] arrDr = dtXepLoai.Select("MaXepLoai = 'XS' Or MaXepLoai = 'G'");
if (arrDr.Length > 0)
{
Condition = "";
foreach (DataRow drXL in arrDr)
{
Condition += (Condition == "" ? "" : " OR ") + "KQHT_XepLoaiTotNghiepID1 = " + drXL["KQHT_XepLoaiTotNghiepID"];
}
SoLuong = int.Parse(dtSinhVien.Compute("Count(SV_SinhVienID)", Condition).ToString());
cel = (Excel.Range)excel.Cells[DongBatDau + SoSinhVien + 2, 2];
excel.Cells[DongBatDau + SoSinhVien + 2, 2] = cel.Text + TinhPhanTram(SoLuong, SoSinhVien);
}
// Tỷ lệ khá
arrDr = dtXepLoai.Select("MaXepLoai = 'K'");
if (arrDr.Length > 0)
{
Condition = "";
foreach (DataRow drXL in arrDr)
{
Condition += (Condition == "" ? "" : " OR ") + "KQHT_XepLoaiTotNghiepID1 = " + drXL["KQHT_XepLoaiTotNghiepID"];
}
SoLuong = int.Parse(dtSinhVien.Compute("Count(SV_SinhVienID)", Condition).ToString());
cel = (Excel.Range)excel.Cells[DongBatDau + SoSinhVien + 3, 2];
excel.Cells[DongBatDau + SoSinhVien + 3, 2] = cel.Text + TinhPhanTram(SoLuong, SoSinhVien);
}
// Tỷ lệ trung bình
arrDr = dtXepLoai.Select("MaXepLoai = 'TBK' OR MaXepLoai = 'TB'");
if (arrDr.Length > 0)
{
Condition = "";
foreach (DataRow drXL in arrDr)
{
Condition += (Condition == "" ? "" : " OR ") + "KQHT_XepLoaiTotNghiepID1 = " + drXL["KQHT_XepLoaiTotNghiepID"];
}
SoLuong = int.Parse(dtSinhVien.Compute("Count(SV_SinhVienID)", Condition).ToString());
cel = (Excel.Range)excel.Cells[DongBatDau + SoSinhVien + 4, 2];
excel.Cells[DongBatDau + SoSinhVien + 4, 2] = cel.Text + TinhPhanTram(SoLuong, SoSinhVien);
}
// Tỷ lệ yếu
arrDr = dtXepLoai.Select("MaXepLoai = 'Y'");
if (arrDr.Length > 0)
{
Condition = "";
foreach (DataRow drXL in arrDr)
{
Condition += (Condition == "" ? "" : " OR ") + "KQHT_XepLoaiTotNghiepID1 = " + drXL["KQHT_XepLoaiTotNghiepID"];
}
SoLuong = int.Parse(dtSinhVien.Compute("Count(SV_SinhVienID)", Condition).ToString());
cel = (Excel.Range)excel.Cells[DongBatDau + SoSinhVien + 2, 4];
excel.Cells[DongBatDau + SoSinhVien + 2, 4] = cel.Text + TinhPhanTram(SoLuong, SoSinhVien);
}
// Tỷ lệ kém
arrDr = dtXepLoai.Select("MaXepLoai = 'K-'");
if (arrDr.Length > 0)
{
Condition = "";
foreach (DataRow drXL in arrDr)
{
Condition += (Condition == "" ? "" : " OR ") + "KQHT_XepLoaiTotNghiepID1 = " + drXL["KQHT_XepLoaiTotNghiepID"];
}
Condition += " OR KQHT_XepLoaiTotNghiepID1 = 0";
SoLuong = int.Parse(dtSinhVien.Compute("Count(SV_SinhVienID)", Condition).ToString());
cel = (Excel.Range)excel.Cells[DongBatDau + SoSinhVien + 3, 4];
excel.Cells[DongBatDau + SoSinhVien + 3, 4] = cel.Text + TinhPhanTram(SoLuong, SoSinhVien);
}
// Set style
cel = excel.get_Range(excel.Cells[DongBatDau, 1],
excel.Cells[DongBatDau + SoSinhVien - 1, _ColStart + SoCot + 2 - 1]);
cel.Borders.LineStyle = Excel.XlLineStyle.xlDot;
cel.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
cel = excel.get_Range(excel.Cells[DongBatDau, 1], excel.Cells[DongBatDau + SoSinhVien - 1, 1]);
cel.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
for (int i = 1; i <= _ColStart - 1; i++)
{
cel = excel.get_Range(excel.Cells[DongBatDau, i], excel.Cells[DongBatDau + SoSinhVien - 1, i]);
cel.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDot;
if (i != 3)
{
cel.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
}
else
{
cel.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
}
}
for (int i = _ColStart; i <= _ColStart + SoCot + 2 - 1; i++)
{
cel = excel.get_Range(excel.Cells[DongBatDau, i], excel.Cells[DongBatDau + SoSinhVien - 1, i]);
cel.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDot;
cel.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
}
cel = excel.get_Range(excel.Cells[DongBatDau + SoSinhVien - 1, 1],
excel.Cells[DongBatDau + SoSinhVien - 1, _ColStart + SoCot + 2 - 1]);
cel.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
}
catch (Exception e)
{
CloseWaitDialog();
this.Cursor = System.Windows.Forms.Cursors.Default;
ThongBaoLoi("Xuất dữ liệu không thành công! Hãy đóng file Excel Phiếu báo điểm trước khi xuất dữ liệu. Thông báo lỗi: " + e.Message);
return;
}
finally
{
excel.Application.Workbooks[1].Save();
excel.Application.Workbooks.Close();
excel.Application.Quit();
excel.Quit();
Process.Start(FileExcel);
CloseWaitDialog();
this.Cursor = System.Windows.Forms.Cursors.Default;
}
}
private void ExpGridtoExcel(string Head, DataGridView gdv, DateTime BeginTime, DateTime EndTime)
{
if (gdv.RowCount != 0)
{
Excel.Application application = new Excel.ApplicationClass();
Excel._Worksheet activeSheet = (Excel._Worksheet)application.Application.Workbooks.Add(true).ActiveSheet;
activeSheet.Name = Head.Replace("\r\n", "") + DateTime.Now.ToString("yy-MM-dd");
application.Visible = true;
activeSheet.get_Range(application.Cells[1, 1], application.Cells[1, gdv.ColumnCount]).Font.Bold = true;
activeSheet.get_Range(application.Cells[1, 1], application.Cells[1, gdv.ColumnCount]).Font.Size = 0x10;
activeSheet.get_Range(application.Cells[1, 1], application.Cells[1, gdv.ColumnCount]).HorizontalAlignment =Excel.XlVAlign.xlVAlignCenter;
activeSheet.get_Range(application.Cells[1, 1], application.Cells[1, gdv.ColumnCount]).Merge(true);
application.Cells[1, 1] = Head;
if (BeginTime.ToString().Length > 0)
{
application.Cells[2, 1] = "从" + BeginTime.ToString() + "到" + EndTime.ToString();
}
application.Cells[3, 1] = " 报表时间 " + DateTime.Now;
activeSheet.get_Range(application.Cells[4, 1], application.Cells[4, gdv.ColumnCount]).Interior.ColorIndex = 0x13;
for (int i = 0; i < gdv.ColumnCount; i++)
{
application.Cells[4, i + 1] = gdv.Columns[i].Name.ToString();
}
for (int j = 0; j < gdv.RowCount; j++)
{
for (int k = 0; k < gdv.ColumnCount; k++)
{
if (gdv[k, j].Value.ToString().Length >= 0)
{
application.Cells[j + 5, k + 1] = gdv[k, j].Value.ToString();
}
else
{
application.Cells[j + 5, k + 1] = "0";
}
}
}
}
}
private void Export_datpt_test(DataTable dt)
{
try
{
string sql = "", err = "";
Excel.ApplicationClass excel = new Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
excel.Cells[1, 1] = "Đơn vị";// [Dòng, cột]
excel.Cells[2, 1] = "THÔNG TIN LÀM THẺ ";
excel.Cells[3, 1] = "STT";
excel.Cells[3, 2] = "card_number";
excel.Cells[3, 3] = "embossed_name";
excel.Cells[3, 4] = "client_code";
for (int i = 0; i < dt.Rows.Count; i++)
{
try
{
//End Dat edit 20/12/2012 for format cell in excel to TEXT
//khai báo biến
string stt = dt.Rows[i][0].ToString();
string card_number = dt.Rows[i][1].ToString();
string embossed_name = dt.Rows[i][2].ToString();
string client_code = dt.Rows[i][3].ToString();
//Dat edit 20/12/2012 for format cell in excel to TEXT
for (int j = 0; j < 9; j++)
{
((Excel.Range)excel.Cells[i + 4, j + 1]).NumberFormat = "@";//.Font.Bold = true;
}
//end Dat edit 20/12/2012 for format cell in excel to TEXT
//Export excel
excel.Cells[i + 4, 1] = "" + stt;// [Dòng, cột]
excel.Cells[i + 4, 2] = "" + card_number;
excel.Cells[i + 4, 3] = "" + embossed_name;
excel.Cells[i + 4, 4] = "" + client_code;
}
catch (Exception ex)
{
}
}
excel.Visible = true;
Excel._Worksheet worksheet = (Excel._Worksheet)excel.ActiveSheet;
//worksheet.Activate();
worksheet.SaveAs("D:\\KTT\\AUTO_CIF\\" + "TaoThe_" + DateTime.Now.ToString("ddMMyyyy") + DateTime.Now.ToString("hh24miss") + ".xls");
Response.TransmitFile("D:\\KTT\\AUTO_CIF\\" + "TaoThe_" + DateTime.Now.ToString("ddMMyyyy") + DateTime.Now.ToString("hh24miss") + ".xls");
Response.End();
}
catch (Exception ex)
{
executedb.WriteLogERROR(Session["Username"].ToString(), "IP: " + Request.UserHostAddress, this.Page.ToString(), System.Reflection.MethodBase.GetCurrentMethod().ToString(), ex.Message);
//executedb.WriteLogPri(Environment.NewLine + DateTime.Now.ToLongDateString() + " " + DateTime.Now.ToLongTimeString() + " - " + Session["Username"].ToString() + ": " + ex.Message);
//Response.Write("<script>alert('Error in ItemDataBound')</script>");
Alert.Show("Error in ItemDataBound!!! \n" + ex.ToString(), this);
}
}
private void ExportExcel(DataTable dtExport)
{
string sql = "", err = "";
Excel.ApplicationClass excel = new Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
excel.Cells[1, 1] = "Đơn vị";// [Dòng, cột]
excel.Cells[2, 1] = "THÔNG TIN LÀM THẺ ";
excel.Cells[3, 1] = "STT";
excel.Cells[3, 2] = "HO_TEN";
excel.Cells[3, 3] = "LOP";
excel.Cells[3, 4] = "NGAY_SINH";
excel.Cells[3, 5] = "NOI_SINH";
excel.Cells[3, 6] = "MA_SV";
excel.Cells[3, 7] = "NIEN_KHOA";
excel.Cells[3, 8] = "SO_THE";
excel.Cells[3, 9] = "CMND";
if (dtExport.Rows.Count > 0) //mếu có dữ liệu
{
for (int i = 0; i < dtExport.Rows.Count; i++)
{
try
{
//End Dat edit 20/12/2012 for format cell in excel to TEXT
//khai báo biến
string stt = dtExport.Rows[i][0].ToString();
string emboss_name = dtExport.Rows[i][1].ToString();
string lop = dtExport.Rows[i][2].ToString();
string ngay_sinh = dtExport.Rows[i][3].ToString();
string noi_sinh = dtExport.Rows[i][4].ToString();
string mssv = dtExport.Rows[i][5].ToString();
string cardnumber = dtExport.Rows[i][7].ToString();
string CMND = dtExport.Rows[i][8].ToString();
//Dat edit 20/12/2012 for format cell in excel to TEXT
for (int j = 0; j < 9; j++)
{
((Excel.Range)excel.Cells[i + 4, j + 1]).NumberFormat = "@";//.Font.Bold = true;
}
//end Dat edit 20/12/2012 for format cell in excel to TEXT
//Export excel
excel.Cells[i + 4, 1] = "" + stt;// [Dòng, cột]
excel.Cells[i + 4, 2] = "" + emboss_name;
excel.Cells[i + 4, 3] = "" + lop;
excel.Cells[i + 4, 4] = "" + ngay_sinh;
excel.Cells[i + 4, 5] = "" + noi_sinh;
excel.Cells[i + 4, 6] = "" + mssv;
excel.Cells[i + 4, 7] = ""; //niên khóa nhập tay
excel.Cells[i + 4, 8] = "" + cardnumber;
excel.Cells[i + 4, 9] = "" + CMND;
}
catch (Exception ex)
{
Insert_Exception("Err ExportExcel catch:", dtExport.Rows[i][0].ToString(), dtExport.Rows[i][7].ToString(), ex.ToString());
}
}
//Tao thu muc
if (!Directory.Exists(folderOut))
{
Directory.CreateDirectory(folderOut);
}
excel.Visible = true;
Excel._Worksheet worksheet = (Excel._Worksheet)excel.ActiveSheet;
worksheet.Activate();
worksheet.SaveAs("D:\\KTT\\AUTO_CIF\\" + "TaoThe_" + DateTime.Now.ToString("ddMMyyyy") + DateTime.Now.ToString("hh24miss")+".xls");
}
else //nếu ko có dữ liệu
{
//Insert_Exception("Not found in DB", stt, cardnumber, "");
MessageBox.Show("No Data Found!!!");
}
//----------------
}
public void GenerateGraph()
{
Excel.ApplicationClass excelApplication = new Excel.ApplicationClass();
Excel.Application obj = new Excel.Application();
Excel.Workbook newWorkbook = null;
Excel.Worksheet targetSheet = null;
Excel.Range dataRange = null;
try
{
string paramWorkbookPath = AppDomain.CurrentDomain.BaseDirectory + "UploadFile\\Graphs\\Failure";
paramWorkbookPath += ".xls";
File.Delete(paramWorkbookPath);
object paramMissing = Type.Missing;
object paramChartFormat = 1;
object paramCategoryLabels = 1;
object paramSeriesLabels = 0;
string strTitle = "";
object paramTitle = strTitle;
object paramCategoryTitle = "Category Title";
object paramValueTitle = "Value Title";
newWorkbook = excelApplication.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
targetSheet = (Excel.Worksheet)(newWorkbook.Worksheets[1]);
targetSheet.Name = "Sheet1";
// Gets the datatable containing the data
DataTable dsData = GetTable();
int colcount = dsData.Columns.Count;
int rowcount = dsData.Rows.Count;
int i = 2;
targetSheet.Cells[1, 1] = "Production Month";
targetSheet.Cells[1, 2] = "Failure/1000 Tractors";
DataRow drFailure = dsData.NewRow();
double[] Failure = new double[dsData.Columns.Count - 1];
drFailure["Field"] = "Failure/1000 Tractor:";
for (int k = 1; k < dsData.Columns.Count; k++)
{
double Production = Convert.ToDouble(dsData.Rows[0][k].ToString());
if (Production > 0)
{
drFailure[k] = Convert.ToString(System.Math.Round(Convert.ToDouble(dsData.Rows[1][k]) * 1000 / Production, 2));
}
else
{
drFailure[k] = "0";
}
}
dsData.Rows.Add(drFailure);
int rowFlag = 1;
foreach (DataRow dr in dsData.Rows)
{
if (rowFlag > 2)
{
int colflag = 1;
foreach (DataColumn dc in dsData.Columns)
{
if (colflag > 1)
{
targetSheet.Cells[i, 1] = "'" + getProductionMonthYear(Convert.ToInt32(dc.ColumnName));
targetSheet.Cells[i, 2] = dr[dc].ToString();
i = i + 1;
}
colflag++;
}
}
rowFlag++;
}
object fromCell = "$A1";
object toCell = "$B" + (colcount - 1).ToString();
dataRange = targetSheet.get_Range(fromCell, toCell);
/**************************************Get Column Graph()************************************************************/
// Generating the graph
Excel.Chart barchart;
barchart = (Excel.Chart)newWorkbook.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
barchart.ChartType = Excel.XlChartType.xlColumnClustered;
ColumnGraph(barchart, targetSheet, dataRange);
/**************************************Get Line Chart()************************************************************/
// Generating the graph
Excel.Chart linechart;
linechart = (Excel.Chart)newWorkbook.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
linechart.ChartType = Excel.XlChartType.xlLine;
ColumnGraph(linechart, targetSheet, dataRange);
newWorkbook.SaveAs(paramWorkbookPath, Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, false, false, null, null, null);
// Release the references to the Excel objects.
}
catch (Exception ex)
{
throw ex;
}
finally
{
dataRange = null;
targetSheet = null;
if (newWorkbook != null)
{
newWorkbook.Close(false, Type.Missing, Type.Missing);
newWorkbook = null;
}
// Quit Excel and release the ApplicationClass object.
if (excelApplication != null)
{
excelApplication.Quit();
excelApplication = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
/// <summary>
/// ��DataTable�����ݵ�����ʾΪ����
/// </summary>
/// <param name="dt">Ҫ����������</param>
/// <param name="strTitle">��������ı���</param>
/// <param name="FilePath">�����ļ���·��</param>
/// <returns></returns>
public string OutputExcel(System.Data.DataTable dt, string strTitle, string FilePath)
{
beforeTime = DateTime.Now;
Excel.Application excel;
Excel._Workbook xBk;
Excel._Worksheet xSt;
int rowIndex = 4;
int colIndex = 1;
excel = new Excel.ApplicationClass();
xBk = excel.Workbooks.Add(true);
xSt = (Excel._Worksheet)xBk.ActiveSheet;
//ȡ���б���
foreach (DataColumn col in dt.Columns)
{
colIndex++;
excel.Cells[4, colIndex] = col.ColumnName;
//���ñ����ʽΪ���ж���
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Font.Bold = true;
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Select();
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Interior.ColorIndex = titleColorindex;//19;//����Ϊdz��ɫ��������56��
}
//ȡ�ñ���е�����
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 1;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
if (col.DataType == System.Type.GetType("System.DateTime"))
{
excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//���������͵��ֶθ�ʽΪ���ж���
}
else
if (col.DataType == System.Type.GetType("System.String"))
{
excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//�����ַ��͵��ֶθ�ʽΪ���ж���
}
else
{
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
}
//����һ���ϼ���
int rowSum = rowIndex + 1;
int colSum = 2;
excel.Cells[rowSum, 2] = "�ϼ�";
xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, 2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//����ѡ�еIJ��ֵ���ɫ
xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Select();
//xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex =Assistant.GetConfigInt("ColorIndex");// 1;//����Ϊdz��ɫ��������56��
//ȡ����������ı���
excel.Cells[2, 2] = strTitle;
//������������ı����ʽ
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Bold = true;
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Size = 22;
//���ñ�����Ϊ����Ӧ���
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Select();
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Columns.AutoFit();
//������������ı���Ϊ���о���
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).Select();
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;
//���Ʊ߿�
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Borders.LineStyle = 1;
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, 2]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThick;//��������Ӵ�
xSt.get_Range(excel.Cells[4, 2], excel.Cells[4, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThick;//�����ϱ��Ӵ�
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThick;//�����ұ��Ӵ�
xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;//�����±��Ӵ�
afterTime = DateTime.Now;
//��ʾЧ��
//excel.Visible=true;
//excel.Sheets[0] = "sss";
ClearFile(FilePath);
string filename = DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls";
excel.ActiveWorkbook.SaveAs(FilePath + filename, Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
//wkbNew.SaveAs strBookName;
//excel.Save(strExcelFileName);
#region ����Excel����
//��Ҫ��Excel��DCOM�����������:dcomcnfg
//excel.Quit();
//excel=null;
xBk.Close(null, null, null);
excel.Workbooks.Close();
excel.Quit();
//ע�⣺�����õ�������Excel����Ҫִ����������������������Excel����
// if(rng != null)
// {
// System.Runtime.InteropServices.Marshal.ReleaseComObject(rng);
// rng = null;
// }
// if(tb != null)
// {
// System.Runtime.InteropServices.Marshal.ReleaseComObject(tb);
// tb = null;
// }
if (xSt != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xSt = null;
}
if (xBk != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
xBk = null;
}
if (excel != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
}
GC.Collect();//��������
#endregion
return filename;
}
private void btnConvert_Click(object sender, EventArgs e)
{
try
{
if (!Directory.Exists(folderOut))
{
Directory.CreateDirectory(folderOut);
}
if (txtElectraFolder.Text != "")
{
FileStream ft = new FileStream(txtElectraFolder.Text, FileMode.Open, FileAccess.Read);
StreamReader trr = new StreamReader(ft);
string[] file_name = filename.Split('.');
//Credit QT (thẻ từ)
filenameMC = "4_"+file_name[0].ToString() + "_MagStripe_MC_Credit.mc";
FileStream ftw_dlCR = new FileStream(folderOut + "" + filenameMC, FileMode.Create, FileAccess.Write, FileShare.None);
StreamWriter tw_dlCR = new StreamWriter(ftw_dlCR);
//Debit và Prepaid QT (thẻ từ)
filenameMC_DB = "2_"+file_name[0].ToString() + "_MagStripe_MC_Debit_Prepaid.mc";
file_nameExcel = "2_" + file_name[0].ToString() + "_MagStripe_MC_Debit_Prepaid.xls";
FileStream ftw_dlMCDB = new FileStream(folderOut + "" + filenameMC_DB, FileMode.Create, FileAccess.Write, FileShare.None);
StreamWriter tw_dlDbPr = new StreamWriter(ftw_dlMCDB);
//debit và credit ND
filenameBNK = "3_"+file_name[0].ToString() + "_MagStripe_BNK_Debit_Credit.mc";
FileStream ftw_dlBNK = new FileStream(folderOut + "" + filenameBNK, FileMode.Create, FileAccess.Write, FileShare.None);
StreamWriter tw_dlBNK = new StreamWriter(ftw_dlBNK);
filenameBNK_PR = "1_" + file_name[0].ToString() + "_MagStripe_BNK_Prepaid.mc";
file_nameExcelBNK_PR = "1_"+file_name[0].ToString() + "_BNK_Prepaid.xls";
FileStream ftw_dlBNK_PR = new FileStream(folderOut + "" + filenameBNK_PR, FileMode.Create, FileAccess.Write, FileShare.None);
StreamWriter tw_dlBNK_PR = new StreamWriter(ftw_dlBNK_PR);
//Credit QT (thẻ chip)
filenameMC_chip = "5_"+file_name[0].ToString() + "_Chip_MC_Credit.mc";
FileStream ftw_dlCR_chip = new FileStream(folderOut + "" + filenameMC_chip, FileMode.Create, FileAccess.Write, FileShare.None);
StreamWriter tw_dlCR_chip = new StreamWriter(ftw_dlCR_chip);
//Debit và Prepaid QT (thẻ chip)
filenameMC_DB_chip = "6_"+file_name[0].ToString() + "_Chip_MC_Debit_Prepaid.mc";
file_nameExcel_MC_chip = "6_" + file_name[0].ToString() + "_Chip_MC_Debit_Prepaid.xls";
FileStream ftw_dlMCDB_chip = new FileStream(folderOut + "" + filenameMC_DB_chip, FileMode.Create, FileAccess.Write, FileShare.None);
StreamWriter tw_dlDbPr_chip = new StreamWriter(ftw_dlMCDB_chip);
string text = "", text1 = "";
int sttCR = 1;
int sttDB = 1;
int sttBNK = 1;
int sttBNK_Pr = 1;
int sttCR_chip = 1;
int sttDB_chip = 1;
//Xuất file Excel cho thẻ từ Debit/Prepaid Master
Excel.ApplicationClass excel = new Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
excel.Cells[1, 1] = "STT";// [Dòng, cột]
excel.Cells[1, 2] = "CARD_NUMBER ";
excel.Cells[1, 3] = "EMBOSING_NAME";
excel.Cells[1, 4] = "Issue_date ";
excel.Cells[1, 5] = "Expire_date";
//Xuất file Excel cho thẻ Prepaid Nội địa
Excel.ApplicationClass excelBNK = new Excel.ApplicationClass();
excelBNK.Application.Workbooks.Add(true);
excelBNK.Cells[1, 1] = "STT";// [Dòng, cột]
excelBNK.Cells[1, 2] = "CARD_NUMBER ";
excelBNK.Cells[1, 3] = "EMBOSING_NAME";
excelBNK.Cells[1, 4] = "Issue_date ";
excelBNK.Cells[1, 5] = "Expire_date";
//Xuất file Excel cho thẻ chip Debit/Prepaid Master
Excel.ApplicationClass excel_chipMC = new Excel.ApplicationClass();
excel_chipMC.Application.Workbooks.Add(true);
excel_chipMC.Cells[1, 1] = "STT";// [Dòng, cột]
excel_chipMC.Cells[1, 2] = "CARD_NUMBER ";
excel_chipMC.Cells[1, 3] = "EMBOSING_NAME";
excel_chipMC.Cells[1, 4] = "Issue_date ";
excel_chipMC.Cells[1, 5] = "Expire_date";
//Xu ly viec convert thong tin
while ((text = trr.ReadLine()) != null)
{
if (text != "")
{
string checkBNK = ""; string check_chip_card ="";
checkBNK = text.Substring(0, 6);
check_chip_card = text.Substring(0, 16);
if (checkBNK == "970428") //Thẻ Banknet
{
#region BNK Card
//Input
string in_cardnumber = "";
string ICAno = "";
string in_from_mounth = "";
string in_from_year = "";
string in_to_mounth = "";
string in_to_year = "";
string in_emboss_name = "";
string in_company_name = "";
string in_member_since = "";
string in_CVC1 = "";
string in_CVC2 = "";
string in_service_code = "";
in_cardnumber = text.Substring(0, 16);
ICAno = text.Substring(16, 4);
in_from_mounth = text.Substring(20, 2);
in_from_year = text.Substring(22, 2);
in_to_mounth = text.Substring(24, 2);
in_to_year = text.Substring(26, 2);
in_emboss_name = text.Substring(28, 22);
in_company_name = text.Substring(50, 22);
in_member_since = text.Substring(72, 2);
in_CVC1 = text.Substring(74, 3);
in_CVC2 = text.Substring(77, 3);
in_service_code = text.Substring(80, 3);
string out_card_num = in_cardnumber.Substring(0, 4) + " " + in_cardnumber.Substring(4, 4) + " " + in_cardnumber.Substring(8, 4) + " " + in_cardnumber.Substring(12, 4);
string Emboss_name = Check_Unembossing_flag(in_cardnumber) == "1" ? ("").PadRight(26, ' ') : in_emboss_name.PadRight(26, ' ');
//check Prepaid
string sql = "", err = "";
sql = " select a.type,b.card_number,b.embossed_name";
sql += " from " + Businessbp.executedb.owner + "nab_phanloaisp a," + Businessbp.executedb.owner + "card b";
sql += " where a.product_code=b.product_code";
sql += " and a.bank_code='970428'";
sql += " and a.type='P'";
sql += " and b.card_number='" + in_cardnumber + "'";
DataTable dt1 = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
else
{
if (dt1.Rows.Count > 0)//Prepaid ND
{
#region Prepaid ND
string card_nbr_dbpr = "";
string issue_date_dbpr = "";
string expired_date_dbpr = "";
string embosing_name_dbpr = "";
text1 = sttBNK_Pr.ToString().PadLeft(6, '0') + "$" + out_card_num.PadRight(26, ' ') + "*" + in_from_mounth.PadLeft(2, ' ') + "/" + in_from_year.PadLeft(2, ' ') +
" !" + in_to_mounth.PadLeft(2, ' ') + "/" + in_to_year.PadLeft(2, ' ') + " !( #" +
//in_emboss_name.PadRight(26, ' ') +
Emboss_name+
//track 1
":%B" + in_cardnumber.PadRight(16, ' ') + "^" + in_emboss_name.PadRight(25, ' ') + "^" +
in_to_year.PadLeft(2, ' ') + in_to_mounth.PadLeft(2, ' ') + in_service_code.PadLeft(3, ' ') +
// "100009999999900" +
in_CVC1.PadLeft(3, ' ') + "?" +
//end track 1
//track 2
";" + in_cardnumber.PadRight(16, ' ') + "=" + in_to_year.PadLeft(2, ' ') + in_to_mounth.PadLeft(2, ' ') +
in_service_code.PadLeft(3, ' ') + in_CVC1.PadLeft(3, ' ') + "?" +
//end track 2
"";
tw_dlBNK_PR.WriteLine(text1);
//Xuất ra Excel theo đúng thứ tự
//Export excel
excelBNK.Cells[sttBNK_Pr + 1, 1] = sttBNK_Pr;// [Dòng, cột]
excelBNK.Cells[sttBNK_Pr + 1, 2] = "'" + out_card_num;
excelBNK.Cells[sttBNK_Pr + 1, 3] = in_emboss_name;
excelBNK.Cells[sttBNK_Pr + 1, 4] = "'" + in_from_mounth.PadLeft(2, ' ') + "/" + in_from_year.PadLeft(2, ' ');
excelBNK.Cells[sttBNK_Pr + 1, 5] = "'" + in_to_mounth.PadLeft(2, ' ') + "/" + in_to_year.PadLeft(2, ' ');
//----------------
sttBNK_Pr += 1;
#endregion Prepaid ND
}
//end check
else//Credit và Debit ND
{
#region Debit_Credit ND
text1 = sttBNK.ToString().PadLeft(6, '0') + "$" + out_card_num.PadRight(26, ' ') + "*" + in_from_mounth.PadLeft(2, ' ') + "/" + in_from_year.PadLeft(2, ' ') +
" !" + in_to_mounth.PadLeft(2, ' ') + "/" + in_to_year.PadLeft(2, ' ') + " !( #" +
//in_emboss_name.PadRight(26, ' ') +
Emboss_name+
//track 1
":%B" + in_cardnumber.PadRight(16, ' ') + "^" + in_emboss_name.PadRight(25, ' ') + "^" +
in_to_year.PadLeft(2, ' ') + in_to_mounth.PadLeft(2, ' ') + in_service_code.PadLeft(3, ' ') +
// "100009999999900" +
in_CVC1.PadLeft(3, ' ') + "?" +
//end track 1
//track 2
";" + in_cardnumber.PadRight(16, ' ') + "=" + in_to_year.PadLeft(2, ' ') + in_to_mounth.PadLeft(2, ' ') +
in_service_code.PadLeft(3, ' ') + in_CVC1.PadLeft(3, ' ') + "?" +
//end track 2
"";
tw_dlBNK.WriteLine(text1);
sttBNK += 1;
#endregion Debit_Credit ND
}
}
#endregion BNK Card
}
else //thẻ quốc tế
{
if (checkBNK != "970428")
{
if (Check_Chip_Card(check_chip_card) == "0") //nếu là thẻ từ
{
#region Master Card (Stripe)
//Input
string in_cardnumber = "";
string ICAno = "";
string in_from_mounth = "";
string in_from_year = "";
string in_to_mounth = "";
string in_to_year = "";
string in_emboss_name = "";
string in_company_name = "";
string in_member_since = "";
string in_CVC1 = "";
string in_CVC2 = "";
string in_service_code = "";
in_cardnumber = text.Substring(0, 16);
ICAno = text.Substring(16, 4);
in_from_mounth = text.Substring(20, 2);
in_from_year = text.Substring(22, 2);
in_to_mounth = text.Substring(24, 2);
in_to_year = text.Substring(26, 2);
in_emboss_name = text.Substring(28, 22);
in_company_name = text.Substring(50, 22);
in_member_since = text.Substring(72, 2);
in_CVC1 = text.Substring(74, 3);
in_CVC2 = text.Substring(77, 3);
in_service_code = text.Substring(80, 3);
string out_card_num = in_cardnumber.Substring(0, 4) + " " + in_cardnumber.Substring(4, 4) + " " + in_cardnumber.Substring(8, 4) + " " + in_cardnumber.Substring(12, 4);
string Emboss_name = Check_Unembossing_flag(in_cardnumber) == "1" ? ("").PadRight(26, ' ') : in_emboss_name.PadRight(26, ' ');
//check Credit
string sql = "", err = "";
sql = " select a.type,b.card_number,b.embossed_name";
sql += " from " + Businessbp.executedb.owner + "nab_phanloaisp a," + Businessbp.executedb.owner + "card b";
sql += " where a.product_code=b.product_code";
sql += " and a.bank_code='970428'";
sql += " and a.type='C'";
sql += " and b.card_number='" + in_cardnumber + "'";
DataTable dt1 = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
else
{
if (dt1.Rows.Count > 0)
{
#region Credit Card
text1 = sttCR.ToString().PadLeft(6, '0') + "$" + out_card_num.PadRight(26, ' ') + "*" + in_from_mounth.PadLeft(2, ' ') + "/" + in_from_year.PadLeft(2, ' ') +
" !" + in_to_mounth.PadLeft(2, ' ') + "/" + in_to_year.PadLeft(2, ' ') + " !( #" +
//in_emboss_name.PadRight(26, ' ') +
Emboss_name +
//track 1
":%B" + in_cardnumber.PadRight(16, ' ') + "^" + in_emboss_name.PadRight(26, ' ') + "^" +
in_to_year.PadLeft(2, ' ') + in_to_mounth.PadLeft(2, ' ') + in_service_code.PadLeft(3, ' ') +
"100009999999900" + in_CVC1.PadLeft(3, ' ') + "?" +
//end track 1
//track 2
";" + in_cardnumber.PadRight(16, ' ') + "=" + in_to_year.PadLeft(2, ' ') + in_to_mounth.PadLeft(2, ' ') +
in_service_code.PadLeft(3, ' ') + "10000" + in_CVC1.PadLeft(3, ' ') + "19999?" + in_CVC2.PadLeft(3, ' ') +
//end track 2
"";
tw_dlCR.WriteLine(text1);
sttCR += 1;
#endregion Credit Card
}
//end check
else
{
#region Debit_Prepaid Card
string card_nbr_dbpr = "";
string issue_date_dbpr = "";
string expired_date_dbpr = "";
string embosing_name_dbpr = "";
text1 = sttDB.ToString().PadLeft(6, '0') + "$" + out_card_num.PadRight(26, ' ') + "*" + in_from_mounth.PadLeft(2, ' ') + "/" + in_from_year.PadLeft(2, ' ') +
" !" + in_to_mounth.PadLeft(2, ' ') + "/" + in_to_year.PadLeft(2, ' ') + " !( #" +
//in_emboss_name.PadRight(26, ' ') +
Emboss_name+
//track 1
":%B" + in_cardnumber.PadRight(16, ' ') + "^" + in_emboss_name.PadRight(26, ' ') + "^" +
in_to_year.PadLeft(2, ' ') + in_to_mounth.PadLeft(2, ' ') + in_service_code.PadLeft(3, ' ') +
"100009999999900" + in_CVC1.PadLeft(3, ' ') + "?" +
//end track 1
//track 2
";" + in_cardnumber.PadRight(16, ' ') + "=" + in_to_year.PadLeft(2, ' ') + in_to_mounth.PadLeft(2, ' ') +
in_service_code.PadLeft(3, ' ') + "10000" + in_CVC1.PadLeft(3, ' ') + "19999?" + in_CVC2.PadLeft(3, ' ') +
//end track 2
"";
tw_dlDbPr.WriteLine(text1);
//Xuất ra Excel theo đúng thứ tự
//Export excel
excel.Cells[sttDB + 1, 1] = sttDB;// [Dòng, cột]
excel.Cells[sttDB + 1, 2] = "'" + out_card_num;
excel.Cells[sttDB + 1, 3] = in_emboss_name;
excel.Cells[sttDB + 1, 4] = "'" + in_from_mounth.PadLeft(2, ' ') + "/" + in_from_year.PadLeft(2, ' ');
excel.Cells[sttDB + 1, 5] = "'" + in_to_mounth.PadLeft(2, ' ') + "/" + in_to_year.PadLeft(2, ' ');
//----------------
sttDB += 1;
#endregion Debit_Prepaid Card
}
}
#endregion Master Card (Stripe)
}
if (Check_Chip_Card(check_chip_card) == "1") //nếu là thẻ chip
{
#region Master Card (chip)
//Input
string in_cardnumber = "";
string ICAno = "";
string in_from_mounth = "";
string in_from_year = "";
string in_to_mounth = "";
string in_to_year = "";
string in_emboss_name = "";
string in_company_name = "";
string in_member_since = "";
string in_CVC1 = "";
string in_CVC2 = "";
string in_service_code = "";
string in_CVC3 = "";
in_cardnumber = text.Substring(0, 16);
ICAno = text.Substring(16, 4);
in_from_mounth = text.Substring(20, 2);
in_from_year = text.Substring(22, 2);
in_to_mounth = text.Substring(24, 2);
in_to_year = text.Substring(26, 2);
in_emboss_name = text.Substring(28, 22);
in_company_name = text.Substring(50, 22);
in_member_since = text.Substring(72, 2);
in_CVC1 = text.Substring(74, 3);
in_CVC2 = text.Substring(77, 3);
in_service_code = text.Substring(80, 3);
in_CVC3 = text.Substring(87, 3);
string out_card_num = in_cardnumber.Substring(0, 4) + " " + in_cardnumber.Substring(4, 4) + " " + in_cardnumber.Substring(8, 4) + " " + in_cardnumber.Substring(12, 4);
//check Credit
string sql = "", err = "";
sql = " select a.type,b.card_number,b.embossed_name";
sql += " from " + Businessbp.executedb.owner + "nab_phanloaisp a," + Businessbp.executedb.owner + "card b";
sql += " where a.product_code=b.product_code";
sql += " and a.bank_code='970428'";
sql += " and a.type='C'";
sql += " and b.card_number='" + in_cardnumber + "'";
DataTable dt1 = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
else
{
if (dt1.Rows.Count > 0)
{
#region Credit Card
text1 = sttCR_chip.ToString().PadLeft(6, '0'); //seq number
text1 += in_cardnumber;//card_number
text1 += in_CVC2; //CVC2
text1 += "013481";//ICA number
text1 += in_from_mounth + "/" + in_from_year;//issuance date
text1 += " "; //space (4 char)
text1 += in_to_mounth + "/" + in_to_year;//expiry date (5 char) (MM/YY)
text1 += "M";//MasterCard symbol
//text1 += in_emboss_name.ToString().PadRight(26, ' ');//embossing name
text1 += Check_Unembossing_flag(in_cardnumber) == "1" ? String.Empty.PadRight(26, ' ') : in_emboss_name.PadRight(26, ' '); //Phong add for un_name_flag 15/08/2013
text1 += ".";//fix (1 char)
text1 += in_company_name;//company name (22 char)
text1 += " "; //space (4 char)
text1 += '"';//magstripe opener (1 char)
//track 1 start
text1 += "%";//Track1 start symbol (1 char)
text1 += "B";//track1 Start code (1 char)
text1 += in_cardnumber;//card no track1 (16 char)
text1 += "^";//t1fs1 (1 char)
text1 += Convert_Embossing_Name_Track1(in_emboss_name).PadRight(26, ' ');//embossing name
text1 += "^";//t1fs1 (1 char)
text1 += in_to_year + in_to_mounth;//expiry date track1 (YYMM)
text1 += in_service_code;//card type track1 (service code) - (3 char)
text1 += "100009999999900";//fix (15 char)
text1 += in_CVC1;//CVC1 track1 (3 char)
text1 += "?";//track1 end (1 char)
//track 1 end
//track 2 start
text1 += ";";//track2 start (1 char)
text1 += in_cardnumber;//card no track2 (16 char)
text1 += "=";//Track2 fix (1 char)
text1 += in_to_year + in_to_mounth;//expiry date (YYMM)
text1 += in_service_code;//card type track2 (service code) - (3 char)
text1 += "10000";//fix (5 char)
text1 += in_CVC1;//CVC1 track2 (3 char)
text1 += "19999";//fix (5 char)
text1 += "?";//track2 end (1 char)
//track 2 end
text1 += ";5f25=";//DT Header (6 char)
text1 += in_from_year + in_from_mounth + "15";//application effective date (YYMMDD)
text1 += ";";//DT Tail (1 char)
text1 += "100009999999900" + in_CVC3;//ChipData Track1 Discretionary Data with CVC3 (iCVC)
text1 += ";";//Separator (1 char)
text1 += in_cardnumber;//card no track2 (16 char)
text1 += "=";//Track2 fix (1 char)
text1 += in_to_year + in_to_mounth;//expiry date (YYMM)
text1 += in_service_code;//card type track2 (service code) - (3 char)
text1 += "10000";//fix (5 char)
text1 += in_CVC3;//CVC3 (iCVC)
text1 += "19999";//fix (5 char)
tw_dlCR_chip.WriteLine(text1);
sttCR_chip += 1;
#endregion Credit Card
}
//end check
else
{
#region Debit_Prepaid Card
string card_nbr_dbpr = "";
string issue_date_dbpr = "";
string expired_date_dbpr = "";
string embosing_name_dbpr = "";
text1 = sttDB_chip.ToString().PadLeft(6, '0'); //seq number
text1 += in_cardnumber;//card_number
text1 += in_CVC2; //CVC2
text1 += "013481";//ICA number
text1 += in_from_mounth + "/" + in_from_year;//issuance date
text1 += " "; //space (4 char)
text1 += in_to_mounth + "/" + in_to_year;//expiry date (5 char) (MM/YY)
text1 += "M";//MasterCard symbol
//text1 += in_emboss_name.ToString().PadRight(26, ' ');//embossing name
text1 += Check_Unembossing_flag(in_cardnumber) == "1" ? String.Empty.PadRight(26, ' ') : in_emboss_name.PadRight(26, ' '); //Phong add for un_name_flag 15/08/2013
text1 += ".";//fix (1 char)
text1 += in_company_name;//company name (22 char)
text1 += " "; //space (4 char)
text1 += '"';//magstripe opener (1 char)
//track 1 start
text1 += "%";//Track1 start symbol (1 char)
text1 += "B";//track1 Start code (1 char)
text1 += in_cardnumber;//card no track1 (16 char)
text1 += "^";//t1fs1 (1 char)
text1 += Convert_Embossing_Name_Track1(in_emboss_name).PadRight(26, ' ');//embossing name
text1 += "^";//t1fs1 (1 char)
text1 += in_to_year + in_to_mounth;//expiry date track1 (YYMM)
text1 += in_service_code;//card type track1 (service code) - (3 char)
text1 += "100009999999900";//fix (15 char)
text1 += in_CVC1;//CVC1 track1 (3 char)
text1 += "?";//track1 end (1 char)
//track 1 end
//track 2 start
text1 += ";";//track2 start (1 char)
text1 += in_cardnumber;//card no track2 (16 char)
text1 += "=";//Track2 fix (1 char)
text1 += in_to_year + in_to_mounth;//expiry date (YYMM)
text1 += in_service_code;//card type track2 (service code) - (3 char)
text1 += "10000";//fix (5 char)
text1 += in_CVC1;//CVC1 track2 (3 char)
text1 += "19999";//fix (5 char)
text1 += "?";//track2 end (1 char)
//track 2 end
text1 += ";5f25=";//DT Header (6 char)
text1 += in_from_year + in_from_mounth + "15";//application effective date (YYMMDD)
text1 += ";";//DT Tail (1 char)
text1 += "100009999999900" + in_CVC3;//ChipData Track1 Discretionary Data with CVC3 (iCVC)
text1 += ";";//Separator (1 char)
text1 += in_cardnumber;//card no track2 (16 char)
text1 += "=";//Track2 fix (1 char)
text1 += in_to_year + in_to_mounth;//expiry date (YYMM)
text1 += in_service_code;//card type track2 (service code) - (3 char)
text1 += "10000";//fix (5 char)
text1 += in_CVC3;//CVC3 (iCVC) ----------------------------------------------------------?
text1 += "19999";//fix (5 char)
tw_dlDbPr_chip.WriteLine(text1);
//Xuất ra Excel theo đúng thứ tự
//Export excel
excel_chipMC.Cells[sttDB_chip + 1, 1] = sttDB_chip;// [Dòng, cột]
excel_chipMC.Cells[sttDB_chip + 1, 2] = "'" + out_card_num;
excel_chipMC.Cells[sttDB_chip + 1, 3] = in_emboss_name;
excel_chipMC.Cells[sttDB_chip + 1, 4] = "'" + in_from_mounth.PadLeft(2, ' ') + "/" + in_from_year.PadLeft(2, ' ');
excel_chipMC.Cells[sttDB_chip + 1, 5] = "'" + in_to_mounth.PadLeft(2, ' ') + "/" + in_to_year.PadLeft(2, ' ');
//----------------
sttDB_chip += 1;
#endregion Debit_Prepaid Card
}
}
#endregion Master Card (chip)
}
}
else
{
text1 = sttDB.ToString().PadLeft(6, '0') + text.Substring(6);
tw_dlCR.WriteLine(text1);
sttDB += 1;
}
}
}
}
#region Genarate File
//Debit và Prepaid QT
tw_dlDbPr.Flush();
tw_dlDbPr.Close();
//Credit QT
tw_dlCR.Flush();
tw_dlCR.Close();
//Debit và Credit ND
tw_dlBNK.Flush();
ftw_dlBNK.Close();
//Prepaid ND
tw_dlBNK_PR.Flush();
ftw_dlBNK_PR.Close();
//Debit và Prepaid QT (thẻ chip)
tw_dlDbPr_chip.Flush();
tw_dlDbPr_chip.Close();
//Credit QT (thẻ từ)
tw_dlCR_chip.Flush();
tw_dlCR_chip.Close();
trr.Close();
ft.Close();
excel.Visible = true;
//Xuất file Excel Debit và Prepaid MC (thẻ từ)
Excel._Worksheet worksheet = (Excel._Worksheet)excel.ActiveSheet;
worksheet.Activate();
worksheet.SaveAs("D:\\KTT\\MASTER_EMBOSSING\\" + file_nameExcel);
excel.Visible = true;
//Xuất file Excel Prepaid ND
Excel._Worksheet worksheetBNK = (Excel._Worksheet)excelBNK.ActiveSheet;
worksheetBNK.Activate();
worksheetBNK.SaveAs("D:\\KTT\\MASTER_EMBOSSING\\" + file_nameExcelBNK_PR);
excelBNK.Visible = true;
//Xuất file Excel Debit và Prepaid MC (thẻ chip)
Excel._Worksheet worksheetMC_chip = (Excel._Worksheet)excel_chipMC.ActiveSheet;
worksheetMC_chip.Activate();
worksheetMC_chip.SaveAs("D:\\KTT\\MASTER_EMBOSSING\\" + file_nameExcel_MC_chip);
excel_chipMC.Visible = true;
#endregion Genarate File
MessageBox.Show("Convert Finished!!!");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void GenerateGraph()
{
Excel.ApplicationClass excelApplication = new Excel.ApplicationClass();
Excel.Application obj = new Excel.Application();
Excel.Workbook newWorkbook = null;
Excel.Worksheet targetSheet = null;
Excel.Range dataRange = null;
try
{
string paramWorkbookPath = AppDomain.CurrentDomain.BaseDirectory + "UploadFile\\Graphs\\Summary";
paramWorkbookPath += ".xls";
File.Delete(paramWorkbookPath);
object paramMissing = Type.Missing;
object paramChartFormat = 1;
object paramCategoryLabels = 1;
object paramSeriesLabels = 0;
string strTitle = "";
object paramTitle = strTitle;
object paramCategoryTitle = "Category Title";
object paramValueTitle = "Value Title";
newWorkbook = excelApplication.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
targetSheet = (Excel.Worksheet)(newWorkbook.Worksheets[1]);
targetSheet.Name = "Sheet1";
// Gets the datatable containing the data
DataTable dsData = GetTable();
int colcount = dsData.Columns.Count;
int rowcount = dsData.Rows.Count;
int i = 2;
targetSheet.Cells[1, 1] = "Financial Year";
targetSheet.Cells[1, 2] = "Value";
// Outputting the data
foreach (DataRow dr in dsData.Rows)
{
//targetSheet.Cells[i, 1] = dr["Fyear"];
targetSheet.Cells[i, 1] = "'"+ dr["Fyear"];
targetSheet.Cells[i, 2] = dr["DefectAmount"];
// Going to the next row
i = i + 1;
}
object fromCell = "$A1";
object toCell = "$B" + (rowcount + 1).ToString();
dataRange = targetSheet.get_Range(fromCell, toCell);
/**************************************Get Column Graph()************************************************************/
// Generating the graph
Excel.Chart barchart;
barchart = (Excel.Chart)newWorkbook.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
barchart.ChartType = Excel.XlChartType.xlColumnClustered;
ColumnGraph(barchart, targetSheet, dataRange);
/**************************************Get pie Graph()************************************************************/
// Generating the graph
Excel.Chart piechart;
piechart = (Excel.Chart)newWorkbook.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
piechart.ChartType = Excel.XlChartType.xlPie;
PieGraph(piechart, targetSheet, dataRange);
/**************************************Get Line Chart()************************************************************/
// Generating the graph
Excel.Chart linechart;
linechart = (Excel.Chart)newWorkbook.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
linechart.ChartType = Excel.XlChartType.xlLine;
ColumnGraph(linechart, targetSheet, dataRange);
/**************************************Get Doughnut Graph()************************************************************/
// Generating the graph
Excel.Chart doughchart;
doughchart = (Excel.Chart)newWorkbook.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
doughchart.ChartType = Excel.XlChartType.xlDoughnut;
PieGraph(doughchart, targetSheet, dataRange);
newWorkbook.SaveAs(paramWorkbookPath, Excel.XlFileFormat.xlHtml, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, false, false, null, null, null);
// Release the references to the Excel objects.
}
catch (Exception ex)
{
throw ex;
}
finally
{
dataRange = null;
targetSheet = null;
if (newWorkbook != null)
{
newWorkbook.Close(false, Type.Missing, Type.Missing);
newWorkbook = null;
}
// Quit Excel and release the ApplicationClass object.
if (excelApplication != null)
{
excelApplication.Quit();
excelApplication = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
/// <summary>
/// 将DataTable的数据导出显示为报表
/// </summary>
/// <param name="dt">要导出的数据</param>
/// <param name="strTitle">导出报表的标题</param>
/// <param name="FilePath">保存文件的路径</param>
/// <returns></returns>
public static void OutputExcel(System.Data.DataTable dt, string strTitle, string filePath)
{
var beforeTime = DateTime.Now;
Excel.Application excel;
Excel._Workbook xBk;
Excel._Worksheet xSt;
int rowIndex = 4;
int colIndex = 1;
excel = new Excel.ApplicationClass();
xBk = excel.Workbooks.Add(true);
xSt = (Excel._Worksheet)xBk.ActiveSheet;
//取得列标题
foreach (DataColumn col in dt.Columns)
{
colIndex++;
excel.Cells[4, colIndex] = col.ColumnName;
//设置标题格式为居中对齐
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Font.Bold = true;
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Select();
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Interior.ColorIndex = 15;//19;//设置为浅黄色，共计有56种
}
//取得表格中的数据
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 1;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
if (col.DataType == System.Type.GetType("System.DateTime"))
{
excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐
}
else
if (col.DataType == System.Type.GetType("System.String"))
{
excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; //设置字符型的字段格式为居中对齐
}
else
{
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
}
//加载一个合计行
int rowSum = rowIndex + 1;
int colSum = 2;
excel.Cells[rowSum, 2] = "合计";
xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, 2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//设置选中的部分的颜色
xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Select();
//xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex =Assistant.GetConfigInt("ColorIndex");// 1;//设置为浅黄色，共计有56种
//取得整个报表的标题
excel.Cells[2, 2] = strTitle;
//设置整个报表的标题格式
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Bold = true;
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Size = 22;
//设置报表表格为最适应宽度
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Select();
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Columns.AutoFit();
//设置整个报表的标题为跨列居中
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).Select();
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;
//绘制边框
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Borders.LineStyle = 1;
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, 2]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThick; //设置左边线加粗
xSt.get_Range(excel.Cells[4, 2], excel.Cells[4, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThick; //设置上边线加粗
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThick; //设置右边线加粗
xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick; //设置下边线加粗
var afterTime = DateTime.Now;
//显示效果
//excel.Visible = true;
//excel.Sheets[0] = "sss";
// ClearFile(FilePath);
//string filename = DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls";
//excel.ActiveWorkbook.SaveAs(filePath, Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
//wkbNew.SaveAs strBookName;
try
{
excel.DisplayAlerts = false;
excel.Save();
}
catch (Exception)
{
return;
}
#region 结束Excel进程
//需要对Excel的DCOM对象进行配置:dcomcnfg
//excel.Quit();
//excel=null;
xBk.Close(null, null, null);
excel.Workbooks.Close();
excel.Quit();
//注意：这里用到的所有Excel对象都要执行这个操作，否则结束不了Excel进程
// if(rng != null)
// {
// System.Runtime.InteropServices.Marshal.ReleaseComObject(rng);
// rng = null;
// }
// if(tb != null)
// {
// System.Runtime.InteropServices.Marshal.ReleaseComObject(tb);
// tb = null;
// }
if (xSt != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xSt = null;
}
if (xBk != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
xBk = null;
}
if (excel != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
}
GC.Collect();//垃圾回收
#endregion
return;
}
private void btnExport_Click(object sender, EventArgs e)
{
try
{
if (dgvMzyj.DataSource == null)
{
return;
}
Excel.Application myExcel = new Excel.ApplicationClass();
//设置不显示
myExcel.ScreenUpdating = false;
//添加工作簿
myExcel.Workbooks.Add(true);
//设置工作表
Excel.Worksheet mySheet = (Excel.Worksheet)myExcel.ActiveWorkbook.ActiveSheet;
mySheet.Name = "TrasenHis";
//设置标题
Excel.Range myTitle = mySheet.get_Range(mySheet.Cells[1, 1], mySheet.Cells[1, dgvMzyj.Columns.Count]);
myTitle.Merge(true);
if (rdoSk.Checked == true)
{
myTitle.Value2 = this.Text + "(" + rdoSk.Text + ")";
}
else
{
myTitle.Value2 = this.Text + "(" + rdoJk.Text + ")";
}
myTitle.Font.Name = "黑体";
myTitle.Font.Size = 20;
myTitle.Font.Bold = true;
myTitle.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
myTitle.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
//设置条件
Excel.Range myNote = mySheet.get_Range(mySheet.Cells[2, 1], mySheet.Cells[2, dgvMzyj.Columns.Count]);
myNote.Merge(true);
myNote.Value2 = dateTimePickerBegin.Value.ToLongDateString() + " " + dateTimePickerBegin.Value.ToLongTimeString() + " —— " + dateTimePickerEnd.Value.ToLongDateString() + " " + dateTimePickerEnd.Value.ToLongTimeString();
myNote.Font.Name = "宋体";
myNote.Font.Size = 12;
myNote.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
myNote.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
//写入表头
for (int i = 0; i < dgvMzyj.Columns.Count; i++)
{
myExcel.Cells[3, i + 1] = dgvMzyj.Columns[i].HeaderText;
}
//设置表头
Excel.Range myHeader = mySheet.get_Range(mySheet.Cells[3, 1], mySheet.Cells[3, dgvMzyj.Columns.Count]);
myHeader.Font.Size = 12;
myHeader.Font.Bold = false;
myHeader.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//写入数据
for (int i = 0; i < dgvMzyj.Rows.Count; i++)
{
for (int j = 0; j < dgvMzyj.Columns.Count; j++)
{
myExcel.Cells[i + 4, j + 1] = dgvMzyj[j, i].Value;
}
}
//设置表格
Excel.Range myTable = mySheet.get_Range(mySheet.Cells[3, 1], mySheet.Cells[dgvMzyj.Rows.Count + 3, dgvMzyj.Columns.Count]);
myTable.Columns.AutoFit();
myTable.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
myTable.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
myTable.Borders.Weight = Excel.XlBorderWeight.xlMedium;
//设置显示
myExcel.Visible = true;
myExcel.ScreenUpdating = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void butexcel_Click(object sender, EventArgs e)
{
if (dataGridView1.DataSource == null)
{
return;
}
try
{
DataTable tb = (DataTable)this.dataGridView1.DataSource;
// 创建Excel对象 --LeeWenjie 2006-11-29
Excel.Application xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
MessageBox.Show("Excel无法启动");
return;
}
// 创建Excel工作薄
Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];
// 列索引，行索引，总列数，总行数
int colIndex = 0;
int RowIndex = 0;
int colCount = 0;
int RowCount = tb.Rows.Count + 1;
for (int i = 0; i <= tb.Columns.Count - 1; i++)
{
colCount = colCount + 1;
}
//查询条件
string swhere = " 日期从:" + dtpjsrq1.Value.ToString() + " 到 " + dtpjsrq2.Value.ToString();
// 设置标题
Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, colCount]);
range.MergeCells = true;
xlApp.ActiveCell.FormulaR1C1 = "预交款明细查询";
xlApp.ActiveCell.Font.Size = 20;
xlApp.ActiveCell.Font.Bold = true;
xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter;
// 设置条件
Excel.Range range1 = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colCount]);
range1.MergeCells = true;
//xlApp.ActiveCell.FormulaR1C1 = swhere;
//xlApp.ActiveCell.Font.Size = 20;
//xlApp.ActiveCell.Font.Bold = true;
//xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter;
// 创建缓存数据
object[,] objData = new object[RowCount + 1, colCount + 1];
// 获取列标题
for (int i = 0; i <= tb.Columns.Count - 1; i++)
{
objData[1, colIndex++] = tb.Columns[i].Caption;
}
// 获取数据
objData[0, 0] = swhere;
for (int i = 0; i <= tb.Rows.Count - 1; i++)
{
colIndex = 0;
for (int j = 0; j <= tb.Columns.Count - 1; j++)
{
//if (myDataGrid1.TableStyles[0].GridColumnStyles[j].Width>0)
//{
if (tb.Columns[j].Caption == "门诊号")
{
objData[i + 2, colIndex++] = "'" + tb.Rows[i][j].ToString();
}
else
{
objData[i + 2, colIndex++] = "" + tb.Rows[i][j].ToString();
}
//}
}
Application.DoEvents();
}
// 写入Excel
range = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]);
range.Value2 = objData;
//
xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[RowCount + 2, colCount]).Borders.LineStyle = 1;
//设置报表表格为最适应宽度
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Select();
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Columns.AutoFit();
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Font.Size = 9;
xlApp.Visible = true;
}
catch (System.Exception err)
{
MessageBox.Show(err.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private bool InToExcel(string ExcelFile)
{
Excel.Application ExcelObj = null;
try
{
ExcelObj = new Excel.ApplicationClass();
ExcelObj.Visible = false;
Excel.Workbook ExcelBook = ExcelObj.Workbooks._Open(ExcelFile, 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 ExcelSheet = (Excel.Worksheet)ExcelBook.Sheets[1];
//死者
DeadManText.Text = ExcelCellToString(ExcelSheet.get_Range("B1", Type.Missing));
//性别
if (ExcelCellToString(ExcelSheet.get_Range("G1", Type.Missing)) == "男")
DeadSexComBoBox.SelectedIndex = 0;
else
DeadSexComBoBox.SelectedIndex = 1;
//出殡时间
DeadManTimeText.Text = ExcelCellToDate(ExcelSheet.get_Range("B2", Type.Missing));
//告别厅
DeadManAddressText.Text = ExcelCellToString(ExcelSheet.get_Range("F2", Type.Missing));
//列表用户
if (GridViewControl.RowCount > 1)
GridViewControl.Rows.Clear();
int i = 4;
int j = 0;
while (true)
{
string Cell1 = ExcelCellToString((Excel.Range)ExcelSheet.Cells[i, 1]);
if (String.IsNullOrEmpty(Cell1))
{
break;
}
else
{
GridViewControl.Rows.Add();
GridViewControl.Rows[j].Cells[0].Value = Cell1;
//称谓(前)
string Cell2 = ExcelCellToString((Excel.Range)ExcelSheet.Cells[i, 2]);
GridViewControl.Rows[j].Cells[1].Value = Cell2;
//称谓(后)
string Cell3 = ExcelCellToString((Excel.Range)ExcelSheet.Cells[i, 3]);
GridViewControl.Rows[j].Cells[2].Value = Cell3;
//姓名1
string Cell4 = ExcelCellToString((Excel.Range)ExcelSheet.Cells[i, 4]);
GridViewControl.Rows[j].Cells[3].Value = Cell4;
//姓名2
string Cell5 = ExcelCellToString((Excel.Range)ExcelSheet.Cells[i, 5]);
GridViewControl.Rows[j].Cells[4].Value = Cell5;
//项目
string Cell6 = ExcelCellToString((Excel.Range)ExcelSheet.Cells[i, 6]);
GridViewControl.Rows[j].Cells[5].Value = Cell6;
//价钱
string Cell7 = ExcelCellToString((Excel.Range)ExcelSheet.Cells[i, 7]);
GridViewControl.Rows[j].Cells[6].Value = Cell7;
FontColorClass FontColorObj = new FontColorClass();
if (FontColorDefault == null)
{
FontColorObj.FontObj = new Font("隶书", (float)40, FontStyle.Bold);
FontColorObj.ColorObj = Color.Black;
}
else
{
FontColorObj = FontColorDefault;
}
FontColorList.Add(FontColorObj);
GridViewSetFont(j, GridViewControl.ColumnCount - 1, FontColorObj.FontObj);
i++;
j++;
}
}
ExcelSheet = null;
ExcelBook.Close(false, false, false);
ExcelBook = null;
ExcelObj.Quit();
ExcelObj = null;
//合计
TotalPriceText.Text = SumCellPrice(6).ToString();
return true;
}
catch (Exception ex)
{
ExcelObj.Quit();
ExcelObj = null;
MessageBox.Show(ex.Message, "导入错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return false;
}
}
public void GenerateGraph()
{
Excel.ApplicationClass excelApplication = new Excel.ApplicationClass();
Excel.Application obj = new Excel.Application();
Excel.Workbook newWorkbook = null;
Excel.Worksheet targetSheet = null;
Excel.Range dataRange = null;
try
{
string paramWorkbookPath = AppDomain.CurrentDomain.BaseDirectory + "UploadFile\\Graphs\\EffectCheck";
paramWorkbookPath += ".xls";
File.Delete(paramWorkbookPath);
object paramMissing = Type.Missing;
object paramChartFormat = 1;
object paramCategoryLabels = 1;
object paramSeriesLabels = 0;
string strTitle = "";
object paramTitle = strTitle;
object paramCategoryTitle = "Category Title";
object paramValueTitle = "Value Title";
newWorkbook = excelApplication.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
targetSheet = (Excel.Worksheet)(newWorkbook.Worksheets[1]);
targetSheet.Name = "Sheet1";
// Gets the datatable containing the data
DataTable dsData = GetTable();
int colcount = dsData.Columns.Count;
int rowcount = dsData.Rows.Count;
int i = 2;
int columnflag = 1;
//foreach (DataColumn dccol in dsData.Columns)
//{
// targetSheet.Cells[1, columnflag] = dccol.ColumnName;
// targetSheet.Cells[2, columnflag] = dsData.Rows[0][dccol].ToString();
// targetSheet.Cells[3, columnflag] = dsData.Rows[1][dccol].ToString();
// columnflag++;
//}
string beforafter;
foreach (DataColumn dccol in dsData.Columns)
{
//New changes for Excel graph presentation
string[] strTest = dccol.ColumnName.Split('-');
targetSheet.Cells[1, columnflag] = strTest[0];
if (dsData.Rows[0][dccol].ToString() == "Before")
{
beforafter = dsData.Rows[0][dccol].ToString() + "-" + drpMonth.SelectedItem.Text + " " + drpYear.SelectedItem.Text;
targetSheet.Cells[2, 1] = beforafter;
}
if (dsData.Rows[1][dccol].ToString() == "After")
{
beforafter = dsData.Rows[1][dccol].ToString() + "-" + drpMonth.SelectedItem.Text + " " + drpYear.SelectedItem.Text;
targetSheet.Cells[3, 1] = beforafter;
}
if (dccol.ToString() != "PM")
{
string str1 = dsData.Rows[0][dccol].ToString();
string str2 = dsData.Rows[1][dccol].ToString();
targetSheet.Cells[2, columnflag] = dsData.Rows[0][dccol].ToString();
targetSheet.Cells[3, columnflag] = dsData.Rows[1][dccol].ToString();
}
columnflag++;
}
object fromCell = "$A1";
//object toCell = "$B" + (colcount - 1).ToString();
//To claculate the excel column for the Exdended months
string Sheetcellno = "$N3";
if (Convert.ToInt32(txtEntendedMonth.Text) > 1)
{
if (Convert.ToInt32(txtEntendedMonth.Text) == 2)
{
Sheetcellno = "$O3";
}
else if (Convert.ToInt32(txtEntendedMonth.Text) == 3)
{
Sheetcellno = "$P3";
}
else if (Convert.ToInt32(txtEntendedMonth.Text) == 4)
{
Sheetcellno = "$Q3";
}
else if (Convert.ToInt32(txtEntendedMonth.Text) == 5)
{
Sheetcellno = "$R3";
}
else if (Convert.ToInt32(txtEntendedMonth.Text) == 6)
{
Sheetcellno = "$S3";
}
else if (Convert.ToInt32(txtEntendedMonth.Text) == 7)
{
Sheetcellno = "$T3";
}
else if (Convert.ToInt32(txtEntendedMonth.Text) == 8)
{
Sheetcellno = "$U3";
}
else if (Convert.ToInt32(txtEntendedMonth.Text) == 9)
{
Sheetcellno = "$V3";
}
else if (Convert.ToInt32(txtEntendedMonth.Text) == 10)
{
Sheetcellno = "$W3";
}
else if (Convert.ToInt32(txtEntendedMonth.Text) == 11)
{
Sheetcellno = "$X3";
}
else if (Convert.ToInt32(txtEntendedMonth.Text) == 12)
{
Sheetcellno = "$Y3";
}
}
//object toCell = "$S3";
object toCell = Sheetcellno;
dataRange = targetSheet.get_Range(fromCell, toCell);
/**************************************Get Line Chart()************************************************************/
// Generating the graph
Excel.Chart linechart;
linechart = (Excel.Chart)newWorkbook.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
linechart.ChartType = Excel.XlChartType.xlLineMarkers;
ColumnGraph(linechart, targetSheet, dataRange);
//newWorkbook.SaveAs(paramWorkbookPath, Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, false, false, null, null, null);
newWorkbook.SaveCopyAs(paramWorkbookPath);
// Release the references to the Excel objects.
}
catch (Exception ex)
{
throw ex;
}
finally
{
dataRange = null;
targetSheet = null;
if (newWorkbook != null)
{
newWorkbook.Close(false, Type.Missing, Type.Missing);
newWorkbook = null;
}
// Quit Excel and release the ApplicationClass object.
if (excelApplication != null)
{
excelApplication.Quit();
excelApplication = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
private void XuatDuLieuRaExcel(DataTable dtChiTiet, string FileExcel)
{
CreateWaitDialog("Đang xuất dữ liệu ra file Excel", "Xin vui lòng chờ!");
this.Cursor = System.Windows.Forms.Cursors.WaitCursor;
int i = 0;
int DongBatDau = 6, MonID = 0;
Excel.Range cel;
Excel.ApplicationClass excel = new Excel.ApplicationClass();
try
{
excel.Application.Workbooks.Open(FileExcel, true, false, true, "", "", true, true, true, true, true, true, true, true, false);
excel.Cells[3, 7] = uctrlLop.trlLop.FocusedNode["TenLop"].ToString().ToUpper();
cel = (Excel.Range)excel.Cells[3, 7];
cel.Font.Bold = true;
excel.Cells[4, 3] = Program.HocKy.ToString();
cel = (Excel.Range)excel.Cells[4, 3];
excel.Cells[4, 7] = Program.NamHoc;
cel = (Excel.Range)excel.Cells[4, 7];
// Them các tiêu đề cột môn học phía sau
for (int j = 0; j < dtMonHoc.Rows.Count; j++)
{
excel.Cells[DongBatDau, j * 2 + 3 + 1] = dtMonHoc.Rows[j]["MaMonHoc"];
cel = (Excel.Range)(excel.Cells[DongBatDau, j * 2 + 3 + 1]);
excel.Cells[DongBatDau + 1, j * 2 + 3 + 1] = dtMonHoc.Rows[j]["SoHocTrinh"];
cel = (Excel.Range)(excel.Cells[DongBatDau + 1, j * 2 + 3 + 1]);
}
for (i = 0; i < dtChiTiet.Rows.Count; i++)
{
excel.Cells[i + DongBatDau + 3, 1] = i + 1;
cel = (Excel.Range)(excel.Cells[i + DongBatDau + 3, 1]);
cel.Borders.Value = 1;
excel.Cells[i + DongBatDau + 3, 2] = dtChiTiet.Rows[i]["HoVaTen"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau + 3, 2]);
cel.Borders.Value = 1;
excel.Cells[i + DongBatDau + 3, 3] = dtChiTiet.Rows[i]["NgaySinh"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau + 3, 3]);
cel.Borders.Value = 1;
int Index = 0;
DataTable dt = new DataTable();
dt = oBKQHT_DiemTongKetHocKy.GetDiemTongKet(int.Parse(dtChiTiet.Rows[i]["SV_SinhVienID"].ToString()), Program.IDNamHoc, Program.HocKy);
for (int j = 0; j < dtChiTiet.Columns.Count; j++)
{
if (int.TryParse(dtChiTiet.Columns[j].ColumnName, out MonID))
{
if (cmbLanThi.SelectedIndex == 0)
{
excel.Cells[i + DongBatDau + 3, 4 + 2 * Index] = dtChiTiet.Rows[i][dtChiTiet.Columns[j].ColumnName];
cel = (Excel.Range)(excel.Cells[i + DongBatDau + 3, 4 + 2 * Index]);
cel.Borders.Value = 1;
excel.Cells[i + DongBatDau + 3, 5 + 2 * Index] = "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau + 3, 5 + 2 * Index]);
cel.Borders.Value = 1;
}
else
{
DataRow[] dr = dt.Select("DM_MonHOcID=" + dtChiTiet.Columns[j].ColumnName);
if (dr != null && dr.Length > 0)
{
excel.Cells[i + DongBatDau + 3, 4 + 2 * Index] = dr[0]["DiemLan1"];
excel.Cells[i + DongBatDau + 3, 5 + 2 * Index] = dr[0]["DiemLan2"];
}
else
{
excel.Cells[i + DongBatDau + 3, 4 + 2 * Index] = "";
excel.Cells[i + DongBatDau + 3, 5 + 2 * Index] = "";
}
cel = (Excel.Range)(excel.Cells[i + DongBatDau + 3, 4 + 2 * Index]);
cel.Borders.Value = 1;
cel = (Excel.Range)(excel.Cells[i + DongBatDau + 3, 5 + 2 * Index]);
cel.Borders.Value = 1;
}
Index += 1;
}
}
excel.Cells[i + DongBatDau + 3, 24] = dtChiTiet.Rows[i]["DiemTK"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau + 3, 24]);
cel.Borders.Value = 1;
}
}
catch (Exception e)
{
CloseWaitDialog();
this.Cursor = System.Windows.Forms.Cursors.Default;
ThongBaoLoi("Xuất dữ liệu không thành công! Hãy đóng file Excel Phiếu báo điểm trước khi xuất dữ liệu. Thông báo lỗi: " + e.Message);
return;
}
finally
{
excel.Application.Workbooks[1].Save();
excel.Application.Workbooks.Close();
excel.Application.Quit();
excel.Quit();
Process.Start(FileExcel);
CloseWaitDialog();
this.Cursor = System.Windows.Forms.Cursors.Default;
}
}
private void button1_Click(object sender, EventArgs e)
{
try
{
this.Cursor = Cursors.WaitCursor;
string sql = "", err1 = "";
DataTable dt;
//Kiem tra processing_date đã xử lý chưa
sql = "select * from " + Businessbp.executedb.owner + "nab_accounting b where trunc(b.date_proccessing)=trunc(to_date('" + dateTimePicker1.Text + "','dd/mm/yyyy')) and b.accounting_type='ACCOUNTING_DCDEBIT'";
//,substr(to_char(sysdate,'hh24miss'),3,2) as phut,substr(to_char(sysdate,'hh24miss'),5,2) as giay
dt = Businessbp.executedb.getTable(sql, ref err1);
if (err1 != "")
{
MessageBox.Show(err1);
return;
}
else
{
if (dt.Rows.Count > 0)
{
MessageBox.Show("Đã xử lý ACCOUNTING_DCDEBIT với ngày này");
return;
}
}
// string fromtime = dateTimePicker1.Text;
//string totime = dtTo.Text;
string date_process = dateTimePicker1.Text;
string name = Businessbp.executedb.owner + "proc_nab_accounting_dcdb";
//if (type == "ACCOUNTING_THAUCHI") //thau chi
//{
// return;//name += "proc_nab_accounting_thauchi";
//}
//else if (type == "ACCOUNTING_DCCR") //dieu chinh credit
//{ name += "proc_nab_accounting_dccr"; }
//else if (type == "ACCOUNTING_TRANSACTION") //hach toan giao dich
//{ name += "proc_nab_accounting"; }
//else if (type == "ACCOUNTING_DCDEBIT") //dieu chinh debit
//{ name += "proc_nab_accounting_dcdb"; }
string[] InParaName = new string[2] { "d_doing", "user_create" };
string[] OutParaName = new string[2] { "Result_out", "Emsg_out" };
OleDbType[] InParaType = new OleDbType[2] { OleDbType.VarChar, OleDbType.VarChar };
OleDbType[] OutParaType = new OleDbType[2] { OleDbType.Numeric, OleDbType.VarChar };
string[] InParaValues = new string[2] { date_process, Businessbp.executedb.Usrid };
string[] OutParaValues = new string[2];
for (int i = 0; i < 2; i++)
OutParaValues[i] = "";
int[] OutParaSize = new int[2] { 0, 300 };
string err = "";
Businessbp.executedb.ExecuteProcELECTRA(name, InParaName, InParaType, InParaValues, OutParaName, OutParaType, OutParaSize, ref OutParaValues, ref err);
if (err != "")
MessageBox.Show(err);
else
{
int Result_out = Int32.Parse(OutParaValues[0].ToString());
string Emsg_out = OutParaValues[1].ToString();
if (Result_out != 1)
{
MessageBox.Show(Emsg_out);
return;
}
else
{
Excel.ApplicationClass excel = new Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
excel.Cells[1, 1] = "Group";// [Dòng, cột]
excel.Cells[1, 2] = "Account ";
excel.Cells[1, 3] = "Ccy_cd";
excel.Cells[1, 4] = "Amount ";
excel.Cells[1, 5] = "Branch_code";
excel.Cells[1, 6] = "Account_Branch ";
excel.Cells[1, 7] = "Dr_Cr";
excel.Cells[1, 8] = "Ex_rate ";
excel.Cells[1, 9] = "Trn_dt";
excel.Cells[1, 10] = "Description ";
//excel.Cells[1, 1] = "Tại thời điểm " + dtTo.Text;
//((Excel.Range)excel.Cells[1, 1]).Font.Bold = true;
sql = ""; err = "";
sql = "select a.group_id,''''||a.account_number as account_number,a.ccy_cd,a.amount_accounting,''''||a.branch_code as branch_code" +
" ,''''||a.account_branch as account_branch,a.dr_cr,a.ex_rate,to_char(a.trn_dt,'mm/dd/yyyy') as trn_dt,a.description,a.user_create,a.accounting_type " +
" from " + Businessbp.executedb.owner + "nab_accounting a " +
" where trunc(a.date_proccessing)= trunc(to_date('" + dateTimePicker1.Text + "','dd/mm/yyyy'))" +
" and upper(trim(a.accounting_type))=upper(trim('ACCOUNTING_DCDEBIT'))" +
" order by a.group_id asc";
DataTable dtExport = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
else
{
if (dtExport.Rows.Count > 0)
{
for (int i = 0; i < dtExport.Rows.Count; i++)
{
string Group = "", Account = "", Ccy_cd = "", Amount = "", Branch_code = "",
Account_Branch = "", Dr_Cr = "", Ex_rate = "", Trn_dt = "", Description = "";
//a.group_id,a.account_number,a.ccy_cd,a.amount_accounting,a.branch_code "+
//" ,a.account_branch,a.dr_cr,a.ex_rate,a.trn_dt,a.description,a.user_create,a.accounting_type "+
Group = dtExport.Rows[i]["group_id"].ToString();
Account = dtExport.Rows[i]["account_number"].ToString();
Ccy_cd = dtExport.Rows[i]["ccy_cd"].ToString();
Amount = dtExport.Rows[i]["amount_accounting"].ToString();
Branch_code = dtExport.Rows[i]["branch_code"].ToString().Trim();
Account_Branch = dtExport.Rows[i]["account_branch"].ToString().Trim();
Dr_Cr = dtExport.Rows[i]["dr_cr"].ToString();
Ex_rate = dtExport.Rows[i]["ex_rate"].ToString();
Trn_dt = dtExport.Rows[i]["trn_dt"].ToString();
Description = dtExport.Rows[i]["description"].ToString();
//Export excel
excel.Cells[i + 2, 1] = Group;// [Dòng, cột]
excel.Cells[i + 2, 2] = Account;
excel.Cells[i + 2, 3] = Ccy_cd;
excel.Cells[i + 2, 4] = Amount;
excel.Cells[i + 2, 5] = Branch_code;
excel.Cells[i + 2, 6] = Account_Branch;
excel.Cells[i + 2, 7] = Dr_Cr;
excel.Cells[i + 2, 8] = Ex_rate;
excel.Cells[i + 2, 9] = Trn_dt;
excel.Cells[i + 2, 10] = Description;
}
}
}
//----------------
excel.Visible = true;
Excel._Worksheet worksheet = (Excel._Worksheet)excel.ActiveSheet;
worksheet.Activate();
}
}
LoadData();
this.Cursor = Cursors.Default;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnPrint_Click(object sender, EventArgs e)
{
try
{
DataTable tb = (DataTable)this.dataGridView1.DataSource;
// 创建Excel对象
Excel.Application xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
MessageBox.Show("Excel无法启动");
return;
}
// 创建Excel工作薄
Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];
// 列索引，行索引，总列数，总行数
int colIndex = 0;
int RowIndex = 0;
int colCount = 0;
int RowCount = tb.Rows.Count;
for (int i = 0; i <= tb.Columns.Count - 1; i++)
{
if (dataGridView1.Columns[tb.Columns[i].ColumnName].Width > 0)
{
colCount = colCount + 1;
}
}
// 设置标题
Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, colCount]);
range.MergeCells = true;
xlApp.ActiveCell.FormulaR1C1 = this.Text;
xlApp.ActiveCell.Font.Size = 20;
xlApp.ActiveCell.Font.Bold = true;
xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter;
//查询条件
string bz = "";
bz = "累计DDD数:" + txtDDD.Text + " 出院人数:" + txtoutPatient.Text + " 平均住院天数:" + txtAvgOutpatient.Text +
" 同期收治患者人天数:" + txtpatientCount.Text;
string swhere = "抗菌药物使用强度:" + txtqiangdu.Text +
"在床使用抗菌药物人数:" + txtkjywrc.Text + " 在床人数:" + txtzcrs.Text.Trim() + " 抗菌药物使用率:" + txtksssyl.Text;
// 设置条件
Excel.Range rangeT = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colCount]);
rangeT.MergeCells = true;
object[,] objDataT = new object[1, 1];
range = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]);
objDataT[0, 0] = bz;
rangeT.Value2 = objDataT;
rangeT = xlSheet.get_Range(xlApp.Cells[3, 1], xlApp.Cells[3, colCount]);
rangeT.MergeCells = true;
object[,] objDataT2 = new object[1, 1];
objDataT2[0, 0] = swhere;
rangeT.Value2 = objDataT2;
// 创建缓存数据
object[,] objData = new object[RowCount + 1, colCount + 1];
// 获取列标题
for (int i = 0; i <= tb.Columns.Count - 1; i++)
{
if (dataGridView1.Columns[tb.Columns[i].ColumnName].Width > 0)
{
objData[0, colIndex++] = dataGridView1.Columns[tb.Columns[i].ColumnName].HeaderText;
}
}
// 获取数据
for (int i = 0; i <= tb.Rows.Count - 1; i++)
{
colIndex = 0;
for (int j = 0; j <= tb.Columns.Count - 1; j++)
{
if (dataGridView1.Columns[tb.Columns[j].ColumnName].Width > 0)
{
objData[i + 1, colIndex++] = "" + tb.Rows[i][j].ToString();
}
}
Application.DoEvents();
}
// 写入Excel
range = xlSheet.get_Range(xlApp.Cells[4, 1], xlApp.Cells[RowCount + 4, colCount]);
range.Value2 = objData;
//
xlApp.get_Range(xlApp.Cells[4, 1], xlApp.Cells[RowCount + 4, colCount]).Borders.LineStyle = 1;
//设置报表表格为最适应宽度
xlApp.get_Range(xlApp.Cells[4, 1], xlApp.Cells[RowCount + 4, colCount]).Select();
xlApp.get_Range(xlApp.Cells[4, 1], xlApp.Cells[RowCount + 4, colCount]).Columns.AutoFit();
xlApp.get_Range(xlApp.Cells[4, 1], xlApp.Cells[RowCount + 4, colCount]).Font.Size = 9;
xlApp.Visible = true;
}
catch (System.Exception err)
{
MessageBox.Show(err.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
this.Cursor = Cursors.Arrow;
}
}
//private static List<string> AvailableColumns = new List<string>();
public bool Export(DataGridView myview)
{
if (myview.Rows.Count < 1)
{
BaseClass.Function.MessageWarning("没有数据可供导出！");
return false;
}
try
{
Excel.ApplicationClass MyExcel = new Excel.ApplicationClass();
MyExcel.Visible = true;
if (MyExcel == null)
{
MessageBox.Show("EXCEL无法启动！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return false;
}
Excel.Workbooks MyWorkBooks = MyExcel.Workbooks;
Excel.Workbook MyWorkBook = MyWorkBooks.Add(Missing.Value);
Excel.Worksheet MyWorkSheet = (Excel.Worksheet)MyWorkBook.Worksheets[1];
//AvailableColumns.Clear();
//foreach (DataGridViewColumn c in myview.Columns)
//{
// if (!c.Visible) continue;
// AvailableColumns.Add(c.HeaderText);
//}
//int count = AvailableColumns.Count+1;
string column = "";
int count = myview.ColumnCount;
if (count > 1 && count <= 26)
{
char col = (char)(65 + count - 1);
column = col.ToString() + "1";
}
if (count > 27 && count <= 52)
{
char col = (char)(65 + count - 26);
column = "A" + col.ToString() + "1";
}
Excel.Range MyRange = MyWorkSheet.get_Range("A1", column);
object[] MyHeader = new object[count];
for (int i = 0; i < count; i++)
{
MyHeader[i] = myview.Columns[i].HeaderText.ToString();
//MyHeader[i] = AvailableColumns[i].ToString();
}
MyRange.Value2 = MyHeader;
if (myview.Rows.Count > 0)
{
MyRange = MyWorkSheet.get_Range("A2", Missing.Value);
object[,] MyData = new Object[myview.Rows.Count, count];
for (int i = 0; i < myview.Rows.Count; i++)
{
for (int j = 0; j < count; j++)
{
MyData[i, j] = myview.Rows[i].Cells[j].Value.ToString();
}
}
MyRange = MyRange.get_Resize(myview.Rows.Count, count);
MyRange.Value2 = MyData;
MyRange.EntireColumn.AutoFit();
}
//MyExcel = null;
}
catch (Exception Err)
{
MessageBox.Show("调用EXCEL程序时出现错误！" + Err.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
return true;
}
/// <summary>
/// 导出EXCEL
/// </summary>
/// <param name="ds"></param>
/// <param name="fileName"></param>
public static void ExportToExcel(DataSet ds, string fileName)
{
OleDbConnection conn = null;
try
{
//加工文件名
string[] strTemp = fileName.Split('\\');
string NewName = string.Empty;
foreach (string s in strTemp)
{
NewName = NewName + "\\\\" + s;
}
NewName = NewName.TrimStart('\\');
if (ds.Tables.Count == 0)
{
return;
}
Excel._Application xlApp = new Excel.ApplicationClass();
Excel._Workbook xlBook = xlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
if (ds.Tables.Count > 1)
{
int i = ds.Tables.Count;
while (i > 1)
{
xlBook.Sheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
i--;
}
}
int loop = 1;
foreach (DataTable dt2 in ds.Tables)
{
FillExcelCaption((Excel._Worksheet)xlBook.Worksheets[loop], dt2);
loop++;
}
xlBook.SaveCopyAs(fileName);
xlBook.Saved = true;
xlApp.Quit();
Kill((Excel.Application)xlApp);
//取数据表
//string con = string.Format("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = {0}; Extended Properties = Excel 8.0", NewName);
//conn = new OleDbConnection(con);
//conn.Open();
ExcelConn(NewName, ref conn);
System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
foreach (DataTable dt in ds.Tables)
{
StringBuilder sql = new StringBuilder();
sql.AppendFormat("INSERT INTO [{0}$] (", dt.TableName);
for (int i = 0; i < dt.Columns.Count; i++)
{
if (!string.IsNullOrEmpty(dt.Columns[i].Caption))
{
sql.AppendFormat("[{0}],", dt.Columns[i].Caption);
}
}
sql.Remove(sql.Length - 1, 1);
sql.Append(") VALUES ");
string head = sql.ToString();
for (int j = 0; j < dt.Rows.Count; j++)
{
sql = new StringBuilder();
sql.Append(head);
sql.Append(" (");
for (int k = 0; k < dt.Columns.Count; k++)
{
if (!string.IsNullOrEmpty(dt.Columns[k].Caption))
{
if (dt.Columns[k].DataType.Equals(typeof(String)) ||
dt.Columns[k].DataType.Equals(typeof(DateTime)) ||
dt.Columns[k].DataType.Equals(typeof(Char)))
{
sql.AppendFormat("'{0}'", dt.Rows[j][k].ToString());
}
else
{
sql.AppendFormat("{0}", dt.Rows[j][k].ToString().Trim().Equals(string.Empty) ? 0 : Convert.ToDecimal(dt.Rows[j][k]));
}
sql.Append(",");
}
}
sql.Remove(sql.Length - 1, 1);
sql.Append(") ");
cmd.CommandText = sql.ToString();
cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn != null)
{
conn.Close();
conn.Dispose();
}
}
}
private void ExportExcel(string batch_id)
{
string folderOut = "D:\\KTT\\AUTO_CIF\\";
//Tao thu muc
if (!Directory.Exists(folderOut))
{
Directory.CreateDirectory(folderOut);
}
string sql = "", err = "";
Excel.ApplicationClass excel = new Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
// excel.Name = "FILENHAP";
excel.Cells[1, 1] = "STT";// [Dòng, cột]
excel.Cells[1, 2] = "CIF";
excel.Cells[1, 3] = "SDT";
// excel.Application.Worksheets.Add("FILENHAP");
sql = " select a.client_code,a.dien_thoai ";
sql += " from "+Businessbp.executedb.owner+"NAB_CIF_CORE_GENERATE a ";
sql += " where 1=1 ";
sql += " and a.batch_id ='" + batch_id.Trim() + "' ";
sql += " and nvl(trim(a.client_code),0) <>'0'";
sql += " and nvl(a.customercare_card_flag,0) <> '1' ";
sql += " order by a.client_code ";
DataTable dtExport = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
Insert_Exception("Err ExportExcel", "", "", err);
}
else
{
if (dtExport.Rows.Count > 0)
{
for (int i = 0; i < dtExport.Rows.Count; i++)
{
try
{
string STT = "", CIF = "", SDT = "";
STT = (i + 1).ToString();
CIF = dtExport.Rows[i]["client_code"].ToString();
SDT = dtExport.Rows[i]["dien_thoai"].ToString();
for (int j = 0; j < 3; j++)
{
((Excel.Range)excel.Cells[i + 2, j + 1]).NumberFormat = "@";//.Font.Bold = true;
}
//Export excel
excel.Cells[i + 2, 1] = "" + STT;// [Dòng, cột]
excel.Cells[i + 2, 2] = "" + CIF;
excel.Cells[i + 2, 3] = "" + SDT;
Update_Database(batch_id.Trim(), CIF);
}
catch(Exception ex)
{
Insert_Exception("Err ExportExcel STT: "+(i + 1).ToString(),"","", ex.ToString() );
}
}
}
}
//----------------
excel.Visible = true;
Excel._Worksheet worksheet = (Excel._Worksheet)excel.ActiveSheet;
worksheet.Activate();
worksheet.SaveAs("D:\\KTT\\AUTO_CIF\\" + "CustomerCare_File_" + DateTime.Now.ToString("ddMMyyyy") + DateTime.Now.ToString("hh24miss") + ".xls");
}
private void 导出EXCELToolStripMenuItem_Click(object sender, EventArgs e)
{
try
{
DataView dv;
DataTable tb;
System.Windows.Forms.DataGridView dgv;
if (tabControl1.SelectedTab == tabPage1)
{
dgv = dgvyjsq;
dv = (DataView)dgv.DataSource;
tb = dv.Table;
}
else
{
dgv = dataGridView2;
tb = (DataTable)dgv.DataSource;
}
// 创建Excel对象
Excel.Application xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
MessageBox.Show("Excel无法启动");
return;
}
// 创建Excel工作薄
Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];
// 列索引，行索引，总列数，总行数
int colIndex = 0;
int RowIndex = 0;
int colCount = 0;
int RowCount = tb.Rows.Count + 1;
for (int i = 0; i <= tb.Columns.Count - 1; i++)
{
if (dgv.Columns[i].Visible == true)
{
colCount = colCount + 1;
}
}
//查询条件
string swhere = "";
if (rdosqrq.Checked == true)
{
swhere = " 申请日期从:" + dtpsqrq1.Value.ToString() + " 到 " + dtpsqrq2.Value.ToString();
}
else
{
swhere = " 确认日期从:" + dtpqrrq1.Value.ToString() + " 到 " + dtpqrrq2.Value.ToString();
}
// 设置标题
Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, colCount]);
range.MergeCells = true;
xlApp.ActiveCell.FormulaR1C1 = "医技确认情况表(" + InstanceForm.BCurrentDept.DeptName + ")";
xlApp.ActiveCell.Font.Size = 20;
xlApp.ActiveCell.Font.Bold = true;
xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter;
// 设置条件
Excel.Range range1 = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colCount]);
range1.MergeCells = true;
//xlApp.ActiveCell.FormulaR1C1 = swhere;
//xlApp.ActiveCell.Font.Size = 20;
//xlApp.ActiveCell.Font.Bold = true;
//xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter;
// 创建缓存数据
object[,] objData = new object[RowCount + 1, colCount + 1];
// 获取列标题
for (int i = 0; i <= tb.Columns.Count - 1; i++)
{
if (dgv.Columns[i].Visible == true)
{
objData[1, colIndex++] = dgv.Columns[i].HeaderText;
}
}
// 获取数据
objData[0, 0] = swhere;
for (int i = 0; i <= tb.Rows.Count - 1; i++)
{
colIndex = 0;
for (int j = 0; j <= tb.Columns.Count - 1; j++)
{
if (dgv.Columns[j].Visible == true)
{
if (tb.Columns[j].Caption == "住院号")
{
objData[i + 2, colIndex++] = "'" + tb.Rows[i][j].ToString();
}
else
{
objData[i + 2, colIndex++] = "" + tb.Rows[i][j].ToString();
}
}
}
Application.DoEvents();
}
// 写入Excel
range = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]);
range.Value2 = objData;
//
xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[RowCount + 2, colCount]).Borders.LineStyle = 1;
//设置报表表格为最适应宽度
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Select();
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Columns.AutoFit();
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Font.Size = 9;
xlApp.Visible = true;
}
catch (System.Exception err)
{
MessageBox.Show(err.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void ExportCIFUpload(string batch_id, string branch)
{
if (batch_id.Trim() == "")
{
MessageBox.Show("Chưa chọn Batch");
return;
}
if (branch.Trim() == "")
{
MessageBox.Show("Chưa chọn Branch");
return;
}
string folderOut = "D:\\KTT\\AUTO_CIF\\";
//Tao thu muc
if (!Directory.Exists(folderOut))
{
Directory.CreateDirectory(folderOut);
}
string sql = "", err = "";
Excel.ApplicationClass excel = new Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
Excel.ApplicationClass excel_persional = new Excel.ApplicationClass();
excel_persional.Application.Workbooks.Add(true);
#region KhaiBaoTenCotExcel_CIFUpload
excel.Cells[1, 1] = " ";
excel.Cells[1, 2] = "SOURCE_CODE";
excel.Cells[1, 3] = "MAINTENANCE_SEQ_NO";
excel.Cells[1, 4] = "CUSTOMER_NO";
excel.Cells[1, 5] = "CUSTOMER_TYPE";
excel.Cells[1, 6] = "CUSTOMER_NAME1";
excel.Cells[1, 7] = "ADDRESS_LINE1";
excel.Cells[1, 8] = "ADDRESS_LINE3";
excel.Cells[1, 9] = "ADDRESS_LINE2";
excel.Cells[1, 10] = "ADDRESS_LINE4";
excel.Cells[1, 11] = "COUNTRY";
excel.Cells[1, 12] = "SHORT_NAME";
excel.Cells[1, 13] = "NATIONALITY";
excel.Cells[1, 14] = "LANGUAGE";
excel.Cells[1, 15] = "EXPOSURE_COUNTRY";
excel.Cells[1, 16] = "LOCAL_BRANCH";
excel.Cells[1, 17] = "LIABILITY_NO";
excel.Cells[1, 18] = "UNIQUE_ID_NAME";
excel.Cells[1, 19] = "UNIQUE_ID_VALUE";
excel.Cells[1, 20] = "FROZEN";
excel.Cells[1, 21] = "DECEASED";
excel.Cells[1, 22] = "WHEREABOUTS_UNKNOWN";
excel.Cells[1, 23] = "CUSTOMER_CATEGORY";
excel.Cells[1, 24] = "HO_AC_NO";
excel.Cells[1, 25] = "FX_CUST_CLEAN_RISK_LIMIT";
excel.Cells[1, 26] = "OVERALL_LIMIT";
excel.Cells[1, 27] = "FX_CLEAN_RISK_LIMIT";
excel.Cells[1, 28] = "CREDIT_RATING";
excel.Cells[1, 29] = "REVISION_DATE";
excel.Cells[1, 30] = "LIMIT_CCY";
excel.Cells[1, 31] = "CAS_CUST";
excel.Cells[1, 32] = "CONVERSION_STATUS_FLAG";
excel.Cells[1, 33] = "ERR_MSG";
excel.Cells[1, 34] = "SEC_CUST_CLEAN_RISK_LIMIT";
excel.Cells[1, 35] = "SEC_CLEAN_RISK_LIMIT";
excel.Cells[1, 36] = "SEC_CUST_PSTL_RISK_LIMIT";
excel.Cells[1, 37] = "SEC_PSTL_RISK_LIMIT";
excel.Cells[1, 38] = "SWIFT_CODE";
excel.Cells[1, 39] = "LIAB_BR";
excel.Cells[1, 40] = "LIAB_NODE";
excel.Cells[1, 41] = "PAST_DUE_FLAG";
excel.Cells[1, 42] = "DEFAULT_MEDIA";
excel.Cells[1, 43] = "LOC_CODE";
excel.Cells[1, 44] = "SHORT_NAME2";
excel.Cells[1, 45] = "SSN";
excel.Cells[1, 46] = "ACTION_CODE";
excel.Cells[1, 47] = "UTILITY_PROVIDER";
excel.Cells[1, 48] = "UTILITY_PROVIDER_ID";
excel.Cells[1, 49] = "RISK_PROFILE";
excel.Cells[1, 50] = "DEBTOR_CATEGORY";
excel.Cells[1, 51] = "UDF_1";
excel.Cells[1, 52] = "UDF_2";
excel.Cells[1, 53] = "UDF_3";
excel.Cells[1, 54] = "UDF_4";
excel.Cells[1, 55] = "UDF_5";
excel.Cells[1, 56] = "MAILERS_REQUIRED";
excel.Cells[1, 57] = "AML_CUSTOMER_GRP";
excel.Cells[1, 58] = "AML_REQUIRED";
excel.Cells[1, 59] = "GROUP_CODE";
excel.Cells[1, 60] = "EXPOSURE_CATEGORY";
excel.Cells[1, 61] = "CUST_CLASSIFICATION";
excel.Cells[1, 62] = "CIF_STATUS";
excel.Cells[1, 63] = "CIF_STATUS_SINCE";
excel.Cells[1, 64] = "INTRODUCER";
excel.Cells[1, 65] = "FT_ACCTING_AS_OF";
excel.Cells[1, 66] = "CUST_UNADVISED";
excel.Cells[1, 67] = "LIAB_UNADVISED";
excel.Cells[1, 68] = "TAX_GROUP";
excel.Cells[1, 69] = "CONSOL_TAX_CERT_REQD";
excel.Cells[1, 70] = "INDIVIDUAL_TAX_CERT_REQD";
excel.Cells[1, 71] = "FX_NETTING_CUSTOMER";
excel.Cells[1, 72] = "CLS_PARTICIPANT";
excel.Cells[1, 73] = "CLS_CCY_ALLOWED";
excel.Cells[1, 74] = "RISK_CATEGORY";
excel.Cells[1, 75] = "FAX_NUMBER";
excel.Cells[1, 76] = "EXT_REF_NO";
excel.Cells[1, 77] = "CRM_CUSTOMER";
excel.Cells[1, 78] = "ISSUER_CUSTOMER";
excel.Cells[1, 79] = "TREASURY_CUSTOMER";
excel.Cells[1, 80] = "CHARGE_GROUP";
excel.Cells[1, 81] = "FULL_NAME";
excel.Cells[1, 82] = "MAKER_ID";
excel.Cells[1, 83] = "MAKER_DT_STAMP";
excel.Cells[1, 84] = "CHECKER_ID";
excel.Cells[1, 85] = "CHECKER_DT_STAMP";
excel.Cells[1, 86] = "CUST_CLG_GROUP";
excel.Cells[1, 87] = "CHK_DIGIT_VALID_REQD";
excel.Cells[1, 88] = "ALG_ID";
excel.Cells[1, 89] = "WHT_PCT";
excel.Cells[1, 90] = "RP_CUSTOMER";
excel.Cells[1, 91] = "GENERATE_MT920";
excel.Cells[1, 92] = "SOURCE_SEQ_NO";
excel.Cells[1, 93] = "BRANCH_CODE";
#endregion KhaiBaoTenCotExcel_CIFUpload
#region KhaiBaoTenCotExcel_CIF_Persional_upload
excel_persional.Cells[1, 1] = " ";
excel_persional.Cells[1, 2] = "CUSTOMER_PREFIX";
excel_persional.Cells[1, 3] = "FIRST_NAME";
excel_persional.Cells[1, 4] = "MIDDLE_NAME";
excel_persional.Cells[1, 5] = "LAST_NAME";
excel_persional.Cells[1, 6] = "DATE_OF_BIRTH";
excel_persional.Cells[1, 7] = "LEGAL_GUARDIAN";
excel_persional.Cells[1, 8] = "MINOR";
excel_persional.Cells[1, 9] = "SEX";
excel_persional.Cells[1, 10] = "P_NATIONAL_ID";
excel_persional.Cells[1, 11] = "PASSPORT_NO";
excel_persional.Cells[1, 12] = "PPT_ISS_DATE";
excel_persional.Cells[1, 13] = "PPT_EXP_DATE";
excel_persional.Cells[1, 14] = "D_ADDRESS1";
excel_persional.Cells[1, 15] = "D_ADDRESS2";
excel_persional.Cells[1, 16] = "D_ADDRESS3";
excel_persional.Cells[1, 17] = "TELEPHONE";
excel_persional.Cells[1, 18] = "FAX";
excel_persional.Cells[1, 19] = "E_MAIL";
excel_persional.Cells[1, 20] = "P_ADDRESS1";
excel_persional.Cells[1, 21] = "P_ADDRESS3";
excel_persional.Cells[1, 22] = "P_ADDRESS2";
excel_persional.Cells[1, 23] = "CUSTOMER_NO";
excel_persional.Cells[1, 24] = "D_COUNTRY";
excel_persional.Cells[1, 25] = "P_COUNTRY";
excel_persional.Cells[1, 26] = "RESIDENT_STATUS";
excel_persional.Cells[1, 27] = "CONVERSION_STATUS_FLAG";
excel_persional.Cells[1, 28] = "ERR_MSG";
excel_persional.Cells[1, 29] = "MAINTENANCE_SEQ_NO";
excel_persional.Cells[1, 30] = "CUSTOMER_PREFIX1";
excel_persional.Cells[1, 31] = "CUSTOMER_PREFIX2";
excel_persional.Cells[1, 32] = "SOURCE_SEQ_NO";
excel_persional.Cells[1, 33] = "BRANCH_CODE";
excel_persional.Cells[1, 34] = "SOURCE_CODE";
#endregion KhaiBaoTenCotExcel_CIF_Persional_upload
//excel.Cells[i + 2, 1] = "Tại thời điểm " + dtTo.Text;
//((Excel.Range)excel.Cells[i + 2, 1]).Font.Bold = true;
sql = ""; err = "";
sql += " select BATCH_ID ,HO ||' '||TEN HO_TEN, HO ,TEN,GIOI_TINH ,to_char(NGAY_SINH,'mm/dd/yyyy')NGAY_SINH , MSSV ,LOP ,NGANH,CMND";
sql += " ,to_char(NGAY_CAP,'mm/dd/yyyy') NGAY_CAP ,to_char(ADD_MONTHS(a.ngay_cap,180),'mm/dd/yyyy') as NgayHieuLuc,NOI_CAP,DIA_CHI1,DIA_CHI2,DIA_CHI3,DIA_CHI4,DIEN_THOAI";
sql += " ,EMAIL,CLIENT_CODE,CARD_NUMBER,CIF_UPLOAD_FLAG,CIF_PERSIONAL_UPLOAD_FLAG,CUSTOMERCARE_CARD_FLAG ,USER_CREATE";
sql += " ,BRANCH_EXPORT ";
sql += " from "+Businessbp.executedb.owner+"NAB_CIF_CORE_GENERATE a";
sql += " where a.batch_id='"+batch_id+"'";
sql += " and nvl(a.cif_upload_flag,'0')<>'1'";
DataTable dtExport = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
else
{
int stt = 1;
if (dtExport.Rows.Count > 0)
{
for (int i = 0; i < dtExport.Rows.Count; i++)
{
#region KhaiBaoBien_CIFUpload
string SOURCE_CODE = "NABCIF";
string MAINTENANCE_SEQ_NO = "" + stt.ToString();
string CUSTOMER_NO = "";
string CUSTOMER_TYPE = "I";
string CUSTOMER_NAME1 = dtExport.Rows[i]["HO_TEN"].ToString();
string ADDRESS_LINE1 = dtExport.Rows[i]["DIA_CHI1"].ToString();
string ADDRESS_LINE3 = dtExport.Rows[i]["DIA_CHI2"].ToString();
string ADDRESS_LINE2 = dtExport.Rows[i]["DIA_CHI3"].ToString();
string ADDRESS_LINE4 = dtExport.Rows[i]["DIA_CHI4"].ToString();
string COUNTRY = "VNM";
string SHORT_NAME = "" + dtExport.Rows[i]["CMND"].ToString();
string NATIONALITY = "VNM";
string LANGUAGE = "VIE";
string EXPOSURE_COUNTRY = "";
string LOCAL_BRANCH = ""+branch.Trim().Substring(1);
string LIABILITY_NO = "";
string UNIQUE_ID_NAME = "CMND";
string UNIQUE_ID_VALUE = SHORT_NAME;//CMND
string FROZEN = "";
string DECEASED = "";
string WHEREABOUTS_UNKNOWN = "";
string CUSTOMER_CATEGORY = "CN-KTCT";
string HO_AC_NO = "";
string FX_CUST_CLEAN_RISK_LIMIT = "";
string OVERALL_LIMIT = "";
string FX_CLEAN_RISK_LIMIT = "";
string CREDIT_RATING = "";
string REVISION_DATE = "";
string LIMIT_CCY = "VND";
string CAS_CUST = "";
string CONVERSION_STATUS_FLAG = "U";
string ERR_MSG = "";
string SEC_CUST_CLEAN_RISK_LIMIT = "";
string SEC_CLEAN_RISK_LIMIT = "";
string SEC_CUST_PSTL_RISK_LIMIT = "";
string SEC_PSTL_RISK_LIMIT = "";
string SWIFT_CODE = "";
string LIAB_BR = "";
string LIAB_NODE = "";
string PAST_DUE_FLAG = "";
string DEFAULT_MEDIA = "";
string LOC_CODE = "";
string SHORT_NAME2 = "";
string SSN = "";
string ACTION_CODE = "NEW";
string UTILITY_PROVIDER = "";
string UTILITY_PROVIDER_ID = "";
string RISK_PROFILE = "";
string DEBTOR_CATEGORY = "";
string UDF_1 = "";
string UDF_2 = "";
string UDF_3 = "";
string UDF_4 = "";
string UDF_5 = "";
string MAILERS_REQUIRED = "";
string AML_CUSTOMER_GRP = "";
string AML_REQUIRED = "";
string GROUP_CODE = "";
string EXPOSURE_CATEGORY = "";
string CUST_CLASSIFICATION = "";
string CIF_STATUS = "";
string CIF_STATUS_SINCE = "";
string INTRODUCER = "";
string FT_ACCTING_AS_OF = "";
string CUST_UNADVISED = "N";
string LIAB_UNADVISED = "N";
string TAX_GROUP = "";
string CONSOL_TAX_CERT_REQD = "";
string INDIVIDUAL_TAX_CERT_REQD = "";
string FX_NETTING_CUSTOMER = "";
string CLS_PARTICIPANT = "";
string CLS_CCY_ALLOWED = "";
string RISK_CATEGORY = "";
string FAX_NUMBER = "";
string EXT_REF_NO = SHORT_NAME;//CMND
string CRM_CUSTOMER = "";
string ISSUER_CUSTOMER = "";
string TREASURY_CUSTOMER = "";
string CHARGE_GROUP = "";
string FULL_NAME = CUSTOMER_NAME1;/////
string MAKER_ID = "ATM_UP";
string MAKER_DT_STAMP = "";
string CHECKER_ID = "ATM_UP";
string CHECKER_DT_STAMP = "";
string CUST_CLG_GROUP = "";
string CHK_DIGIT_VALID_REQD = "";
string ALG_ID = "";
string WHT_PCT = "";
string RP_CUSTOMER = "";
string GENERATE_MT920 = "";
string SOURCE_SEQ_NO = "" + stt.ToString();
string BRANCH_CODE = "" + branch.Trim().Substring(1);
#endregion KhaiBaoBien_CIFUpload
#region KhaiBaoBien_persional
string per_CUSTOMER_PREFIX = "";
string per_FIRST_NAME = dtExport.Rows[i]["HO"].ToString();
string per_MIDDLE_NAME = "";
string per_LAST_NAME = dtExport.Rows[i]["TEN"].ToString();
string per_DATE_OF_BIRTH = "" + dtExport.Rows[i]["NGAY_SINH"].ToString();
string per_LEGAL_GUARDIAN = "";
string per_MINOR = "";
string per_SEX = dtExport.Rows[i]["GIOI_TINH"].ToString();
string per_P_NATIONAL_ID = "VNM";
string per_PASSPORT_NO = "" + dtExport.Rows[i]["CMND"].ToString(); ;
string per_PPT_ISS_DATE = "" + dtExport.Rows[i]["NGAY_CAP"].ToString();
// "'" + dtExport.Rows[i]["NGAY_SINH"].ToString();
string per_PPT_EXP_DATE = "" + dtExport.Rows[i]["NgayHieuLuc"].ToString();
string per_D_ADDRESS1 = dtExport.Rows[i]["DIA_CHI1"].ToString();
string per_D_ADDRESS2 = dtExport.Rows[i]["DIA_CHI2"].ToString();
string per_D_ADDRESS3 = dtExport.Rows[i]["DIA_CHI3"].ToString();
string per_TELEPHONE = dtExport.Rows[i]["DIEN_THOAI"].ToString();
string per_FAX = "";
string per_E_MAIL = "";
string per_P_ADDRESS1 = "";
string per_P_ADDRESS3 = "";
string per_P_ADDRESS2 = "";
string per_CUSTOMER_NO = "";
string per_D_COUNTRY = "VNM";
string per_P_COUNTRY = "VNM";
string per_RESIDENT_STATUS = "";
string per_CONVERSION_STATUS_FLAG = "U";
string per_ERR_MSG = "";
string per_MAINTENANCE_SEQ_NO = "'" + stt.ToString();
string per_CUSTOMER_PREFIX1 = "";
string per_CUSTOMER_PREFIX2 = "";
string per_SOURCE_SEQ_NO = per_MAINTENANCE_SEQ_NO;
string per_BRANCH_CODE = "" + branch.Trim().Substring(1);
string per_SOURCE_CODE = "NABCIF";
#endregion KhaiBaoBien_persional
//Dat edit 20/12/2012 for format cell in excel to TEXT
for (int j = 0; j < 93; j++)
{
((Excel.Range)excel.Cells[i + 2, j+1]).NumberFormat = "@";//.Font.Bold = true;
}
for (int k = 0; k < 34; k++)
{
((Excel.Range)excel_persional.Cells[i + 2, k + 1]).NumberFormat = "@";//.Font.Bold = true;
}
//end Dat edit 20/12/2012 for format cell in excel to TEXT
//Export excel
#region Export excel CIF Upload
excel.Cells[i + 2, 1] = "" + stt.ToString();
excel.Cells[i + 2, 2] = SOURCE_CODE;
excel.Cells[i + 2, 3] = MAINTENANCE_SEQ_NO;
excel.Cells[i + 2, 4] = CUSTOMER_NO;
excel.Cells[i + 2, 5] = CUSTOMER_TYPE;
excel.Cells[i + 2, 6] = CUSTOMER_NAME1;
excel.Cells[i + 2, 7] = ADDRESS_LINE1;
excel.Cells[i + 2, 8] = ADDRESS_LINE3;
excel.Cells[i + 2, 9] = ADDRESS_LINE2;
excel.Cells[i + 2, 10] = ADDRESS_LINE4;
// ((Excel.Range)excel.Cells[i + 2, 11]).NumberFormat = "@";//.Font.Bold = true;
excel.Cells[i + 2, 11] = COUNTRY;
// ((Excel.Range)excel.Cells[i + 2, 12]).NumberFormat = "@";//.Font.Bold = true;
excel.Cells[i + 2, 12] = SHORT_NAME;
excel.Cells[i + 2, 13] = NATIONALITY;
excel.Cells[i + 2, 14] = LANGUAGE;
excel.Cells[i + 2, 15] = EXPOSURE_COUNTRY;
// ((Excel.Range)excel.Cells[i + 2, 16]).NumberFormat = "@";//.Font.Bold = true;
excel.Cells[i + 2, 16] = LOCAL_BRANCH;
excel.Cells[i + 2, 17] = LIABILITY_NO;
// ((Excel.Range)excel.Cells[i + 2, 18]).NumberFormat = "@";//.Font.Bold = true;
excel.Cells[i + 2, 18] = UNIQUE_ID_NAME;
// excel.Range[i + 2, 19].NumberFormat = "@";//Format text
// ((Excel.Range)excel.Cells[i + 2, 19]).NumberFormat = "@";//.Font.Bold = true;
excel.Cells[i + 2, 19] = UNIQUE_ID_VALUE;
excel.Cells[i + 2, 20] = FROZEN;
excel.Cells[i + 2, 21] = DECEASED;
excel.Cells[i + 2, 22] = WHEREABOUTS_UNKNOWN;
excel.Cells[i + 2, 23] = CUSTOMER_CATEGORY;
excel.Cells[i + 2, 24] = HO_AC_NO;
excel.Cells[i + 2, 25] = FX_CUST_CLEAN_RISK_LIMIT;
// ((Excel.Range)excel.Cells[i + 2, 26]).NumberFormat = "@";//.Font.Bold = true;
excel.Cells[i + 2, 26] = OVERALL_LIMIT;
excel.Cells[i + 2, 27] = FX_CLEAN_RISK_LIMIT;
excel.Cells[i + 2, 28] = CREDIT_RATING;
excel.Cells[i + 2, 29] = REVISION_DATE;
excel.Cells[i + 2, 30] = LIMIT_CCY;
excel.Cells[i + 2, 31] = CAS_CUST;
excel.Cells[i + 2, 32] = CONVERSION_STATUS_FLAG;
excel.Cells[i + 2, 33] = ERR_MSG;
excel.Cells[i + 2, 34] = SEC_CUST_CLEAN_RISK_LIMIT;
excel.Cells[i + 2, 35] = SEC_CLEAN_RISK_LIMIT;
excel.Cells[i + 2, 36] = SEC_CUST_PSTL_RISK_LIMIT;
excel.Cells[i + 2, 37] = SEC_PSTL_RISK_LIMIT;
excel.Cells[i + 2, 38] = SWIFT_CODE;
excel.Cells[i + 2, 39] = LIAB_BR;
excel.Cells[i + 2, 40] = LIAB_NODE;
excel.Cells[i + 2, 41] = PAST_DUE_FLAG;
excel.Cells[i + 2, 42] = DEFAULT_MEDIA;
excel.Cells[i + 2, 43] = LOC_CODE;
// excel.Range[i + 2, 44].NumberFormat = "@";//Format text
// ((Excel.Range)excel.Cells[i + 2, 44]).NumberFormat = "@";//.Font.Bold = true;
excel.Cells[i + 2, 44] = SHORT_NAME2;
excel.Cells[i + 2, 45] = SSN;
excel.Cells[i + 2, 46] = ACTION_CODE;
excel.Cells[i + 2, 47] = UTILITY_PROVIDER;
excel.Cells[i + 2, 48] = UTILITY_PROVIDER_ID;
excel.Cells[i + 2, 49] = RISK_PROFILE;
excel.Cells[i + 2, 50] = DEBTOR_CATEGORY;
excel.Cells[i + 2, 51] = UDF_1;
excel.Cells[i + 2, 52] = UDF_2;
excel.Cells[i + 2, 53] = UDF_3;
excel.Cells[i + 2, 54] = UDF_4;
excel.Cells[i + 2, 55] = UDF_5;
excel.Cells[i + 2, 56] = MAILERS_REQUIRED;
excel.Cells[i + 2, 57] = AML_CUSTOMER_GRP;
excel.Cells[i + 2, 58] = AML_REQUIRED;
excel.Cells[i + 2, 59] = GROUP_CODE;
excel.Cells[i + 2, 60] = EXPOSURE_CATEGORY;
excel.Cells[i + 2, 61] = CUST_CLASSIFICATION;
excel.Cells[i + 2, 62] = CIF_STATUS;
excel.Cells[i + 2, 63] = CIF_STATUS_SINCE;
excel.Cells[i + 2, 64] = INTRODUCER;
excel.Cells[i + 2, 65] = FT_ACCTING_AS_OF;
excel.Cells[i + 2, 66] = CUST_UNADVISED;
excel.Cells[i + 2, 67] = LIAB_UNADVISED;
excel.Cells[i + 2, 68] = TAX_GROUP;
excel.Cells[i + 2, 69] = CONSOL_TAX_CERT_REQD;
excel.Cells[i + 2, 70] = INDIVIDUAL_TAX_CERT_REQD;
excel.Cells[i + 2, 71] = FX_NETTING_CUSTOMER;
excel.Cells[i + 2, 72] = CLS_PARTICIPANT;
excel.Cells[i + 2, 73] = CLS_CCY_ALLOWED;
excel.Cells[i + 2, 74] = RISK_CATEGORY;
excel.Cells[i + 2, 75] = FAX_NUMBER;
excel.Cells[i + 2, 76] = EXT_REF_NO;
excel.Cells[i + 2, 77] = CRM_CUSTOMER;
excel.Cells[i + 2, 78] = ISSUER_CUSTOMER;
excel.Cells[i + 2, 79] = TREASURY_CUSTOMER;
excel.Cells[i + 2, 80] = CHARGE_GROUP;
excel.Cells[i + 2, 81] = FULL_NAME;
excel.Cells[i + 2, 82] = MAKER_ID;
excel.Cells[i + 2, 83] = MAKER_DT_STAMP;
excel.Cells[i + 2, 84] = CHECKER_ID;
excel.Cells[i + 2, 85] = CHECKER_DT_STAMP;
excel.Cells[i + 2, 86] = CUST_CLG_GROUP;
excel.Cells[i + 2, 87] = CHK_DIGIT_VALID_REQD;
excel.Cells[i + 2, 88] = ALG_ID;
excel.Cells[i + 2, 89] = WHT_PCT;
excel.Cells[i + 2, 90] = RP_CUSTOMER;
excel.Cells[i + 2, 91] = GENERATE_MT920;
excel.Cells[i + 2, 92] = SOURCE_SEQ_NO;
// excel.Range[i + 2, 93].NumberFormat = "@";//Format text
excel.Cells[i + 2, 93] = BRANCH_CODE;
#endregion Export excel CIF Upload
#region Export excel CIF Persional Upload
//excel_persional.Columns.NumberFormat = "@";
excel_persional.Cells[i + 2, 1] = "" + stt.ToString();
excel_persional.Cells[i + 2, 2] = per_CUSTOMER_PREFIX;
excel_persional.Cells[i + 2, 3] = per_FIRST_NAME;
excel_persional.Cells[i + 2, 4] = per_MIDDLE_NAME;
excel_persional.Cells[i + 2, 5] = per_LAST_NAME;
excel_persional.Cells[i + 2, 6] = per_DATE_OF_BIRTH;
excel_persional.Cells[i + 2, 7] = per_LEGAL_GUARDIAN;
excel_persional.Cells[i + 2, 8] = per_MINOR;
excel_persional.Cells[i + 2, 9] = per_SEX;
excel_persional.Cells[i + 2, 10] = per_P_NATIONAL_ID;
// excel_persional.Range[i + 2, 11].NumberFormat = "@";//Format text
excel_persional.Cells[i + 2, 11] = per_PASSPORT_NO;
// excel_persional.Range[i + 2, 12].NumberFormat = "@";//Format text
excel_persional.Cells[i + 2, 12] = per_PPT_ISS_DATE;
excel_persional.Cells[i + 2, 13] = per_PPT_EXP_DATE;
excel_persional.Cells[i + 2, 14] = per_D_ADDRESS1;
excel_persional.Cells[i + 2, 15] = per_D_ADDRESS2;
excel_persional.Cells[i + 2, 16] = per_D_ADDRESS3;
// excel_persional.Range[i + 2, 17].NumberFormat = "@";//Format text
excel_persional.Cells[i + 2, 17] = per_TELEPHONE;
// excel_persional.Range[i + 2, 18].NumberFormat = "@";//Format text
excel_persional.Cells[i + 2, 18] = per_FAX;
excel_persional.Cells[i + 2, 19] = per_E_MAIL;
excel_persional.Cells[i + 2, 20] = per_P_ADDRESS1;
excel_persional.Cells[i + 2, 21] = per_P_ADDRESS3;
excel_persional.Cells[i + 2, 22] = per_P_ADDRESS2;
excel_persional.Cells[i + 2, 23] = per_CUSTOMER_NO;
excel_persional.Cells[i + 2, 24] = per_D_COUNTRY;
excel_persional.Cells[i + 2, 25] = per_P_COUNTRY;
excel_persional.Cells[i + 2, 26] = per_RESIDENT_STATUS;
excel_persional.Cells[i + 2, 27] = per_CONVERSION_STATUS_FLAG;
excel_persional.Cells[i + 2, 28] = per_ERR_MSG;
excel_persional.Cells[i + 2, 29] = per_MAINTENANCE_SEQ_NO;
excel_persional.Cells[i + 2, 30] = per_CUSTOMER_PREFIX1;
excel_persional.Cells[i + 2, 31] = per_CUSTOMER_PREFIX2;
excel_persional.Cells[i + 2, 32] = per_SOURCE_SEQ_NO;
// excel_persional.Range[i + 2, 33].NumberFormat = "@";//Format text
excel_persional.Cells[i + 2, 33] = per_BRANCH_CODE;
excel_persional.Cells[i + 2, 34] = per_SOURCE_CODE;
#endregion Export excel CIF Persional Upload
stt += 1;
}
sql = ""; err = "";
sql += " update " + Businessbp.executedb.owner + "NAB_CIF_CORE_GENERATE a set CIF_UPLOAD_FLAG='1',CIF_PERSIONAL_UPLOAD_FLAG='1'";
sql += " ,BRANCH_EXPORT='"+branch+"', USER_EXTRACT_CNTT='" + Businessbp.executedb.Usrid + "', DATE_EXTRACT_CNTT=sysdate ";
sql += " where a.batch_id='" + batch_id + "'";
sql += " and nvl(a.cif_upload_flag,'0')<>'1'";
Businessbp.executedb.ExecuteDML(sql, ref err);
if (err != "")
{ MessageBox.Show(err); return; }
else
MessageBox.Show("Export successful!!!");
}
}
//----------------
excel.Visible = true;
Excel._Worksheet worksheet = (Excel._Worksheet)excel.ActiveSheet;
worksheet.Activate();
worksheet.SaveAs("D:\\KTT\\AUTO_CIF\\" + "CIF_UPLOAD_" + DateTime.Now.ToString("ddMMyyyy") + DateTime.Now.ToString("hh24miss") + ".xls");
excel_persional.Visible = true;
Excel._Worksheet worksheet2 = (Excel._Worksheet)excel_persional.ActiveSheet;
worksheet2.Activate();
worksheet2.SaveAs("D:\\KTT\\AUTO_CIF\\" + "cif_persional_upload_" + DateTime.Now.ToString("ddMMyyyy") + DateTime.Now.ToString("hh24miss") + ".xls");
}
private void btn_export_Click(object sender, EventArgs e)
{
//Export to excel
try
{
Excel.ApplicationClass excel = new Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
System.Data.DataTable table = dt;
excel.Cells[1, 1] = "Print Date :";
excel.Cells[1, 2] = DateTime.Now;
excel.Cells[3, 1] = " S2S DETAIL TRANSACTION REPORT";
excel.Cells[4, 1] = " From Date :";
excel.Cells[4, 2] = t1;
excel.Cells[4, 3] = " To Date :";
excel.Cells[4, 4] = t2;
excel.Cells[5, 1] = "CARD NUMBER";
excel.Cells[5, 2] = "TYPE TRAN";
excel.Cells[5, 3] = "DATE";
excel.Cells[5, 4] = "TIME";
excel.Cells[5, 5] = "AMOUNT";
excel.Cells[5, 6] = "TERM.ID";
excel.Cells[5, 7] = "TERM.LOCATION";
excel.Cells[5, 8] = "STAN";
excel.Cells[5, 9] = "AUTH.ID";
excel.Cells[5, 10] = "ERROR DESCRIPTION";
excel.Cells[5, 11] = "SURCHARGE";
int ColumnIndex = 0;
//foreach (System.Data.DataColumn col in table.Columns)
//{
// ColumnIndex++;
// excel.Cells[6, ColumnIndex] = col.ColumnName;
//}
int rowIndex = 4;
foreach (DataRow row in table.Rows)
{
rowIndex++;
ColumnIndex = 0; foreach (DataColumn col in table.Columns)
{
ColumnIndex++;
if (ColumnIndex == 1 || ColumnIndex == 8)
{
excel.Cells[rowIndex + 1, ColumnIndex] = "'" + row[col.ColumnName].ToString();
((Excel.Range)excel.Cells[rowIndex + 1, ColumnIndex]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
}
else
{
excel.Cells[rowIndex + 1, ColumnIndex] = row[col.ColumnName];
((Excel.Range)excel.Cells[rowIndex + 1, ColumnIndex]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
}
}
}
int bl_tc = 0, cw_tc = 0, rs_tc = 0;
for (int p = 0; p < table.Rows.Count; p++)
{
if (table.Rows[p][1].ToString() == "Balance Inquiry")
bl_tc += 1;
else if (table.Rows[p][1].ToString() == "Cash Withdrawal")
cw_tc += 1;
else
rs_tc += 1;
}
excel.Cells[6 + table.Rows.Count, 2] = "TOTAL RECORDS :";
((Excel.Range)excel.Cells[6 + table.Rows.Count, 2]).Font.Bold = true;
excel.Cells[6 + table.Rows.Count, 3] = table.Rows.Count.ToString();
excel.Cells[7 + table.Rows.Count, 2] = "SUCCESSFUL BALANCE INQUIRY :";
((Excel.Range)excel.Cells[7 + table.Rows.Count, 2]).Font.Bold = true;
excel.Cells[7 + table.Rows.Count, 3] = bl_tc;
excel.Cells[8 + table.Rows.Count, 2] = "SUCCESSFUL WITHDRAWAL :";
((Excel.Range)excel.Cells[8 + table.Rows.Count, 2]).Font.Bold = true;
excel.Cells[8 + table.Rows.Count, 3] = cw_tc;
excel.Cells[9 + table.Rows.Count, 2] = "SUCCESSFUL REVERSAL :";
((Excel.Range)excel.Cells[9 + table.Rows.Count, 2]).Font.Bold = true;
excel.Cells[9 + table.Rows.Count, 3] = rs_tc;
excel.Visible = true;
Excel._Worksheet worksheet = (Excel._Worksheet)excel.ActiveSheet;
// FORMAT DATA
((Excel.Range)excel.Cells[1, 2]).NumberFormat = "m/d/yyyy h:mm:ss AM/PM";
((Excel.Range)excel.Cells[4, 2]).NumberFormat = "m/d/yyyy h:mm:ss AM/PM";
((Excel.Range)excel.Cells[4, 4]).NumberFormat = "m/d/yyyy h:mm:ss AM/PM";
((Excel.Range)worksheet.Cells[5, 5]).EntireColumn.NumberFormat = "##,##0.00";
((Excel.Range)worksheet.Cells[5, 11]).EntireColumn.NumberFormat = "##,##0.00";
// cell border
((Excel.Range)worksheet.Cells[5, 1]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
((Excel.Range)worksheet.Cells[5, 2]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
((Excel.Range)worksheet.Cells[5, 3]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
((Excel.Range)worksheet.Cells[5, 4]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
((Excel.Range)worksheet.Cells[5, 5]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
((Excel.Range)worksheet.Cells[5, 6]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
((Excel.Range)worksheet.Cells[5, 7]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
((Excel.Range)worksheet.Cells[5, 8]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
((Excel.Range)worksheet.Cells[5, 9]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
((Excel.Range)worksheet.Cells[5, 10]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
((Excel.Range)worksheet.Cells[5, 11]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
// font style
((Excel.Range)excel.Cells[3, 1]).Font.Bold = true;
((Excel.Range)excel.Cells[5, 1]).Font.Bold = true;
((Excel.Range)excel.Cells[5, 2]).Font.Bold = true;
((Excel.Range)excel.Cells[5, 3]).Font.Bold = true;
((Excel.Range)excel.Cells[5, 4]).Font.Bold = true;
((Excel.Range)excel.Cells[5, 5]).Font.Bold = true;
((Excel.Range)excel.Cells[5, 6]).Font.Bold = true;
((Excel.Range)excel.Cells[5, 7]).Font.Bold = true;
((Excel.Range)excel.Cells[5, 8]).Font.Bold = true;
((Excel.Range)excel.Cells[5, 9]).Font.Bold = true;
((Excel.Range)excel.Cells[5, 10]).Font.Bold = true;
((Excel.Range)excel.Cells[5, 11]).Font.Bold = true;
worksheet.Activate();
}
catch (Exception exml)
{
// catch an xmlexception errors
MessageBox.Show(exml.Message);
}
}
public static void ExportToExcel(DataTable dt, string type, string title)
{
//Export to excel
try
{
Excel.ApplicationClass excel = new Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
System.Data.DataTable table = dt;
excel.Cells[1, 1] = "Print Date: " + DateTime.Now;
excel.Cells[3, 1] = " "+title;
//excel.Cells[4, 1] = " Từ Ngày:";
//excel.Cells[4, 2] = dtFrom.Text;
//excel.Cells[4, 3] = " Đến Ngày:";
//excel.Cells[4, 4] = dtTo.Text;
((Excel.Range)excel.Cells[3, 1]).Font.Bold = true;
if (type == "ELECTRA")
{
excel.Cells[5, 1] = "STT";
excel.Cells[5, 2] = "LoaiGD";
excel.Cells[5, 3] = "Card_number";
excel.Cells[5, 4] = "Account_number";
excel.Cells[5, 5] = "Trace_number";
excel.Cells[5, 6] = "Approve_code";
excel.Cells[5, 7] = "Amount";
excel.Cells[5, 8] = "Transaction_date";
excel.Cells[5, 9] = "Ten_may";
excel.Cells[5, 10] = "Reference_number";
excel.Cells[5, 11] = "processing_code";
}
else if (type == "FLEXCUBE")
{
excel.Cells[5, 1] = "STT";
excel.Cells[5, 2] = "LoaiGD";
excel.Cells[5, 3] = "Card_number";
excel.Cells[5, 4] = "Account_number";
excel.Cells[5, 5] = "Trace_number";
excel.Cells[5, 6] = "Amount";
excel.Cells[5, 7] = "Transaction_date";
excel.Cells[5, 8] = "Ten may";
excel.Cells[5, 9] = "Processing_code";
}
int ColumnIndex = 0;
int rowIndex = 4;
foreach (DataRow row in table.Rows)
{
rowIndex++;
ColumnIndex = 0;
foreach (DataColumn col in table.Columns)
{
ColumnIndex++;
if (ColumnIndex == 3)
excel.Cells[rowIndex + 1, ColumnIndex] = "'"+row[col.ColumnName];
else
excel.Cells[rowIndex + 1, ColumnIndex] = row[col.ColumnName];
}
}
excel.Visible = true;
Excel._Worksheet worksheet = (Excel._Worksheet)excel.ActiveSheet;
// FORMAT DATA
if (type == "ELECTRA")
{
((Excel.Range)excel.Cells[1, 8]).EntireColumn.NumberFormat = "d/m/yyyy h:mm:ss";
((Excel.Range)excel.Cells[1, 11]).EntireColumn.NumberFormat = "0#####";
((Excel.Range)worksheet.Cells[1, 7]).EntireColumn.NumberFormat = "##,##0.00";
((Excel.Range)worksheet.Cells[1, 3]).EntireColumn.NumberFormat = "##### #### #### #### ####";
((Excel.Range)worksheet.Cells[1, 4]).EntireColumn.NumberFormat = "########################";
((Excel.Range)worksheet.Cells[1, 5]).EntireColumn.NumberFormat = "00000#";
((Excel.Range)worksheet.Cells[1, 10]).EntireColumn.NumberFormat = "############";
// font style
((Excel.Range)excel.Cells[5, 1]).Font.Bold = true;
((Excel.Range)excel.Cells[5, 1]).Font.Bold = true;
((Excel.Range)excel.Cells[5, 2]).Font.Bold = true;
((Excel.Range)excel.Cells[5, 3]).Font.Bold = true;
((Excel.Range)excel.Cells[5, 4]).Font.Bold = true;
((Excel.Range)excel.Cells[5, 5]).Font.Bold = true;
((Excel.Range)excel.Cells[5, 6]).Font.Bold = true;
((Excel.Range)excel.Cells[5, 7]).Font.Bold = true;
((Excel.Range)excel.Cells[5, 8]).Font.Bold = true;
((Excel.Range)excel.Cells[5, 9]).Font.Bold = true;
((Excel.Range)excel.Cells[5, 9]).Font.Bold = true;
((Excel.Range)excel.Cells[5, 10]).Font.Bold = true;
((Excel.Range)excel.Cells[5, 11]).Font.Bold = true;
}
else if (type == "FLEXCUBE")
{
((Excel.Range)excel.Cells[1, 7]).EntireColumn.NumberFormat = "d/m/yyyy h:mm:ss";
((Excel.Range)excel.Cells[1, 8]).EntireColumn.NumberFormat = "0#####";
((Excel.Range)worksheet.Cells[1, 6]).EntireColumn.NumberFormat = "##,##0.00";
((Excel.Range)worksheet.Cells[1, 3]).EntireColumn.NumberFormat = "##### #### #### #### ####";
((Excel.Range)worksheet.Cells[1, 4]).EntireColumn.NumberFormat = "########################";
((Excel.Range)worksheet.Cells[1, 5]).EntireColumn.NumberFormat = "00000#";
// font style
((Excel.Range)excel.Cells[5, 1]).Font.Bold = true;
((Excel.Range)excel.Cells[5, 1]).Font.Bold = true;
((Excel.Range)excel.Cells[5, 2]).Font.Bold = true;
((Excel.Range)excel.Cells[5, 3]).Font.Bold = true;
((Excel.Range)excel.Cells[5, 4]).Font.Bold = true;
((Excel.Range)excel.Cells[5, 5]).Font.Bold = true;
((Excel.Range)excel.Cells[5, 6]).Font.Bold = true;
((Excel.Range)excel.Cells[5, 7]).Font.Bold = true;
((Excel.Range)excel.Cells[5, 8]).Font.Bold = true;
((Excel.Range)excel.Cells[5, 9]).Font.Bold = true;
}
worksheet.Activate();
}
catch (Exception exml)
{
// catch an xmlexception errors
MessageBox.Show(exml.Message);
}
}
private void button3_Click(object sender, EventArgs e)
{
if (dtt.Rows.Count < 0)
{
MessageBox.Show("Bấm search trước rồi mới Export!!!");
return;
}
else
{
long total30 = 0;
string trn_date = "";
Excel.ApplicationClass excel = new Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
excel.Cells[1, 1] = "Group";// [Dòng, cột]
excel.Cells[1, 2] = "Account ";
excel.Cells[1, 3] = "Ccy_cd";
excel.Cells[1, 4] = "Amount";
excel.Cells[1, 5] = "Branch_code";
excel.Cells[1, 6] = "Account_Branch ";
excel.Cells[1, 7] = "Dr_Cr";
excel.Cells[1, 8] = "Ex_rate ";
excel.Cells[1, 9] = "Trn_dt";
excel.Cells[1, 10] = "Description ";
int i = 0;
if (dtt.Rows.Count > 0)
{
for (; i < dtt.Rows.Count; i++)
{
string Group = "", Account = "", Ccy_cd = "", Amount = "", Branch_code = "",
Account_Branch = "", Dr_Cr = "", Ex_rate = "", Trn_dt = "", Description = "";
//a.group_id,a.account_number,a.ccy_cd,a.amount_accounting,a.branch_code "+
//" ,a.account_branch,a.dr_cr,a.ex_rate,a.trn_dt,a.description,a.user_create,a.accounting_type "+
Group = "1";// dtt.Rows[i]["group_id"].ToString();
Account = dtt.Rows[i]["account"].ToString();
Ccy_cd = dtt.Rows[i]["ccy_cd"].ToString();
Amount = dtt.Rows[i]["transaction_amount"].ToString();
Branch_code = "'000";// dtt.Rows[i]["Branch"].ToString().Trim();
Account_Branch = dtt.Rows[i]["Branch_account"].ToString().Trim();
Dr_Cr = dtt.Rows[i]["dr_cr"].ToString();
Ex_rate = dtt.Rows[i]["ex_rate"].ToString();
Trn_dt = dtt.Rows[i]["trn_dt"].ToString();
trn_date = dtt.Rows[i]["trn_dt"].ToString();//used for Total CASA 30
Description = dtt.Rows[i]["Decription"].ToString();
//Export excel
excel.Cells[i + 2, 1] = Group;// [Dòng, cột]
excel.Cells[i + 2, 2] = Account;
excel.Cells[i + 2, 3] = Ccy_cd;
excel.Cells[i + 2, 4] = Amount;
excel.Cells[i + 2, 5] = Branch_code;
excel.Cells[i + 2, 6] = Account_Branch;
excel.Cells[i + 2, 7] = Dr_Cr;
excel.Cells[i + 2, 8] = Ex_rate;
excel.Cells[i + 2, 9] = Trn_dt;
excel.Cells[i + 2, 10] = Description;
total30 = total30 + Int64.Parse(Amount);
}
}
excel.Cells[i + 2, 1] = "1";// [Dòng, cột]
excel.Cells[i + 2, 2] = "'000000000100030";//CASA 30
excel.Cells[i + 2, 3] = "VND";
excel.Cells[i + 2, 4] = total30.ToString();
excel.Cells[i + 2, 5] = "'000";//Branch_code
excel.Cells[i + 2, 6] = "'000";//Account_Branch
excel.Cells[i + 2, 7] = "D";
excel.Cells[i + 2, 8] = "1";
excel.Cells[i + 2, 9] = trn_date;
excel.Cells[i + 2, 10] = "HACH TOAN ACQ NGAY " + trn_date;
excel.Visible = true;
Excel._Worksheet worksheet = (Excel._Worksheet)excel.ActiveSheet;
worksheet.Activate();
}
}
private void buttonX11_Click(object sender, EventArgs e)
{
Excel.Application xls_exp = null;
//int rowindex = 1;
int colindex = 0;
//创建一个workbook,一个worksheet
Excel._Workbook xls_book = null;
Excel._Worksheet xls_sheet = null;
try
{
xls_exp = new Excel.ApplicationClass();
xls_book = xls_exp.Workbooks.Add(true);
xls_sheet = (Excel._Worksheet)xls_book.ActiveSheet;
//C#创建Excel文件之取得数据
DataTable aa = GetData();
//将所得到的表的列名,赋值给单元格
foreach (DataColumn col in aa.Columns)
{
colindex++;
int i = 0;
i = aa.Rows.Count;
xls_exp.Cells[1, colindex] = col.ColumnName;
//水平对齐
xls_sheet.get_Range(xls_exp.Cells[1, colindex],
xls_exp.Cells[1, colindex]).HorizontalAlignment =
Excel.XlVAlign.xlVAlignCenter;
//C#创建Excel文件之垂直对齐
xls_sheet.get_Range(xls_exp.Cells[1, colindex],
xls_exp.Cells[1, colindex]).VerticalAlignment =
Excel.XlVAlign.xlVAlignCenter;
//行高、列宽自适应
// xls_sheet.Cells.Rows.AutoFill(null,Excel.XlAutoFillType.xlFillDefault);
((Excel.Range)xls_sheet.Columns["A:A", System.Type.Missing]).ColumnWidth = 22;
((Excel.Range)xls_sheet.Columns["B:B", System.Type.Missing]).ColumnWidth = 18;
((Excel.Range)xls_sheet.Columns["C:C", System.Type.Missing]).ColumnWidth = 14;
((Excel.Range)xls_sheet.Columns["D:D", System.Type.Missing]).ColumnWidth = 18;
((Excel.Range)xls_sheet.Columns["E:E", System.Type.Missing]).NumberFormatLocal = "0.00";
((Excel.Range)xls_sheet.Columns["F:F", System.Type.Missing]).NumberFormatLocal = "0.00";
((Excel.Range)xls_sheet.Columns["G:G", System.Type.Missing]).NumberFormatLocal = "0.00";
((Excel.Range)xls_sheet.Columns["H:H", System.Type.Missing]).NumberFormatLocal = "0.00";
((Excel.Range)xls_sheet.Columns["I:I", System.Type.Missing]).NumberFormatLocal = "0.00";
((Excel.Range)xls_sheet.Columns["J:J", System.Type.Missing]).NumberFormatLocal = "0.00";
xls_sheet.get_Range(xls_exp.Cells[1, colindex],
xls_exp.Cells[i + 1, colindex]).Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
}
int rowIndex = 1;
int colIndex = 0;
foreach (DataRow row in aa.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in aa.Columns)
{
colIndex++;
xls_exp.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
//不可见,即后台处理
xls_exp.Visible = true;
xls_sheet.Protect("123");
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
finally
{
xls_exp.Quit();
}
}
private void ExportExcel(string type)
{
Excel.ApplicationClass excel = new Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
excel.Cells[1, 1] = "Group";// [Dòng, cột]
excel.Cells[1, 2] = "Account ";
excel.Cells[1, 3] = "Ccy_cd";
excel.Cells[1, 4] = "Amount ";
excel.Cells[1, 5] = "Branch_code";
excel.Cells[1, 6] = "Account_Branch ";
excel.Cells[1, 7] = "Dr_Cr";
excel.Cells[1, 8] = "Ex_rate ";
excel.Cells[1, 9] = "Trn_dt";
excel.Cells[1, 10] = "Description ";
//excel.Cells[1, 1] = "Tại thời điểm " + dtTo.Text;
//((Excel.Range)excel.Cells[1, 1]).Font.Bold = true;
string sql = "", err = "";
sql = "select a.group_id,''''||a.account_number as account_number,a.ccy_cd,a.amount_accounting,''''||a.branch_code as branch_code" +
" ,''''||a.account_branch as account_branch,a.dr_cr,a.ex_rate,to_char(a.trn_dt,'mm/dd/yyyy') as trn_dt,a.description,a.user_create,a.accounting_type " +
" from " + Businessbp.executedb.owner + "nab_accounting a " +
" where trunc(a.date_proccessing)= trunc(to_date('" + dateTimePicker1.Text + "','dd/mm/yyyy'))";
sql += " and a.file_id not in ('1','2','3','4')";
sql += " order by a.group_id asc";
DataTable dtExport = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
this.Cursor = Cursors.Default;
return;
}
else
{
if (dtExport.Rows.Count > 0)
{
for (int i = 0; i < dtExport.Rows.Count; i++)
{
string Group = "", Account = "", Ccy_cd = "", Amount = "", Branch_code = "",
Account_Branch = "", Dr_Cr = "", Ex_rate = "", Trn_dt = "", Description = "";
//a.group_id,a.account_number,a.ccy_cd,a.amount_accounting,a.branch_code "+
//" ,a.account_branch,a.dr_cr,a.ex_rate,a.trn_dt,a.description,a.user_create,a.accounting_type "+
Group = dtExport.Rows[i]["group_id"].ToString();
Account = dtExport.Rows[i]["account_number"].ToString();
Ccy_cd = dtExport.Rows[i]["ccy_cd"].ToString();
Amount = dtExport.Rows[i]["amount_accounting"].ToString();
Branch_code = dtExport.Rows[i]["branch_code"].ToString().Trim();
Account_Branch = dtExport.Rows[i]["account_branch"].ToString().Trim();
Dr_Cr = dtExport.Rows[i]["dr_cr"].ToString();
Ex_rate = dtExport.Rows[i]["ex_rate"].ToString();
Trn_dt = dtExport.Rows[i]["trn_dt"].ToString();
Description = dtExport.Rows[i]["description"].ToString();
//Export excel
excel.Cells[i + 2, 1] = Group;// [Dòng, cột]
excel.Cells[i + 2, 2] = Account;
excel.Cells[i + 2, 3] = Ccy_cd;
excel.Cells[i + 2, 4] = Amount;
excel.Cells[i + 2, 5] = Branch_code;
excel.Cells[i + 2, 6] = Account_Branch;
excel.Cells[i + 2, 7] = Dr_Cr;
excel.Cells[i + 2, 8] = Ex_rate;
excel.Cells[i + 2, 9] = Trn_dt;
excel.Cells[i + 2, 10] = Description;
}
}
}
//----------------
excel.Visible = true;
Excel._Worksheet worksheet = (Excel._Worksheet)excel.ActiveSheet;
worksheet.Activate();
// worksheet.SaveAs("D:\\");
}
/// <summary>
/// builds gamma distribution to specified alpha and beta, and stores in local array
/// </summary>
public void InitGammaDistribution(double alpha, double beta)
{
//GammaDeviate gammaDev = new GammaDeviate(1);
Excel.Application app = new Excel.ApplicationClass();
//compute integral at 0.01 minute interval
this.alpha = alpha;
this.beta = beta;
this.gamma = Math.Exp(app.WorksheetFunction.GammaLn(alpha));
h = 0.01;
f = new double[STEPS];
for(int i = 0; i < STEPS; i++)
{
f[i] = (1 / gamma / Math.Pow(beta, alpha)) * Math.Pow(h, (alpha - 1)) * Math.Exp(-(h) / beta); //equation 1
h += 0.01;
}
fsum = 0;
fsumtotal = 0;
g = new double[MINS5];
//sum first 2.5 minutes and put into first slot of gamma distribution array
// r = 0;
// for(r = 0; r < 250; r++)
// {
// fsumtotal += f[r] + 0.01;
// }
// g[0] = fsumtotal;
int t = 0; //counter 0 thru 288
int s = 0;
for(int r = 0; r < STEPS; r = s)
{
for(s = r; s < 500 + r; s++)
{
fsum += f[s] * 0.01; //getting area under curve
fsumtotal += f[s] * 0.01; //will become very close to 1 when finished
}
g[t] = fsum;
t++;
fsum = 0;
}
//m = t - 1;//length of transfer function array
}
private void butexcel_Click(object sender, EventArgs e)
{
try
{
DataTable tb = null;
string ss = "";
tb = (DataTable)this.dataGridView1.DataSource;
ss = this._chineseName;
// 创建Excel对象
Excel.Application xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
MessageBox.Show("Excel无法启动");
return;
}
// 创建Excel工作薄
Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];
// 列索引，行索引，总列数，总行数
int colIndex = 0;
int RowIndex = 0;
int colCount = 0;
int RowCount = tb.Rows.Count + 1;
for (int i = 0; i <= tb.Columns.Count - 1; i++)
{
colCount = colCount + 1;
}
//查询条件
string swhere = "";
swhere = " 记费日期从:" + dtpBjksj.Value.ToString() + " 到 " + dtpEjksj.Value.ToString();
// 设置标题
Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, colCount]);
range.MergeCells = true;
xlApp.ActiveCell.FormulaR1C1 = ss;
xlApp.ActiveCell.Font.Size = 20;
xlApp.ActiveCell.Font.Bold = true;
xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter;
// 设置条件
Excel.Range range1 = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colCount]);
range1.MergeCells = true;
// 创建缓存数据
object[,] objData = new object[RowCount + 1, colCount + 1];
// 获取列标题
for (int i = 0; i <= tb.Columns.Count - 1; i++)
{
objData[1, colIndex++] = tb.Columns[i].Caption;
}
// 获取数据
objData[0, 0] = swhere;
for (int i = 0; i <= tb.Rows.Count - 1; i++)
{
colIndex = 0;
for (int j = 0; j <= tb.Columns.Count - 1; j++)
{
objData[i + 2, colIndex++] = "" + tb.Rows[i][j].ToString();
}
Application.DoEvents();
}
// 写入Excel
range = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]);
range.Value2 = objData;
//
xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[RowCount + 2, colCount]).Borders.LineStyle = 1;
//设置报表表格为最适应宽度
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Select();
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Columns.AutoFit();
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Font.Size = 9;
xlApp.Visible = true;
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
}
private void btn_export_Click(object sender, EventArgs e)
{
//Export to excel
try
{
Excel.ApplicationClass excel = new Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
System.Data.DataTable table = dt;
excel.Cells[1, 1] = "Print Date :";
excel.Cells[1, 2] = DateTime.Now;
excel.Cells[3, 1] = " DETAIL TRANSACTIONS REPORT- ACB CARDS AT SCB'S ATM ";
excel.Cells[4, 1] = " Từ Ngày:";
excel.Cells[4, 2] = t1;
excel.Cells[4, 3] = " Đến Ngày:";
excel.Cells[4, 4] = t2;
excel.Cells[6, 1] = "CARD_NUMBER";
excel.Cells[6, 2] = "TXN_TYPE";
excel.Cells[6, 3] = "TXN_DATE";
excel.Cells[6, 4] = "TXN_TIME";
excel.Cells[6, 5] = "TRANSACTION_AMOUNT";
excel.Cells[6, 6] = "CARD_ACCEPTOR_TERM_ID";
excel.Cells[6, 7] = "CARD_ACC_NAME_ADDRESS";
excel.Cells[6, 8] = "REFERENCE_NUMBER";
excel.Cells[6, 9] = "AUTHORIZATION_CODE";
excel.Cells[6, 10] = "ERROR_DESCRIPTION";
excel.Cells[6, 11] = "TOTAL_SURCHARGE";
excel.Cells[6, 12] = "DATE_CREATE";
int ColumnIndex = 0;
int rowIndex = 5;
foreach (DataRow row in table.Rows)
{
rowIndex++;
ColumnIndex = 0; foreach (DataColumn col in table.Columns)
{
ColumnIndex++;
if (ColumnIndex == 1 || ColumnIndex == 8 || ColumnIndex == 9)
{
excel.Cells[rowIndex + 1, ColumnIndex] = "'" + row[col.ColumnName].ToString();
((Excel.Range)excel.Cells[rowIndex + 1, ColumnIndex]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
}
else
{
excel.Cells[rowIndex + 1, ColumnIndex] = row[col.ColumnName];
((Excel.Range)excel.Cells[rowIndex + 1, ColumnIndex]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
}
}
}
excel.Visible = true;
Excel._Worksheet worksheet = (Excel._Worksheet)excel.ActiveSheet;
// FORMAT DATA
((Excel.Range)excel.Cells[1, 2]).NumberFormat = "m/d/yyyy h:mm:ss AM/PM";
((Excel.Range)excel.Cells[4, 2]).NumberFormat = "m/d/yyyy h:mm:ss AM/PM";
((Excel.Range)excel.Cells[4, 4]).NumberFormat = "m/d/yyyy h:mm:ss AM/PM";
((Excel.Range)worksheet.Cells[6, 5]).EntireColumn.NumberFormat = "##,##0.00";
((Excel.Range)worksheet.Cells[6, 11]).EntireColumn.NumberFormat = "##,##0.00";
// cell border
((Excel.Range)worksheet.Cells[6, 1]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
((Excel.Range)worksheet.Cells[6, 2]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
((Excel.Range)worksheet.Cells[6, 3]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
((Excel.Range)worksheet.Cells[6, 4]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
((Excel.Range)worksheet.Cells[6, 5]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
((Excel.Range)worksheet.Cells[6, 6]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
((Excel.Range)worksheet.Cells[6, 7]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
((Excel.Range)worksheet.Cells[6, 8]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
((Excel.Range)worksheet.Cells[6, 9]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
((Excel.Range)worksheet.Cells[6, 10]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
((Excel.Range)worksheet.Cells[6, 11]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
((Excel.Range)worksheet.Cells[6, 12]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
// font style
((Excel.Range)excel.Cells[3, 1]).Font.Bold = true;
((Excel.Range)excel.Cells[6, 1]).Font.Bold = true;
((Excel.Range)excel.Cells[6, 2]).Font.Bold = true;
((Excel.Range)excel.Cells[6, 3]).Font.Bold = true;
((Excel.Range)excel.Cells[6, 4]).Font.Bold = true;
((Excel.Range)excel.Cells[6, 5]).Font.Bold = true;
((Excel.Range)excel.Cells[6, 6]).Font.Bold = true;
((Excel.Range)excel.Cells[6, 7]).Font.Bold = true;
((Excel.Range)excel.Cells[6, 8]).Font.Bold = true;
((Excel.Range)excel.Cells[6, 9]).Font.Bold = true;
((Excel.Range)excel.Cells[6, 10]).Font.Bold = true;
((Excel.Range)excel.Cells[6, 11]).Font.Bold = true;
((Excel.Range)excel.Cells[6, 12]).Font.Bold = true;
worksheet.Activate();
}
catch (Exception exml)
{
// catch an xmlexception errors
MessageBox.Show(exml.Message);
}
}
public void ExpTreeVwtoExcel(string Head, TreeView tvw)
{
if (tvw.Nodes.Count != 0)
{
Excel.Application application = new Excel.ApplicationClass();
Excel._Worksheet activeSheet = (Excel._Worksheet)application.Application.Workbooks.Add(true).ActiveSheet;
activeSheet.Name = Head.Replace("\r\n", "") + DateTime.Now.ToString("yy-MM-dd");
application.Visible = true;
TreeNode temNode = tvw.Nodes[0];
activeSheet.get_Range(application.Cells[1, 1], application.Cells[1, 5]).Font.Bold = true;
activeSheet.get_Range(application.Cells[1, 1], application.Cells[1, 5]).Font.Size = 0x10;
activeSheet.get_Range(application.Cells[1, 1], application.Cells[1, 5]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
activeSheet.get_Range(application.Cells[1, 1], application.Cells[1, 5]).Merge(true);
application.Cells[1, 1] = Head;
application.Cells[3, 1] = " 报表时间 " + DateTime.Now;
activeSheet.get_Range(application.Cells[4, 1], application.Cells[4, 5]).Interior.ColorIndex = 0x13;
int row = 5;
this.ReadNodetoSheet(temNode, activeSheet, ref row);
}
}
private bool OutToExcel(string ExcelFile)
{
Excel.Application ExcelObj = null;
try
{
ExcelObj = new Excel.ApplicationClass();
ExcelObj.Visible = false;
Excel.Workbook ExcelBook = ExcelObj.Workbooks.Add(Missing.Value);
Excel.Worksheet ExcelSheet = (Excel.Worksheet)ExcelBook.Sheets[1];
//死者
StringToExcelCell(ExcelSheet, "A1", "死者：", 3);
StringToExcelCell(ExcelSheet, "B1", DeadManText.Text, 1);
//性别
StringToExcelCell(ExcelSheet, "F1", "性别：", 3);
StringToExcelCell(ExcelSheet, "G1", DeadSexComBoBox.Text, 1);
//出殡日期
StringToExcelCell(ExcelSheet, "A2", "出殡日期：", 3);
StringToExcelCell(ExcelSheet, "B2", DeadManTimeText.Text, 1);
//告别厅
StringToExcelCell(ExcelSheet, "G2", "告别厅", 1);
StringToExcelCell(ExcelSheet, "F2", DeadManAddressText.Text, 3);
//列表标题
StringToExcelCell(ExcelSheet, "A3", "序号", 2);
StringToExcelCell(ExcelSheet, "B3", "称 谓（前）", 2);
StringToExcelCell(ExcelSheet, "C3", "称 谓（后）", 2);
StringToExcelCell(ExcelSheet, "D3", "姓名1", 2);
StringToExcelCell(ExcelSheet, "E3", "姓名2", 2);
StringToExcelCell(ExcelSheet, "F3", "项目", 2);
StringToExcelCell(ExcelSheet, "G3", "价格", 2);
//列表数据
int j = 4;
for (int i = 0; i < GridViewControl.RowCount - 1; i++)
{
StringToExcelCell(ExcelSheet, j, 1, Convert.ToString(i + 1), 2);
StringToExcelCell(ExcelSheet, j, 2, GridViewCellToString(GridViewControl.Rows[i].Cells[1]), 2);
StringToExcelCell(ExcelSheet, j, 3, GridViewCellToString(GridViewControl.Rows[i].Cells[2]), 2);
StringToExcelCell(ExcelSheet, j, 4, GridViewCellToString(GridViewControl.Rows[i].Cells[3]), 2);
StringToExcelCell(ExcelSheet, j, 5, GridViewCellToString(GridViewControl.Rows[i].Cells[4]), 2);
StringToExcelCell(ExcelSheet, j, 6, GridViewCellToString(GridViewControl.Rows[i].Cells[5]), 2);
StringToExcelCell(ExcelSheet, j, 7, GridViewCellToString(GridViewControl.Rows[i].Cells[6]), 2);
j++;
}
//合计
StringToExcelCell(ExcelSheet, j, 6, "合计：", 3);
StringToExcelCell(ExcelSheet, j, 7, TotalPriceText.Text, 2);
//保存
ExcelBook.SaveAs(ExcelFile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
ExcelSheet = null;
ExcelBook.Close(false, false, false);
ExcelBook = null;
ExcelObj.Quit();
ExcelObj = null;
return true;
}
catch (Exception ex)
{
ExcelObj.Quit();
ExcelObj = null;
MessageBox.Show(ex.Message, "导出错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return false;
}
}
public override ResultadoLectura Leer(string archivo, ParametrosLectura param)
{
ResultadoLectura resultado = new ResultadoLectura();
int hoja = 1;
int filaInicio = 3;
DataTable tabla = new CreadorTablaLectura().CrearTabla();
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Open(archivo, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(hoja);
bool continuar = true;
while (continuar)
{
if ((xlWorkSheet.Rows.Cells[filaInicio, 1] as Excel.Range).Value2 == null)
{
continuar = false;
}
else
{
DateTime f1 = new DateTime(1900, 1, 1);
DateTime fecha = f1.AddDays(-2 + (double)(xlWorkSheet.Rows.Cells[filaInicio, 1] as Excel.Range).Value2);
TimeSpan hora = fecha.TimeOfDay;
RegistroLectura reg = RegistroLectura.GetRegistroLectura(fecha, hora);
resultado.Registros.Add(reg);
foreach (MC_RPuntoMedicionFormatoDetalle r in param.DetalleMagElec)
{
int numColumna = r.GetNumColumna();
if (numColumna >= 0)
{
DataRow row = tabla.NewRow();
row["Fecha"] = reg.Fecha.Date; //fecha.Date;
row["Hora"] = reg.Hora; //hora;
row["Canal"] = r.Canal;
row["CodInfCanal"] = r.FkCodMagnitudElec;
row["Valor"] = (double)(xlWorkSheet.Rows.Cells[filaInicio, numColumna + 1] as Excel.Range).Value2;
tabla.Rows.Add(row);
reg.AdicionarItem(r.FkCodMagnitudElec, (double)row["Valor"]);
reg.AdicionarRow(row);
}
}
filaInicio++;
}
}
DestructorObjetosExcel.ReleaseObject(xlWorkSheet);
xlWorkBook.Close(true, null, null);
xlApp.Quit();
DestructorObjetosExcel.ReleaseObject(xlWorkBook);
DestructorObjetosExcel.ReleaseObject(xlApp);
resultado.Tabla = tabla;
return(resultado);
}
private void btn_export_Click(object sender, EventArgs e)
{
//Export to excel
try
{
Excel.ApplicationClass excel = new Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
System.Data.DataTable table = dt;
excel.Cells[1, 1] = "Print Date :";
excel.Cells[1, 2] = DateTime.Now;
excel.Cells[3, 1] = " S2S - CAPTURED CARDS REPORT AT ACB'S ATM";
excel.Cells[4, 1] = " From Date :";
excel.Cells[4, 2] = t1;
excel.Cells[4, 3] = " To Date :";
excel.Cells[4, 4] = t2;
excel.Cells[6, 1] = "TERMINAL ID";
excel.Cells[6, 2] = "TERMINAL LOCATION";
excel.Cells[6, 3] = "CARD NUMBER";
excel.Cells[6, 4] = "DATE";
excel.Cells[6, 5] = "TIME";
excel.Cells[6, 6] = "RESPONSE CODE";
excel.Cells[6, 7] = "ERROR DESCRIPTION";
excel.Cells[6, 8] = "DATE CREATE";
int ColumnIndex = 0;
int rowIndex = 5;
foreach (DataRow row in table.Rows)
{
rowIndex++;
ColumnIndex = 0; foreach (DataColumn col in table.Columns)
{
ColumnIndex++;
if (ColumnIndex == 3)
{
excel.Cells[rowIndex + 1, ColumnIndex] = "'" + row[col.ColumnName].ToString();
((Excel.Range)excel.Cells[rowIndex + 1, ColumnIndex]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
}
else
{
excel.Cells[rowIndex + 1, ColumnIndex] = row[col.ColumnName];
((Excel.Range)excel.Cells[rowIndex + 1, ColumnIndex]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
}
}
}
excel.Cells[7 + table.Rows.Count, 2] = "FINAL TOTALS";
((Excel.Range)excel.Cells[7 + table.Rows.Count, 5]).Font.Bold = true;
excel.Cells[7 + table.Rows.Count, 3] = table.Rows.Count.ToString();
excel.Cells[9 + table.Rows.Count, 1] = "***************END OF REPORT***********************";
excel.Visible = true;
Excel._Worksheet worksheet = (Excel._Worksheet)excel.ActiveSheet;
// FORMAT DATA
((Excel.Range)excel.Cells[1, 2]).NumberFormat = "m/d/yyyy h:mm:ss AM/PM";
((Excel.Range)excel.Cells[4, 2]).NumberFormat = "m/d/yyyy h:mm:ss AM/PM";
((Excel.Range)excel.Cells[4, 4]).NumberFormat = "m/d/yyyy h:mm:ss AM/PM";
((Excel.Range)excel.Cells[4, 8]).NumberFormat = "m/d/yyyy h:mm:ss AM/PM";
// cell border
((Excel.Range)worksheet.Cells[6, 1]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
((Excel.Range)worksheet.Cells[6, 2]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
((Excel.Range)worksheet.Cells[6, 3]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
((Excel.Range)worksheet.Cells[6, 4]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
((Excel.Range)worksheet.Cells[6, 5]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
((Excel.Range)worksheet.Cells[6, 6]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
((Excel.Range)worksheet.Cells[6, 7]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
((Excel.Range)worksheet.Cells[6, 8]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
// font style
((Excel.Range)excel.Cells[3, 1]).Font.Bold = true;
((Excel.Range)excel.Cells[6, 1]).Font.Bold = true;
((Excel.Range)excel.Cells[6, 2]).Font.Bold = true;
((Excel.Range)excel.Cells[6, 3]).Font.Bold = true;
((Excel.Range)excel.Cells[6, 4]).Font.Bold = true;
((Excel.Range)excel.Cells[6, 5]).Font.Bold = true;
((Excel.Range)excel.Cells[6, 6]).Font.Bold = true;
((Excel.Range)excel.Cells[6, 7]).Font.Bold = true;
((Excel.Range)excel.Cells[6, 8]).Font.Bold = true;
worksheet.Activate();
}
catch (Exception exml)
{
// catch an xmlexception errors
MessageBox.Show(exml.Message);
}
}
