using System.IO;
using System.Data;
using System;
using System.Text;
using System.Drawing;
using System.Collections.Generic;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Eval;
using NPOI.HSSF.Util;
using NPOI.SS.Util;

namespace UAS_MES_NEW.DataOperate
{
    class ExcelHandler
    {
        DataHelper dh = new DataHelper();
        /// <summary>
        /// 导出Excel,返回文件在客户端的路径
        /// </summary>
        public string ExportExcel(DataTable dt, string FolderPath)
        {
            //创建一个内存流，用来接收转换成Excel的内容
            MemoryStream ms;
            ms = DataTableToExcel(dt);
            //以系统当前时间命名文件，FileMode.Create表示创建文件，FileAccess.Write表示拥有写的权限
            string filePath = @FolderPath + "\\" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
            FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write);
            byte[] data = ms.ToArray();
            fs.Write(data, 0, data.Length);
            fs.Flush();
            //释放当前Excel文件，否则打开文件的时候会显示文件被占用
            ms.Dispose();
            fs.Dispose();
            return filePath;
        }


        /// <summary>
        /// 导出Excel,返回文件在客户端的路径
        /// </summary>
        public string ExportExcel(System.Windows.Forms.DataGridView dt, string FolderPath)
        {
            //创建一个内存流，用来接收转换成Excel的内容
            MemoryStream ms;
            ms = DataTableToExcel(dt);
            //以系统当前时间命名文件，FileMode.Create表示创建文件，FileAccess.Write表示拥有写的权限
            string filePath = @FolderPath + "\\" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
            FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write);
            byte[] data = ms.ToArray();
            fs.Write(data, 0, data.Length);
            fs.Flush();
            //释放当前Excel文件，否则打开文件的时候会显示文件被占用
            ms.Dispose();
            fs.Dispose();
            return filePath;
        }

        /// <summary>
        /// 导出Excel,返回文件在客户端的路径
        /// </summary>
        public string ExportExcel_BAIDU(DataTable dt, DateTime begindate, int DateNum, string FolderPath)
        {
            //创建一个内存流，用来接收转换成Excel的内容
            MemoryStream ms;
            ms = DataTableToExcel_BAIDU(dt, begindate, DateNum);
            //以系统当前时间命名文件，FileMode.Create表示创建文件，FileAccess.Write表示拥有写的权限
            string filePath = @FolderPath + "\\" + DateTime.Now.ToString("yyyyMMddHHmmss") + "各工序直通率.xls";
            FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write);
            byte[] data = ms.ToArray();
            fs.Write(data, 0, data.Length);
            fs.Flush();
            //释放当前Excel文件，否则打开文件的时候会显示文件被占用
            ms.Dispose();
            fs.Dispose();
            return filePath;
        }

        /// <summary>
        /// 导入Excel
        /// </summary>
        public DataTable ImportExcel(string FolderPath, string TableName)
        {
            DataTable dt = new DataTable();
            dt.TableName = TableName;
            if (FolderPath.Contains(".xls") || FolderPath.Contains(".xlsx") || FolderPath.Contains(".XLS") || FolderPath.Contains(".XLSX"))
            {
                using (FileStream file = new FileStream(FolderPath, FileMode.Open, FileAccess.Read))
                {
                    //获取文件流
                    HSSFWorkbook workbook = new HSSFWorkbook(file);
                    ISheet sheet = workbook.GetSheetAt(0);
                    //获取所有的列名
                    foreach (ICell item in sheet.GetRow(sheet.FirstRowNum).Cells)
                    {
                        dt.Columns.Add(item.ToString().Trim(), typeof(string));
                    }

                    System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                    while (rows.MoveNext())
                    {
                        IRow row = (HSSFRow)rows.Current;
                        if (row.RowNum == sheet.FirstRowNum)
                        {
                            continue;
                        }

                        DataRow dr = dt.NewRow();
                        foreach (ICell item in row.Cells)
                        {
                            switch (item.CellType)
                            {
                                case CellType.BOOLEAN:
                                    dr[item.ColumnIndex] = item.BooleanCellValue;
                                    break;
                                case CellType.ERROR:
                                    dr[item.ColumnIndex] = ErrorEval.GetText(item.ErrorCellValue);
                                    break;
                                case CellType.FORMULA:
                                    switch (item.CachedFormulaResultType)
                                    {
                                        case CellType.BOOLEAN:
                                            dr[item.ColumnIndex] = item.BooleanCellValue;
                                            break;
                                        case CellType.ERROR:
                                            dr[item.ColumnIndex] = ErrorEval.GetText(item.ErrorCellValue);
                                            break;
                                        case CellType.NUMERIC:
                                            if (DateUtil.IsCellDateFormatted(item))
                                            {
                                                dr[item.ColumnIndex] = item.DateCellValue.ToString("yyyy-MM-dd hh:MM:ss");
                                            }
                                            else
                                            {
                                                dr[item.ColumnIndex] = item.NumericCellValue;
                                            }
                                            break;
                                        case CellType.STRING:
                                            string str = item.StringCellValue;
                                            if (!string.IsNullOrEmpty(str))
                                            {
                                                dr[item.ColumnIndex] = str.ToString();
                                            }
                                            else
                                            {
                                                dr[item.ColumnIndex] = null;
                                            }
                                            break;
                                        case CellType.Unknown:
                                        case CellType.BLANK:
                                        default:
                                            dr[item.ColumnIndex] = string.Empty;
                                            break;
                                    }
                                    break;
                                case CellType.NUMERIC:
                                    if (DateUtil.IsCellDateFormatted(item))
                                    {
                                        dr[item.ColumnIndex] = item.DateCellValue.ToString("yyyy-MM-dd hh:MM:ss");
                                    }
                                    else
                                    {
                                        dr[item.ColumnIndex] = item.NumericCellValue;
                                    }
                                    break;
                                case CellType.STRING:
                                    string strValue = item.StringCellValue;
                                    if (!string.IsNullOrEmpty(strValue))
                                    {
                                        dr[item.ColumnIndex] = strValue.ToString();
                                    }
                                    else
                                    {
                                        dr[item.ColumnIndex] = null;
                                    }
                                    break;
                                case CellType.Unknown:
                                case CellType.BLANK:
                                default:
                                    dr[item.ColumnIndex] = string.Empty;
                                    break;
                            }
                        }
                        dt.Rows.Add(dr);
                    }
                }
            }
    
            return dt;
        }

        public void WriteTxt(DataTable dt, string FolderPath, string FileName)
        {
            StreamWriter sr;
            string filePath = @FolderPath;
            if (File.Exists(filePath + "\\" + FileName))   //如果文件存在,则创建File.AppendText对象   
            {
                File.Delete(filePath + "\\" + FileName);
            }
            sr = File.CreateText(filePath + "\\" + FileName);
            StringBuilder sb = new StringBuilder();
            string Title = "";
            foreach (DataColumn dc in dt.Columns)
            {
                Title += string.Format("{0,10}", dc.ColumnName.ToString());
            }
            sr.WriteLine(Title + "\r");
            foreach (DataRow dr in dt.Rows)
            {
                string text = "";
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    text += String.Format("{0,10}", dr[i].ToString());
                }
                sr.WriteLine(text + "\r");
            }
            sr.Close();
        }

        /// <summary>
        /// 将DataTable形式的数据转成Excel格式的，然后用字节流的形式写入文件
        /// </summary>
        /// <param name="DataTable"></param>
        /// <returns></returns>
        public MemoryStream DataTableToExcel(DataTable DataTable)
        {
            //创建内存流
            MemoryStream ms = new MemoryStream();
            //创建一个Book，相当于一个Excel文件
            HSSFWorkbook book = new HSSFWorkbook();
            //Excel中的Sheet
            ISheet sheet = book.CreateSheet("sheet1");
            //获取行数量和列数量
            int rowNum = DataTable.Rows.Count;
            int columnNum = DataTable.Columns.Count;
            //设置列的宽度，根据首行的列的内容的长度来设置
            for (int i = 0; i < columnNum; i++)
            {
                int dataLength = DataTable.Columns[i].ColumnName.Length;
                sheet.SetColumnWidth(i, dataLength * 700);
            }
            //首先画好第一行带颜色的，单独写出来，避免写在循环里面
            IRow row = sheet.CreateRow(0);
            //冻结第一行
            sheet.CreateFreezePane(0, 1, 0, 1);
            ICellStyle style = book.CreateCellStyle();
            style.FillForegroundColor = HSSFColor.PALE_BLUE.index;
            style.FillPattern = FillPatternType.BIG_SPOTS;
            style.FillBackgroundColor = HSSFColor.LIGHT_GREEN.index;
            //设置边框
            style.BorderBottom = BorderStyle.THICK;
            style.BorderLeft = BorderStyle.THICK;
            style.BorderRight = BorderStyle.THICK;
            style.BorderTop = BorderStyle.THICK;
            row.HeightInPoints = 20;
            //固定第一行
            //row.RowStyle.IsLocked=true;
            //给第一行的标签赋值样式和值
            for (int j = 0; j < columnNum; j++)
            {
                row.CreateCell(j);
                row.Cells[j].CellStyle = style;
                row.Cells[j].CellStyle.VerticalAlignment = VerticalAlignment.CENTER;
                row.Cells[j].CellStyle.Alignment = HorizontalAlignment.CENTER;
                row.Cells[j].SetCellValue(DataTable.Columns[j].ColumnName);
            }

            //将DataTable的值循环赋值给book，Aligment设置居中
            //之前已经画了带颜色的第一行，所以从i=1开始画
            for (int i = 0; i < rowNum; i++)
            {
                IRow row1 = sheet.CreateRow(i + 1);
                row1.HeightInPoints = 20;
                for (int j = 0; j < columnNum; j++)
                {
                    row1.CreateCell(j);
                    row1.Cells[j].SetCellValue(DataTable.Rows[i][j].ToString());
                    row1.GetCell(j).CellStyle.VerticalAlignment = VerticalAlignment.CENTER;
                }
            }
            //将book的内容写入内存流中返回
            book.Write(ms);
            return ms;
        }



        /// <summary>
        /// 将DataTable形式的数据转成Excel格式的，然后用字节流的形式写入文件
        /// </summary>
        /// <param name="DataTable"></param>
        /// <returns></returns>
        public MemoryStream DataTableToExcel(System.Windows.Forms.DataGridView DataTable)
        {
            //创建内存流
            MemoryStream ms = new MemoryStream();
            //创建一个Book，相当于一个Excel文件
            HSSFWorkbook book = new HSSFWorkbook();
            //Excel中的Sheet
            ISheet sheet = book.CreateSheet("sheet1");
            //获取行数量和列数量
            int rowNum = DataTable.Rows.Count;
            int columnNum = DataTable.Columns.Count;
            //设置列的宽度，根据首行的列的内容的长度来设置
            for (int i = 0; i < columnNum; i++)
            {
                int dataLength = DataTable.Columns[i].Width;
                if (dataLength == 5)
                {
                    sheet.SetColumnWidth(i, 0);
                }
                else
                {
                    sheet.SetColumnWidth(i, dataLength*25);
                }
            }
            //首先画好第一行带颜色的，单独写出来，避免写在循环里面
            IRow row = sheet.CreateRow(0);
            //冻结第一行
            sheet.CreateFreezePane(0, 1, 0, 1);
            ICellStyle style = book.CreateCellStyle();
            style.FillForegroundColor = HSSFColor.PALE_BLUE.index;
            style.FillPattern = FillPatternType.BIG_SPOTS;
            style.FillBackgroundColor = HSSFColor.LIGHT_GREEN.index;
            //设置边框
            style.BorderBottom = BorderStyle.THICK;
            style.BorderLeft = BorderStyle.THICK;
            style.BorderRight = BorderStyle.THICK;
            style.BorderTop = BorderStyle.THICK;
            row.HeightInPoints = 20;
            //固定第一行
            //row.RowStyle.IsLocked=true;
            //给第一行的标签赋值样式和值
            for (int j = 0; j < columnNum; j++)
            {
                row.CreateCell(j);
                row.Cells[j].CellStyle = style;
                row.Cells[j].CellStyle.VerticalAlignment = VerticalAlignment.CENTER;
                row.Cells[j].CellStyle.Alignment = HorizontalAlignment.CENTER;
                row.Cells[j].SetCellValue(DataTable.Columns[j].HeaderText);
            }

            //将DataTable的值循环赋值给book，Aligment设置居中
            //之前已经画了带颜色的第一行，所以从i=1开始画
            for (int i = 0; i < rowNum; i++)
            {
                IRow row1 = sheet.CreateRow(i + 1);
                row1.HeightInPoints = 20;
                for (int j = 0; j < columnNum; j++)
                {
                    row1.CreateCell(j);
                    row1.Cells[j].SetCellValue(DataTable.Rows[i].Cells[j].Value.ToString());
                    row1.GetCell(j).CellStyle.VerticalAlignment = VerticalAlignment.CENTER;
                }
            }
            //将book的内容写入内存流中返回
            book.Write(ms);
            return ms;
        }


        /// <summary>
        /// 将DataTable形式的数据转成Excel格式的，然后用字节流的形式写入文件
        /// </summary>
        /// <param name="DataTable"></param>
        /// <returns></returns>
        public MemoryStream DataTableToExcel_BAIDU(DataTable DataTable, DateTime begindate, int DateNum)
        {
            string[] Step = new[] { "1-MT1", "2-MT2", "3-MMI", "4-RSA(耦合)", "5-AUD(曲线)", "6-SCW(写号)", "7-SCK(验号)" };
            string[] StepCode = new[] { "B_MT1", "B_MT2", "B_MMI", "B_RSA", "B_AUD", "B_WRITE", "B_SN", "B_OUTLOOK" };
            string[] Kind = new[] { "测试数", "通过数", "不良数", "误测通过数", "误测数", "FPY", "RPY" };
            string[] TotalKind = new[] { "总投入数", "总不良数", "FPY", "RPY" };
            string[] OutLook = new[] { "测试数", "不良数", "FPY" };
            //每行的内容
            int ContentRow = 7;
            //外观的展示的行
            int OutLookRow = 55;
            MemoryStream ms = new MemoryStream();
            //创建一个Book，相当于一个Excel文件
            HSSFWorkbook book = new HSSFWorkbook();

            ICellStyle NONE = book.CreateCellStyle();
            NONE.VerticalAlignment = VerticalAlignment.CENTER;
            NONE.Alignment = HorizontalAlignment.CENTER;
            NONE.BorderBottom = BorderStyle.THIN;
            NONE.BorderLeft = BorderStyle.THIN;
            NONE.BorderRight = BorderStyle.THIN;
            NONE.BorderTop = BorderStyle.THIN;

            ICellStyle TAN = book.CreateCellStyle();
            TAN.VerticalAlignment = VerticalAlignment.CENTER;
            TAN.Alignment = HorizontalAlignment.CENTER;
            TAN.FillForegroundColor = HSSFColor.TAN.index;
            TAN.FillPattern = FillPatternType.SOLID_FOREGROUND;
            TAN.BorderBottom = BorderStyle.THIN;
            TAN.BorderLeft = BorderStyle.THIN;
            TAN.BorderRight = BorderStyle.THIN;
            TAN.BorderTop = BorderStyle.THIN;

            ICellStyle PALE_BLUE = book.CreateCellStyle();
            PALE_BLUE.VerticalAlignment = VerticalAlignment.CENTER;
            PALE_BLUE.Alignment = HorizontalAlignment.CENTER;
            PALE_BLUE.FillForegroundColor = HSSFColor.PALE_BLUE.index;
            PALE_BLUE.FillPattern = FillPatternType.SOLID_FOREGROUND;
            PALE_BLUE.BorderBottom = BorderStyle.THIN;
            PALE_BLUE.BorderLeft = BorderStyle.THIN;
            PALE_BLUE.BorderRight = BorderStyle.THIN;
            PALE_BLUE.BorderTop = BorderStyle.THIN;

            ICellStyle LIME = book.CreateCellStyle();
            LIME.VerticalAlignment = VerticalAlignment.CENTER;
            LIME.Alignment = HorizontalAlignment.CENTER;
            LIME.FillForegroundColor = HSSFColor.LIME.index;
            LIME.FillPattern = FillPatternType.SOLID_FOREGROUND;
            LIME.BorderBottom = BorderStyle.THIN;
            LIME.BorderLeft = BorderStyle.THIN;
            LIME.BorderRight = BorderStyle.THIN;
            LIME.BorderTop = BorderStyle.THIN;

            ICellStyle LEMON_CHIFFON = book.CreateCellStyle();
            LEMON_CHIFFON.VerticalAlignment = VerticalAlignment.CENTER;
            LEMON_CHIFFON.Alignment = HorizontalAlignment.CENTER;
            LEMON_CHIFFON.FillForegroundColor = HSSFColor.LEMON_CHIFFON.index;
            LEMON_CHIFFON.FillPattern = FillPatternType.SOLID_FOREGROUND;
            LEMON_CHIFFON.BorderBottom = BorderStyle.THIN;
            LEMON_CHIFFON.BorderLeft = BorderStyle.THIN;
            LEMON_CHIFFON.BorderRight = BorderStyle.THIN;
            LEMON_CHIFFON.BorderTop = BorderStyle.THIN;
            LEMON_CHIFFON.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");


            ICellStyle GOLD = book.CreateCellStyle();
            GOLD.VerticalAlignment = VerticalAlignment.CENTER;
            GOLD.Alignment = HorizontalAlignment.CENTER;
            GOLD.FillForegroundColor = HSSFColor.GOLD.index;
            GOLD.FillPattern = FillPatternType.SOLID_FOREGROUND;
            GOLD.BorderBottom = BorderStyle.THIN;
            GOLD.BorderLeft = BorderStyle.THIN;
            GOLD.BorderRight = BorderStyle.THIN;
            GOLD.BorderTop = BorderStyle.THIN;

            ICellStyle LIGHT_GREEN = book.CreateCellStyle();
            LIGHT_GREEN.VerticalAlignment = VerticalAlignment.CENTER;
            LIGHT_GREEN.Alignment = HorizontalAlignment.CENTER;
            LIGHT_GREEN.FillForegroundColor = HSSFColor.LIGHT_GREEN.index;
            LIGHT_GREEN.FillPattern = FillPatternType.SOLID_FOREGROUND;
            LIGHT_GREEN.BorderBottom = BorderStyle.THIN;
            LIGHT_GREEN.BorderLeft = BorderStyle.THIN;
            LIGHT_GREEN.BorderRight = BorderStyle.THIN;
            LIGHT_GREEN.BorderTop = BorderStyle.THIN;
            //LIGHT_GREEN.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");

            ICellStyle DARK_BLUE = book.CreateCellStyle();
            DARK_BLUE.VerticalAlignment = VerticalAlignment.CENTER;
            DARK_BLUE.Alignment = HorizontalAlignment.CENTER;
            DARK_BLUE.FillForegroundColor = HSSFColor.LIGHT_BLUE.index;
            DARK_BLUE.FillPattern = FillPatternType.SOLID_FOREGROUND;
            DARK_BLUE.BorderBottom = BorderStyle.THIN;
            DARK_BLUE.BorderLeft = BorderStyle.THIN;
            DARK_BLUE.BorderRight = BorderStyle.THIN;
            DARK_BLUE.BorderTop = BorderStyle.THIN;
            DARK_BLUE.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");

            ICellStyle LIGHT_CORNFLOWER_BLUE = book.CreateCellStyle();
            LIGHT_CORNFLOWER_BLUE.VerticalAlignment = VerticalAlignment.CENTER;
            LIGHT_CORNFLOWER_BLUE.Alignment = HorizontalAlignment.CENTER;
            LIGHT_CORNFLOWER_BLUE.FillForegroundColor = HSSFColor.LIGHT_CORNFLOWER_BLUE.index;
            LIGHT_CORNFLOWER_BLUE.FillPattern = FillPatternType.SOLID_FOREGROUND;
            LIGHT_CORNFLOWER_BLUE.BorderBottom = BorderStyle.THIN;
            LIGHT_CORNFLOWER_BLUE.BorderLeft = BorderStyle.THIN;
            LIGHT_CORNFLOWER_BLUE.BorderRight = BorderStyle.THIN;
            LIGHT_CORNFLOWER_BLUE.BorderTop = BorderStyle.THIN;
            LIGHT_CORNFLOWER_BLUE.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");


            ICellStyle GREY_25_PERCENT = book.CreateCellStyle();
            GREY_25_PERCENT.VerticalAlignment = VerticalAlignment.CENTER;
            GREY_25_PERCENT.Alignment = HorizontalAlignment.CENTER;
            GREY_25_PERCENT.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
            GREY_25_PERCENT.FillPattern = FillPatternType.SOLID_FOREGROUND;
            GREY_25_PERCENT.BorderBottom = BorderStyle.THIN;
            GREY_25_PERCENT.BorderLeft = BorderStyle.THIN;
            GREY_25_PERCENT.BorderRight = BorderStyle.THIN;
            GREY_25_PERCENT.BorderTop = BorderStyle.THIN;

            ICellStyle PINK = book.CreateCellStyle();
            PINK.VerticalAlignment = VerticalAlignment.CENTER;
            PINK.Alignment = HorizontalAlignment.CENTER;
            PINK.FillForegroundColor = HSSFColor.LIGHT_ORANGE.index;
            PINK.FillPattern = FillPatternType.SOLID_FOREGROUND;
            PINK.BorderBottom = BorderStyle.THIN;
            PINK.BorderLeft = BorderStyle.THIN;
            PINK.BorderRight = BorderStyle.THIN;
            PINK.BorderTop = BorderStyle.THIN;

            //Excel中的Sheet
            ISheet sheet = book.CreateSheet("sheet1");
            IRow row = sheet.CreateRow(0);
            ICell cell = row.CreateCell(0);
            //画第一行的抬头
            cell.SetCellValue("组装制程品质数据");
            cell.CellStyle = NONE;
            CellRangeAddress region = new CellRangeAddress(0, 0, 0, DateNum + 1);
            sheet.AddMergedRegion(region);
            //第一行的日期标题
            row = sheet.CreateRow(1);
            cell = row.CreateCell(0);
            cell.CellStyle = NONE;
            cell.SetCellValue("站点");
            cell = row.CreateCell(1);
            cell.SetCellValue("类别");
            cell.CellStyle = NONE;
            for (int i = 0; i < DateNum; i++)
            {
                cell = row.CreateCell(i + 2);
                cell.SetCellValue(begindate.AddDays(i).ToString("MM/dd"));
                cell.CellStyle = NONE;
            }
            //画第一列的工序名称和第二列的类别
            //总良率数据
            row = sheet.CreateRow(2);
            cell = row.CreateCell(0);
            cell.SetCellValue("总良率");
            cell.CellStyle = LEMON_CHIFFON;
            region = new CellRangeAddress(2, 5, 0, 0);
            sheet.AddMergedRegion(region);
            //总良率的统计数据
            for (int i = 0; i < TotalKind.Length; i++)
            {
                row = sheet.GetRow(i + 2);
                if (row == null)
                {
                    row = sheet.CreateRow(i + 2);
                }
                cell = row.CreateCell(1);
                cell.SetCellValue(TotalKind[i]);
                cell.CellStyle = LEMON_CHIFFON;
                switch (i)
                {
                    case 0:
                        cell.CellStyle = LIME;
                        break;
                    case 1:
                        cell.CellStyle = TAN;
                        break;
                    case 2:
                        cell.CellStyle = DARK_BLUE;
                        break;
                    case 3:
                        cell.CellStyle = LIGHT_CORNFLOWER_BLUE;
                        break;
                    default:
                        break;
                }
            }

            //中间的设备测试工序
            for (int i = 0; i < Step.Length; i++)
            {
                //除去前面6行
                int rowindex = 6 + i * ContentRow;
                row = sheet.CreateRow(rowindex);
                cell = row.CreateCell(0);
                cell.SetCellValue(Step[i]);
                cell.CellStyle = PALE_BLUE;
                region = new CellRangeAddress(rowindex, rowindex + ContentRow - 1, 0, 0);
                sheet.AddMergedRegion(region);
                for (int j = rowindex; j < rowindex + ContentRow; j++)
                {
                    row = sheet.GetRow(j);
                    if (row == null)
                    {
                        row = sheet.CreateRow(j);
                    }
                    cell = row.CreateCell(1);
                    cell.SetCellValue(Kind[j - rowindex]);
                    switch (j - rowindex)
                    {
                        case 0:
                            cell.CellStyle = GREY_25_PERCENT;
                            break;
                        case 1:
                            cell.CellStyle = PINK;
                            break;
                        case 2:
                            cell.CellStyle = TAN;
                            break;
                        case 3:
                            cell.CellStyle = GOLD;
                            break;
                        case 4:
                            cell.CellStyle = LIGHT_GREEN;
                            break;
                        case 5:
                            cell.CellStyle = LIGHT_CORNFLOWER_BLUE;
                            break;
                        case 6:
                            cell.CellStyle = LEMON_CHIFFON;
                            break;
                        default:
                            break;
                    }
                }
            }
            //最后一行外观数据
            row = sheet.CreateRow(OutLookRow);
            cell = row.CreateCell(0);
            cell.SetCellValue("8-外观");
            cell.CellStyle = PALE_BLUE;
            region = new CellRangeAddress(OutLookRow, OutLookRow + 2, 0, 0);
            sheet.AddMergedRegion(region);

            //外观的统计数据
            for (int i = 0; i < OutLook.Length; i++)
            {
                row = sheet.GetRow(OutLookRow + i);
                if (row == null)
                {
                    row = sheet.CreateRow(OutLookRow + i);
                }
                cell = row.CreateCell(1);
                cell.SetCellValue(OutLook[i]);
                cell.CellStyle = PALE_BLUE;
                switch (i)
                {
                    case 0:
                        cell.CellStyle = GREY_25_PERCENT;
                        break;
                    case 1:
                        cell.CellStyle = TAN;
                        break;
                    case 2:
                        cell.CellStyle = LIGHT_GREEN;
                        break;
                    default:
                        break;
                }
            }
            sheet.SetColumnWidth(0, 3700);

            for (int i = 0; i < DateNum; i++)
            {
                double TotalFPY = -1;
                double TotalRPY = -1;
                double TotalNG = 0;
                double TotalIN = 0;
                for (int j = 0; j < StepCode.Length; j++)
                {
                    int rowindex = 6 + j * ContentRow;
                    DataTable dt = PublicMethod.BaseUtil.filterDataTable(DataTable, "sp_date='" + begindate.AddDays(i).ToString("yyyy-MM-dd") + "' and 工序编号='" + StepCode[j] + "'");
                    if (StepCode[j] != "B_OUTLOOK")
                    {
                        for (int k = rowindex; k < rowindex + ContentRow; k++)
                        {
                            row = sheet.GetRow(k);
                            if (row == null)
                            {
                                row = sheet.CreateRow(k);
                            }
                            cell = row.CreateCell(i + 2);
                            switch (k - rowindex)
                            {
                                case 0:
                                    double 测试数;
                                    if (dt.Rows.Count > 0)
                                    {
                                        if (double.TryParse(dt.Rows[0]["测试数"].ToString(), out 测试数))
                                        {
                                            cell.SetCellValue(测试数);
                                        }
                                    }
                                    cell.CellStyle = GREY_25_PERCENT;
                                    break;
                                case 1:
                                    double 通过数;
                                    if (dt.Rows.Count > 0)
                                    {
                                        if (double.TryParse(dt.Rows[0]["通过总数"].ToString(), out 通过数))
                                        {
                                            cell.SetCellValue(通过数);
                                        }
                                    }
                                    cell.CellStyle = PINK;
                                    break;
                                case 2:
                                    double 不良数;
                                    if (dt.Rows.Count > 0)
                                    {
                                        if (double.TryParse(dt.Rows[0]["不良数"].ToString(), out 不良数))
                                        {
                                            cell.SetCellValue(不良数);
                                            TotalNG = TotalNG + 不良数;
                                        }
                                    }
                                    cell.CellStyle = TAN;
                                    break;
                                case 3:
                                    double 误测通过数;
                                    if (dt.Rows.Count > 0)
                                    {
                                        if (double.TryParse(dt.Rows[0]["误测通过数"].ToString(), out 误测通过数))
                                        {
                                            cell.SetCellValue(误测通过数);
                                        }
                                    }
                                    cell.CellStyle = GOLD;
                                    break;
                                case 4:
                                    double 误测数;
                                    if (dt.Rows.Count > 0)
                                    {
                                        if (double.TryParse(dt.Rows[0]["误测数"].ToString(), out 误测数))
                                        {
                                            cell.SetCellValue(误测数);
                                        }
                                    }
                                    cell.CellStyle = LIGHT_GREEN;
                                    break;
                                case 5:
                                    double FPY;
                                    if (dt.Rows.Count > 0)
                                    {
                                        if (double.TryParse(dt.Rows[0]["FPY"].ToString(), out FPY))
                                        {
                                            cell.SetCellValue(FPY);
                                            //累计所有FPY
                                            if (TotalFPY == -1)
                                            {
                                                TotalFPY = FPY;
                                            }
                                            else
                                            {
                                                TotalFPY = TotalFPY * FPY;
                                            }
                                        }
                                    }
                                    cell.CellStyle = LIGHT_CORNFLOWER_BLUE;
                                    break;
                                case 6:
                                    double RPY;
                                    if (dt.Rows.Count > 0)
                                    {
                                        if (double.TryParse(dt.Rows[0]["RPY"].ToString(), out RPY))
                                        {
                                            cell.SetCellValue(RPY);
                                            //累计所有RPY
                                            if (TotalRPY == -1)
                                            {
                                                TotalRPY = RPY;
                                            }
                                            else
                                            {
                                                TotalRPY = TotalRPY * RPY;
                                            }
                                        }
                                    }
                                    cell.CellStyle = LEMON_CHIFFON;
                                    break;
                                default:
                                    break;
                            }
                        }
                    }
                    else
                    {
                        for (int k = rowindex; k < rowindex + 3; k++)
                        {
                            row = sheet.GetRow(k);
                            if (row == null)
                            {
                                row = sheet.CreateRow(k);
                            }
                            cell = row.CreateCell(i + 2);
                            switch (k - rowindex)
                            {
                                case 0:
                                    double 测试数;
                                    if (dt.Rows.Count > 0)
                                    {
                                        if (double.TryParse(dt.Rows[0]["测试数"].ToString(), out 测试数))
                                        {
                                            cell.SetCellValue(测试数);
                                        }
                                    }
                                    cell.CellStyle = GREY_25_PERCENT;
                                    break;
                                case 1:
                                    double 不良数;
                                    if (dt.Rows.Count > 0)
                                    {
                                        if (double.TryParse(dt.Rows[0]["不良数"].ToString(), out 不良数))
                                        {
                                            cell.SetCellValue(不良数);
                                            TotalNG = TotalNG + 不良数;
                                        }
                                    }
                                    cell.CellStyle = TAN;
                                    break;
                                case 2:
                                    double FPY;
                                    if (dt.Rows.Count > 0)
                                    {
                                        if (double.TryParse(dt.Rows[0]["FPY"].ToString(), out FPY))
                                        {
                                            cell.SetCellValue(FPY);
                                            //累计所有FPY
                                            if (TotalFPY == -1)
                                            {
                                                TotalFPY = FPY;
                                            }
                                            else
                                            {
                                                TotalFPY = TotalFPY * FPY;
                                            }
                                        }
                                    }
                                    cell.CellStyle = LIGHT_GREEN;
                                    break;
                                default:
                                    break;
                            }
                        }
                    }
                }
                DataTable dt1 = PublicMethod.BaseUtil.filterDataTable(DataTable, "sp_date='" + begindate.AddDays(i).ToString("yyyy-MM-dd") + "' and 工序编号='B_LCDBA1'");
                double 投入数;
                if (dt1.Rows.Count > 0)
                {
                    if (double.TryParse(dt1.Rows[0]["测试数"].ToString(), out 投入数))
                    {
                        TotalIN = 投入数;
                    }
                }

                //设置最上方的总计数量
                row = sheet.GetRow(2);
                cell = row.CreateCell(i + 2);
                cell.CellStyle = LIME;
                cell.SetCellValue(TotalIN);

                row = sheet.GetRow(3);
                cell = row.CreateCell(i + 2);
                cell.CellStyle = TAN;
                cell.SetCellValue(TotalNG);

                row = sheet.GetRow(4);
                cell = row.CreateCell(i + 2);
                cell.CellStyle = DARK_BLUE;
                cell.SetCellValue(TotalFPY == -1 ? 0 : TotalFPY);

                row = sheet.GetRow(5);
                cell = row.CreateCell(i + 2);
                cell.CellStyle = LIGHT_CORNFLOWER_BLUE;
                cell.SetCellValue(TotalRPY == -1 ? 0 : TotalRPY);
            }
            for (int i = 0; i < sheet.PhysicalNumberOfRows; i++)
            {
                sheet.GetRow(i).Height = 300;
            }
            //将book的内容写入内存流中返回
            book.Write(ms);
            return ms;
        }
    }
}