xls2frx

转换EXCEL文件为FRX文件，只解析EXCEL的某个表单的结构，构造为FRX的表单主体，使用前需要自定义TEMPLATE中的内容。省去画表的痛苦。

源码请至我的资源中下载

示例如下：

<?xml version="1.0" encoding="utf-8"?>
<Report ScriptLanguage="CSharp" DoublePass="true" ReportInfo.Created="11/13/2011 11:20:14" ReportInfo.Modified="11/24/2011 12:18:59" ReportInfo.CreatorVersion="1.7.1.0" PrintSettings.PrintOnSheetWidth="150" PrintSettings.PrintOnSheetHeight="210">
  <ScriptText>
    using System;
    using System.Collections;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Windows.Forms;
    using System.Drawing;
    using System.Data;
    using System.Text;
    using FastReport;
    using FastReport.Data;
    using FastReport.Dialog;
    using FastReport.Barcode;
    using FastReport.Table;
    using FastReport.Utils;


    namespace FastReport
    {
    public class ReportScript
    {




 
    }
    }
  </ScriptText>
  <Dictionary>
      <Parameter Name="Par_Title" DataType="System.String"/>
     <Parameter Name="Par_Footer" DataType="System.String"/>
  </Dictionary>
  <ReportPage Name="Page1" Landscape="true" PaperWidth="297" PaperHeight="210" RawPaperSize="9" Guides="457.38">
   <ReportTitleBand Name="ReportTitle1" Width="1047.06" Height="673" Guides="423.36">
      <TableObject Name="Table2" Width="1062" Height="673" ManualBuildEvent="Table2_ManualBuild">
        
$#report#$
        
      </TableObject>
    </ReportTitleBand>
  </ReportPage>
  </Report>

以下为转换代码

#region 版权信息
/*------------------------------------------------------------ 
 * Copyright ©  2010-2011 QinQouShui All rights reserved
 * 版权所有：                   http://www.qoushui.com
 * 创建者：                     QinQouShui
 * 创建日期：                   2011-11-24 10:41:49 
 * CLR版本：                    4.0.30319.239 
 * E-mail：                     ToQoo@126.com
 * Blog:                        www.cnblogs.com 
 * QQ:                          5663572
 * MSN:                         qoushuijob@hotmail.com
 * 
 * 
 * NameSpace:                   xls2frx   
 * FileName:                    xls2frx   
 * --------------------------------------------------------------  
 */
#endregion








using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;


namespace xls2frx
{
    /// <summary>
    /// xls2frx
    /// </summary>
    public class xls2frx
    {
        static System.Drawing.PointF dpi = new System.Drawing.PointF(new System.Windows.Forms.Form().CreateGraphics().DpiX, new System.Windows.Forms.Form().CreateGraphics().DpiY);
        public static System.Drawing.Size GetLoaction(Range range)
        {
            return new System.Drawing.Size((int)(Math.Ceiling((double)range.Left * dpi.X / 72f)) + 3,
              (int)(Math.Ceiling((double)range.Top * dpi.Y / 72f)) + 3);


        }


        public static System.Drawing.Size GetSize(Range range)
        {
            return new System.Drawing.Size((int)(Math.Ceiling((double)range.Width * dpi.X / 72f)),
              (int)(Math.Ceiling((double)range.Height * dpi.Y / 72f)));


        }
        public static int Inch2Pix(double inch, bool isX)
        {
            if (isX)
                return (int)Math.Round(inch * dpi.X, 0);
            else
                return (int)Math.Round(inch * dpi.Y, 0);
        }


        public static int Tie2Pix(double heigth)
        {
            return (int)Math.Floor((double)heigth * dpi.X / 72f);
        }


        public static double Pix2mm(double pix, bool isX)
        {
            if (isX)
                return Math.Round(pix / (double)dpi.X * 25.4, 0);
            else
                return Math.Round(pix / (double)dpi.Y * 25.4, 0);


        }


        public static System.Drawing.Size GetPaperSize(System.Drawing.Printing.PaperSize size)
        {
            return new System.Drawing.Size((int)(Math.Round(size.Width * 25.4 / 100d, 0)), (int)(Math.Round(size.Height * 25.4 / 100d, 0)));


        }
        public static void ReadExcel(string fileName)
        {








            ApplicationClass app = new ApplicationClass();
            Workbook book = app.Workbooks.Open(fileName);
            List<string> sheetNames = new List<string>();
            foreach (Worksheet item in book.Sheets) {
                sheetNames.Add((item as Worksheet).Name);


            }
            System.Drawing.Printing.PrinterSettings.PaperSizeCollection papers = new System.Drawing.Printing.PrintDocument().PrinterSettings.PaperSizes;




            foreach (string sheetName in sheetNames) {


                Worksheet sheet = book.Sheets[sheetName] as Worksheet;
                Range rows = sheet.UsedRange;
                if (rows.Rows.Count < 1 || rows.Columns.Count < 1)
                    continue;


                System.IO.StringWriter mem = new System.IO.StringWriter();
                System.Xml.XmlTextWriter sw = new System.Xml.XmlTextWriter(mem);
                sw.Formatting = System.Xml.Formatting.Indented;
                sw.WriteStartElement("ReportPage");
                sw.WriteAttributeString("Name", "Page1");
                // sw.WriteAttributeString("RawPaperSize", 
                int kind = (int)sheet.PageSetup.PaperSize;
                bool find = false;
                System.Drawing.Size paperSize = new System.Drawing.Size();
                foreach (System.Drawing.Printing.PaperSize paper in papers) {
                    if (kind == paper.RawKind) {
                        sw.WriteAttributeString("RawPaperSize", kind.ToString());
                        paperSize = new System.Drawing.Size(Inch2Pix(paper.Width / 100, true), Inch2Pix(paper.Height / 100, false));
                        if (sheet.PageSetup.Orientation == XlPageOrientation.xlLandscape) {
                            sw.WriteAttributeString("Landscape", "true");
                            sw.WriteAttributeString("PaperWidth", GetPaperSize(paper).Height.ToString());
                            sw.WriteAttributeString("PaperHeight", GetPaperSize(paper).Width.ToString());
                        }
                        else {
                            sw.WriteAttributeString("PaperWidth", GetPaperSize(paper).Width.ToString());
                            sw.WriteAttributeString("PaperHeight", GetPaperSize(paper).Height.ToString());


                        }
                        find = true;
                        break;
                    }
                }
                if (!find) {
                    sw.WriteAttributeString("RawPaperSize", "9");
                    sw.WriteAttributeString("PaperWidth", "297");
                    sw.WriteAttributeString("PaperHeight", "210");
                }
                sw.WriteAttributeString("LeftMargin", Pix2mm(sheet.PageSetup.LeftMargin, true).ToString());
                sw.WriteAttributeString("TopMargin", Pix2mm(sheet.PageSetup.TopMargin, false).ToString());
                sw.WriteAttributeString("RightMargin", Pix2mm(sheet.PageSetup.RightMargin, true).ToString());
                sw.WriteAttributeString("BottomMargin", Pix2mm(sheet.PageSetup.BottomMargin, false).ToString());


                sw.WriteStartElement("ReportTitleBand");
                sw.WriteAttributeString("Name", "ReportTitle1");
                if (sheet.PageSetup.Orientation == XlPageOrientation.xlLandscape) {


                    sw.WriteAttributeString("Height", (paperSize.Width - (sheet.PageSetup.LeftMargin + sheet.PageSetup.RightMargin)).ToString());
                    sw.WriteAttributeString("Width", (paperSize.Height - (sheet.PageSetup.TopMargin + sheet.PageSetup.BottomMargin)).ToString());
                }
                else {
                    sw.WriteAttributeString("Width", (paperSize.Width - (sheet.PageSetup.LeftMargin + sheet.PageSetup.RightMargin)).ToString());
                    sw.WriteAttributeString("Height", (paperSize.Height - (sheet.PageSetup.TopMargin + sheet.PageSetup.BottomMargin)).ToString());


                }
                sw.WriteStartElement("TableObject");
                sw.WriteAttributeString("Name", "Table2");
                if (sheet.PageSetup.Orientation == XlPageOrientation.xlLandscape) {


                    sw.WriteAttributeString("Height", (paperSize.Width - (sheet.PageSetup.LeftMargin + sheet.PageSetup.RightMargin)).ToString());
                    sw.WriteAttributeString("Width", (paperSize.Height - (sheet.PageSetup.TopMargin + sheet.PageSetup.BottomMargin)).ToString());
                }
                else {
                    sw.WriteAttributeString("Width", (paperSize.Width - (sheet.PageSetup.LeftMargin + sheet.PageSetup.RightMargin)).ToString());
                    sw.WriteAttributeString("Height", (paperSize.Height - (sheet.PageSetup.TopMargin + sheet.PageSetup.BottomMargin)).ToString());


                }
                //sw.WriteAttributeString("ManualBuildEvent", "Table2_ManualBuild");
                sw.WriteAttributeString("AfterDataEvent", "Table2_AfterData");


                int startRow = rows.Row;
                int startColumn = rows.Column;
                for (int c = startColumn; c <= rows.Columns.Count; c++) {
                    sw.WriteStartElement("TableColumn");
                    sw.WriteAttributeString("Name", "Column" + c.ToString());
                    sw.WriteAttributeString("Width", Tie2Pix((double)(sheet.Cells[1, c] as Range).Width).ToString());
                    sw.WriteEndElement();
                }


                for (int r = startRow; r <= rows.Rows.Count; r++) {
                    sw.WriteStartElement("TableRow");
                    sw.WriteAttributeString("Name", "Row" + r.ToString());
                    sw.WriteAttributeString("Height", Tie2Pix((double)(sheet.Cells[r, 1] as Range).Height).ToString());




                    for (int c = startColumn; c <= rows.Columns.Count; c++) {


                        Range cell = sheet.Cells[r, c] as Range;
                        sw.WriteStartElement("TableCell");
                        sw.WriteAttributeString("Name", "Cell" + (c * 1000 + r).ToString());
                        if ((sheet.Cells[r, c] as Range).Text != null) {
                            sw.WriteStartAttribute("Text");
                            double d;
                            if (double.TryParse((sheet.Cells[r, c] as Range).Text.ToString(), out d) && d == 0) {
                                sw.WriteValue("");
                            }
                            else
                                sw.WriteValue((sheet.Cells[r, c] as Range).Text);
                            sw.WriteEndAttribute();
                        }
                        sw.WriteAttributeString("HideZeros", "true");
                        sw.WriteStartAttribute("HorzAlign");
                        switch ((Constants)cell.HorizontalAlignment) {
                            case Constants.xlCenter:
                            case Constants.xlDistributed:
                                sw.WriteValue("Center");
                                break;
                            case Constants.xlRight:
                                sw.WriteValue("Right");
                                break;
                            case Constants.xlLeft:
                            default:
                                sw.WriteValue("Left");
                                break;
                        }
                        sw.WriteEndAttribute();
                        sw.WriteAttributeString("VertAlign", "Center");
                        sw.WriteAttributeString("Font", string.Format("{0},{1}pt{2}", cell.Font.Name, cell.Font.Size, (bool)cell.Font.Bold ? ",style=Bold" : ""));
                        if ((bool)cell.MergeCells && cell.MergeArea.Column == c)
                            sw.WriteAttributeString("ColSpan", cell.MergeArea.Columns.Count.ToString());
                        if ((bool)cell.MergeCells && cell.MergeArea.Row == r)
                            sw.WriteAttributeString("RowSpan", cell.MergeArea.Rows.Count.ToString());


                        switch (cell.NumberFormatLocal.ToString()) {
                            case "0.00_ ":
                                sw.WriteAttributeString("Format", "Number");
                                sw.WriteAttributeString("Format.UseLocale", "false");
                                sw.WriteAttributeString("Format.DecimalDigits", "2");
                                sw.WriteAttributeString("Format.DecimalSeparator", ".");
                                sw.WriteAttributeString("Format.GroupSeparator", "");
                                sw.WriteAttributeString("Format.NegativePattern", "1");


                                break;
                            case "0_ ":
                                sw.WriteAttributeString("Format", "Number");
                                sw.WriteAttributeString("Format.UseLocale", "false");
                                sw.WriteAttributeString("Format.DecimalDigits", "0");
                                sw.WriteAttributeString("Format.DecimalSeparator", ".");
                                sw.WriteAttributeString("Format.GroupSeparator", "");
                                sw.WriteAttributeString("Format.NegativePattern", "1");
                                break;
                            default:
                                break;


                        }
                        //Border.Lines="Right, Top, Bottom" Border.BottomLine.Color="255, 0, 0" Border.BottomLine.Width="2"


                        string line = string.Format("{0}{1}{2}{3}",
                                    (XlLineStyle)cell.Borders[XlBordersIndex.xlEdgeLeft].LineStyle != XlLineStyle.xlLineStyleNone ? ",Left" : "",
                                    (XlLineStyle)cell.Borders[XlBordersIndex.xlEdgeRight].LineStyle != XlLineStyle.xlLineStyleNone ? ",Right" : "",
                                    (XlLineStyle)cell.Borders[XlBordersIndex.xlEdgeBottom].LineStyle != XlLineStyle.xlLineStyleNone ? ",Bottom" : "",
                                    (XlLineStyle)cell.Borders[XlBordersIndex.xlEdgeTop].LineStyle != XlLineStyle.xlLineStyleNone ? ",Top" : ""


                                  );
                        if (line.Length > 0)
                            sw.WriteAttributeString("Border.Lines", line.Substring(1));




                        sw.WriteEndElement();






                    }
                    sw.WriteEndElement();
                }


                sw.WriteEndElement();
                sw.WriteEndElement();
                sw.WriteEndElement();


                mem.Flush();


                System.IO.File.WriteAllText(
                                    System.IO.Path.Combine(
                                    System.IO.Path.GetDirectoryName(fileName), sheetName + ".frx"),
                                    Properties.Resources.template.Replace("$#report#{1}quot;, mem.ToString().Replace("
", "
")), System.Text.Encoding.UTF8);
                sw.Close();
                mem.Close();
            }
            book.Close(false, Type.Missing, Type.Missing);
            app.Quit();
        }




    }
}