Excel2007Export.cs 99 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359136013611362136313641365136613671368136913701371137213731374137513761377137813791380138113821383138413851386138713881389139013911392139313941395139613971398139914001401140214031404140514061407140814091410141114121413141414151416141714181419142014211422142314241425142614271428142914301431143214331434143514361437143814391440144114421443144414451446144714481449145014511452145314541455145614571458145914601461146214631464146514661467146814691470147114721473147414751476147714781479148014811482148314841485148614871488148914901491149214931494149514961497149814991500150115021503150415051506150715081509151015111512151315141515151615171518151915201521152215231524152515261527152815291530153115321533153415351536153715381539154015411542154315441545154615471548154915501551155215531554155515561557155815591560156115621563156415651566156715681569157015711572157315741575157615771578157915801581158215831584158515861587158815891590159115921593159415951596159715981599160016011602160316041605160616071608160916101611161216131614161516161617161816191620162116221623162416251626162716281629163016311632163316341635163616371638163916401641164216431644164516461647164816491650165116521653165416551656165716581659166016611662166316641665166616671668166916701671167216731674167516761677167816791680168116821683168416851686168716881689169016911692169316941695169616971698169917001701170217031704170517061707170817091710171117121713171417151716171717181719172017211722172317241725172617271728172917301731173217331734173517361737173817391740174117421743174417451746174717481749175017511752175317541755175617571758175917601761176217631764176517661767176817691770177117721773177417751776177717781779178017811782178317841785178617871788178917901791179217931794179517961797179817991800180118021803180418051806180718081809181018111812181318141815181618171818181918201821182218231824182518261827182818291830183118321833183418351836183718381839184018411842184318441845184618471848184918501851185218531854185518561857185818591860186118621863186418651866186718681869187018711872187318741875187618771878187918801881188218831884188518861887188818891890189118921893189418951896189718981899190019011902190319041905190619071908190919101911191219131914191519161917191819191920192119221923192419251926192719281929193019311932193319341935193619371938193919401941194219431944194519461947194819491950195119521953195419551956195719581959196019611962196319641965196619671968196919701971197219731974197519761977197819791980198119821983198419851986198719881989199019911992199319941995199619971998199920002001200220032004200520062007200820092010201120122013201420152016201720182019202020212022202320242025202620272028202920302031203220332034203520362037203820392040204120422043204420452046204720482049205020512052205320542055205620572058205920602061206220632064206520662067206820692070207120722073207420752076207720782079208020812082208320842085208620872088208920902091209220932094209520962097209820992100210121022103210421052106210721082109211021112112211321142115211621172118211921202121212221232124212521262127212821292130213121322133213421352136213721382139214021412142214321442145214621472148214921502151215221532154215521562157215821592160216121622163216421652166216721682169217021712172217321742175217621772178217921802181218221832184218521862187218821892190219121922193219421952196219721982199220022012202220322042205220622072208220922102211221222132214221522162217221822192220222122222223222422252226222722282229223022312232223322342235223622372238223922402241224222432244224522462247224822492250225122522253225422552256225722582259226022612262226322642265226622672268226922702271227222732274227522762277227822792280228122822283228422852286228722882289229022912292229322942295229622972298229923002301230223032304230523062307230823092310231123122313231423152316231723182319232023212322232323242325232623272328232923302331233223332334233523362337233823392340234123422343234423452346234723482349235023512352235323542355235623572358235923602361236223632364236523662367236823692370237123722373237423752376237723782379238023812382238323842385238623872388238923902391239223932394239523962397239823992400240124022403240424052406240724082409241024112412241324142415241624172418241924202421242224232424242524262427242824292430243124322433243424352436243724382439244024412442244324442445244624472448244924502451245224532454245524562457245824592460246124622463246424652466246724682469247024712472247324742475247624772478247924802481248224832484248524862487248824892490249124922493249424952496249724982499250025012502250325042505250625072508250925102511251225132514251525162517251825192520252125222523252425252526252725282529253025312532253325342535253625372538253925402541254225432544254525462547254825492550255125522553255425552556255725582559256025612562256325642565256625672568256925702571257225732574257525762577257825792580258125822583258425852586258725882589259025912592259325942595259625972598259926002601260226032604260526062607260826092610261126122613261426152616261726182619262026212622262326242625
  1. using FastReport.Format;
  2. using FastReport.Utils;
  3. using System;
  4. using System.Collections;
  5. using System.Collections.Generic;
  6. using System.Drawing;
  7. using System.Globalization;
  8. using System.IO;
  9. using System.Linq;
  10. using System.Reflection;
  11. using System.Text;
  12. using System.Windows.Forms;
  13. namespace FastReport.Export.OoXML
  14. {
  15. /// <summary>
  16. /// Drawing class
  17. /// </summary>
  18. internal class OoXMLDrawing : OoXMLBase
  19. {
  20. #region Private properties
  21. private int picCount;
  22. private StringBuilder drawing_string;
  23. private StringBuilder picture_rels;
  24. private Excel2007Export ooXML;
  25. #endregion Private properties
  26. public int PicCount { get { return picCount; } }
  27. #region Class overrides
  28. public override string RelationType
  29. {
  30. get { return "http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing"; }
  31. }
  32. public override string ContentType
  33. {
  34. get { return "application/vnd.openxmlformats-officedocument.drawing+xml"; }
  35. }
  36. public override string FileName
  37. {
  38. get { return "xl/drawings/drawing1.xml"; }
  39. }
  40. #endregion Class overrides
  41. private void ExportBorder(ExportIEMStyle style)
  42. {
  43. if (style.Border.Lines == BorderLines.All)
  44. {
  45. drawing_string.AppendFormat("<a:ln w=\"{0}\">", style.Border.LeftLine.Width);
  46. drawing_string.Append("<a:solidFill>");
  47. drawing_string.AppendFormat("<a:srgbClr val=\"{0:X2}{1:X2}{2:X2}\"/>",
  48. style.Border.LeftLine.Color.R,
  49. style.Border.LeftLine.Color.G,
  50. style.Border.LeftLine.Color.B);
  51. drawing_string.Append("</a:solidFill>");
  52. drawing_string.Append(GetDashStyle(style.Border.LeftLine.DashStyle));
  53. drawing_string.Append("</a:ln>");
  54. }
  55. }
  56. public void Append(ExportIEMObject Obj, int x, int y, int dx, int dy, int rowNum, int matrixWidth)
  57. {
  58. picCount++;
  59. string rid = "\"rId" + picCount.ToString() + "\"";
  60. string picFileName = "xl/media/" + Obj.Hash + ".png";
  61. if (!ooXML.Zip.FileExists(picFileName))
  62. {
  63. Obj.PictureStream.Position = 0;
  64. ooXML.Zip.AddStream(picFileName, Obj.PictureStream);
  65. }
  66. picture_rels.AppendLine("<Relationship Id=" + rid +
  67. " Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/image\" " +
  68. "Target=\"/" + picFileName + "\" />");
  69. string pictureProperties;
  70. switch (ooXML.PictureProperties)
  71. {
  72. case Excel2007Export.ePictureProperties.OnlyMove:
  73. pictureProperties = " editAs=\"oneCell\"";
  74. break;
  75. case Excel2007Export.ePictureProperties.Unchangeable:
  76. pictureProperties = " editAs=\"absolute\"";
  77. break;
  78. default:
  79. pictureProperties = "";
  80. break;
  81. }
  82. drawing_string.Append($"<xdr:twoCellAnchor{pictureProperties}>");
  83. drawing_string.Append("<xdr:from>");
  84. drawing_string.Append("<xdr:col>").Append(x.ToString()).Append("</xdr:col>");
  85. drawing_string.Append("<xdr:colOff>0</xdr:colOff>");
  86. if (!ooXML.Seamless)
  87. {
  88. drawing_string.Append("<xdr:row>").Append(y.ToString()).Append("</xdr:row>");
  89. }
  90. else
  91. {
  92. drawing_string.Append("<xdr:row>").Append((rowNum - 1).ToString()).Append("</xdr:row>");
  93. }
  94. drawing_string.Append("<xdr:rowOff>0</xdr:rowOff>");
  95. drawing_string.Append("</xdr:from>");
  96. drawing_string.Append("<xdr:to>");
  97. int colTo = x + dx;
  98. //if (x == 0 && colTo > matrixWidth - 1)
  99. // colTo = matrixWidth - 1;
  100. drawing_string.Append("<xdr:col>").Append(colTo.ToString()).Append("</xdr:col>");
  101. drawing_string.Append("<xdr:colOff>0</xdr:colOff>");
  102. if (!ooXML.Seamless)
  103. {
  104. drawing_string.Append("<xdr:row>").Append((y + dy).ToString()).Append("</xdr:row>");
  105. }
  106. else
  107. {
  108. drawing_string.Append("<xdr:row>").Append((rowNum - 1 + dy).ToString()).Append("</xdr:row>");
  109. }
  110. drawing_string.Append("<xdr:rowOff>0</xdr:rowOff>");
  111. drawing_string.Append("</xdr:to>");
  112. drawing_string.Append("<xdr:pic>");
  113. drawing_string.Append("<xdr:nvPicPr>");
  114. drawing_string.Append("<xdr:cNvPr id=\"").
  115. Append(picCount.ToString()).Append("\" name=\"image").
  116. Append(picCount.ToString()).Append(".png\"> ");
  117. if (Obj.URL != null && Obj.URL.Length > 0)
  118. {
  119. ++picCount;
  120. string rIdLink = "\"rId" + picCount.ToString() + "\"";
  121. picture_rels.AppendLine("<Relationship Id=" + rIdLink +
  122. " Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink\" " +
  123. "Target=\"" + Utils.Converter.ToXml(Obj.URL) + "\" TargetMode=\"External\" />");
  124. drawing_string.Append("<a:hlinkClick xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\" r:id=" +
  125. rIdLink + "/>");
  126. }
  127. drawing_string.Append("</xdr:cNvPr>");
  128. drawing_string.Append("<xdr:cNvPicPr>");
  129. drawing_string.Append("<a:picLocks noChangeAspect=\"1\"/>");
  130. drawing_string.Append("</xdr:cNvPicPr>");
  131. drawing_string.Append("</xdr:nvPicPr>");
  132. drawing_string.Append("<xdr:blipFill>");
  133. drawing_string.Append(
  134. "<a:blip xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\" r:embed=").
  135. Append(rid).Append("/>");
  136. //Append(" state=\"hqprint\"/>"); // fix for Excel 2007
  137. drawing_string.Append("<a:stretch><a:fillRect/></a:stretch>");
  138. drawing_string.Append("</xdr:blipFill>");
  139. drawing_string.Append("<xdr:spPr>");
  140. drawing_string.Append("<a:prstGeom prst=\"rect\"><a:avLst/></a:prstGeom>");
  141. ExportBorder(Obj.Style);
  142. drawing_string.Append("</xdr:spPr>");
  143. drawing_string.Append("</xdr:pic>");
  144. //drawing_string.Append("<xdr:clientData fLocksWithSheet=\"1\"/>"); fix for Excel 2007
  145. drawing_string.Append("<xdr:clientData />");
  146. drawing_string.AppendLine("</xdr:twoCellAnchor>");
  147. }
  148. public void Start()
  149. {
  150. //=== johnny appended 2012/6/17
  151. drawing_string = new StringBuilder(8192);
  152. picture_rels = new StringBuilder(256);
  153. drawing_string.Append(xml_header);
  154. drawing_string.Append(
  155. "<xdr:wsDr xmlns:xdr=\"http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing\" ").
  156. AppendLine("xmlns:a=\"http://schemas.openxmlformats.org/drawingml/2006/main\">");
  157. picture_rels.Append("<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\" ?>");
  158. picture_rels.Append("<Relationships xmlns=\"http://schemas.openxmlformats.org/package/2006/relationships\">");
  159. }
  160. //public void Stop()
  161. //=== johnny appended 2012/6/17
  162. public void Stop(string sSheetID)
  163. {
  164. drawing_string.Append("</xdr:wsDr>");
  165. picture_rels.Append("</Relationships>");
  166. if (picCount != 0)
  167. {
  168. MemoryStream file = new MemoryStream();
  169. ExportUtils.WriteLn(file, picture_rels.ToString());
  170. file.Position = 0;
  171. ooXML.Zip.AddStream("xl/drawings/_rels/drawing" + sSheetID + ".xml.rels", file);
  172. file = new MemoryStream();
  173. ExportUtils.WriteLn(file, drawing_string.ToString());
  174. file.Position = 0;
  175. ooXML.Zip.AddStream("xl/drawings/drawing" + sSheetID + ".xml", file);
  176. picCount = 0;
  177. }
  178. }
  179. public OoXMLDrawing(Excel2007Export OoXML)
  180. {
  181. ooXML = OoXML;
  182. picCount = 0;
  183. drawing_string = new StringBuilder(8192);
  184. picture_rels = new StringBuilder(1024);
  185. }
  186. }
  187. /// <summary>
  188. /// Share all strings in document
  189. /// </summary>
  190. internal class OoXMLSharedStringTable : OoXMLBase
  191. {
  192. #region Private properties
  193. private List<string> sharedStringList;
  194. #endregion Private properties
  195. #region Class overrides
  196. public override string RelationType
  197. {
  198. get { return "http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings"; }
  199. }
  200. public override string ContentType
  201. {
  202. get { return "application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"; }
  203. }
  204. public override string FileName { get { return "sharedStrings.xml"; } }
  205. #endregion Class overrides
  206. private struct CurrentStyle
  207. {
  208. public int Size;
  209. public bool Bold;
  210. public bool Italic;
  211. public bool Underline;
  212. public bool Strike;
  213. public bool Sub;
  214. public bool Sup;
  215. public Color Colour;
  216. public Font Font;
  217. public string LastTag;
  218. }
  219. private string ConvertHtmlItem(CurrentStyle style)
  220. {
  221. StringBuilder str = new StringBuilder(256);
  222. str.Append("<rPr>");
  223. if (style.Bold) str.Append("<b/>");
  224. if (style.Italic) str.Append("<i/>");
  225. if (style.Underline) str.Append("<u/>");
  226. if (style.Strike) str.Append("<strike/>");
  227. if (style.Sup) str.Append("<vertAlign val=\"superscript\"/>");
  228. if (style.Sub) str.Append("<vertAlign val=\"subscript\"/>");
  229. str.Append(string.Format(CultureInfo.InvariantCulture, "<sz val=\"{0}\" />", style.Size));
  230. str.Append("<rFont val=\"").Append(style.Font.Name).Append("\"/>");
  231. if (!style.Colour.ToArgb().Equals(Color.Black.ToArgb()))
  232. {
  233. str.Append(string.Format(CultureInfo.InvariantCulture,
  234. "<color rgb=\"{0:X2}{1:X2}{2:X2}{3:X2}\" /> ",
  235. style.Colour.A, style.Colour.R, style.Colour.G, style.Colour.B));
  236. }
  237. str.Append("</rPr>");
  238. return str.ToString();
  239. }
  240. private void ParseFont(string s, CurrentStyle style, out CurrentStyle result_style)
  241. {
  242. result_style = style;
  243. string[] font_items = s.Split('=');
  244. string Tag = font_items[0].ToUpper();
  245. switch (Tag)
  246. {
  247. case "COLOR":
  248. string[] val = font_items[1].Split('\"');
  249. result_style.Colour = Color.FromName(val[1]);
  250. break;
  251. //default:
  252. //throw new Exception("Unsupported font item: " + Tag);
  253. }
  254. }
  255. private string ParseHtmlTags(string s, ExportIEMObject obj)
  256. {
  257. int Index = 0;
  258. int Begin = 0;
  259. int End = 0;
  260. string Tag;
  261. string Text;
  262. CurrentStyle current_style = new CurrentStyle();
  263. CurrentStyle previos_style;
  264. current_style.Size = (obj.Style.Font.Size > 0) ? (int)obj.Style.Font.Size : 10;
  265. current_style.Bold = false;
  266. current_style.Italic = false;
  267. current_style.Underline = false;
  268. current_style.Colour = Color.FromName("Black");
  269. current_style.Strike = false;
  270. current_style.Sub = false;
  271. current_style.Sup = false;
  272. current_style.Font = obj.Style.Font;
  273. StringBuilder result = new StringBuilder(s.Length + 7);
  274. Stack<CurrentStyle> style_stack = new Stack<CurrentStyle>();
  275. Begin = s.IndexOfAny(new char[1] { '<' }, Index);
  276. while (Begin != -1)
  277. {
  278. if (Begin != 0 && Index == 0)
  279. {
  280. result.Append("<r><t xml:space=\"preserve\">").
  281. Append(ExportUtils.Excel2007String(s.Substring(Index, Begin), obj.TextRenderType)).
  282. Append("</t></r>");
  283. }
  284. End = s.IndexOfAny(new char[1] { '>' }, Begin + 1);
  285. if (End == -1)
  286. break;
  287. Tag = s.Substring(Begin + 1, End - Begin - 1);
  288. bool CloseTag = Tag.StartsWith("/");
  289. if (CloseTag)
  290. Tag = Tag.Remove(0, 1);
  291. string[] items = Tag.Split(' ');
  292. Tag = items[0].ToUpper();
  293. if (!CloseTag)
  294. {
  295. current_style.LastTag = Tag;
  296. style_stack.Push(current_style);
  297. switch (Tag)
  298. {
  299. case "B": current_style.Bold = true; break;
  300. case "I": current_style.Italic = true; break;
  301. case "U": current_style.Underline = true; break;
  302. case "STRIKE": current_style.Strike = true; break;
  303. case "SUB": current_style.Sub = true; break;
  304. case "SUP": current_style.Sup = true; break;
  305. case "FONT":
  306. /*current_style.Font = items[1];*/
  307. ParseFont(items[1], current_style, out current_style);
  308. break;
  309. //default:
  310. // throw new Exception("Unsupported HTML TAG");
  311. }
  312. }
  313. else
  314. {
  315. if (style_stack.Count > 0)
  316. {
  317. previos_style = style_stack.Pop();
  318. if (previos_style.LastTag != Tag)
  319. {
  320. //throw new Exception("Unaligned HTML TAGS");
  321. }
  322. current_style = previos_style;
  323. }
  324. }
  325. Index = End + 1;
  326. Begin = s.IndexOfAny(new char[1] { '<' }, Index);
  327. if (Begin == -1)
  328. {
  329. Text = s.Substring(Index);
  330. }
  331. else
  332. {
  333. Text = s.Substring(Index, Begin - Index);
  334. }
  335. if (Text.Length > 0)
  336. result.Append("<r>").Append(ConvertHtmlItem(current_style)).
  337. Append("<t xml:space=\"preserve\">").Append(ExportUtils.Excel2007String(Text, obj.TextRenderType)).
  338. Append("</t></r>");
  339. }
  340. return result.ToString();
  341. }
  342. private bool HaveSeqSpc(string s)
  343. {
  344. int len = s.Length;
  345. for (int i = 0; i < len; i++)
  346. {
  347. if (s[i] == ' ' &&
  348. // there is only one character in the string, and that character is a space
  349. (s.Length == 1 ||
  350. // the current character is not the last character of the string and the next character after it is also a space
  351. (i < (len - 1) && s[i + 1] == ' ') ||
  352. // the current character is not the first character of the string and the previous character before it is also a space
  353. (i > 0 && s[i - 1] == ' ') ||
  354. // the current character is the last character of the string
  355. i == len - 1 ||
  356. // the first character of the string is a space and the character following it is not a space
  357. (i == 0 && s[i + 1] != ' ')))
  358. return true;
  359. }
  360. return false;
  361. }
  362. public int Add(string s, ExportIEMObject obj)
  363. {
  364. int idx;
  365. string parsed_tags = String.Empty;
  366. switch (obj.TextRenderType)
  367. {
  368. case TextRenderType.HtmlTags:
  369. s = s.Replace("<br>", "\r\n");
  370. parsed_tags = ParseHtmlTags(s, obj);
  371. break;
  372. case TextRenderType.HtmlParagraph:
  373. s = s.Replace("<br>", "\r\n");
  374. parsed_tags = ParseHtmlTags(s, obj);
  375. //TODO DETRAV EXCEL2007
  376. break;
  377. }
  378. if (!String.IsNullOrEmpty(parsed_tags))
  379. s = parsed_tags;
  380. // checking for consecutive spaces in a string
  381. else if (HaveSeqSpc(s))
  382. s = "<t xml:space=\"preserve\">" + s + "</t>";
  383. else
  384. s = "<t>" + s + "</t>";
  385. StringBuilder str = new StringBuilder();
  386. foreach (char c in s)
  387. {
  388. if ((int)c < 32 || ((int)c >= 127 && (int)c < 160))
  389. {
  390. str.Append("_x" + ((int)c).ToString("X4") + "_");
  391. }
  392. else
  393. {
  394. str.Append(c);
  395. }
  396. }
  397. s = str.ToString();
  398. if (sharedStringList.Count > 100)
  399. idx = sharedStringList.IndexOf(s, sharedStringList.Count - 100);
  400. else
  401. idx = sharedStringList.IndexOf(s);
  402. if (idx == -1)
  403. {
  404. sharedStringList.Add(s);
  405. idx = sharedStringList.Count - 1;
  406. }
  407. return idx;
  408. }
  409. private void WriteSharedStringsBuffer(StringBuilder strings, Stream stream)
  410. {
  411. ExportUtils.WriteLn(stream, strings.ToString());
  412. strings.Length = 0;
  413. }
  414. public void Export(Excel2007Export OoXML)
  415. {
  416. Stream file;
  417. if (OoXML.UseFileStream)
  418. {
  419. string tempFileName = OoXML.GetFileName() + "1" + ".tmp";
  420. file = new FileStream(tempFileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
  421. OoXML.TempFileNames.Add(tempFileName);
  422. }
  423. else
  424. file = new MemoryStream();
  425. ExportUtils.WriteLn(file, xml_header);
  426. ExportUtils.WriteLn(file, "<sst xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" count=\"" +
  427. sharedStringList.Count + "\" uniqueCount=\"" + sharedStringList.Count + "\">");
  428. int i = 0;
  429. StringBuilder sharedStrings = new StringBuilder(8192);
  430. foreach (string item in sharedStringList)
  431. {
  432. // Next block is a fix of bug with not escaped ampersands.
  433. //=================================================================================
  434. int start = -1;
  435. List<int> indexes = new List<int>();
  436. start = item.IndexOf("&");
  437. // Find index of each ampersand in the string.
  438. while (start > -1)
  439. {
  440. indexes.Add(start);
  441. start = item.IndexOf("&", start + 1);
  442. }
  443. // Remove index of each correct ampersand.
  444. for (int j = indexes.Count - 1; j >= 0; j--)
  445. {
  446. if (item.IndexOf("&amp;", indexes[j]) == indexes[j])
  447. indexes.RemoveAt(j);
  448. else if (item.IndexOf("&#10", indexes[j]) == indexes[j])
  449. indexes.RemoveAt(j);
  450. else if (item.IndexOf("&quot;", indexes[j]) == indexes[j])
  451. indexes.RemoveAt(j);
  452. else if (item.IndexOf("&apos;", indexes[j]) == indexes[j])
  453. indexes.RemoveAt(j);
  454. else if (item.IndexOf("&lt;", indexes[j]) == indexes[j])
  455. indexes.RemoveAt(j);
  456. else if (item.IndexOf("&gt;", indexes[j]) == indexes[j])
  457. indexes.RemoveAt(j);
  458. else if (item.IndexOf("&#", indexes[j]) == indexes[j])
  459. indexes.RemoveAt(j);
  460. }
  461. // Escape each incorrect ampersand.
  462. string correctItem = item;
  463. for (int k = indexes.Count - 1; k >= 0; k--)
  464. {
  465. correctItem = correctItem.Remove(indexes[k], 1);
  466. correctItem = correctItem.Insert(indexes[k], "&amp;");
  467. }
  468. //=================================================================================
  469. sharedStrings.Append("<si>").Append(correctItem).Append("</si>");
  470. if (i++ >= 100)
  471. {
  472. WriteSharedStringsBuffer(sharedStrings, file);
  473. i = 0;
  474. }
  475. }
  476. WriteSharedStringsBuffer(sharedStrings, file);
  477. ExportUtils.WriteLn(file, "</sst>");
  478. file.Position = 0;
  479. OoXML.Zip.AddStream("xl/" + FileName, file);
  480. }
  481. public OoXMLSharedStringTable()
  482. {
  483. sharedStringList = new List<string>();
  484. }
  485. }
  486. /// <summary>
  487. /// Share all URL in document
  488. /// </summary>
  489. internal class OoXMLSharedURLTable : OoXMLBase
  490. {
  491. #if READONLY_STRUCTS
  492. private readonly struct Item
  493. #else
  494. private struct Item
  495. #endif
  496. {
  497. internal readonly int id;
  498. internal readonly int x;
  499. internal readonly int y;
  500. internal readonly string url;
  501. internal Item(int id, int x, int y, string url)
  502. {
  503. this.id = id;
  504. this.x = x;
  505. this.y = y;
  506. this.url = url;
  507. }
  508. }
  509. #region Private properties
  510. private List<Item> uRL = new List<Item>();
  511. #endregion Private properties
  512. #region Class overrides
  513. /* "http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings"; */
  514. public override string RelationType { get { throw new Exception("Not decalred"); } }
  515. /* "application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"; */
  516. public override string ContentType { get { throw new Exception("Not decalred"); } }
  517. /* return "sharedStrings.xml"; */
  518. public override string FileName { get { throw new Exception("Not decalred"); } }
  519. #endregion Class overrides
  520. internal void Add(int x, int y, string url)
  521. {
  522. int id = uRL.Count + 2;
  523. uRL.Add(new Item(id, x, y, url));
  524. }
  525. internal int Count { get { return uRL.Count; } }
  526. internal StringBuilder ExportBody(Excel2007Export OoXML)
  527. {
  528. StringBuilder builder = new StringBuilder(512);
  529. builder.Append("<hyperlinks>");
  530. foreach (Item i in uRL)
  531. {
  532. builder.AppendFormat("<hyperlink ref=\"{0}\" r:id=\"{1}{2}\"/>",
  533. ExportUtils.GetCellReference(i.x, i.y + 1), // Quoted("A2")
  534. "rId", i.id.ToString() // Quoted("rId1")
  535. );
  536. }
  537. builder.AppendLine("</hyperlinks>");
  538. return builder;
  539. }
  540. internal StringBuilder ExportRelationship()
  541. {
  542. StringBuilder builder = new StringBuilder(256);
  543. foreach (Item i in uRL)
  544. {
  545. builder.AppendFormat(
  546. "<Relationship TargetMode=\"External\" Target={0} " +
  547. "Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink\" Id={1}/>",
  548. Quoted(ExportUtils.Excel2007String(i.url, TextRenderType.Default)),
  549. Quoted("rId" + i.id.ToString())
  550. );
  551. }
  552. return builder;
  553. }
  554. }
  555. /// <summary>
  556. /// Document styles
  557. /// </summary>
  558. internal class OoXMLDocumentStyles : OoXMLBase
  559. {
  560. #region Private properties
  561. private List<ExportIEMStyle> styles;
  562. private int formatIndex;
  563. private bool currencyToAccounting;
  564. private bool generalToText;
  565. #endregion Private properties
  566. #region Class overrides
  567. public override string RelationType
  568. {
  569. get { return "http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles"; }
  570. }
  571. public override string ContentType
  572. {
  573. get { return "application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"; }
  574. }
  575. public override string FileName { get { return "styles.xml"; } }
  576. internal bool UseLocaleFormating { get; set; }
  577. #endregion Class overrides
  578. #region Helpers
  579. private string GetRGBString(Color c)
  580. {
  581. return "\"" +
  582. ExportUtils.ByteToHex(c.A) +
  583. ExportUtils.ByteToHex(c.R) +
  584. ExportUtils.ByteToHex(c.G) +
  585. ExportUtils.ByteToHex(c.B) +
  586. "\"";
  587. }
  588. private string HAlign2String(HorzAlign ha)
  589. {
  590. switch (ha)
  591. {
  592. case HorzAlign.Center: return "center";
  593. case HorzAlign.Left: return "left";
  594. case HorzAlign.Right: return "right";
  595. case HorzAlign.Justify: return "justify";
  596. }
  597. return "";
  598. }
  599. private string VAlign2String(VertAlign va)
  600. {
  601. switch (va)
  602. {
  603. case VertAlign.Bottom: return "bottom";
  604. case VertAlign.Center: return "center";
  605. case VertAlign.Top: return "top";
  606. }
  607. return "";
  608. }
  609. private string Styles2String(LineStyle style, float Width)
  610. {
  611. if (Width < 2) switch (style)
  612. {
  613. case LineStyle.Solid: return "\"thin\"";
  614. case LineStyle.Double: return "\"double\"";
  615. case LineStyle.Dot: return "\"dotted\"";
  616. case LineStyle.DashDotDot: return "\"dashDotDot\"";
  617. case LineStyle.DashDot: return "\"dashDot\"";
  618. case LineStyle.Dash: return "\"dashed\"";
  619. }
  620. else if (Width < 3.5) switch (style)
  621. {
  622. case LineStyle.Solid: return "\"medium\"";
  623. case LineStyle.Double: return "\"double\"";
  624. case LineStyle.Dot: return "\"mediumDashed\""; // Due no "mediumDotted" do not exist in spec
  625. case LineStyle.DashDotDot: return "\"mediumDashDotDot\"";
  626. case LineStyle.DashDot: return "\"mediumDashDot\"";
  627. case LineStyle.Dash: return "\"mediumDashed\"";
  628. }
  629. else switch (style)
  630. {
  631. case LineStyle.Solid: return "\"thick\"";
  632. case LineStyle.Double: return "\"double\"";
  633. case LineStyle.Dot: return "\"mediumDashed\""; // Due no "mediumDotted" do not exist in spec
  634. case LineStyle.DashDotDot: return "\"mediumDashDotDot\"";
  635. case LineStyle.DashDot: return "\"slantDashDot\"";
  636. case LineStyle.Dash: return "\"mediumDashed\"";
  637. }
  638. return "";
  639. }
  640. private string ConvertRotationAngle(int Angle)
  641. {
  642. if (Angle != 0 && Angle <= 90)
  643. {
  644. Angle = 90 + Angle;
  645. }
  646. else if (Angle >= 270)
  647. {
  648. Angle = 360 - Angle;
  649. }
  650. else
  651. {
  652. Angle = 0;
  653. }
  654. return Angle.ToString();
  655. }
  656. #endregion Helpers
  657. private void ExportFormats(Stream file)
  658. {
  659. StringBuilder result = new StringBuilder();
  660. int res_count = 0;
  661. for (int i = 0; i < styles.Count; i++)
  662. {
  663. ExportIEMStyle EStyle = styles[i];
  664. FormatBase format_base = EStyle.Format;
  665. if (format_base is CurrencyFormat || format_base is NumberFormat || format_base is DateFormat
  666. || format_base is PercentFormat)
  667. {
  668. if (GetDefaultNumberCode(format_base) == -1)
  669. {
  670. ++res_count;
  671. result.AppendFormat(CultureInfo.InvariantCulture, "<numFmt numFmtId=\"{0}\" formatCode=\"{1}\" />",
  672. formatIndex + res_count,
  673. ExportUtils.GetExcelFormatSpecifier(format_base, UseLocaleFormating, currencyToAccounting));
  674. }
  675. }
  676. }
  677. if (res_count > 0)
  678. {
  679. ExportUtils.WriteLn(file, "<numFmts count=\"" + res_count + "\">");
  680. ExportUtils.WriteLn(file, result.ToString());
  681. ExportUtils.WriteLn(file, "</numFmts>");
  682. }
  683. }
  684. private int GetDefaultNumberCode(FormatBase format_base)
  685. {
  686. //https://github.com/closedxml/closedxml/wiki/NumberFormatId-Lookup-Table
  687. if (format_base is NumberFormat)
  688. {
  689. NumberFormat nbr = (format_base as NumberFormat);
  690. if (nbr.DecimalDigits == 0 &&
  691. (/*nbr.GroupSeparator == " " ||*/ nbr.GroupSeparator == ""/* || // redmine 2525
  692. nbr.GroupSeparator.ToCharArray()[0] == 160*/) &&
  693. nbr.NegativePattern == 1) // -n
  694. return 1;
  695. else if (nbr.DecimalDigits == 2 &&
  696. nbr.GroupSeparator == "" && //without group separator
  697. nbr.NegativePattern == 1) // -n
  698. return 2;
  699. else if (nbr.DecimalDigits == 0 &&
  700. nbr.GroupSeparator == "," &&
  701. nbr.NegativePattern == 1) // -n
  702. return 3;
  703. else if (nbr.DecimalDigits == 2 &&
  704. nbr.GroupSeparator == "," &&
  705. nbr.NegativePattern == 1) // -n
  706. return 4;
  707. else if (nbr.DecimalDigits == 0 &&
  708. nbr.GroupSeparator == "," &&
  709. nbr.NegativePattern == 0) // (n)
  710. return 37;
  711. else if (nbr.DecimalDigits == 2 &&
  712. nbr.GroupSeparator == "," &&
  713. nbr.NegativePattern == 0) // (n)
  714. return 39;
  715. else
  716. return -1;
  717. }
  718. else
  719. return -1;
  720. }
  721. private int GetFormatCode(ExportIEMStyle LookingForStyle)
  722. {
  723. int res_count = 0;
  724. for (int i = 0; i < styles.Count; i++)
  725. {
  726. ExportIEMStyle EStyle = styles[i];
  727. FormatBase format_base = EStyle.Format;
  728. if (format_base is CurrencyFormat || format_base is NumberFormat || format_base is DateFormat
  729. || format_base is PercentFormat)
  730. {
  731. int code = GetDefaultNumberCode(format_base);
  732. bool defaultCode = code != -1;
  733. if (!defaultCode)
  734. ++res_count;
  735. if (LookingForStyle == styles[i])
  736. {
  737. if (defaultCode)
  738. return code;
  739. else
  740. return formatIndex + res_count;
  741. }
  742. }
  743. }
  744. return generalToText ? 49 : 0;
  745. }
  746. private void ExportFonts(Stream file)
  747. {
  748. int i;
  749. ExportIEMStyle EStyle;
  750. StringBuilder fonts = new StringBuilder(4096);
  751. fonts.Append("<fonts count=\"").Append(styles.Count.ToString()).AppendLine("\">");
  752. for (i = 0; i < styles.Count; i++)
  753. {
  754. EStyle = styles[i];
  755. Font font = EStyle.Font;
  756. fonts.Append("<font>");
  757. if (font.Bold)
  758. fonts.Append("<b/>");
  759. if (font.Italic)
  760. fonts.Append("<i/>");
  761. if (font.Underline)
  762. fonts.Append("<u/>");
  763. if (font.Strikeout)
  764. fonts.Append("<strike val=\"1\"/>");
  765. fonts.Append("<sz val=\"").
  766. Append(font.Size.ToString(CultureInfo.InvariantCulture.NumberFormat)).
  767. AppendLine("\" />");
  768. Color c = EStyle.TextColor;
  769. if (c.A == 0 && c.R == 255 && c.G == 255 && c.B == 255)
  770. {
  771. c = Color.Black;
  772. }
  773. fonts.Append("<color rgb=").Append(GetRGBString(c)).AppendLine(" />");
  774. fonts.Append("<name val=\"").Append(font.Name).AppendLine("\" />");
  775. fonts.AppendLine("</font>");
  776. }
  777. fonts.AppendLine("</fonts>");
  778. ExportUtils.Write(file, fonts.ToString());
  779. }
  780. private void ExportFills(Stream file)
  781. {
  782. int i;
  783. ExportIEMStyle EStyle;
  784. StringBuilder fills = new StringBuilder(4096);
  785. fills.Append("<fills count=\"").Append((styles.Count + 2).ToString()).AppendLine("\">");
  786. fills.AppendLine("<fill><patternFill patternType=\"none\"/></fill>");
  787. fills.AppendLine("<fill><patternFill patternType=\"gray125\"/></fill>");
  788. for (i = 0; i < styles.Count; i++)
  789. {
  790. EStyle = styles[i];
  791. fills.AppendLine("<fill>");
  792. if (EStyle.Fill is LinearGradientFill)
  793. {
  794. LinearGradientFill linear = EStyle.Fill as LinearGradientFill;
  795. fills.Append("<gradientFill degree=\"").Append(linear.Angle.ToString()).AppendLine("\">");
  796. fills.AppendLine("<stop position=\"0\">");
  797. fills.Append("<color rgb=").Append(GetRGBString(linear.StartColor)).AppendLine(" /> ");
  798. fills.AppendLine("</stop>");
  799. fills.AppendLine("<stop position=\"1\">");
  800. fills.Append("<color rgb=").Append(GetRGBString(linear.EndColor)).AppendLine(" /> ");
  801. fills.AppendLine("</stop>");
  802. fills.AppendLine("</gradientFill>");
  803. }
  804. else if (EStyle.Fill is HatchFill)
  805. {
  806. bool swap_color = false;
  807. string PatternType = "none";
  808. HatchFill hatch = EStyle.Fill as HatchFill;
  809. switch (hatch.Style)
  810. {
  811. case System.Drawing.Drawing2D.HatchStyle.DarkUpwardDiagonal:
  812. PatternType = "lightUp";
  813. swap_color = true;
  814. break;
  815. case System.Drawing.Drawing2D.HatchStyle.BackwardDiagonal:
  816. PatternType = "lightUp";
  817. break;
  818. // case System.Drawing.Drawing2D.HatchStyle.Cross:
  819. // case System.Drawing.Drawing2D.HatchStyle.Max:
  820. case System.Drawing.Drawing2D.HatchStyle.LargeGrid:
  821. case System.Drawing.Drawing2D.HatchStyle.SmallGrid:
  822. PatternType = "lightGrid";
  823. break;
  824. case System.Drawing.Drawing2D.HatchStyle.ForwardDiagonal:
  825. swap_color = true;
  826. PatternType = "darkDown";
  827. break;
  828. case System.Drawing.Drawing2D.HatchStyle.DarkDownwardDiagonal:
  829. PatternType = "darkDown";
  830. break;
  831. case System.Drawing.Drawing2D.HatchStyle.DarkHorizontal:
  832. PatternType = "darkHorizontal";
  833. break;
  834. case System.Drawing.Drawing2D.HatchStyle.LightHorizontal:
  835. PatternType = "lightHorizontal";
  836. break;
  837. case System.Drawing.Drawing2D.HatchStyle.LightVertical:
  838. PatternType = "lightVertical";
  839. break;
  840. case System.Drawing.Drawing2D.HatchStyle.DarkVertical:
  841. PatternType = "darkVertical";
  842. break;
  843. case System.Drawing.Drawing2D.HatchStyle.Trellis:
  844. PatternType = "darkTrellis";
  845. break;
  846. case System.Drawing.Drawing2D.HatchStyle.Percent05:
  847. PatternType = "gray0625";
  848. break;
  849. //case System.Drawing.Drawing2D.HatchStyle.Min:
  850. case System.Drawing.Drawing2D.HatchStyle.Horizontal:
  851. case System.Drawing.Drawing2D.HatchStyle.Vertical:
  852. case System.Drawing.Drawing2D.HatchStyle.DiagonalCross:
  853. case System.Drawing.Drawing2D.HatchStyle.Percent10:
  854. case System.Drawing.Drawing2D.HatchStyle.Percent20:
  855. case System.Drawing.Drawing2D.HatchStyle.Percent25:
  856. case System.Drawing.Drawing2D.HatchStyle.Percent30:
  857. case System.Drawing.Drawing2D.HatchStyle.Percent40:
  858. case System.Drawing.Drawing2D.HatchStyle.Percent50:
  859. case System.Drawing.Drawing2D.HatchStyle.Percent60:
  860. case System.Drawing.Drawing2D.HatchStyle.Percent70:
  861. case System.Drawing.Drawing2D.HatchStyle.Percent75:
  862. case System.Drawing.Drawing2D.HatchStyle.Percent80:
  863. case System.Drawing.Drawing2D.HatchStyle.Percent90:
  864. case System.Drawing.Drawing2D.HatchStyle.LightDownwardDiagonal:
  865. case System.Drawing.Drawing2D.HatchStyle.LightUpwardDiagonal:
  866. case System.Drawing.Drawing2D.HatchStyle.WideDownwardDiagonal:
  867. case System.Drawing.Drawing2D.HatchStyle.WideUpwardDiagonal:
  868. case System.Drawing.Drawing2D.HatchStyle.NarrowVertical:
  869. case System.Drawing.Drawing2D.HatchStyle.NarrowHorizontal:
  870. case System.Drawing.Drawing2D.HatchStyle.DashedDownwardDiagonal:
  871. case System.Drawing.Drawing2D.HatchStyle.DashedUpwardDiagonal:
  872. case System.Drawing.Drawing2D.HatchStyle.DashedHorizontal:
  873. case System.Drawing.Drawing2D.HatchStyle.DashedVertical:
  874. case System.Drawing.Drawing2D.HatchStyle.SmallConfetti:
  875. case System.Drawing.Drawing2D.HatchStyle.LargeConfetti:
  876. case System.Drawing.Drawing2D.HatchStyle.ZigZag:
  877. case System.Drawing.Drawing2D.HatchStyle.Wave:
  878. case System.Drawing.Drawing2D.HatchStyle.DiagonalBrick:
  879. case System.Drawing.Drawing2D.HatchStyle.HorizontalBrick:
  880. case System.Drawing.Drawing2D.HatchStyle.Weave:
  881. case System.Drawing.Drawing2D.HatchStyle.Plaid:
  882. case System.Drawing.Drawing2D.HatchStyle.Divot:
  883. case System.Drawing.Drawing2D.HatchStyle.DottedGrid:
  884. case System.Drawing.Drawing2D.HatchStyle.DottedDiamond:
  885. case System.Drawing.Drawing2D.HatchStyle.Shingle:
  886. case System.Drawing.Drawing2D.HatchStyle.Sphere:
  887. case System.Drawing.Drawing2D.HatchStyle.SmallCheckerBoard:
  888. case System.Drawing.Drawing2D.HatchStyle.LargeCheckerBoard:
  889. case System.Drawing.Drawing2D.HatchStyle.OutlinedDiamond:
  890. case System.Drawing.Drawing2D.HatchStyle.SolidDiamond:
  891. break;
  892. }
  893. fills.Append("<patternFill patternType=").Append(Quoted(PatternType)).AppendLine(" >");
  894. if (!swap_color)
  895. {
  896. fills.Append("<fgColor rgb=").Append(GetRGBString(hatch.ForeColor)).AppendLine("/>");
  897. fills.Append("<bgColor rgb=").Append(GetRGBString(hatch.BackColor)).Append("/>");
  898. }
  899. else
  900. {
  901. fills.Append("<fgColor rgb=").Append(GetRGBString(hatch.BackColor)).AppendLine("/>");
  902. fills.Append("<bgColor rgb=").Append(GetRGBString(hatch.ForeColor)).AppendLine("/>");
  903. }
  904. fills.AppendLine("</patternFill>");
  905. }
  906. else
  907. {
  908. if (EStyle.FillColor == Color.Transparent)
  909. {
  910. fills.AppendLine("<patternFill patternType=\"solid\">");
  911. fills.Append("<fgColor rgb=").Append(GetRGBString(EStyle.FillColor)).AppendLine("/>");
  912. fills.AppendLine("</patternFill>");
  913. }
  914. else
  915. {
  916. fills.AppendLine("<patternFill patternType=\"solid\">");
  917. fills.Append("<fgColor rgb=").Append(GetRGBString(EStyle.FillColor)).AppendLine("/>");
  918. fills.AppendLine("</patternFill>");
  919. }
  920. }
  921. fills.AppendLine("</fill>");
  922. }
  923. fills.AppendLine("</fills>");
  924. ExportUtils.WriteLn(file, fills.ToString());
  925. }
  926. private void ExportBorders(Stream file)
  927. {
  928. StringBuilder borderResult = new StringBuilder(256);
  929. borderResult.Append("<borders count=\"").Append(styles.Count.ToString()).AppendLine("\">");
  930. for (int i = 0; i < styles.Count; i++)
  931. {
  932. Border border = styles[i].Border;
  933. borderResult.AppendLine("<border>");
  934. if ((border.Lines & BorderLines.Left) != 0)
  935. borderResult.AppendLine(
  936. "<left style=" +
  937. Styles2String(border.LeftLine.Style, border.LeftLine.Width) +
  938. "><color rgb=" +
  939. GetRGBString(border.LeftLine.Color) +
  940. " /></left>");
  941. else
  942. borderResult.AppendLine("<left />");
  943. if ((border.Lines & BorderLines.Right) != 0)
  944. borderResult.AppendLine(
  945. "<right style=" +
  946. Styles2String(border.RightLine.Style, border.RightLine.Width) +
  947. "><color rgb=" +
  948. GetRGBString(border.RightLine.Color) +
  949. " /></right>");
  950. else
  951. borderResult.AppendLine("<right />");
  952. if ((border.Lines & BorderLines.Top) != 0)
  953. borderResult.AppendLine(
  954. "<top style=" +
  955. Styles2String(border.TopLine.Style, border.TopLine.Width) +
  956. "><color rgb=" +
  957. GetRGBString(border.TopLine.Color) +
  958. " /></top>");
  959. else
  960. borderResult.AppendLine("<top />");
  961. if ((border.Lines & BorderLines.Bottom) != 0)
  962. borderResult.AppendLine(
  963. "<bottom style=" +
  964. Styles2String(border.BottomLine.Style, border.BottomLine.Width) +
  965. "><color rgb=" +
  966. GetRGBString(border.BottomLine.Color) +
  967. " /></bottom>");
  968. else
  969. borderResult.AppendLine("<bottom />");
  970. borderResult.AppendLine("<diagonal />");
  971. borderResult.AppendLine("</border>");
  972. }
  973. borderResult.AppendLine("</borders>");
  974. ExportUtils.WriteLn(file, borderResult.ToString());
  975. }
  976. private VertAlign TranslateVAligment270(HorzAlign ha)
  977. {
  978. switch (ha)
  979. {
  980. case HorzAlign.Left:
  981. return VertAlign.Bottom;
  982. case HorzAlign.Right:
  983. return VertAlign.Top;
  984. }
  985. return VertAlign.Center;
  986. }
  987. private HorzAlign TranslateHAligment270(VertAlign va)
  988. {
  989. switch (va)
  990. {
  991. case VertAlign.Top: return HorzAlign.Left;
  992. case VertAlign.Bottom: return HorzAlign.Right;
  993. }
  994. return HorzAlign.Center;
  995. }
  996. private VertAlign TranslateVAligment90(HorzAlign ha)
  997. {
  998. switch (ha)
  999. {
  1000. case HorzAlign.Left:
  1001. return VertAlign.Top;
  1002. case HorzAlign.Right:
  1003. return VertAlign.Bottom;
  1004. }
  1005. return VertAlign.Center;
  1006. }
  1007. private HorzAlign TranslateHAligment90(VertAlign va)
  1008. {
  1009. switch (va)
  1010. {
  1011. case VertAlign.Top: return HorzAlign.Right;
  1012. case VertAlign.Bottom: return HorzAlign.Left;
  1013. }
  1014. return HorzAlign.Center;
  1015. }
  1016. private VertAlign TranslateVAligment180(VertAlign va)
  1017. {
  1018. switch (va)
  1019. {
  1020. case VertAlign.Top: return VertAlign.Bottom;
  1021. case VertAlign.Bottom: return VertAlign.Top;
  1022. }
  1023. return VertAlign.Center;
  1024. }
  1025. private HorzAlign TranslateHAligment180(HorzAlign ha)
  1026. {
  1027. switch (ha)
  1028. {
  1029. case HorzAlign.Left: return HorzAlign.Right;
  1030. case HorzAlign.Right: return HorzAlign.Left;
  1031. }
  1032. return HorzAlign.Center;
  1033. }
  1034. private void ExportAlgnment(StringBuilder file, ExportIEMStyle EStyle)
  1035. {
  1036. HorzAlign halign = EStyle.HAlign;
  1037. VertAlign valign = EStyle.VAlign;
  1038. if (EStyle.Angle != 0)
  1039. {
  1040. if (EStyle.Angle == 90)
  1041. {
  1042. valign = TranslateVAligment90(EStyle.HAlign);
  1043. halign = TranslateHAligment90(EStyle.VAlign);
  1044. }
  1045. else if (EStyle.Angle == 180)
  1046. {
  1047. valign = TranslateVAligment180(EStyle.VAlign);
  1048. halign = TranslateHAligment180(EStyle.HAlign);
  1049. }
  1050. else if (EStyle.Angle == 270)
  1051. {
  1052. valign = TranslateVAligment270(EStyle.HAlign); // VertAlign.Bottom;
  1053. halign = TranslateHAligment270(EStyle.VAlign); // HorzAlign.Right;
  1054. }
  1055. else if (EStyle.Angle < 90)
  1056. {
  1057. halign = HorzAlign.Center;
  1058. valign = VertAlign.Center;
  1059. }
  1060. else if (EStyle.Angle > 270)
  1061. {
  1062. halign = HorzAlign.Center;
  1063. valign = VertAlign.Center;
  1064. }
  1065. }
  1066. const double paddingScale = 10.0;
  1067. int padding = 0;
  1068. if (halign == HorzAlign.Left)
  1069. padding = EStyle.Padding.Left;
  1070. else if (halign == HorzAlign.Right)
  1071. padding = EStyle.Padding.Right;
  1072. if (EStyle.WordWrap)
  1073. file.Append("<alignment horizontal=\"").Append(HAlign2String(halign)).
  1074. Append("\" vertical=\"").Append(VAlign2String(valign)).
  1075. Append("\" textRotation=\"").Append(ConvertRotationAngle(EStyle.Angle)).
  1076. Append("\" wrapText=\"1").Append("\" indent=\"").
  1077. Append((int)Math.Round(padding / paddingScale)).AppendLine("\" />");
  1078. else
  1079. file.Append("<alignment horizontal=\"").Append(HAlign2String(halign)).
  1080. Append("\" vertical=\"").Append(VAlign2String(valign)).
  1081. Append("\" textRotation=\"").Append(ConvertRotationAngle(EStyle.Angle)).
  1082. Append("\" wrapText=\"0").Append("\" indent=\"").
  1083. Append((int)Math.Round(padding / paddingScale)).AppendLine("\" />");
  1084. }
  1085. private void ExportCellStyles(Stream file)
  1086. {
  1087. StringBuilder styles = new StringBuilder(4096);
  1088. styles.Append("<cellXfs count=\"").Append(this.styles.Count.ToString()).AppendLine("\">");
  1089. for (int i = 0; i < this.styles.Count; i++)
  1090. {
  1091. ExportIEMStyle EStyle = this.styles[i];
  1092. if (EStyle.FillColor == Color.Transparent)
  1093. {
  1094. styles.Append("<xf numFmtId=\"").Append(GetFormatCode(EStyle)).AppendLine("\"");
  1095. styles.Append("fontId=\"").Append(i.ToString()).AppendLine("\" ");
  1096. styles.AppendLine("fillId=\"0\"");
  1097. styles.Append("borderId=\"").Append(i.ToString()).AppendLine("\" ");
  1098. styles.AppendLine("xfId=\"0\" ");
  1099. styles.AppendLine("applyAlignment=\"1\">");
  1100. ExportAlgnment(styles, EStyle);
  1101. styles.AppendLine("</xf>");
  1102. }
  1103. else
  1104. {
  1105. styles.Append("<xf numFmtId=\"").Append(GetFormatCode(EStyle)).AppendLine("\"");
  1106. styles.Append("fontId=\"").Append(i.ToString()).AppendLine("\" ");
  1107. styles.Append("fillId=\"").Append((i + 2).ToString()).Append("\" ");
  1108. styles.Append("borderId=\"").Append(i.ToString()).AppendLine("\" ");
  1109. styles.AppendLine("xfId=\"0\" ");
  1110. styles.AppendLine("applyAlignment=\"1\">");
  1111. ExportAlgnment(styles, EStyle);
  1112. styles.AppendLine("</xf>");
  1113. }
  1114. }
  1115. styles.AppendLine("</cellXfs>");
  1116. ExportUtils.Write(file, styles.ToString());
  1117. }
  1118. public void Export(Excel2007Export OoXML)
  1119. {
  1120. Stream file;
  1121. if (OoXML.UseFileStream)
  1122. {
  1123. string tempFileName = OoXML.GetFileName() + "2" + ".tmp";
  1124. file = new FileStream(tempFileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
  1125. OoXML.TempFileNames.Add(tempFileName);
  1126. }
  1127. else
  1128. file = new MemoryStream();
  1129. ExportUtils.WriteLn(file, xml_header);
  1130. ExportUtils.WriteLn(file, "<styleSheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">");
  1131. currencyToAccounting = OoXML.CurrencyToAccounting;
  1132. generalToText = OoXML.GeneralToText;
  1133. ExportFormats(file);
  1134. ExportFonts(file);
  1135. ExportFills(file);
  1136. ExportBorders(file);
  1137. // Cell styles
  1138. ExportCellStyles(file);
  1139. ExportUtils.WriteLn(file, "</styleSheet>");
  1140. file.Position = 0;
  1141. OoXML.Zip.AddStream("xl/" + FileName, file);
  1142. }
  1143. public OoXMLDocumentStyles()
  1144. {
  1145. styles = new List<ExportIEMStyle>();
  1146. formatIndex = 164;
  1147. currencyToAccounting = false;
  1148. generalToText = false;
  1149. }
  1150. internal int Add(OoXMLSheet sh)
  1151. {
  1152. ExportIEMStyle style = new ExportIEMStyle();
  1153. style.Border.Color = Color.Red;
  1154. style.Border.Lines = BorderLines.All;
  1155. //style.Font. = Color.Blue;
  1156. styles.Add(style);
  1157. styles.AddRange(sh.Matrix.Styles);
  1158. return styles.Count;
  1159. }
  1160. }
  1161. /// <summary>
  1162. /// Workbook
  1163. /// </summary>
  1164. internal class OoXMLWorkbook : OoXMLBase
  1165. {
  1166. #region Class overrides
  1167. public override string RelationType
  1168. {
  1169. get { return "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument"; }
  1170. }
  1171. public override string ContentType
  1172. {
  1173. get { return "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"; }
  1174. }
  1175. public override string FileName
  1176. {
  1177. get { return "xl/workbook.xml"; }
  1178. }
  1179. #endregion Class overrides
  1180. #region Private properties
  1181. private List<OoXMLSheet> sheetList = new List<OoXMLSheet>();
  1182. #endregion Private properties
  1183. #region Internal properties
  1184. internal OoXMLSheet[] SheetList { get { return sheetList.ToArray(); } }
  1185. #endregion Internal properties
  1186. internal void Export(Excel2007Export OoXML/*, ExportMatrix FMatrix*/)
  1187. {
  1188. Stream file;
  1189. if (OoXML.UseFileStream)
  1190. {
  1191. string tempFileName = OoXML.GetFileName() + "3" + ".tmp";
  1192. file = new FileStream(tempFileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
  1193. OoXML.TempFileNames.Add(tempFileName);
  1194. }
  1195. else
  1196. file = new MemoryStream();
  1197. StringBuilder sheets = new StringBuilder(4096);
  1198. sheets.AppendLine(xml_header);
  1199. sheets.AppendLine(
  1200. "<workbook xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" " +
  1201. "xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\">");
  1202. sheets.AppendLine("<fileVersion appName=\"xl\" lastEdited=\"4\" lowestEdited=\"4\" rupBuild=\"4505\" />");
  1203. sheets.AppendLine("<workbookPr defaultThemeVersion=\"124226\" />");
  1204. sheets.AppendLine("<bookViews>");
  1205. sheets.AppendLine("<workbookView xWindow=\"0\" yWindow=\"0\" windowWidth=\"8610\" windowHeight=\"6225\" />");
  1206. sheets.AppendLine("</bookViews>");
  1207. sheets.AppendLine("<sheets>");
  1208. int page_name_idx = 0;
  1209. foreach (OoXMLSheet sh in this.sheetList)
  1210. {
  1211. StringBuilder page_name_sb = new StringBuilder(sh.Name);
  1212. if (page_name_sb.Length == 0)
  1213. {
  1214. page_name_idx++;
  1215. page_name_sb.Append("Page_" + page_name_idx.ToString());
  1216. }
  1217. else
  1218. {
  1219. page_name_sb.Replace("&", "&amp;");
  1220. page_name_sb.Replace("<", "&lt;");
  1221. page_name_sb.Replace(">", "&gt;");
  1222. //forbidden character in name page title Excel2007
  1223. page_name_sb.Replace("?", string.Empty);
  1224. }
  1225. sheets.Append("<sheet name=").Append(Quoted(page_name_sb.ToString())).Append(" sheetId=").Append(Quoted(sh.SheetID)).
  1226. Append(" r:id=").Append(Quoted(sh.rId)).AppendLine(" />");
  1227. }
  1228. sheets.AppendLine("</sheets>");
  1229. sheets.AppendLine("<calcPr calcId=\"124519\" />");
  1230. sheets.AppendLine("</workbook>");
  1231. ExportUtils.Write(file, sheets.ToString());
  1232. file.Position = 0;
  1233. OoXML.Zip.AddStream(FileName, file);
  1234. }
  1235. internal void AddSheet(OoXMLSheet sh)
  1236. {
  1237. sheetList.Add(sh);
  1238. this.AddRelation(sheetList.Count + 20, sh);
  1239. }
  1240. }
  1241. /// <summary>
  1242. /// OoXMLSheet class
  1243. /// </summary>
  1244. internal class OoXMLSheet : OoXMLBase
  1245. {
  1246. #region Private properties
  1247. private const float MARG_DIV = 25.4F;
  1248. private ExportMatrix matrix;
  1249. private string name;
  1250. private int internal_index;
  1251. #endregion Private properties
  1252. #region Internal properties
  1253. internal ExportMatrix Matrix { get { return matrix; } }
  1254. internal string Name { get { return name; } }
  1255. internal string SheetID { get { return internal_index.ToString(); } }
  1256. internal int ID { set { internal_index = value; } }
  1257. #endregion Internal properties
  1258. #region Class overrides
  1259. public override string RelationType
  1260. {
  1261. get { return "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"; }
  1262. }
  1263. public override string ContentType
  1264. {
  1265. get { return "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"; }
  1266. }
  1267. public override string FileName
  1268. {
  1269. get { return "worksheets/sheet" + internal_index.ToString() + ".xml"; }
  1270. }
  1271. #endregion Class overrides
  1272. private string ExportCell(Excel2007Export OoXML, ExportIEMObject Obj, int x, int y, int rowNum, int FirstStyleIndex)
  1273. {
  1274. StringBuilder cell = new StringBuilder(Obj.Text.Length + 30);
  1275. if (Obj.IsNumeric || Obj.IsDateTime || Obj.IsPercent)
  1276. {
  1277. cell.Append("<c r=\"").Append(ExportUtils.GetCellReference(x, rowNum)).
  1278. Append("\" s=\"").Append((Obj.StyleIndex + FirstStyleIndex + 1).ToString()).Append("\"><v>");
  1279. if (Obj.IsDateTime)
  1280. cell.Append(Convert.ToString(Convert.ToDateTime(Obj.Value).ToOADate(),
  1281. CultureInfo.InvariantCulture.NumberFormat)).Append("</v></c>");
  1282. else if (Obj.IsPercent)
  1283. cell.Append(Convert.ToString(Obj.Value, CultureInfo.InvariantCulture.NumberFormat)).Append("</v></c>");
  1284. else
  1285. cell.Append(Convert.ToString(Obj.Value, CultureInfo.InvariantCulture.NumberFormat)).Append("</v></c>");
  1286. }
  1287. else
  1288. {
  1289. String s = ExportUtils.Excel2007String(Obj.Text, Obj.TextRenderType);
  1290. if (!String.IsNullOrEmpty(Obj.URL))
  1291. OoXML.URLTable.Add(x, y, Obj.URL);
  1292. cell.Append("<c r=\"").Append(ExportUtils.GetCellReference(x, rowNum)).Append("\" s=\"").
  1293. Append((Obj.StyleIndex + FirstStyleIndex + 1).ToString());
  1294. if (s != string.Empty)
  1295. {
  1296. int idx = OoXML.StringTable.Add(s, Obj);
  1297. cell.Append("\" t=\"s\"><v>").Append(idx.ToString()).Append("</v></c>");
  1298. }
  1299. else
  1300. cell.Append("\" t=\"s\"></c>");
  1301. }
  1302. return cell.ToString();
  1303. }
  1304. internal string GetRangeReference(int i)
  1305. {
  1306. int fx, fy, dx, dy;
  1307. matrix.ObjectPos(i, out fx, out fy, out dx, out dy);
  1308. return "\"" +
  1309. ExportUtils.GetCellReference(fx, fy + 1) + ":" +
  1310. ExportUtils.GetCellReference(fx + dx - 1, fy + dy) +
  1311. "\"";
  1312. }
  1313. internal string GetRangeReference(int i, int rowNum)
  1314. {
  1315. int fx, fy, dx, dy;
  1316. matrix.ObjectPos(i, out fx, out fy, out dx, out dy);
  1317. return "\"" +
  1318. ExportUtils.GetCellReference(fx, rowNum - dy + 1) + ":" +
  1319. ExportUtils.GetCellReference(fx + dx - 1, rowNum) +
  1320. "\"";
  1321. }
  1322. internal void Export(Excel2007Export OoXML, int FirstStyleIndex, bool PageBreaks)
  1323. {
  1324. int merged_cells_count = 0;
  1325. Stream file;
  1326. if (OoXML.UseFileStream)
  1327. {
  1328. string tempFileName = OoXML.GetFileName() + "4" + SheetID + ".tmp";
  1329. file = new FileStream(tempFileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
  1330. OoXML.TempFileNames.Add(tempFileName);
  1331. }
  1332. else
  1333. file = new MemoryStream();
  1334. StringBuilder builder = new StringBuilder(8192);
  1335. StringBuilder merged = new StringBuilder(256);
  1336. builder.Append(xml_header);
  1337. builder.AppendLine("<worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" " +
  1338. "xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\">");
  1339. builder.AppendLine(OoXML.GetPageSetUpPr());
  1340. builder.AppendLine(OoXML.GetMatrixDimension(matrix));
  1341. builder.Append("<sheetViews>");
  1342. builder.Append($"<sheetView showGridLines=\"{(OoXML.ShowGridlines ? "1" : "0")}\" tabSelected=\"{(!OoXML.UngroupSheets).ToString().ToLower()}\" workbookViewId=\"0\" >");
  1343. if (OoXML.PinX > 0 || OoXML.PinY > 0)
  1344. {
  1345. string pinnedCells = "";
  1346. if (OoXML.PinX > 0)
  1347. {
  1348. pinnedCells += $" xSplit={Quoted(OoXML.PinX)}";
  1349. }
  1350. if (OoXML.PinY > 0)
  1351. {
  1352. pinnedCells += $" ySplit={Quoted(OoXML.PinY)}";
  1353. }
  1354. pinnedCells += $" topLeftCell={Quoted(ExportUtils.IndexToName(OoXML.PinX) + (OoXML.PinY + 1))}";
  1355. pinnedCells += " activePane=\"" + (OoXML.PinY > 0 ? "bottom" : "top") + (OoXML.PinX > 0 ? "Right" : "Left") + "\"";
  1356. builder.Append($"<pane {pinnedCells} state=\"frozen\"/>");
  1357. }
  1358. builder.AppendLine("</sheetView>");
  1359. builder.AppendLine("</sheetViews>");
  1360. builder.AppendLine("<sheetFormatPr defaultRowHeight=\"15\" />");
  1361. // sheet columns
  1362. if (matrix.ObjectsCount > 0)
  1363. {
  1364. builder.Append("<cols>");
  1365. for (int x = 1; x < matrix.Width; x++)
  1366. {
  1367. builder.Append("<col min=\"").Append(x.ToString()).Append("\" max=\"").Append(x).Append("\" width=\"").
  1368. Append(ExportUtils.FloatToString((matrix.XPosById(x) - matrix.XPosById(x - 1)) / OoXML.XDivider)).
  1369. Append("\" customWidth=\"1\" />");
  1370. }
  1371. builder.AppendLine("</cols>");
  1372. }
  1373. //if empty sheet
  1374. if (matrix.Height == 0 && matrix.Width == 0)
  1375. {
  1376. builder.AppendLine("<sheetData/>");
  1377. }
  1378. else
  1379. {
  1380. builder.AppendLine("<sheetData>");
  1381. ExportUtils.Write(file, builder.ToString());
  1382. builder.Length = 0;
  1383. for (int y = 0, rowNum = 1; y < matrix.Height - 1; y++)
  1384. {
  1385. StringBuilder line = new StringBuilder(128);
  1386. float ht = 0;
  1387. if (!matrix.IsYPosByIdNull(y) && !matrix.IsYPosByIdNull(y + 1))
  1388. {
  1389. ht = (matrix.YPosById(y + 1) - matrix.YPosById(y)) / OoXML.YDivider;
  1390. }
  1391. else
  1392. {
  1393. ht = 14.5f; // Set default row height in Excel with font size equal 11.
  1394. }
  1395. int nonEmptyCells = 0;
  1396. bool firstCellInRow = true;
  1397. //Open the row without the closing tag ">" for further work with its attributes
  1398. line.Append("<row");
  1399. for (int x = 0; x < matrix.Width; x++)
  1400. {
  1401. int i = matrix.Cell(x, y);
  1402. if (i != -1)
  1403. {
  1404. int fx, fy, dx, dy;
  1405. matrix.ObjectPos(i, out fx, out fy, out dx, out dy);
  1406. ExportIEMObject Obj = matrix.ObjectById(i);
  1407. if (firstCellInRow)
  1408. {
  1409. if (Obj.AutoSize)
  1410. {
  1411. line.Append(" r=\"").Append((rowNum).ToString()).Append("\">");
  1412. }
  1413. else
  1414. {
  1415. line.Append(" r=\"").Append((rowNum).ToString()).Append("\" ht=\"").
  1416. Append(ExportUtils.FloatToString(ht)).Append("\" customHeight=\"1\">");
  1417. }
  1418. firstCellInRow = false;
  1419. }
  1420. if (Obj.Counter < dy)
  1421. {
  1422. if ((dy == 1) || Obj.Counter == 0)
  1423. {
  1424. line.Append(ExportCell(OoXML, Obj, x, y, rowNum, FirstStyleIndex));
  1425. }
  1426. else
  1427. {
  1428. line.Append("<c r=\"").Append(ExportUtils.GetCellReference(x, rowNum)).Append("\" s=").
  1429. Append(Quoted(Obj.StyleIndex + FirstStyleIndex + 1)).Append(" />");
  1430. }
  1431. for (int j = 1; j < dx; j++)
  1432. {
  1433. x++;
  1434. line.Append("<c r=\"").Append(ExportUtils.GetCellReference(x, rowNum)).Append("\" s=").
  1435. Append(Quoted(Obj.StyleIndex + FirstStyleIndex + 1)).Append(" />");
  1436. }
  1437. if (!String.IsNullOrEmpty(Obj.Text) ||
  1438. (Obj.Style != null &&
  1439. (Obj.Style.Border.Lines != BorderLines.None ||
  1440. Obj.Style.FillColor != Color.White ||
  1441. Obj.Style.FillColor.A != 0)
  1442. )
  1443. )
  1444. nonEmptyCells++;
  1445. if (!Obj.IsText)
  1446. {
  1447. if (Obj.Width > 0 && Obj.Counter == 0)
  1448. {
  1449. OoXML.Drawing.Append(Obj, fx, fy, dx, dy, rowNum, matrix.Width);
  1450. nonEmptyCells++;
  1451. }
  1452. }
  1453. Obj.Counter++;
  1454. if ((Obj.Counter == dy) && (dx > 1 || dy > 1))
  1455. {
  1456. merged_cells_count++;
  1457. if (!OoXML.Seamless)
  1458. {
  1459. merged.Append("<mergeCell ref=").Append(GetRangeReference(i)).Append("/>");
  1460. }
  1461. else
  1462. {
  1463. merged.Append("<mergeCell ref=").Append(GetRangeReference(i, rowNum)).Append("/>");
  1464. }
  1465. }
  1466. }
  1467. }
  1468. }
  1469. if (firstCellInRow)
  1470. {
  1471. line.Append(" r=\"").Append((rowNum).ToString()).Append("\" ht=\"").
  1472. Append(ExportUtils.FloatToString(ht)).Append("\" customHeight=\"1\">");
  1473. }
  1474. line.AppendLine("</row>");
  1475. if (!(OoXML.Seamless && nonEmptyCells == 0))
  1476. {
  1477. ExportUtils.Write(file, line.ToString());
  1478. rowNum++;
  1479. }
  1480. if (y % 1000 == 0)
  1481. Config.DoEvent();
  1482. }
  1483. builder.AppendLine("</sheetData>");
  1484. }
  1485. // merge cells
  1486. if (merged_cells_count != 0)
  1487. {
  1488. builder.Append("<mergeCells count=\"").Append(merged_cells_count).Append("\">");
  1489. builder.Append(merged);
  1490. builder.Append("</mergeCells>");
  1491. }
  1492. // url list
  1493. if (OoXML.URLTable.Count != 0)
  1494. {
  1495. builder.Append(OoXML.URLTable.ExportBody(OoXML));
  1496. }
  1497. builder.Append("<pageMargins left=\"").Append(ExportUtils.FloatToString(matrix.PageLMargin(0) / MARG_DIV)).
  1498. Append("\" right=\"").Append(ExportUtils.FloatToString(matrix.PageRMargin(0) / MARG_DIV)).
  1499. Append("\" top=\"").Append(ExportUtils.FloatToString(matrix.PageTMargin(0) / MARG_DIV)).
  1500. Append("\" bottom=\"").Append(ExportUtils.FloatToString(matrix.PageBMargin(0) / MARG_DIV)).
  1501. AppendLine("\" header=\"0\" footer=\"0\" />");
  1502. /* page setup */
  1503. builder.Append("<pageSetup paperSize=").
  1504. Append(Quoted(matrix.RawPaperSize(0)));
  1505. if (OoXML.UsePresetScale)
  1506. builder.Append(GetPrintFitProp(OoXML));
  1507. else
  1508. builder.Append(" scale=").Append(Quoted(OoXML.PrintScale));
  1509. builder.Append(" orientation=").Append(Quoted(matrix.Landscape(0) ? "landscape" : "portrait")).Append(" horizontalDpi=").Append(Quoted(300)).
  1510. Append(" verticalDpi=").Append(Quoted(300)).AppendLine(" />");
  1511. /* row breaks */
  1512. if (PageBreaks)
  1513. {
  1514. int pages = 0;
  1515. List<string> pageBreaks = new List<string>();
  1516. for (int i = 0; i <= matrix.Height - 1 && pages <= 1024; i++)
  1517. {
  1518. if (!matrix.IsYPosByIdNull(i) && (matrix.YPosById(i) >= matrix.PageBreak(pages)))
  1519. {
  1520. pageBreaks.Add(string.Format("<brk id=\"{0}\" max=\"16383\" man=\"1\"/>", i));
  1521. pages++;
  1522. }
  1523. }
  1524. if (pages > 0)
  1525. {
  1526. builder.AppendFormat("<rowBreaks count=\"{0}\" manualBreakCount=\"{1}\">", pages - 1, pages - 1);
  1527. builder.AppendLine();
  1528. for (int i = 0; i < pageBreaks.Count; i++)
  1529. {
  1530. builder.AppendLine(pageBreaks[i]);
  1531. }
  1532. builder.AppendLine("</rowBreaks>");
  1533. }
  1534. }
  1535. /* drawing */
  1536. if (OoXML.Drawing.PicCount != 0)
  1537. {
  1538. builder.Append("<drawing r:id=\"rId1\" />");
  1539. }
  1540. /* Relations present in documents */
  1541. if (OoXML.URLTable.Count != 0 || OoXML.Drawing.PicCount != 0)
  1542. {
  1543. Stream rel_file;
  1544. if (OoXML.UseFileStream)
  1545. {
  1546. string tempFileName = OoXML.GetFileName() + "5" + SheetID + ".tmp";
  1547. rel_file = new FileStream(tempFileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
  1548. OoXML.TempFileNames.Add(tempFileName);
  1549. }
  1550. else
  1551. rel_file = new MemoryStream();
  1552. StreamWriter rel_out = new StreamWriter(rel_file);
  1553. rel_out.WriteLine(xml_header);
  1554. rel_out.WriteLine("<Relationships xmlns=\"http://schemas.openxmlformats.org/package/2006/relationships\">");
  1555. if (OoXML.Drawing.PicCount != 0)
  1556. {
  1557. rel_out.WriteLine(
  1558. "<Relationship Id=\"rId1\" " +
  1559. "Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing\" " +
  1560. "Target=\"../drawings/drawing" + SheetID + ".xml\"/>");
  1561. OoXML.SheetIDsWithPics.Add(SheetID);
  1562. }
  1563. if (OoXML.URLTable.Count != 0)
  1564. {
  1565. rel_out.WriteLine(OoXML.URLTable.ExportRelationship());
  1566. }
  1567. rel_out.WriteLine("</Relationships>");
  1568. rel_out.Flush();
  1569. rel_file.Position = 0;
  1570. OoXML.Zip.AddStream("xl/worksheets/_rels/sheet" + SheetID + ".xml.rels", rel_file);
  1571. }
  1572. builder.Append("</worksheet>");
  1573. ExportUtils.Write(file, builder.ToString());
  1574. file.Position = 0;
  1575. OoXML.Zip.AddStream("xl/worksheets/sheet" + SheetID + ".xml", file);
  1576. }
  1577. private string GetPrintFitProp(Excel2007Export OoXML)
  1578. {
  1579. switch (OoXML.PrintFit)
  1580. {
  1581. case Excel2007Export.PrintFitMode.FitAllColumsOnOnePage:
  1582. return " fitToHeight=\"0\"";
  1583. case Excel2007Export.PrintFitMode.FitAllRowsOnOnePage:
  1584. return " fitToWidth=\"0\"";
  1585. default:
  1586. return String.Empty;
  1587. }
  1588. }
  1589. //
  1590. public OoXMLSheet(ExportMatrix matrix, string Name, int Index)
  1591. {
  1592. this.matrix = matrix;
  1593. name = Name;
  1594. internal_index = Index;
  1595. }
  1596. }
  1597. /// <summary>
  1598. /// Excel 2007 export class
  1599. /// </summary>
  1600. public partial class Excel2007Export : OOExportBase
  1601. {
  1602. #region Enums
  1603. /// <summary>
  1604. /// Defines Print Scaling Mode.
  1605. /// </summary>
  1606. public enum PrintFitMode
  1607. {
  1608. /// <summary>
  1609. /// Print sheets at thier actual size.
  1610. /// </summary>
  1611. NoScaling,
  1612. /// <summary>
  1613. /// Shrink the printout so that it fits on one page.
  1614. /// </summary>
  1615. FitSheetOnOnePage,
  1616. /// <summary>
  1617. /// Shrink the printout so that it is one page wide.
  1618. /// </summary>
  1619. FitAllColumsOnOnePage,
  1620. /// <summary>
  1621. /// Shrink the printout so that it is one page high.
  1622. /// </summary>
  1623. FitAllRowsOnOnePage
  1624. }
  1625. public enum ePictureProperties
  1626. {
  1627. /// <summary>
  1628. /// Move and modify the picture along with the cell.
  1629. /// </summary>
  1630. MoveAndModify,
  1631. /// <summary>
  1632. /// Move but not resize the picture along with the cell.
  1633. /// </summary>
  1634. OnlyMove,
  1635. /// <summary>
  1636. /// Do not move or resize the picture along with the cell.
  1637. /// </summary>
  1638. Unchangeable
  1639. }
  1640. #endregion Enums
  1641. #region Constants
  1642. private const float oxmlXDivider = 6f;
  1643. private const float oxmlYDivider = 1.333f;
  1644. private float fontScale = 1f;
  1645. #endregion Constants
  1646. #region Private fields
  1647. private bool useFileStream;
  1648. private bool wysiwyg;
  1649. private bool pageBreaks;
  1650. private bool dataOnly;
  1651. private bool seamless;
  1652. private bool showGridlines;
  1653. private bool groupOnSeparateSheet;
  1654. private string groupName;
  1655. private bool usePresetScale;
  1656. private bool useLocaleFormating;
  1657. private bool currencyToAccounting;
  1658. private bool generalToText;
  1659. private int printScale;
  1660. private int pinX;
  1661. private int pinY;
  1662. private PrintFitMode printFitMode;
  1663. private ePictureProperties pictureProperties;
  1664. private OoXMLSharedStringTable stringTable;
  1665. private OoXMLSharedURLTable uRLTable;
  1666. internal OoXMLDrawing drawing;
  1667. private OoXMLCoreDocumentProperties coreDocProp;
  1668. private OoXMLApplicationProperties appProp;
  1669. private OoXMLDocumentStyles docStyles;
  1670. private OoXMLWorkbook workBook;
  1671. private Dictionary<string, OoXMLSheet> sheets;
  1672. private List<string> tempFileNames;
  1673. private ExportMatrix matrix;
  1674. private bool printOptimized = false;
  1675. private bool splitPages;
  1676. private ReportPage currentPage;
  1677. private bool firstPage = true;
  1678. private float bandHeight = 0;
  1679. private bool firstGroupHeaderBand = true;
  1680. private bool ungroupSheets = false;
  1681. private List<string> sheetIDsWithPics;
  1682. private bool needCreateNewList = false;
  1683. #endregion Private fields
  1684. #region Properties
  1685. internal OoXMLDrawing Drawing { get { return drawing; } }
  1686. internal OoXMLSharedStringTable StringTable { get { return stringTable; } }
  1687. internal OoXMLSharedURLTable URLTable { get { return uRLTable; } }
  1688. internal float YDivider { get { return oxmlYDivider; } }
  1689. internal float XDivider { get { return oxmlXDivider; } }
  1690. internal List<string> SheetIDsWithPics
  1691. {
  1692. get { return sheetIDsWithPics; }
  1693. set { sheetIDsWithPics = value; }
  1694. }
  1695. /// <summary>
  1696. /// Gets or sets the Print Fit Mode.
  1697. /// </summary>
  1698. public PrintFitMode PrintFit
  1699. {
  1700. get { return printFitMode; }
  1701. set { printFitMode = value; }
  1702. }
  1703. /// <summary>
  1704. /// Gets or sets the print scaling (value from 10 to 400).
  1705. /// </summary>
  1706. public int PrintScale
  1707. {
  1708. get { return printScale; }
  1709. set { printScale = value; }
  1710. }
  1711. /// <summary>
  1712. /// Gets or sets the pinned cells in X ordinate.
  1713. /// </summary>
  1714. public int PinX
  1715. {
  1716. get { return pinX; }
  1717. set { pinX = value; }
  1718. }
  1719. /// <summary>
  1720. /// Gets or sets the pinned cells in Y ordinate.
  1721. /// </summary>
  1722. public int PinY
  1723. {
  1724. get { return pinY; }
  1725. set { pinY = value; }
  1726. }
  1727. /// <summary>
  1728. /// Sets or gets the flag using the preset print scale.
  1729. /// </summary>
  1730. public bool UsePresetScale
  1731. {
  1732. get { return usePresetScale; }
  1733. set { usePresetScale = value; }
  1734. }
  1735. /// <summary>
  1736. /// Gets or sets a value that determines whether the using locale data formatting.
  1737. /// </summary>
  1738. public bool UseLocaleFormating
  1739. {
  1740. get { return useLocaleFormating; }
  1741. set { useLocaleFormating = value; }
  1742. }
  1743. /// <summary>
  1744. /// Enable or disable show gridlines.
  1745. /// </summary>
  1746. public bool ShowGridlines
  1747. {
  1748. get { return showGridlines; }
  1749. set { showGridlines = value; }
  1750. }
  1751. /// <summary>
  1752. /// Gets or sets the font scale value.
  1753. /// <remarks> Default value is <b>1</b></remarks>
  1754. /// </summary>
  1755. public float FontScale
  1756. {
  1757. get { return fontScale; }
  1758. set { fontScale = value != 0 ? value : 0.01f; }
  1759. }
  1760. /// <summary>
  1761. /// Enable or disable a resolution optimization.
  1762. /// </summary>
  1763. public bool PrintOptimized
  1764. {
  1765. get { return printOptimized; }
  1766. set { printOptimized = value; }
  1767. }
  1768. /// <summary>
  1769. /// Each report page is placed on a new Excel page.
  1770. /// </summary>
  1771. public bool SplitPages
  1772. {
  1773. get { return splitPages; }
  1774. set { splitPages = value; }
  1775. }
  1776. /// <summary>
  1777. /// Each group is placed on a new Excel page.
  1778. /// </summary>
  1779. public bool GroupOnSeparateSheet
  1780. {
  1781. get { return groupOnSeparateSheet; }
  1782. set { groupOnSeparateSheet = value; }
  1783. }
  1784. /// <summary>
  1785. /// Name of the selected group.
  1786. /// </summary>
  1787. public string GroupName
  1788. {
  1789. get { return groupName; }
  1790. set { groupName = value; }
  1791. }
  1792. /// <summary>
  1793. /// Gets or sets a value that determines whether the wysiwyg mode should be used
  1794. /// for better results.
  1795. /// </summary>
  1796. /// <remarks>
  1797. /// Default value is <b>true</b>. In wysiwyg mode, the resulting Excel file will look
  1798. /// as close as possible to the prepared report. On the other side, it may have a lot
  1799. /// of small rows/columns, which will make it less editable. If you set this property
  1800. /// to <b>false</b>, the number of rows/columns in the resulting file will be decreased.
  1801. /// You will get less wysiwyg, but more editable file.
  1802. /// </remarks>
  1803. public bool Wysiwyg
  1804. {
  1805. get { return wysiwyg; }
  1806. set { wysiwyg = value; }
  1807. }
  1808. /// <summary>
  1809. /// Gets or sets a value indicating that FileStream should be used instead of MomoryStream during export.
  1810. /// </summary>
  1811. /// <remarks>
  1812. /// Enabling this option will reduce memory consumption, but increase the time of export.
  1813. /// </remarks>
  1814. public bool UseFileStream
  1815. {
  1816. get { return useFileStream; }
  1817. set { useFileStream = value; }
  1818. }
  1819. /// <summary>
  1820. /// Gets or sets a value indicating that currency should be converted to accounting format.
  1821. /// </summary>
  1822. public bool CurrencyToAccounting
  1823. {
  1824. get { return currencyToAccounting; }
  1825. set { currencyToAccounting = value; }
  1826. }
  1827. /// <summary>
  1828. /// Gets or sets a value indicating that general should be converted to text format.
  1829. /// </summary>
  1830. public bool GeneralToText
  1831. {
  1832. get { return generalToText; }
  1833. set { generalToText = value; }
  1834. }
  1835. /// <summary>
  1836. /// For delete temp files
  1837. /// </summary>
  1838. internal List<string> TempFileNames
  1839. {
  1840. get { return tempFileNames; }
  1841. set { tempFileNames = value; }
  1842. }
  1843. /// <summary>
  1844. /// Gets or sets a value that determines whether to insert page breaks in the output file or not.
  1845. /// </summary>
  1846. public bool PageBreaks
  1847. {
  1848. get { return pageBreaks; }
  1849. set { pageBreaks = value; }
  1850. }
  1851. /// <summary>
  1852. /// Gets or sets a value that determines whether to export the databand rows only.
  1853. /// </summary>
  1854. public bool DataOnly
  1855. {
  1856. get { return dataOnly; }
  1857. set { dataOnly = value; }
  1858. }
  1859. /// <summary>
  1860. /// Enable or disable export of page footers and next page headers without table breaks.
  1861. /// </summary>
  1862. public bool Seamless
  1863. {
  1864. get { return seamless; }
  1865. set { seamless = value; }
  1866. }
  1867. /// <summary>
  1868. /// Gets or sets a value that determines whether to export the databand rows only.
  1869. /// </summary>
  1870. public ePictureProperties PictureProperties
  1871. {
  1872. get { return pictureProperties; }
  1873. set { pictureProperties = value; }
  1874. }
  1875. /// <summary>
  1876. /// Grouping or ungrouping sheets. Grouping is enabled by default.
  1877. /// </summary>
  1878. public bool UngroupSheets
  1879. {
  1880. get { return ungroupSheets; }
  1881. set { ungroupSheets = value; }
  1882. }
  1883. #endregion Properties
  1884. #region Private Methods
  1885. private void CreateThemes()
  1886. {
  1887. // get a reference to the current assembly
  1888. Assembly a = Assembly.GetExecutingAssembly();
  1889. // get a list of resource names from the manifest
  1890. //string[] resNames = a.GetManifestResourceNames();
  1891. using (Stream outStream = a.GetManifestResourceStream("FastReport.Resources.OoXML.theme1.xml"))
  1892. {
  1893. Stream fs;
  1894. if (UseFileStream)
  1895. {
  1896. string tempFileName = GetFileName() + "6" + ".tmp";
  1897. fs = new FileStream(tempFileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
  1898. TempFileNames.Add(tempFileName);
  1899. }
  1900. else
  1901. fs = new MemoryStream();
  1902. const int BUFFER_SIZE = 4096;
  1903. outStream.CopyTo(fs, BUFFER_SIZE);
  1904. fs.Position = 0;
  1905. Zip.AddStream("xl/theme/theme1.xml", fs);
  1906. }
  1907. }
  1908. private void CreateContentTypes()
  1909. {
  1910. Stream file;
  1911. if (UseFileStream)
  1912. {
  1913. string tempFileName = GetFileName() + "7" + ".tmp";
  1914. file = new FileStream(tempFileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
  1915. TempFileNames.Add(tempFileName);
  1916. }
  1917. else
  1918. file = new MemoryStream();
  1919. ExportUtils.WriteLn(file, xml_header);
  1920. ExportUtils.Write(file, "<Types xmlns=\"http://schemas.openxmlformats.org/package/2006/content-types\">");
  1921. CreateThemes();
  1922. ExportUtils.Write(file,
  1923. "<Override PartName=\"/xl/theme/theme1.xml\" " +
  1924. "ContentType=\"application/vnd.openxmlformats-officedocument.theme+xml\" />");
  1925. ExportUtils.Write(file, "<Override PartName=" + QuotedRoot("xl/" + docStyles.FileName) +
  1926. " ContentType=" + Quoted(docStyles.ContentType) + "/>");
  1927. ExportUtils.Write(file, "<Default Extension=\"rels\" " +
  1928. "ContentType=\"application/vnd.openxmlformats-package.relationships+xml\" />");
  1929. ExportUtils.Write(file, "<Default Extension=\"xml\" ContentType=\"application/xml\" />");
  1930. ExportUtils.Write(file, "<Default Extension=\"png\" ContentType=\"image/png\"/>");
  1931. ExportUtils.Write(file, "<Override PartName=" + QuotedRoot(workBook.FileName) +
  1932. " ContentType=" + Quoted(workBook.ContentType) + "/>");
  1933. ExportUtils.Write(file, "<Override PartName=" + QuotedRoot(appProp.FileName) +
  1934. " ContentType=" + Quoted(appProp.ContentType) + "/>");
  1935. CreateWorkbookRelations();
  1936. foreach (OoXMLSheet sh in workBook.SheetList)
  1937. {
  1938. ExportUtils.Write(file, "<Override PartName=" + QuotedRoot("xl/" + sh.FileName) +
  1939. " ContentType=" + Quoted(sh.ContentType) + " />");
  1940. //===johnny appended 2012/6/17
  1941. if (SheetIDsWithPics.Contains(sh.SheetID))
  1942. {
  1943. ExportUtils.Write(file, "<Override PartName=" + QuotedRoot("xl/drawings/drawing" + sh.SheetID + ".xml") +
  1944. " ContentType=" + Quoted(drawing.ContentType) + "/>");
  1945. }
  1946. }
  1947. ExportUtils.Write(file, "<Override PartName=" + QuotedRoot("xl/" + stringTable.FileName) +
  1948. " ContentType=" + Quoted(stringTable.ContentType) + "/>");
  1949. ExportUtils.Write(file, "<Override PartName=" + QuotedRoot(coreDocProp.FileName) + " ContentType=" +
  1950. Quoted(coreDocProp.ContentType) + "/>");
  1951. ExportUtils.Write(file, "</Types>");
  1952. file.Position = 0;
  1953. Zip.AddStream("[Content_Types].xml", file);
  1954. }
  1955. private void CreateRelations()
  1956. {
  1957. MemoryStream file = new MemoryStream();
  1958. ExportUtils.WriteLn(file, xml_header);
  1959. ExportUtils.WriteLn(file,
  1960. "<Relationships xmlns=\"http://schemas.openxmlformats.org/package/2006/relationships\">");
  1961. ExportUtils.WriteLn(file, "<Relationship Id=\"rId3\" Type=" + Quoted(appProp.RelationType) +
  1962. " Target=" + Quoted(appProp.FileName) + " />");
  1963. ExportUtils.WriteLn(file, "<Relationship Id=\"rId2\" Type=" + Quoted(coreDocProp.RelationType) +
  1964. " Target=" + Quoted(coreDocProp.FileName) + " />");
  1965. ExportUtils.WriteLn(file, "<Relationship Id=\"rId1\" Type=" + Quoted(workBook.RelationType) +
  1966. " Target=" + Quoted(workBook.FileName) + " />");
  1967. ExportUtils.WriteLn(file, "</Relationships>");
  1968. file.Position = 0;
  1969. Zip.AddStream("_rels/.rels", file);
  1970. }
  1971. private void CreateWorkbookRelations()
  1972. {
  1973. MemoryStream file = new MemoryStream();
  1974. ExportUtils.WriteLn(file, xml_header);
  1975. ExportUtils.WriteLn(file,
  1976. "<Relationships xmlns=\"http://schemas.openxmlformats.org/package/2006/relationships\">");
  1977. ExportUtils.WriteLn(file, "<Relationship Id=\"rId3\" Type=" + Quoted(docStyles.RelationType) +
  1978. " Target=" + Quoted(docStyles.FileName) + " />");
  1979. ExportUtils.WriteLn(file, "<Relationship Id=\"rId2\" Type=" +
  1980. Quoted("http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme") +
  1981. " Target=" + Quoted("theme/theme1.xml") + " />");
  1982. foreach (OoXMLSheet sh in workBook.SheetList)
  1983. {
  1984. ExportUtils.WriteLn(file, "<Relationship Id=" + Quoted(sh.rId) + " Type=" + Quoted(sh.RelationType) +
  1985. " Target=" + Quoted(sh.FileName) + " />");
  1986. }
  1987. ExportUtils.WriteLn(file, "<Relationship Id=\"rId6\" Type=" + Quoted(stringTable.RelationType) +
  1988. " Target=" + Quoted(stringTable.FileName) + " />");
  1989. ExportUtils.WriteLn(file, "</Relationships>");
  1990. file.Position = 0;
  1991. Zip.AddStream("xl/_rels/workbook.xml.rels", file);
  1992. }
  1993. internal string GetMatrixDimension(ExportMatrix Matrix)
  1994. {
  1995. if (Matrix.Width == 0 && Matrix.Height == 0)
  1996. return "<dimension ref=\"A1\"/>";
  1997. else
  1998. return "<dimension ref=\"A1:" + ExportUtils.GetCellReference(Matrix.Width - 2, Matrix.Height) + "\"/>";
  1999. }
  2000. internal string GetPageSetUpPr()
  2001. {
  2002. if (PrintFit != PrintFitMode.NoScaling && usePresetScale)
  2003. return "<sheetPr><pageSetUpPr fitToPage=\"1\"/></sheetPr>";
  2004. else
  2005. return String.Empty;
  2006. }
  2007. private void ExportOOXML(Stream Stream)
  2008. {
  2009. CreateRelations();
  2010. int current_style_set = 0;
  2011. foreach (OoXMLSheet sh in workBook.SheetList)
  2012. {
  2013. sh.Matrix.Prepare();
  2014. Drawing.Start();
  2015. int styles_count = 0;
  2016. if (FontScale != 1)
  2017. for (int i = 0; i < sh.Matrix.Styles.Count; i++)
  2018. {
  2019. Font font = sh.Matrix.Styles[i].Font;
  2020. float scaledSize = (float)Math.Round(font.Size * FontScale);
  2021. float newSize = scaledSize == 0 ? 1 : scaledSize;
  2022. sh.Matrix.Styles[i].Font = new Font(font.FontFamily, newSize, font.Style);
  2023. }
  2024. styles_count = docStyles.Add(sh);
  2025. sh.Export(this, current_style_set, pageBreaks);
  2026. current_style_set = styles_count;
  2027. Drawing.Stop(sh.SheetID);
  2028. }
  2029. docStyles.Export(this);
  2030. appProp.Export(this);
  2031. stringTable.Export(this);
  2032. coreDocProp.Export(this);
  2033. workBook.Export(this);
  2034. CreateContentTypes();
  2035. }
  2036. private void NewMatrix()
  2037. {
  2038. matrix = new ExportMatrix();
  2039. if (wysiwyg)
  2040. matrix.Inaccuracy = 0.05f;
  2041. else
  2042. matrix.Inaccuracy = 5;
  2043. matrix.ShowProgress = ShowProgress;
  2044. matrix.PlainRich = true;
  2045. matrix.AreaFill = false;
  2046. matrix.CropAreaFill = true;
  2047. matrix.Report = Report;
  2048. matrix.Images = true;
  2049. matrix.PrintOptimized = printOptimized;
  2050. if (printOptimized)
  2051. matrix.ImageResolution = 300;
  2052. else
  2053. matrix.ImageResolution = 150;
  2054. matrix.WrapText = false;
  2055. matrix.FullTrust = false;
  2056. matrix.DataOnly = DataOnly;
  2057. matrix.MaxCellHeight = 409 * oxmlYDivider;
  2058. matrix.Seamless = seamless;
  2059. }
  2060. #endregion Private Methods
  2061. #region Protected Methods
  2062. /// <inheritdoc/>
  2063. protected override void Start()
  2064. {
  2065. base.Start();
  2066. Zip = new ZipArchive();
  2067. stringTable = new OoXMLSharedStringTable();
  2068. uRLTable = new OoXMLSharedURLTable();
  2069. drawing = new OoXMLDrawing(this);
  2070. coreDocProp = new OoXMLCoreDocumentProperties();
  2071. appProp = new OoXMLApplicationProperties();
  2072. docStyles = new OoXMLDocumentStyles();
  2073. docStyles.UseLocaleFormating = UseLocaleFormating;
  2074. workBook = new OoXMLWorkbook();
  2075. sheets = new Dictionary<string, OoXMLSheet>();
  2076. }
  2077. /// <inheritdoc/>
  2078. protected override void ExportPageBegin(ReportPage page)
  2079. {
  2080. if ((groupOnSeparateSheet))
  2081. {
  2082. base.ExportPageBegin(page);
  2083. if (firstPage)
  2084. NewMatrix();
  2085. if (currentPage != null)
  2086. matrix.AddPageEnd(currentPage);
  2087. this.currentPage = page;
  2088. matrix.AddPageBegin(page);
  2089. firstPage = false;
  2090. }
  2091. else
  2092. {
  2093. base.ExportPageBegin(page);
  2094. // If after exporting the next page,
  2095. // the number of rows in the matrix (matrix.Height) is greater than 1 000 000,
  2096. // a new sheet is created
  2097. if (matrix != null && matrix.Height > 1000000)
  2098. needCreateNewList = true;
  2099. else
  2100. needCreateNewList = false;
  2101. if (sheets.ContainsKey(page.Name) && !SplitPages && !needCreateNewList)
  2102. {
  2103. matrix = sheets.LastOrDefault().Value.Matrix;
  2104. }
  2105. else
  2106. {
  2107. NewMatrix();
  2108. }
  2109. matrix.AddPageBegin(page);
  2110. }
  2111. }
  2112. /// <inheritdoc/>
  2113. protected override void ExportBand(BandBase band)
  2114. {
  2115. // added a bool variable and a condition that determines by which GroupHeaderBand to divide the report into sheets
  2116. // firstGroupHeaderBand is true by default and changes its value for the following GroupHeaderBand
  2117. // so that new sheets are created for them
  2118. // GroupName contains the name of the group:
  2119. // if there is one group in the report or the value "All groups" is selected, then the variable will be null,
  2120. // otherwise - the name of the group by which the report is divided into sheets
  2121. if (band.ClassName is "GroupHeaderBand" && groupOnSeparateSheet && !firstGroupHeaderBand && (GroupName is null || GroupName == band.Name))
  2122. {
  2123. OoXMLSheet sh = new OoXMLSheet(matrix, "Page" + (sheets.Count + 1), sheets.Count + 1);
  2124. sheets["Page" + (sheets.Count + 1)] = sh;
  2125. workBook.AddSheet(sh);
  2126. NewMatrix();
  2127. bandHeight = 0;
  2128. RectangleF rect = new RectangleF(band.Bounds.X, bandHeight, band.Bounds.Width, band.Bounds.Height);
  2129. band.Bounds = rect;
  2130. bandHeight += band.Bounds.Height;
  2131. matrix.AddPageBegin(currentPage);
  2132. base.ExportBand(band);
  2133. matrix.AddBand(band, this);
  2134. }
  2135. else
  2136. {
  2137. if (band.ClassName is "GroupHeaderBand" && groupOnSeparateSheet && (GroupName is null || GroupName == band.Name))
  2138. firstGroupHeaderBand = false;
  2139. if (groupOnSeparateSheet)
  2140. {
  2141. if (bandHeight + band.Bounds.Height > currentPage.Height)
  2142. bandHeight = 0;
  2143. RectangleF rect = new RectangleF(band.Bounds.X, bandHeight, band.Bounds.Width, band.Bounds.Height);
  2144. band.Bounds = rect;
  2145. bandHeight += band.Bounds.Height;
  2146. }
  2147. base.ExportBand(band);
  2148. matrix.AddBand(band, this);
  2149. }
  2150. }
  2151. /// <inheritdoc/>
  2152. protected override void ExportPageEnd(ReportPage page)
  2153. {
  2154. if (!groupOnSeparateSheet)
  2155. {
  2156. matrix.AddPageEnd(page);
  2157. if (!sheets.ContainsKey(page.Name) || SplitPages || needCreateNewList)
  2158. {
  2159. string suffix = "";
  2160. if (SplitPages || needCreateNewList)
  2161. {
  2162. if (sheets.ContainsKey(page.Name + "-1"))
  2163. {
  2164. int repeats = 2;
  2165. while (sheets.ContainsKey(page.Name + "-" + repeats.ToString()))
  2166. repeats++;
  2167. suffix = "-" + repeats.ToString();
  2168. }
  2169. else
  2170. {
  2171. ReportPage nextPage = Report.PreparedPages.GetPage(sheets.Count + 1);
  2172. if (nextPage != null && nextPage.Name == page.Name)
  2173. suffix = "-1";
  2174. }
  2175. }
  2176. OoXMLSheet sh = new OoXMLSheet(matrix, (String.IsNullOrEmpty(page.ExportAlias) ? page.Name : page.ExportAlias) + suffix, sheets.Count + 1);
  2177. sheets[page.Name + suffix] = sh;
  2178. workBook.AddSheet(sh);
  2179. }
  2180. }
  2181. }
  2182. /// <inheritdoc/>
  2183. protected override void Finish()
  2184. {
  2185. if (groupOnSeparateSheet)
  2186. {
  2187. matrix.AddPageEnd(currentPage);
  2188. OoXMLSheet sh = new OoXMLSheet(matrix, "Page" + (sheets.Count + 1), sheets.Count + 1);
  2189. sheets["Page" + (sheets.Count + 1)] = sh;
  2190. workBook.AddSheet(sh);
  2191. }
  2192. ExportOOXML(Stream);
  2193. Zip.SaveToStream(Stream);
  2194. if (useFileStream)
  2195. {
  2196. Zip.Clear();
  2197. Stream.Close();
  2198. foreach (string item in TempFileNames)
  2199. {
  2200. File.Delete(item);
  2201. }
  2202. }
  2203. else
  2204. Zip.Clear();
  2205. foreach (OoXMLSheet sheet in workBook.SheetList)
  2206. sheet.Matrix.Dispose();
  2207. GC.Collect(2);
  2208. }
  2209. /// <inheritdoc/>
  2210. protected override string GetFileFilter()
  2211. {
  2212. return new MyRes("FileFilters").Get("XlsxFile");
  2213. }
  2214. #endregion Protected Methods
  2215. /// <summary>
  2216. /// Gets the file name to export to.
  2217. /// </summary>
  2218. /// <returns>The full file name to export to.</returns>
  2219. public string GetFileName()
  2220. {
  2221. return FileName;
  2222. }
  2223. /// <inheritdoc/>
  2224. public override void Serialize(FRWriter writer)
  2225. {
  2226. base.Serialize(writer);
  2227. writer.WriteBool("Wysiwyg", Wysiwyg);
  2228. writer.WriteBool("PageBreaks", PageBreaks);
  2229. writer.WriteBool("DataOnly", DataOnly);
  2230. writer.WriteBool("GroupOnSeparateSheet", GroupOnSeparateSheet);
  2231. writer.WriteBool("Seamless", Seamless);
  2232. writer.WriteBool("PrintOptimized", PrintOptimized);
  2233. writer.WriteBool("SplitPages", SplitPages);
  2234. writer.WriteFloat("FontScale", FontScale);
  2235. writer.WriteValue("PrintFit", PrintFit);
  2236. writer.WriteBool("ShowGridlines", ShowGridlines);
  2237. writer.WriteBool("UngroupSheets", UngroupSheets);
  2238. writer.WriteBool("UsePresetScale", UsePresetScale);
  2239. writer.WriteInt("PrintScale", PrintScale);
  2240. writer.WriteValue("PictureProperties", PictureProperties);
  2241. writer.WriteInt("PinY", PinY);
  2242. writer.WriteInt("PinX", PinX);
  2243. writer.WriteBool("UseLocaleFormating", UseLocaleFormating);
  2244. writer.WriteBool("CurrencyToAccounting", CurrencyToAccounting);
  2245. writer.WriteBool("GeneralToText", GeneralToText);
  2246. }
  2247. /// <summary>
  2248. /// Initializes a new instance of the class.
  2249. /// </summary>
  2250. public Excel2007Export()
  2251. {
  2252. pageBreaks = true;
  2253. seamless = false;
  2254. wysiwyg = true;
  2255. showGridlines = true;
  2256. printFitMode = PrintFitMode.NoScaling;
  2257. pictureProperties = ePictureProperties.MoveAndModify;
  2258. sheetIDsWithPics = new List<string>();
  2259. tempFileNames = new List<string>();
  2260. printScale = 10;
  2261. usePresetScale = true;
  2262. useLocaleFormating = false;
  2263. currencyToAccounting = false;
  2264. generalToText = false;
  2265. useFileStream = false;
  2266. pinX = 0;
  2267. pinY = 0;
  2268. }
  2269. }
  2270. }