CSV 导出

 

    #region CSV导出
    /// <summary>
    /// CSV导出
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnCSV_Click(object sender, EventArgs e)
    {
        // 模块名
        String module = this.pageId + ":btnCSV_Click";

        try
        {
            // 函数开始时写Log
            LogHelper.f_Info(module, CommonLogString.Entry);

            //检索条件设定

            ArrayList paramList = new ArrayList();

            paramList.Clear();

            // 出库要求批号
            paramList.Add(hidBookingRequestNo.Value);

            // 品目
            paramList.Add(hidItemID.Value);

            // CODE
            paramList.Add(hidCode.Value);

            // 出库序列号
            paramList.Add(hidSerialNoFrom.Value);

            paramList.Add(hidSerialNoTo.Value);

            // 机种

            paramList.Add(hidModel.Value);

            // WP
            paramList.Add(hidWP.Value);

            // 出库日期

            paramList.Add(hidBookingDateFrom.Value);

            paramList.Add(hidBookingDateTo.Value);

            // 出库操作者

            paramList.Add(hidBookingBy.Value);

            // 要求数量

            paramList.Add(hidRequestQty.Value);

            // 出库数量

            paramList.Add(hidBookingQty.Value);

            DataTable dtbl = new DataTable();

            // 检索出库详细信息
            dtbl = DbHelper.ExecuteCombine(pageId, SQL001, paramList);

            if (dtbl.Rows.Count > 65536)
            {
                // 確認メッセージを表示する
                string q0014 = msgHelper.f_GetMsg("数据量已超过Excel行数的最大范围，继续吗？");
                f_GetDisplayInfo().f_SetConfirmMessage(q0014, Page);

                return;
            }

            //获得sheet名(每个DataTable对应一个sheet)
            //从资源文件取得画面信息
            string resourceValue = (string)base.GetLocalResourceObject("pageInfoResource");
            string[] sheetname ={ resourceValue };

            //获得Excel文件名

            string filename = resourceValue;

            // 列名国际化
            Int32 columncount = dtbl.Columns.Count;
            for (Int32 iCol = 0; iCol < columncount; iCol++)
            {
                string columnName = dtbl.Columns[iCol].ColumnName;
                string resourcekey = columnName + "Resource";
                string resourcesValue = (string)base.GetLocalResourceObject(resourcekey);
                filename = filename + "," + resourcesValue;
            }

            //获得列索引

            int columnIndexFrom = 0;

            int columnIndexTo = dtbl.Columns.Count - 1;

            //生成Excel文件
            new PrintHelper().f_WriteCSV(dtbl, filename, columnIndexFrom, columnIndexTo);

            // 函数结束时写Log
            LogHelper.f_Info(module, CommonLogString.Exit);
        }
        catch (Exception ex)
        {
            LogHelper.f_Fatal(module, ex);
            throw ex;
        }
    }
    #endregion

 

        /// <param name="dt">数据表</param>
        /// <param name="Header">文件头</param>
        /// <param name="columnCount">指定显示DataTable的第几列到第几列，不包括GridView的模板列</param>
        public void f_WriteCSV(DataTable dt, string Header, int from, int to)
        {
            StringWriter sw = new StringWriter();
            sw.WriteLine(Header);

            string outLine = string.Empty;
            int No = 0;//生成自动编号
            string value = string.Empty;
            string strnew = string.Empty;
            string strres = string.Empty;
            foreach (DataRow drow in dt.Rows)
            {
                for (int i = from; i < to; i++)
                {
                    //隐藏字段不写入客户端
                    value = DbHelper.Nc(drow, dt.Columns[i].ColumnName).ToString();

                    //判断列值是否为空
                    if (value.Equals("") || value == null)
                    {
                        strres = "";

                    }
                    else
                    {
                        // change by ZhuangGuoqiang in 2009/11/30 start
                        //if (value.Contains(","))
                        //{
                        //    strarr = value.Split(',');
                        //    foreach (string str in strarr)
                        //    {
                        //        strres += str;

                        //    }

                        //}
                        //else
                        //{
                        //    strres = value;
                        //}
                        strres = value.Replace(",", "，").Replace("\n", "").Replace("\r", "");
                        // change by ZhuangGuoqiang in 2009/11/30 end
                    }

                    outLine = outLine + strres + ",";
                    strres = string.Empty;

                }
                //去除每行最后一个逗号
                if (outLine.EndsWith(","))
                {
                    outLine = outLine.Substring(0, outLine.Length - 1);
                }
                sw.WriteLine(No + 1 + "," + outLine);
                No++;
                outLine = string.Empty;
            }
            sw.Flush();
            sw.Close();
            HttpContext.Current.Response.ContentType = "application/ms-excel";

            //进行编码处理
            string language = HttpContext.Current.Request.UserLanguages[0];

            if (language.Contains("cn")) //中文
            {
                HttpContext.Current.Response.Charset = Encoding.GetEncoding("gb2312").EncodingName;
                HttpContext.Current.Response.ContentEncoding = Encoding.GetEncoding("gb2312");
            }
            else if (language.Contains("ja")) //日文
            {
                HttpContext.Current.Response.Charset = Encoding.GetEncoding("shift_jis").EncodingName;
                HttpContext.Current.Response.ContentEncoding = Encoding.GetEncoding("shift_jis");
            }
            else //其他语言
            {
                HttpContext.Current.Response.Charset = Encoding.UTF8.EncodingName;
                HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
            }

            //CSV文件以页面ID加上年月日，小时分秒为文件名的形式保存
            string curPageId = (string)HttpContext.Current.Session["curPageId"];
            string strdate = DateTime.Now.ToString("yyyyMMddHHmmss");
            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + curPageId + strdate + ".csv");
            HttpContext.Current.Response.Write(sw);
            HttpContext.Current.Response.End();
        }

 

       public static object Nc(DataRow row, String colName)
        {
            object rtn = null;

            string module = null;
            module = "DbHeper.Nc(row,colName)";

            //函数开始Log
            LogHelper.f_Info(module, CommonLogString.Entry);

            try
            {
                string coltype;
                coltype = row.Table.Columns[colName].DataType.ToString();

                if (Convert.IsDBNull(row[colName]))
                {
                    switch (coltype)
                    {
                        case "System.Boolean":  // bit系਍ഀ
                            rtn = false;
                            break;

                        case "System.Int16":  // 数値系
                            rtn = 0;
                            break;
                        case "System.Int32":
                            rtn = 0;
                            break;
                        case "System.Int64":
                            rtn = 0;
                            break;
                        case "System.Decimal":
                            rtn = (Decimal)0;
                            break;
                        case "System.Double":
                            rtn = (Double)0;
                            break;
                        case "System.Single":
                            rtn = (Single)0;
                            break;

                        case "System.String":  // 文字列系
                            rtn = String.Empty;
                            break;
                        default:     // その他
                            // その他はそのままの値を返す
                            rtn = row[colName];
                            break;
                    }
                }
                else
                {
                    switch (coltype)
                    {
                        case "System.String":  // 文字列系
                            rtn = row[colName].ToString().Trim();
                            break;
                        default:     // その他
                            rtn = row[colName];
                            break;
                    }
                }

            }
            catch (Exception ex)
            {
                LogHelper.f_Fatal(module, ex);
                throw (ex);
            }

            //函数结束Log
            LogHelper.f_Info(module, CommonLogString.Exit);

            return rtn;
        }