using System;
|
using System.Collections.Generic;
|
using System.Text;
|
using System.Windows.Forms;
|
using System.Drawing;
|
using System.Data;
|
using System.Runtime.InteropServices;
|
using System.IO;
|
|
namespace DBUtility
|
{
|
public class Gy_BaseFun
|
{
|
|
//¼ÓÔØÊ÷ÐÎ
|
public static void LoadTreeRWD(TreeView tv, ImageList imageList1, string Text, string BillName, string HH, string PH, string DJH)//Lock
|
{
|
SQLHelper.ClsCN oCn = new SQLHelper.ClsCN();
|
try
|
{
|
tv.Nodes.Clear();
|
tv.ImageList = imageList1;
|
TreeNode sNode = tv.Nodes.Add("T0", Text, 0, 1);
|
DataSet Ds = oCn.RunProcReturn("select distinct HGoodsNum from " + BillName + " where HGoodsNum like '%" + HH + "%' and hbatchno like '%" + PH + "%' and HBillNo like '%" + DJH + "%'", BillName, ref ClsPub.sExeReturnInfo);
|
for (int i = 0; i < Ds.Tables[0].Rows.Count; i++)
|
{
|
TreeNode oNode = sNode.Nodes.Add("R" + Ds.Tables[0].Rows[i]["HGoodsNum"].ToString(), Ds.Tables[0].Rows[i]["HGoodsNum"].ToString(), 0, 1);
|
}
|
sNode.Expand();
|
//LoadAllNodesRWD(sNode, BillName);
|
}
|
catch (Exception e)
|
{
|
MessageBox.Show("¼ÓÔØÊ÷ÐÍʧ°Ü£¡" + e.Message, "Ìáʾ");
|
}
|
}
|
|
public static void LoadAllNodesRWD(TreeNode sNode, string BillName, string PH, string DJH) //Lock
|
{
|
SQLHelper.ClsCN oCn = new SQLHelper.ClsCN();
|
if (sNode != null)
|
{
|
try
|
{
|
string sName = Convert.ToString(sNode.Name.Substring(1, sNode.Name.Length - 1));
|
sNode.Nodes.Clear();
|
DataSet Ds = oCn.RunProcReturn("select HGoodsNum,HBatchNo,HInterID,HBillNo from " + BillName + " where HGoodsNum='" + sName + "' and HBatchNo like '%" + PH + "%' and HBillNo like '%" + DJH + "%'", BillName, ref ClsPub.sExeReturnInfo);
|
for (int i = 0; i < Ds.Tables[0].Rows.Count; i++)
|
{
|
TreeNode oNode = sNode.Nodes.Add("T" + Ds.Tables[0].Rows[i]["HInterID"].ToString(), Ds.Tables[0].Rows[i]["HBatchNo"].ToString() + "-" + Ds.Tables[0].Rows[i]["HBillNo"].ToString(), 0, 1);
|
}
|
sNode.Expand();
|
}
|
catch (Exception e)
|
{
|
MessageBox.Show("¼ÓÔØ×ÓÏîĿʧ°Ü£¡" + e.Message, "Ìáʾ");
|
}
|
}
|
|
}
|
//------------------------------------
|
|
//¼ÓÔØÊ÷ÐÎ
|
public static void LoadTree(TreeView tv,ImageList imageList1,string Text,string BillName)//Lock
|
{
|
try
|
{
|
tv.Nodes.Clear();
|
tv.ImageList = imageList1;
|
TreeNode sNode = tv.Nodes.Add("T0", Text, 0, 1);
|
LoadAllNodes(sNode, BillName);
|
}
|
catch (Exception e)
|
{
|
MessageBox.Show("¼ÓÔØÊ÷ÐÍʧ°Ü£¡" + e.Message, "Ìáʾ");
|
}
|
}
|
|
//¼ÓÔØÊ÷ÐÎ
|
public static void LoadTree(TreeView tv, ImageList imageList1, string Text, string BillName,Int64 sKey)//Lock
|
{
|
try
|
{
|
tv.Nodes.Clear();
|
tv.ImageList = imageList1;
|
TreeNode sNode = tv.Nodes.Add("T" + sKey.ToString(), Text, 0, 1);
|
LoadAllNodes(sNode, BillName);
|
}
|
catch (Exception e)
|
{
|
MessageBox.Show("¼ÓÔØÊ÷ÐÍʧ°Ü£¡" + e.Message, "Ìáʾ");
|
}
|
}
|
|
public static void LoadAllNodes(TreeNode sNode, string BillName) //Lock
|
{
|
SQLHelper.ClsCN oCn = new SQLHelper.ClsCN();
|
if (sNode != null)
|
{
|
try
|
{
|
long sName = Convert.ToInt64(sNode.Name.Substring(1, sNode.Name.Length - 1));
|
sNode.Nodes.Clear();
|
DataSet Ds = oCn.RunProcReturn("select hitemid,hnumber,hname from " + BillName + " where HParentID='" + sName + "' order by Hnumber", BillName, ref ClsPub.sExeReturnInfo);
|
for (int i = 0; i < Ds.Tables[0].Rows.Count; i++)
|
{
|
TreeNode oNode = sNode.Nodes.Add("T" + Ds.Tables[0].Rows[i]["HItemID"].ToString(), Ds.Tables[0].Rows[i]["HNumber"].ToString() + "-" + Ds.Tables[0].Rows[i]["HName"].ToString(), 0, 1);
|
}
|
sNode.Expand();
|
}
|
catch (Exception e)
|
{
|
MessageBox.Show("¼ÓÔØ×ÓÏîĿʧ°Ü£¡" + e.Message, "Ìáʾ");
|
}
|
}
|
|
}
|
|
//µÃµ½¶ÔÓ¦ÁÐ
|
public static Int32 Fun_GetCol(string sCol, DataGridView grdMain)//Lock
|
{
|
for (int c = 0; c < grdMain.ColumnCount; c++)
|
{
|
if (ClsPub.isStrNull(grdMain.Columns[c].HeaderText).ToUpper().Trim() == sCol.Trim().ToUpper())
|
{
|
return c;
|
}
|
}
|
return -1;
|
}
|
|
|
|
//³õʼ»¯GRID
|
public static void initGrid(DataGridView grdMain)
|
{
|
grdMain.SelectionMode = DataGridViewSelectionMode.FullRowSelect; //Ñ¡ÖÐģʽ--ÐÐÑ¡ÖÐ
|
grdMain.ColumnHeadersVisible = true; //±êÌâÁÐÏÔʾ--ÊÇ
|
grdMain.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.DisableResizing; //±êÌâÁгߴçģʽ--£¨×Ô¶¯µ÷Õû£¬¿Éµ÷Õû£©
|
grdMain.RowTemplate.Height = 18;
|
grdMain.RowTemplate.MinimumHeight = 18;
|
grdMain.ColumnHeadersHeight = 35; //±êÌâÐиß
|
grdMain.RowHeadersVisible = false; //±êÌâÐпɼû--·ñ
|
grdMain.AllowUserToAddRows = false; //ÊÇ·ñÔÊÐí×ÔÔö--·ñ
|
grdMain.AllowUserToDeleteRows = false; //ÊÇ·ñÔÊÐíɾ³ý--·ñ
|
grdMain.ColumnCount = 0; //×ÜÁÐÊý--5
|
grdMain.RowCount = 0;
|
grdMain.AllowUserToResizeColumns = true; //ÔÊÐíµ÷ÕûÁпí--ÊÇ
|
grdMain.AllowUserToResizeRows = false; //ÔÊÐíµ÷ÕûÐиß--·ñ
|
grdMain.ReadOnly = true; //Ö»¶Á--ÊÇ
|
grdMain.BackgroundColor = Color.White;
|
grdMain.GridColor = Color.LightGray;
|
grdMain.DefaultCellStyle.SelectionBackColor = Color.Black;
|
grdMain.EnableHeadersVisualStyles = false; //Íø¸ñ±êÌâ Ñùʽ
|
grdMain.ColumnHeadersDefaultCellStyle.BackColor = Color.LightGray;
|
}
|
|
|
|
//Çå¿ÕÐÅÏ¢
|
public static void Sub_Clear(TabPage tabPage, ref Int64 HItemID, TextBox txtHNumber) //Lock
|
{
|
//Çå¿Õ½çÃæ¿Ø¼þ for ¿Ø¼þ
|
foreach (Control ct in tabPage.Controls)
|
{
|
switch (ct.GetType().Name)
|
{
|
case "ListBox":
|
((ListBox)ct).Items.Clear();
|
break;
|
case "CheckBox":
|
((CheckBox)ct).Checked = false;
|
break;
|
case "RadioButton":
|
|
break;
|
case "TextBox":
|
((TextBox)ct).Text = "";
|
break;
|
case "DateTimePicker":
|
((DateTimePicker)ct).Value = DateTime.Today;
|
break;
|
case "ComboBox":
|
((ComboBox)ct).SelectedIndex = 0;
|
break;
|
default:
|
break;
|
}
|
}
|
//
|
HItemID = 0;
|
txtHNumber.Focus();
|
}
|
|
//Çå¿ÕÐÅÏ¢
|
public static void Sub_Clear(TabPage tabPage) //Lock
|
{
|
//Çå¿Õ½çÃæ¿Ø¼þ for ¿Ø¼þ
|
foreach (Control ct in tabPage.Controls)
|
{
|
switch (ct.GetType().Name)
|
{
|
case "ListBox":
|
((ListBox)ct).Items.Clear();
|
break;
|
case "CheckBox":
|
((CheckBox)ct).Checked = false;
|
break;
|
case "RadioButton":
|
|
break;
|
case "TextBox":
|
((TextBox)ct).Text = "";
|
break;
|
case "DateTimePicker":
|
((DateTimePicker)ct).Value = DateTime.Today;
|
break;
|
case "ComboBox":
|
((ComboBox)ct).SelectedIndex = 0;
|
break;
|
default:
|
break;
|
}
|
}
|
//
|
}
|
|
//ÏÔʾÁбí
|
public static void DisplayGrid(DataGridView grdMain, string Name) // »ÃÐÄ
|
{
|
//¼ÓÔØÁпí
|
for (int i = 0; i < grdMain.ColumnCount; i++)
|
{
|
grdMain.Columns[i].Width = 100;
|
if (grdMain.Columns[i].HeaderText.Substring(0, 1).ToLower() == "h")
|
{
|
grdMain.Columns[i].Visible = false;
|
}
|
else
|
{
|
grdMain.Columns[i].Visible = true;
|
}
|
}
|
ClsPub.GetGridView( grdMain, Name, ClsPub.AppPath);
|
//
|
}
|
|
public static void SetCondition(frm_B_BillQueryCondition_New frmCondition, string ViewName, string Name)
|
{
|
frmCondition.Tag = "";
|
frmCondition.ViewName = ViewName;
|
frmCondition.ModName = Name;
|
}
|
|
//Ñ¡ÖÐÐÐÊÇ·ñÕý³£
|
public static bool AllowRow(DataGridView grdMain) //Lock
|
{
|
if (grdMain.CurrentRow != null)
|
return true;
|
else
|
return false;
|
}
|
|
public static void FindRow(string sCode,string ModName,ref string sCondition)//Lock
|
{
|
sCode = sCode.Trim();
|
char c = Convert.ToChar("-");
|
string[] s = sCode.Split(c);
|
if (s.Length > 1)
|
{
|
sCondition = " Where " + ModName + "´úÂë like '" + s[0].Trim() + "%'";
|
//
|
}
|
else if (s.Length > 0)
|
{
|
sCondition = " Where " + ModName + "´úÂë like '%%'";
|
//
|
}
|
else
|
{
|
sCondition = " ";
|
}
|
}
|
|
//±£´æµ¥¾ÝÐÅÏ¢
|
public static void SaveControlInfoBill(GroupBox gp, string sName)
|
{
|
foreach (Control ct in gp.Controls)
|
{
|
switch (ct.GetType().Name)
|
{
|
case "ListBox":
|
|
break;
|
case "CheckBox":
|
//Pub_Class.ClsIni.WriteIni(sName, ((CheckBox)ct).Name, ((CheckBox)ct).Checked.ToString(), Pub_Class.ClsPub.AppPath + @"/ControlInfo.ini");
|
break;
|
case "RadioButton":
|
//Pub_Class.ClsIni.WriteIni(sName, ((RadioButton)ct).Name, ((RadioButton)ct).Checked.ToString(), Pub_Class.ClsPub.AppPath + @"/ControlInfo.ini");
|
break;
|
case "ComboBox":
|
//Pub_Class.ClsIni.WriteIni(sName, ((ComboBox)ct).Name, ((ComboBox)ct).Text, Pub_Class.ClsPub.AppPath + @"/ControlInfo.ini");
|
break;
|
case "TextBox":
|
if (((TextBox)ct).ForeColor == Color.Blue)
|
{
|
Pub_Class.ClsIni.WriteIni(sName, ((TextBox)ct).Name, ((TextBox)ct).Text, Pub_Class.ClsPub.AppPath + @"/ControlInfoBill.ini");
|
Pub_Class.ClsIni.WriteIni(sName, ((TextBox)ct).Name + "-tag", ClsPub.isStrNull(((TextBox)ct).Tag), Pub_Class.ClsPub.AppPath + @"/ControlInfoBill.ini");
|
}
|
break;
|
case "DateTimePicker":
|
//Pub_Class.ClsIni.WriteIni(sName, ((DateTimePicker)ct).Name, ((DateTimePicker)ct).Value.ToShortDateString(), Pub_Class.ClsPub.AppPath + @"/ControlInfo.ini");
|
break;
|
default:
|
break;
|
}
|
}
|
}
|
|
//µÃµ½µ¥¾ÝÐÅÏ¢
|
public static void GetControlInfoBill(GroupBox gp, string sName)
|
{
|
string s = "";
|
foreach (Control ct in gp.Controls)
|
{
|
switch (ct.GetType().Name)
|
{
|
case "ListBox":
|
|
break;
|
case "CheckBox":
|
//s = Pub_Class.ClsIni.ReadIni(sName, ((CheckBox)ct).Name, Pub_Class.ClsPub.AppPath + @"/ControlInfo.ini");
|
//if (s != "ûÓÐÕÒµ½£¡")
|
//{
|
// ((CheckBox)ct).Checked = ClsPub.isBool(s);
|
//}
|
break;
|
case "RadioButton":
|
//s = Pub_Class.ClsIni.ReadIni(sName, ((RadioButton)ct).Name, Pub_Class.ClsPub.AppPath + @"/ControlInfo.ini");
|
//if (s != "ûÓÐÕÒµ½£¡")
|
//{
|
// ((RadioButton)ct).Checked = ClsPub.isBool(s);
|
//}
|
break;
|
case "ComboBox":
|
//s = Pub_Class.ClsIni.ReadIni(sName, ((ComboBox)ct).Name, Pub_Class.ClsPub.AppPath + @"/ControlInfo.ini");
|
//if (s != "ûÓÐÕÒµ½£¡")
|
//{
|
// ((ComboBox)ct).Text = s;
|
//}
|
break;
|
case "TextBox":
|
s = Pub_Class.ClsIni.ReadIni(sName, ((TextBox)ct).Name, Pub_Class.ClsPub.AppPath + @"/ControlInfoBill.ini");
|
if (s != "ûÓÐÕÒµ½£¡")
|
{
|
((TextBox)ct).Text = s;
|
}
|
s = Pub_Class.ClsIni.ReadIni(sName, ((TextBox)ct).Name + "-tag", Pub_Class.ClsPub.AppPath + @"/ControlInfoBill.ini");
|
if (s != "ûÓÐÕÒµ½£¡")
|
{
|
((TextBox)ct).Tag = s;
|
}
|
break;
|
case "DateTimePicker":
|
//s = Pub_Class.ClsIni.ReadIni(sName, ((DateTimePicker)ct).Name, Pub_Class.ClsPub.AppPath + @"/ControlInfo.ini");
|
//if (s != "ûÓÐÕÒµ½£¡")
|
//{
|
// ((DateTimePicker)ct).Value = ClsPub.isDate(s);
|
//}
|
break;
|
default:
|
break;
|
}
|
}
|
}
|
|
//±£´æ¹ýÂË¿òÐÅÏ¢
|
public static void SaveControlInfo(GroupBox gp,string sName)
|
{
|
foreach (Control ct in gp.Controls)
|
{
|
switch (ct.GetType().Name)
|
{
|
case "ListBox":
|
|
break;
|
case "CheckBox":
|
Pub_Class.ClsIni.WriteIni(sName, ((CheckBox)ct).Name, ((CheckBox)ct).Checked.ToString(), Pub_Class.ClsPub.AppPath + @"/ControlInfo.ini");
|
break;
|
case "RadioButton":
|
Pub_Class.ClsIni.WriteIni(sName, ((RadioButton)ct).Name, ((RadioButton)ct).Checked.ToString(), Pub_Class.ClsPub.AppPath + @"/ControlInfo.ini");
|
break;
|
case "ComboBox":
|
Pub_Class.ClsIni.WriteIni(sName, ((ComboBox)ct).Name, ((ComboBox)ct).Text, Pub_Class.ClsPub.AppPath + @"/ControlInfo.ini");
|
break;
|
case "TextBox":
|
Pub_Class.ClsIni.WriteIni(sName, ((TextBox)ct).Name, ((TextBox)ct).Text, Pub_Class.ClsPub.AppPath + @"/ControlInfo.ini");
|
Pub_Class.ClsIni.WriteIni(sName, ((TextBox)ct).Name + "-tag", ClsPub.isStrNull(((TextBox)ct).Tag), Pub_Class.ClsPub.AppPath + @"/ControlInfo.ini");
|
break;
|
case "DateTimePicker":
|
Pub_Class.ClsIni.WriteIni(sName, ((DateTimePicker)ct).Name, ((DateTimePicker)ct).Value.ToShortDateString(), Pub_Class.ClsPub.AppPath + @"/ControlInfo.ini");
|
break;
|
default:
|
break;
|
}
|
}
|
}
|
|
//µÃµ½¹ýÂË¿òÐÅÏ¢
|
public static void GetControlInfo(GroupBox gp, string sName)
|
{
|
string s = "";
|
foreach (Control ct in gp.Controls)
|
{
|
switch (ct.GetType().Name)
|
{
|
case "ListBox":
|
|
break;
|
case "CheckBox":
|
s = Pub_Class.ClsIni.ReadIni(sName, ((CheckBox)ct).Name, Pub_Class.ClsPub.AppPath + @"/ControlInfo.ini");
|
if (s != "ûÓÐÕÒµ½£¡")
|
{
|
((CheckBox)ct).Checked = ClsPub.isBool(s);
|
}
|
break;
|
case "RadioButton":
|
s = Pub_Class.ClsIni.ReadIni(sName, ((RadioButton)ct).Name, Pub_Class.ClsPub.AppPath + @"/ControlInfo.ini");
|
if (s != "ûÓÐÕÒµ½£¡")
|
{
|
((RadioButton)ct).Checked = ClsPub.isBool(s);
|
}
|
break;
|
case "ComboBox":
|
s = Pub_Class.ClsIni.ReadIni(sName, ((ComboBox)ct).Name, Pub_Class.ClsPub.AppPath + @"/ControlInfo.ini");
|
if (s != "ûÓÐÕÒµ½£¡")
|
{
|
((ComboBox)ct).Text = s;
|
}
|
break;
|
case "TextBox":
|
s = Pub_Class.ClsIni.ReadIni(sName, ((TextBox)ct).Name, Pub_Class.ClsPub.AppPath + @"/ControlInfo.ini");
|
if (s != "ûÓÐÕÒµ½£¡")
|
{
|
((TextBox)ct).Text = s;
|
}
|
s = Pub_Class.ClsIni.ReadIni(sName, ((TextBox)ct).Name+"-tag", Pub_Class.ClsPub.AppPath + @"/ControlInfo.ini");
|
if (s != "ûÓÐÕÒµ½£¡")
|
{
|
((TextBox)ct).Tag = s;
|
}
|
break;
|
case "DateTimePicker":
|
s = Pub_Class.ClsIni.ReadIni(sName, ((DateTimePicker)ct).Name, Pub_Class.ClsPub.AppPath + @"/ControlInfo.ini");
|
if (s != "ûÓÐÕÒµ½£¡")
|
{
|
((DateTimePicker)ct).Value = ClsPub.isDate(s);
|
}
|
break;
|
default:
|
break;
|
}
|
}
|
|
}
|
|
//»ñÈ¡ÁÐ±í ºÏ¼ÆÁÐ
|
/// <summary>
|
/// »ñÈ¡ÁÐ±í ºÏ¼ÆÁУ¨Double£¬Single£¬Int16£¬Int32£¬Int64£¬Decimal£©
|
/// </summary>
|
/// <param name="DSet"></param>
|
/// <returns></returns>
|
public static string GetTotalCols(DataSet DSet)
|
{
|
string sTotalCol = "";
|
for (int i = 0; i < DSet.Tables[0].Columns.Count; i++)
|
{
|
if (DSet.Tables[0].Columns[i].DataType == Type.GetType("System.Double"))
|
{
|
sTotalCol = sTotalCol + "," + i.ToString();
|
}
|
else if (DSet.Tables[0].Columns[i].DataType == Type.GetType("System.Single"))
|
{
|
sTotalCol = sTotalCol + "," + i.ToString();
|
}
|
else if (DSet.Tables[0].Columns[i].DataType == Type.GetType("System.Int16"))
|
{
|
sTotalCol = sTotalCol + "," + i.ToString();
|
}
|
else if (DSet.Tables[0].Columns[i].DataType == Type.GetType("System.Int32"))
|
{
|
sTotalCol = sTotalCol + "," + i.ToString();
|
}
|
else if (DSet.Tables[0].Columns[i].DataType == Type.GetType("System.Int64"))
|
{
|
sTotalCol = sTotalCol + "," + i.ToString();
|
}
|
else if (DSet.Tables[0].Columns[i].DataType == Type.GetType("System.Decimal"))
|
{
|
sTotalCol = sTotalCol + "," + i.ToString();
|
}
|
}
|
if (sTotalCol != "")
|
{
|
sTotalCol = sTotalCol.Substring(1, sTotalCol.Length - 1);
|
}
|
return sTotalCol;
|
}
|
|
//±¨±íSQL¹ýÂË´¦Àí
|
public static void SetSQLWhere_Rtp(ref string sDlgWhere)
|
{
|
if (sDlgWhere.Trim().Length > 0)
|
{
|
sDlgWhere = sDlgWhere.Substring(1, sDlgWhere.Length - 1);
|
}
|
}
|
|
//¼ÓÔØ ×éºÏ¿ò£¨»á¼ÆÆڼ䣩
|
public static void SetComboxPeriod(ComboBox cmbHPeriod)
|
{
|
cmbHPeriod.Items.Clear();
|
cmbHPeriod.Items.Add("1");
|
cmbHPeriod.Items.Add("2");
|
cmbHPeriod.Items.Add("3");
|
cmbHPeriod.Items.Add("4");
|
cmbHPeriod.Items.Add("5");
|
cmbHPeriod.Items.Add("6");
|
cmbHPeriod.Items.Add("7");
|
cmbHPeriod.Items.Add("8");
|
cmbHPeriod.Items.Add("9");
|
cmbHPeriod.Items.Add("10");
|
cmbHPeriod.Items.Add("11");
|
cmbHPeriod.Items.Add("12");
|
cmbHPeriod.Text = DateTime.Today.Month.ToString();
|
}
|
|
//¼ÓÔØ ×éºÏ¿ò£¨»á¼ÆÄ꣩
|
public static void SetComboxYear(ComboBox cmbHYear)
|
{
|
cmbHYear.Items.Clear();
|
cmbHYear.Items.Add(DateTime.Today.Year);
|
cmbHYear.Items.Add(DateTime.Today.AddYears(1).Year);
|
cmbHYear.Items.Add(DateTime.Today.AddYears(2).Year);
|
cmbHYear.Items.Add(DateTime.Today.AddYears(3).Year);
|
cmbHYear.Items.Add(DateTime.Today.AddYears(4).Year);
|
cmbHYear.Items.Add(DateTime.Today.AddYears(-5).Year);
|
cmbHYear.Items.Add(DateTime.Today.AddYears(-4).Year);
|
cmbHYear.Items.Add(DateTime.Today.AddYears(-3).Year);
|
cmbHYear.Items.Add(DateTime.Today.AddYears(-2).Year);
|
cmbHYear.Items.Add(DateTime.Today.AddYears(-1).Year);
|
cmbHYear.Text = DateTime.Today.Year.ToString();
|
}
|
|
#region DataGridView¼ÓÔØExcel
|
|
public static bool OpenExcelData(string sPath, string sSheetName, DataGridView grdMain)
|
{
|
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sPath + ";" +
|
"Extended Properties='Excel 8.0;IMEX=1'";
|
DataSet ds = new DataSet();
|
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
|
conn.Open();
|
try
|
{
|
System.Data.OleDb.OleDbDataAdapter Da = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sSheetName + "$]", conn);
|
Da.Fill(ds);
|
grdMain.DataSource = ds.Tables[0];
|
return true;
|
}
|
catch (Exception e)
|
{
|
MessageBox.Show("ÒýÈëEXCELÊý¾Ýʧ°Ü£¡ÇëÓë¹ÜÀíÔ±ÁªÏµ£¡" + e.Message);
|
return false;
|
}
|
}
|
|
#endregion
|
|
#region Êý¾Ýµ¼³ö2
|
|
|
/// <summary>
|
/// Íø¸ñ µ¼³öEXCEL Íø¸ñ£¬ÎļþÃû£¬±êÌâÃû
|
/// </summary>
|
public static void DataGridViewToExcel2(DataGridView grdMain, string sText, string sBTText)
|
{
|
SaveFileDialog saveFileDialog = new SaveFileDialog();
|
saveFileDialog.Filter = "ExeclÎļþ(*.xls)|*.xls";
|
saveFileDialog.FilterIndex = 0;
|
saveFileDialog.RestoreDirectory = true;
|
saveFileDialog.CreatePrompt = true;
|
saveFileDialog.Title = "Êý¾ÝÊÓͼµ¼³öEXCELÎļþ";
|
saveFileDialog.FileName = sText;
|
//saveFileDialog.ShowDialog();
|
if (saveFileDialog.ShowDialog() == DialogResult.OK)
|
{
|
Stream myStream;
|
myStream = saveFileDialog.OpenFile();
|
StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding("gb2312"));
|
string str = "";
|
DateTime start = DateTime.Now;
|
try
|
{
|
if (sBTText != "")
|
{
|
sw.WriteLine(sBTText);
|
}
|
//д±êÌâ
|
bool sFirstCol = true;
|
for (int i = 0; i < grdMain.ColumnCount; i++)
|
{
|
if (i > 0 && grdMain.Columns[i].HeaderText.Substring(0, 1) != "h" && grdMain.Columns[i].HeaderText.Substring(0, 1) != "H" && sFirstCol != true)
|
{
|
str += "\t";
|
}
|
if (grdMain.Columns[i].HeaderText.Substring(0, 1) != "h" && grdMain.Columns[i].HeaderText.Substring(0, 1) != "H")
|
{
|
sFirstCol = false;
|
str += grdMain.Columns[i].HeaderText;
|
}
|
}
|
str = str.Replace("\n", "");
|
sw.WriteLine(str);
|
|
//дÄÚÈÝ
|
for (int j = 0; j < grdMain.Rows.Count; j++)
|
{
|
string tempStr = "";
|
sFirstCol = true;
|
for (int k = 0; k < grdMain.Columns.Count; k++)
|
{
|
if (k > 0 && grdMain.Columns[k].HeaderText.Substring(0, 1) != "h" && grdMain.Columns[k].HeaderText.Substring(0, 1) != "H" && sFirstCol != true)
|
{
|
tempStr += "\t";
|
}
|
if (grdMain.Columns[k].HeaderText.Substring(0, 1) != "h" && grdMain.Columns[k].HeaderText.Substring(0, 1) != "H")
|
{
|
sFirstCol = false;
|
if (grdMain.Rows[j].Cells[k].Value == null)
|
{
|
tempStr += string.Empty;
|
}
|
else
|
{
|
tempStr += grdMain.Rows[j].Cells[k].Value.ToString();
|
}
|
}
|
}
|
tempStr = tempStr.Replace("\n", "");
|
sw.WriteLine(tempStr);
|
}
|
sw.Close();
|
myStream.Close();
|
}
|
catch (Exception ex)
|
{
|
MessageBox.Show(ex.Message);
|
}
|
finally
|
{
|
sw.Close();
|
myStream.Close();
|
}
|
MessageBox.Show("½«´Ë¹¤×÷±íµ¼³öΪexcel¹²ºÄʱ£º" + DateTime.Now.Subtract(start).TotalMilliseconds.ToString() + "ºÁÃë");
|
}
|
}
|
/// <summary>
|
/// Êý¾Ýµ¼³ö
|
/// </summary>
|
public static void DataToExcel2(string sText, DataGridView grdMain)
|
{
|
SaveFileDialog saveFileDialog = new SaveFileDialog();
|
saveFileDialog.Filter = "ExeclÎļþ(*.xls)|*.xls";
|
saveFileDialog.FilterIndex = 0;
|
saveFileDialog.RestoreDirectory = true;
|
saveFileDialog.CreatePrompt = true;
|
saveFileDialog.Title = "Êý¾ÝÊÓͼµ¼³öEXCELÎļþ";
|
saveFileDialog.FileName = sText;
|
saveFileDialog.ShowDialog();
|
if (saveFileDialog.FileName == "")
|
{
|
return;
|
}
|
System.IO.Stream myStream;
|
myStream = saveFileDialog.OpenFile();
|
System.IO.StreamWriter sw = new System.IO.StreamWriter(myStream, System.Text.Encoding.GetEncoding("gb2312"));
|
string str = "";
|
DateTime start = DateTime.Now;
|
try
|
{
|
//д±êÌâ
|
for (int i = 0; i < grdMain.ColumnCount; i++)
|
{
|
if (i > 0)
|
{
|
str += "\t";
|
}
|
str += grdMain.Columns[i].HeaderText;
|
}
|
sw.WriteLine(str);
|
|
//дÄÚÈÝ
|
for (int j = 0; j < grdMain.Rows.Count; j++)
|
{
|
string tempStr = "";
|
for (int k = 0; k < grdMain.Columns.Count; k++)
|
{
|
if (k > 0)
|
{
|
tempStr += "\t";
|
}
|
if (grdMain.Rows[j].Cells[k].Value == null)
|
{
|
tempStr += string.Empty;
|
}
|
else
|
{
|
tempStr += grdMain.Rows[j].Cells[k].Value.ToString();
|
}
|
}
|
sw.WriteLine(tempStr);
|
}
|
sw.Close();
|
myStream.Close();
|
}
|
catch (Exception ex)
|
{
|
MessageBox.Show(ex.Message);
|
}
|
finally
|
{
|
sw.Close();
|
myStream.Close();
|
}
|
MessageBox.Show("½«´Ë¹¤×÷±íµ¼³öΪexcel¹²ºÄʱ£º" + DateTime.Now.Subtract(start).TotalMilliseconds.ToString() + "ºÁÃë");
|
}
|
|
|
|
|
#endregion
|
|
#region Êý¾Ýµ¼³ö
|
|
[DllImport("User32.dll", CharSet = CharSet.Auto)]
|
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
|
public static void DataToExcel(string sText, DataGridView grdMain)
|
{
|
string saveFileName = "";
|
bool fileSaved = false;
|
SaveFileDialog saveDialog = new SaveFileDialog();
|
saveDialog.DefaultExt = "xls";
|
saveDialog.Filter = "ExcelÎļþ|*.xls";
|
saveDialog.FileName = sText;
|
saveDialog.ShowDialog();
|
saveFileName = saveDialog.FileName;
|
if (saveFileName.IndexOf(":") < 0) return; //±»µãÁËÈ¡Ïû
|
//
|
Excel.Application xlApp = new Excel.Application();
|
|
if (xlApp == null)
|
{
|
MessageBox.Show("ÎÞ·¨´´½¨Excel¶ÔÏ󣬿ÉÄÜÄúµÄ»ú×Óδ°²×°Excel", "Ìáʾ");
|
return;
|
}
|
Excel.Workbooks workbooks = xlApp.Workbooks;
|
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
|
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//È¡µÃsheet1
|
//дÈë ±êÌâ
|
for (int i = 0; i < grdMain.ColumnCount; i++)
|
{
|
worksheet.Cells[1, i + 1] = ClsPub.isStrNull(grdMain.Columns[i].HeaderText);
|
}
|
//дÈëÄÚÈÝ
|
for (int r = 0; r < grdMain.RowCount; r++)
|
{
|
for (int i = 0; i < grdMain.ColumnCount; i++)
|
{
|
worksheet.Cells[r + 2, i + 1] = "'" + ClsPub.isStrNull(grdMain.Rows[r].Cells[i].Value);
|
}
|
System.Windows.Forms.Application.DoEvents();
|
}
|
worksheet.Columns.EntireColumn.AutoFit();//Áпí×ÔÊÊÓ¦¡£
|
if (saveFileName != "")
|
{
|
try
|
{
|
workbook.Saved = true;
|
workbook.SaveCopyAs(saveFileName);
|
fileSaved = true;
|
}
|
catch (Exception ex)
|
{
|
fileSaved = false;
|
MessageBox.Show( "µ¼³öÎļþʱ³ö´í,Îļþ¿ÉÄÜÕý±»´ò¿ª£¡\n" + ex.Message, "Ìáʾ");
|
}
|
}
|
else
|
{
|
fileSaved = false;
|
}
|
xlApp.Quit();
|
//
|
IntPtr t = new IntPtr(xlApp.Hwnd); //µÃµ½Õâ¸ö¾ä±ú£¬¾ßÌå×÷ÓÃÊǵõ½Õâ¿éÄÚ´æÈë¿Ú
|
int k = 0;
|
GetWindowThreadProcessId(t, out k); //µÃµ½±¾½ø³ÌΨһ±êÖ¾k
|
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); //µÃµ½¶Ô½ø³ÌkµÄÒýÓÃ
|
p.Kill(); //¹Ø±Õ½ø³Ìk
|
xlApp = null;
|
//GC.Collect();//Ç¿ÐÐÏú»Ù
|
//if (fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //´ò¿ªEXCEL
|
MessageBox.Show("Òý³öÍê±Ï£¡");
|
}
|
|
// ¶ÁÈ¡Òþ²ØÁÐdatagridview ²¢ÉèÖà ¶ÔÆ뷽ʽ
|
//bug: ż¶û ÎÞ·¨´´½¨±íÍ·list Êý¾Ý ***** ¼±Ðè½â¾ö
|
public static List<string> GetGridViewHide(DataGridView oGrd, string KeyItem)
|
{
|
List<string> Hide = new List<string>();
|
int Cols;
|
char c = Convert.ToChar(",");
|
char s = Convert.ToChar("|");
|
string sStr = Pub_Class.ClsIni.ReadIni("GridHide", KeyItem, DBUtility.ClsPub.AppPath + @"\" + "grdWidth.wyini");
|
if (sStr != "ûÓÐÕÒµ½£¡")
|
{
|
string[] Col = sStr.Split(c);
|
for (int i = 0; i < Col.Length; i++)
|
{
|
string[] sCol = Col[i].Split(s);
|
if (sCol.Length > 1)
|
{
|
if (ClsPub.isStrNull(sCol[1]) == "0")
|
{
|
//Hide.Add(oGrd.Columns[ClsPub.isInt(sCol[0])].Name); //»ñȡδÒþ²ØµÄÁмÓÈëlist
|
Hide.Add(oGrd.Columns[ClsPub.isInt(sCol[0])].HeaderText);
|
}
|
|
}
|
//
|
|
}
|
}
|
|
return Hide;
|
}
|
|
|
//µ¼³ö·ÇÒþ²ØÁÐ
|
/// <summary>
|
/// µ¼³ö·ÇÒþ²ØÁÐ
|
/// </summary>
|
/// <param name="sText"></param>
|
/// <param name="grdMain"></param>
|
/// <param name="KeyItem"></param>
|
public static void Data2Excel(string sText, DataGridView grdMain, string KeyItem)
|
{
|
string saveFileName = "";
|
bool fileSaved = false;
|
List<string> hide = GetGridViewHide(grdMain, KeyItem);
|
if (hide.Count == 0)
|
{
|
DataToExcel(sText, grdMain);
|
return;
|
}
|
SaveFileDialog saveDialog = new SaveFileDialog();
|
Excel.Application xlApp = new Excel.Application();
|
if (xlApp == null)
|
{
|
MessageBox.Show("ÎÞ·¨´´½¨ExcelÎļþ£¬¿ÉÄÜÄúµÄµçÄÔδ°²×°Excel", "Ìáʾ");
|
return;
|
}
|
string av = xlApp.Version;
|
if (Convert.ToDouble(xlApp.Version) < 12)
|
{
|
saveDialog.DefaultExt = "xls";
|
saveDialog.Filter = "ExcelÎļþ|*.xls";
|
}
|
else
|
{
|
saveDialog.DefaultExt = "xlsx";
|
saveDialog.Filter = "ExcelÎļþ|*.xlsx";
|
}
|
saveDialog.FileName = sText;
|
saveDialog.ShowDialog();
|
saveFileName = saveDialog.FileName;
|
if (saveFileName.IndexOf(":") < 0) return; //±»µãÁËÈ¡Ïû
|
|
|
|
Excel.Workbooks workbooks = xlApp.Workbooks;
|
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
|
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1]; //È¡µÃsheet1
|
//дÈë ±êÌâ
|
for (int i = 0; i < hide.Count; i++)
|
{
|
worksheet.Cells[1, i + 1] = hide[i];
|
}
|
|
for (int r = 0; r < grdMain.RowCount; r++)
|
{
|
for (int i = 0; i < hide.Count; i++)
|
{
|
|
worksheet.Cells[r + 2, i + 1] = "" + ClsPub.isStrNull(grdMain.Rows[r].Cells[DBUtility.Gy_BaseFun.Fun_GetCol(hide[i], grdMain)].Value);
|
}
|
System.Windows.Forms.Application.DoEvents();
|
}
|
|
//for (int r = 0; r < grdMain.RowCount; r++)
|
//{
|
// for (int i = 0; i < grdMain.ColumnCount; i++)
|
// {
|
// worksheet.Cells[r + 2, i + 1] = "'" + ClsPub.isStrNull(grdMain.Rows[r].Cells[i].Value);
|
// }
|
// System.Windows.Forms.Application.DoEvents();
|
//}
|
|
|
worksheet.Columns.EntireColumn.AutoFit();//Áпí×ÔÊÊÓ¦¡£
|
if (saveFileName != "")
|
{
|
try
|
{
|
workbook.Saved = true;
|
workbook.SaveCopyAs(saveFileName);
|
fileSaved = true;
|
}
|
catch (Exception ex)
|
{
|
fileSaved = false;
|
MessageBox.Show("µ¼³öÎļþʱ³ö´í,Îļþ¿ÉÄÜÕý±»´ò¿ª£¡\n" + ex.Message, "Ìáʾ");
|
}
|
}
|
else
|
{
|
fileSaved = false;
|
}
|
xlApp.Quit();
|
//
|
IntPtr t = new IntPtr(xlApp.Hwnd); //µÃµ½Õâ¸ö¾ä±ú£¬¾ßÌå×÷ÓÃÊǵõ½Õâ¿éÄÚ´æÈë¿Ú
|
int k = 0;
|
GetWindowThreadProcessId(t, out k); //µÃµ½±¾½ø³ÌΨһ±êÖ¾k
|
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); //µÃµ½¶Ô½ø³ÌkµÄÒýÓÃ
|
p.Kill(); //¹Ø±Õ½ø³Ìk
|
xlApp = null;
|
//GC.Collect();//Ç¿ÐÐÏú»Ù
|
//if (fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //´ò¿ªEXCEL
|
MessageBox.Show("Òý³öÍê±Ï£¡");
|
}
|
|
|
#endregion
|
|
}
|
}
|
