using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.IO;

using NPOI.HSSF.UserModel;

using NPOI.SS.UserModel;

using System.Data;

using YTO.WeiXin.Model;

namespace YTO.WeiXin.Core

{

    public class ExcelToDB

    {

        public HSSFWorkbook hssfworkbook;

        //将excel文件转换成list

        public IList<ContactInfo> ExcelToList(string path)

        {

            IList<ContactInfo> list = new List<ContactInfo>();

            try

            {

                using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))

                {

                    hssfworkbook = new HSSFWorkbook(file);

                    HSSFSheet sheet = hssfworkbook.GetSheetAt() as HSSFSheet;

                    for (int i = ; i <= sheet.LastRowNum; i++)

                    {

                        HSSFRow row = sheet.GetRow(i) as HSSFRow;

                        ContactInfo contactInfo = new ContactInfo();

                        contactInfo.Id = Guid.NewGuid().ToString();

                        if (row.GetCell() != null)

                        {

                            row.GetCell().SetCellType(CellType.STRING);

                            contactInfo.CenterName = row.GetCell().StringCellValue.ToString();

                        }

                        else

                        {

                            contactInfo.CenterName = "";

                        }

                        if (row.GetCell() != null)

                        {

                            row.GetCell().SetCellType(CellType.STRING);

                            contactInfo.Name = row.GetCell().StringCellValue.ToString();

                        }

                        else

                        {

                            contactInfo.Name = "";

                        }

                        if (row.GetCell() != null)

                        {

                            row.GetCell().SetCellType(CellType.STRING);

                            contactInfo.PhoneNumber = row.GetCell().StringCellValue.ToString();

                        }

                        else

                        {

                            contactInfo.PhoneNumber = "";

                        }

                        if (row.GetCell() != null)

                        {

                            row.GetCell().SetCellType(CellType.STRING);

                            contactInfo.Address = row.GetCell().StringCellValue.ToString();

                        }

                        else

                        {

                            contactInfo.Address = "";

                        }

                        list.Add(contactInfo);

                    }

                }

                return list;

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

        //中心联系方式导出

        public MemoryStream ExportToExcel(string fileName, IList<ContactInfo> list)

        {

            HSSFWorkbook workbook = new HSSFWorkbook();

            Sheet sheet1 = workbook.CreateSheet("Sheet1");

            sheet1.SetColumnWidth(,  * );

            sheet1.SetColumnWidth(,  * );

            sheet1.SetColumnWidth(,  * );

            sheet1.SetColumnWidth(,  * );

            Row row = sheet1.CreateRow();

            row.HeightInPoints = ;

            row.CreateCell().SetCellValue("中心名称");

            row.CreateCell().SetCellValue("联系人");

            row.CreateCell().SetCellValue("联系方式");

            row.CreateCell().SetCellValue("地址");

            CellStyle style = workbook.CreateCellStyle();

            style.Alignment = HorizontalAlignment.CENTER;

            style.WrapText = true;

            Font font = workbook.CreateFont();

            font.FontHeightInPoints = ;

            font.Boldweight = (short)FontBoldWeight.BOLD;

            font.Color = (short)FontColor.RED;

            style.SetFont(font);

            for (int i = ; i < ; i++)

            {

                row.GetCell(i).CellStyle = style;

            }

            for (int i = ; i < list.Count; i++)

            {

                row = sheet1.CreateRow(i);

                row.CreateCell().SetCellValue(list[i - ].CenterName);

                row.CreateCell().SetCellValue(list[i - ].Name);

                row.CreateCell().SetCellValue(list[i - ].PhoneNumber);

                row.CreateCell().SetCellValue(list[i - ].Address);

            }

            MemoryStream ms = new MemoryStream();

            workbook.Write(ms);

            ms.Flush();

            ms.Position = ;

            return ms;

        }

        //异常信息导出

        public MemoryStream ExportExcptionToExcel(string fileName, IList<ExcptionInfo> list)

        {

            HSSFWorkbook workbook = new HSSFWorkbook();

            Sheet sheet1 = workbook.CreateSheet("Sheet1");

            sheet1.SetColumnWidth(,  * );

            sheet1.SetColumnWidth(,  * );

            sheet1.SetColumnWidth(,  * );

            sheet1.SetColumnWidth(,  * );

            sheet1.SetColumnWidth(,  * );

            sheet1.SetColumnWidth(,  * );

            sheet1.SetColumnWidth(,  * );

            Row row = sheet1.CreateRow();

            row.HeightInPoints = ;

            row.CreateCell().SetCellValue("车牌号");

            row.CreateCell().SetCellValue("线路");

            row.CreateCell().SetCellValue("手机号");

            row.CreateCell().SetCellValue("异常情况");

            row.CreateCell().SetCellValue("异常类型");

            row.CreateCell().SetCellValue("位置");

            row.CreateCell().SetCellValue("上报时间");

            CellStyle style = workbook.CreateCellStyle();

            style.Alignment = HorizontalAlignment.CENTER;

            style.WrapText = true;

            Font font = workbook.CreateFont();

            font.FontHeightInPoints = ;

            font.Boldweight = (short)FontBoldWeight.BOLD;

            font.Color = (short)FontColor.RED;

            style.SetFont(font);

            for (int i = ; i < ; i++)

            {

                row.GetCell(i).CellStyle = style;

            }

            for (int i = ; i < list.Count; i++)

            {

                row = sheet1.CreateRow(i);

                row.CreateCell().SetCellValue(list[i].LiencePlateNumber);

                row.CreateCell().SetCellValue(list[i].CarLine);

                row.CreateCell().SetCellValue(list[i].PhoneNumber);

                row.CreateCell().SetCellValue(list[i].Remark);

                row.CreateCell().SetCellValue(list[i].ExcptionCategory);

                row.CreateCell().SetCellValue(list[i].Position);

                row.CreateCell().SetCellValue(list[i].CreateTime.ToString());

            }

            MemoryStream ms = new MemoryStream();

            workbook.Write(ms);

            ms.Flush();

            ms.Position = ;

            return ms;

        }

    }

}