C# Excel操作之读，写，追加

C# 写Excel

上一篇介绍了读Excel:读Excel

本节介绍写Excel：
写Excel比读Excel的学问要多很多，主要是关于字体，保存的格式，颜色等等的设置部分。
首先上代码：

/// <summary>
        /// 将数组数据导出到Excel文件一个Sheet中
        /// </summary>
        /// <param name="s">                数据</param>
        /// <param name="file_path">        文件完整路径</param>
        /// <param name="sheet_name">       Sheet命名</param>
        /// <returns>true 成功  false 失败</returns>  
        public bool Array_To_Excel(string[,] s, string file_path, string sheet_name = "Sheet1")
        {
            bool bxls = false;
            ISheet sheet = null;
            XSSFWorkbook xssfworkbook = null;
            HSSFWorkbook hssfworkbook = null;
            HSSFCellStyle cell_Style = null;

            string[] temp = file_path.Split('.');
            if (temp[temp.Count() - 1].Equals("xlsx"))              // 2007版本  
            {
                xssfworkbook = new XSSFWorkbook();
                sheet = xssfworkbook.CreateSheet(sheet_name);

            }
            else if (temp[temp.Count() - 1].Equals("xls"))          // 2003版本 
            {
                bxls = true;
                hssfworkbook = new HSSFWorkbook();
                sheet = hssfworkbook.CreateSheet(sheet_name);

                //设置单元格格式
                cell_Style = (HSSFCellStyle)hssfworkbook.CreateCellStyle();                 //定义一个单元格样式 
                                                                                            //设置单元格背景色 
                cell_Style.FillForegroundColor = HSSFColor.COLOR_NORMAL;                    //取其它颜色：HSSFColor.Red.Index;
                cell_Style.FillPattern = FillPattern.SolidForeground;

                //设置文字对齐方式 
                cell_Style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;        //水平居中 
                cell_Style.VerticalAlignment = VerticalAlignment.Center;                    //垂直居中 

                //设置字体格式开始 
                HSSFFont hssf_font = (HSSFFont)hssfworkbook.CreateFont();
                //设置字体的颜色为自定义颜色 
                HSSFPalette palette = hssfworkbook.GetCustomPalette();
                palette.SetColorAtIndex(HSSFColor.Lime.Index, (byte)0, (byte)0, (byte)0);   //RGB颜色值 
                hssf_font.Color = HSSFColor.Lime.Index;
                //字体属性 
                hssf_font.FontName = "微软雅黑";
                hssf_font.FontHeightInPoints = 10;                                          //设置字体大小 
                //hssf_font.IsBold = true;
                cell_Style.SetFont(hssf_font);                                              //设置cellStyle 样式的字体 

                IDataFormat dataFormat = hssfworkbook.CreateDataFormat();                   //设置单元格格式
                cell_Style.DataFormat = dataFormat.GetFormat("@");
                //边框 
                cell_Style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;               //None 无边框     Thin 细线  Thick 粗线   
                cell_Style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;                 //Hair Dotted Dashed Medium 点状线
                cell_Style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;                //SlantedDashDot MediumDashDotDot DashDotDot MediumDashDot DashDot MediumDashed虚线
                cell_Style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;                  //Double双细线   
            }
            else
            {
                Trace("file format error,not excel file");
                return false;
            }

            try
            {
                float output = 0.0f;
                IRow row = sheet.CreateRow(0);
                for (int i = 0; i < s.GetLength(1); i++)          //表头
                {
                    ICell cell = row.CreateCell(i);
                    //cell.SetCellValue(s[0,i]);

                    if (float.TryParse(s[0, i], out output))
                        cell.SetCellValue(Convert.ToDouble(output.ToString("0.00")));//输出数字
                    else
                        cell.SetCellValue(s[0, i]);

                    if (bxls)
                        cell.CellStyle = cell_Style;
                }

                for (int i = 1; i < s.GetLength(0); i++)             //数据
                {
                    IRow row1 = sheet.CreateRow(i);
                    for (int j = 0; j < s.GetLength(1); j++)
                    {
                        ICell cell = row1.CreateCell(j);

                        if (float.TryParse(s[i, j], out output))
                            cell.SetCellValue(Convert.ToDouble(output.ToString("0.00")));//输出数字
                        else
                            cell.SetCellValue(s[i, j]);

                        if (bxls)
                            cell.CellStyle = cell_Style;
                    }
                }

                MemoryStream stream = new MemoryStream();                   //转为byte(buf变量)数组
                if (temp[temp.Count() - 1].Equals("xlsx"))
                    xssfworkbook.Write(stream);
                else
                    hssfworkbook.Write(stream);
                var buf = stream.ToArray();

                using (FileStream fs = new FileStream(file_path, FileMode.Create, FileAccess.Write))        //保存为Excel文件
                {
                    fs.Write(buf, 0, buf.Length);
                    fs.Flush();
                }
            }
            catch (Exception e)
            {
                Trace(e.Message);
                return false;
            }

            return true;
        }

        private void button_写_Click(object sender, EventArgs e)
        {
            DataTable dt = Excel_To_DataTable("D:\\test.xlsx");

            string[,] content = DataTable_To_Array(dt);

            Array_To_Excel(content, "D://test1.xlsx", "test");
        }

从上图可以看出，输出的Excel中字体都是垂直置中，具体格式大家根据自己的需求再进行修改！