Excel导出导入代码

来源：互联网发布：淘宝助理导出csv 编辑：程序博客网时间：2024/06/05 20:01

//Excel导出不需要创建Excel的
    public class ExcelOut
    {
        private string cHouseCode;//文件名称，路径名
        private string FileName =@"D:\数据导出\";
        LogWriter logWriter = new LogWriter();
        //接受参数查询数据并导出到指定Excel
        public int DBWriteToExcel(System.Data.DataSet myds,string FileName)
        {
            if (!Directory.Exists(FileName))
            {
                Directory.CreateDirectory(FileName);
            }
            int result = 0;
            //DataSet myds = DSAddRowColume(DSS);
            if (cHouseCode == "维修/加工库")
            {
                FileName += "维修加工库库存Excel.xls";
            }
            else
            {
                FileName += cHouseCode + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute + DateTime.Now.Second + "闪迪.xls";
            }
            if (FileName != "")
            {
                FileStream fs = new FileStream(FileName, FileMode.OpenOrCreate);
                StreamWriter writer = new StreamWriter(fs, Encoding.Unicode);
                try
                {
                    //取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符
                    StringBuilder colHeaders = new StringBuilder();
                    for (int i = 0; i < myds.Tables[0].Columns.Count; i++)
                    {
                        colHeaders.Append(myds.Tables[0].Columns[i].Caption.ToString() + "\t");
                        //colHeaders += myds.Tables[0].Columns[i].Caption.ToString() + "\t";
                    }
                    colHeaders.Append("\n");
                    //输出流中写入取得的数据信息
                    writer.Write(colHeaders.ToString());
                    //逐行处理数据
                    StringBuilder ls_item = new StringBuilder();
                    for (int i = 0; i < myds.Tables[0].Rows.Count; i++)
                    {
                        for (int j = 0; j < myds.Tables[0].Columns.Count; j++)
                        {
                            ls_item.Append(myds.Tables[0].Rows[i][j].ToString().Replace("\n", "").Replace("\t", "") + "\t"); ;
                        }
                        ls_item.Append("\n");
                    }
                    //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据
                    writer.Write(ls_item.ToString());
                    result = 1;
                }
                catch (Exception excep)
                {
                    logWriter.WriteLog("未知错误2   ", excep.Message.ToString(), "", LogLevel.ServiceAction);
                    result = 0;
                }
                finally
                {
                    writer.Flush();
                    writer.Close();
                    fs.Close();
                }
            }
            else
            {
                //FileName += cHouseCode+DateTime.Now.Year.ToString()+DateTime.Now.Month.ToString()+DateTime.Now.Day.ToString()+DateTime.Now.Hour.ToString()+DateTime.Now.Minute+DateTime.Now.Second + "库存.xls";
                result = 0;
            }

            return result;
        }

        private void KillProcess(string processName)
        {
            System.Diagnostics.Process myproc = new System.Diagnostics.Process();
            //得到所有打开的进程
            try
            {
                foreach (System.Diagnostics.Process thisproc in System.Diagnostics.Process.GetProcessesByName(processName))
                {
                    if (!thisproc.CloseMainWindow())
                    {
                        thisproc.Kill();
                    }
                }
            }
            catch (Exception Exc)
            {
                throw new Exception("", Exc);
            }
        }

}

读取EXCEL导入C# 返回DS

public DataSet ReadExcel(string filePath)
        {
            if (filePath=="")
            {
                return null;
            }
            //文件格式不正确
            if (!filePath.Substring(filePath.LastIndexOf('.')).ToUpper().Equals(".XLSX"))
            {
                return null;
            }

            //找不到文件
            if (!File.Exists(filePath))
            {
                return null;
            }

            DataSet ds = null;
            try
            {
                ds = new DataSet();
                string strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", filePath);

                using (OleDbConnection conn = new OleDbConnection(strConn))
                {
                    conn.Open();
                    //获取第一个SheetName
                    //包含excel中表名的字符串数组

                    DataTable SheetList = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });

                    //找不到数据
                    if (SheetList.Rows.Count <= 0)
                    {
                        return null;
                    }
                    string TableName = Convert.ToString(SheetList.Rows[0]["TABLE_NAME"]);
                    OleDbDataAdapter odda = new OleDbDataAdapter("select * from [" + TableName + "]", strConn);
                    odda.Fill(ds, TableName);
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return ds;
        }