private void buttonCommand11_Click(object sender, CommandEventArgs e)
{
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Filter = "Excel 文件(*.xls)|*.xls|Excel 文件(*.xlsx)|*.xlsx|所有文件(*.*)|*.*";//以文件“*.xls”导出
saveFileDialog1.FileName = cboHospital.ComboBox.Text + "_" + cboYear.ComboBox.Text;
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
string sFilePathName = saveFileDialog1.FileName;
// MessageBox.Show(sFilePathName);
DataSet ds = new DataSet();
DataTable dtdrug = new DataTable("医院在用药品");
DataRow drdrug = dtdrug.NewRow();
//dtdrug.Columns.Add("序号", System.Type.GetType("System.String"));
dtdrug.Columns.Add("药品编码", System.Type.GetType("System.String"));
dtdrug.Columns.Add("药品名称", System.Type.GetType("System.String"));
dtdrug.Columns.Add("药品规格", System.Type.GetType("System.String"));
dtdrug.Columns.Add("药品单位", System.Type.GetType("System.String"));
dtdrug.Columns.Add("药品厂家", System.Type.GetType("System.String"));
dtdrug.Columns.Add("抗菌药", System.Type.GetType("System.String"));
dtdrug.Columns.Add("中标", System.Type.GetType("System.String"));
dtdrug.Columns.Add("统计", System.Type.GetType("System.String"));
dtdrug.Rows.Add(drdrug);
DataTable dtmaterial = new DataTable("医用耗材");
DataRow drmaterial = dtmaterial.NewRow();
// dtmaterial.Columns.Add("序号", System.Type.GetType("System.String"));
dtmaterial.Columns.Add("耗材编码", System.Type.GetType("System.String"));
dtmaterial.Columns.Add("耗材名称", System.Type.GetType("System.String"));
dtmaterial.Columns.Add("耗材单位", System.Type.GetType("System.String"));
dtmaterial.Columns.Add("耗材厂家", System.Type.GetType("System.String"));
dtmaterial.Columns.Add("耗材规格", System.Type.GetType("System.String"));
dtmaterial.Columns.Add("中标", System.Type.GetType("System.String"));
dtmaterial.Columns.Add("统计", System.Type.GetType("System.String"));
//DataTable dtbasedata = new DataTable("基础数据");
//DataTable dtbasedata=dsSubmitData.Tables[0].Copy();
//DataRow drbasedata = dtbasedata.NewRow();
//// dtbasedata.Columns.Add("序号", System.Type.GetType("System.String"));
//dtbasedata.Columns.Add("名称", System.Type.GetType("System.String"));
//dtbasedata.Columns.Add("值", System.Type.GetType("System.String"));
//dtbasedata.Columns.Add("说明", System.Type.GetType("System.String"));
//string sql = "select NAME AS 名称,VALUE AS 值,info AS 说明 from hpes_submit_data where hid=(select id from hpes_hospital where name='" + cboHospital.ComboBox.Text + "') and yid=(select yid from hpes_time where year='" + cboYear.ComboBox.Text+"')";
string sql = "select NAME AS 名称,info AS 说明 from hpes_submit_dictionary";
DataTable dtbasedata = new DBOperate().GetSubmitData(sql).Copy();
dtbasedata.Columns.Add("值",typeof(string));
//dtbasedata.Columns.Add("HID", typeof(int));
dtbasedata.TableName = "基础数据";
ds.Tables.Add(dtbasedata);
ds.Tables.Add(dtmaterial);
ds.Tables.Add(dtdrug);
MessageBox.Show(DataSetToExcell.ExportToExcel(ds, sFilePathName));
}
}
public void TransferData(string excelFile)
{
DBOperate operate = new DBOperate();//创建操作数据库对象
int hid = operate.GetIdByName("select id from hpes_hospital where name='" + cboHospital.ComboBox.Text + "'");
int yid = operate.GetIdByName("select yid from hpes_time where year='" + cboYear.ComboBox.Text + "'");
try
{
//string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties='Excel 12.0;HDR=YES;IMEX=1;'"
;
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelFile + ";" + "Extended Properties='Excel 12.0;HDR=YES;IMEX=1;'";
//(1)HDR表示要把第一行作为数据还是作为列名,作为数据用HDR=no,作为列名用HDR=yes;通过Imex=1来把混合型作为文本型读取,避免 null值。
//(2)左右两个单引号不能少
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataSet ds = new DataSet();
DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
string[] strTableNames = new string[dtSheetName.Rows.Count]; //包含excel中表名的字符串数组
for (int k = 0; k < dtSheetName.Rows.Count; k++)
{
strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
string sheetName = strTableNames[k].Substring(0, strTableNames[k].Length - 1);
string targetTable = "";
string strExcel = "";
if (sheetName.Equals("基础数据"))
{
targetTable = "hpes_submit_data";
strExcel = "select 名称,值,说明 from [" + strTableNames[k] + "]";
}
else if (sheetName.Equals("医用耗材"))
{
targetTable = "hpes_material";
strExcel = "select 耗材编码,耗材名称,耗材单位,耗材厂家,耗材规格,中标,统计 from [" + strTableNames[k] + "]";
}
else if (sheetName.Equals("医院在用药品"))
{
targetTable = "hpes_drug";
//strexcel = "select 药品编码,药品名称,药品规格,药品单位,药品厂家,抗菌药,中标,统计 from [" + strtablenames[k] + "]";
strExcel = "select * from [" + strTableNames[k] + "]";
}
OleDbDataAdapter myCommand = null;
myCommand = new OleDbDataAdapter(strExcel, strConn);
myCommand.Fill(ds, sheetName);
ds.Tables[k].Columns.Add("HID", typeof(int));
ds.Tables[k].Columns.Add("YID", typeof(int));
if (sheetName.Equals("基础数据"))
{
for (int i = 0; i < ds.Tables[sheetName].Rows.Count; i++) //开始循环赋值
{
//ds.Tables[k].Rows[i]["HID"] = hid;
//ds.Tables[k].Rows[i]["YID"] = yid;
string sql = "if exists (select * from hpes_submit_data where name='" + ds.Tables[k].Rows[i]["名称"] + "')"
+ "begin update hpes_submit_data set value='" + ds.Tables[k].Rows[i]["值"] + "',info='" + ds.Tables[k].Rows[i]["说明"] + "',hid=" + hid + ",yid=" + yid
+ " where name='" + ds.Tables[k].Rows[i]["名称"] + "' end"
+ " else begin insert into hpes_submit_data(name,value,info,hid,yid) values('" + ds.Tables[k].Rows[i]["名称"] + "','" + ds.Tables[k].Rows[i]["值"] + "','" + ds.Tables[k].Rows[i]["说明"] + "'," + hid + "," + yid + ") end";
//string sql = "insert into hpes_submit_data(name,value,info,hid,yid) values('" + ds.Tables[k].Rows[i]["名称"] + "','" + ds.Tables[k].Rows[i]["值"] + "','" + ds.Tables[k].Rows[i]["说明"] + "'," + hid + "," + yid + ")";
int num = new DBOperate().UpdateSubmitData(sql);
}
}
else
{
for (int i = 0; i < ds.Tables[k].Rows.Count; i++) //开始循环赋值
{
ds.Tables[k].Rows[i]["HID"] = hid;
ds.Tables[k].Rows[i]["YID"] = yid;
}
System.Data.SqlClient.SqlBulkCopy bcp = DBConnection.SqlBulkCopy();
using (bcp)
{
bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);
bcp.BatchSize = 100;//每次传输的行数
bcp.NotifyAfter = 100;//进度提示的行数
bcp.DestinationTableName = targetTable;//目标表
if (sheetName.Equals("医用耗材"))
{
bcp.ColumnMappings.Add("耗材编码", "MCODE");
bcp.ColumnMappings.Add("耗材名称", "NAME");
bcp.ColumnMappings.Add("耗材单位", "UNIT");
bcp.ColumnMappings.Add("耗材厂家", "FACTORY");
bcp.ColumnMappings.Add("耗材规格", "SPECIFICATION");
bcp.ColumnMappings.Add("中标", "BID");
bcp.ColumnMappings.Add("统计", "STATISTICS");
bcp.ColumnMappings.Add("HID", "HID");
bcp.ColumnMappings.Add("YID", "YID");
}
else if (sheetName.Equals("医院在用药品"))
{
bcp.ColumnMappings.Add("药品编码", "DCODE");
bcp.ColumnMappings.Add("药品名称", "NAME");
bcp.ColumnMappings.Add("药品规格", "SPECIFICATION");
bcp.ColumnMappings.Add("药品单位", "UNIT");
bcp.ColumnMappings.Add("药品厂家", "FACTORY");
bcp.ColumnMappings.Add("抗菌药", "ANTIBACTERIAL");
bcp.ColumnMappings.Add("中标", "BID");
bcp.ColumnMappings.Add("统计", "STATISTICS");
bcp.ColumnMappings.Add("HID", "HID");
bcp.ColumnMappings.Add("YID", "YID");
}
bcp.WriteToServer(ds.Tables[k]);
bcp.Close();
}
}
lblSysMessage.Visible = true;
uiProgressBar1.Visible = true;
uiProgressBar1.Value = 0;
uiProgressBar1.Minimum = 0;
uiProgressBar1.Maximum = ds.Tables[k].Rows.Count;
// MessageBox.Show("进度条长度"+uiProgressBar1.Maximum.ToString());
for (int i = 0; i < uiProgressBar1.Maximum; i++)
{
uiProgressBar1.Value = i + 1;
Application.DoEvents();
this.lblSysMessage.Text = Convert.ToString("已经导入" + uiProgressBar1.Value + "条数据");
}
}
conn.Close();
MessageBox.Show("数据导入成功！");
}
catch (Exception ex)
{
MessageBox.Show("数据导入异常！请检查文件名称及数据格式。");
}
}