using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

/// <summary>

    /// DataTable导出Excel

    /// </summary>

    /// <param name="dt">datatable数据源</param>

    /// <param name="strFileName">文件名</param>

    /// <param name="strSheetName">工作簿名</param>

    public void ExportExcel(DataTable dt, string strFileName, string strSheetName)

    {

        HSSFWorkbook book = new HSSFWorkbook();

        ISheet sheet = book.CreateSheet(strSheetName);

        IRow headerrow = sheet.CreateRow();

        ICellStyle style = book.CreateCellStyle();

        style.Alignment = HorizontalAlignment.Center;

        style.VerticalAlignment = VerticalAlignment.Center;

        HSSFRow dataRow = (HSSFRow)sheet.CreateRow();

        string strColumns = "治具编号,验收项目,数据,连片类型,Gerber,光板料号,适用机种,制作厂家,厂商确认,进厂日期,应力测试日期";

        string[] strArry = strColumns.Split(',');

        for (int i = ; i < strArry.Length; i++)

        {

            dataRow.CreateCell(i).SetCellValue(strArry[i]);

            dataRow.GetCell(i).CellStyle = style;

        }

        for (int i = ; i < dt.Rows.Count; i++)

        {

            dataRow = (HSSFRow)sheet.CreateRow(i + );

            for (int j = ; j < dt.Columns.Count; j++)

            {

                string ValueType = "";

                string Value = "";

                if (dt.Rows[i][j].ToString() != null)

                {

                    ValueType = dt.Rows[i][j].GetType().ToString();

                    Value = dt.Rows[i][j].ToString();

                }

                switch (ValueType)

                {

                    case "System.String"://字符串类型

                        dataRow.CreateCell(j).SetCellValue(Value);

                        break;

                    case "System.DateTime"://日期类型

                        System.DateTime dateV;

                        System.DateTime.TryParse(Value, out dateV);

                        dataRow.CreateCell(j).SetCellValue(dateV);

                        break;

                    case "System.Boolean"://布尔型

                        bool boolV = false;

                        bool.TryParse(Value, out boolV);

                        dataRow.CreateCell(j).SetCellValue(boolV);

                        break;

                    case "System.Int16"://整型

                    case "System.Int32":

                    case "System.Int64":

                    case "System.Byte":

                        int intV = ;

                        int.TryParse(Value, out intV);

                        dataRow.CreateCell(j).SetCellValue(intV);

                        break;

                    case "System.Decimal"://浮点型

                    case "System.Double":

                        double doubV = ;

                        double.TryParse(Value, out doubV);

                        dataRow.CreateCell(j).SetCellValue(doubV);

                        break;

                    case "System.DBNull"://空值处理

                        dataRow.CreateCell(j).SetCellValue("");

                        break;

                    default:

                        dataRow.CreateCell(j).SetCellValue("");

                        break;

                }

                dataRow.GetCell(j).CellStyle = style;

                //设置宽度

                sheet.SetColumnWidth(j, (Value.Length + ) * );

            }

        }

        MemoryStream ms = new MemoryStream();

        book.Write(ms);

        Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8)));

        Response.BinaryWrite(ms.ToArray());

        Response.End();

        book = null;

        ms.Close();

        ms.Dispose();

    }