ExcelHandler.cs 254 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562156315641565156615671568156915701571157215731574157515761577157815791580158115821583158415851586158715881589159015911592159315941595159615971598159916001601160216031604160516061607160816091610161116121613161416151616161716181619162016211622162316241625162616271628162916301631163216331634163516361637163816391640164116421643164416451646164716481649165016511652165316541655165616571658165916601661166216631664166516661667166816691670167116721673167416751676167716781679168016811682168316841685168616871688168916901691169216931694169516961697169816991700170117021703170417051706170717081709171017111712171317141715171617171718171917201721172217231724172517261727172817291730173117321733173417351736173717381739174017411742174317441745174617471748174917501751175217531754175517561757175817591760176117621763176417651766176717681769177017711772177317741775177617771778177917801781178217831784178517861787178817891790179117921793179417951796179717981799180018011802180318041805180618071808180918101811181218131814181518161817181818191820182118221823182418251826182718281829183018311832183318341835183618371838183918401841184218431844184518461847184818491850185118521853185418551856185718581859186018611862186318641865186618671868186918701871187218731874187518761877187818791880188118821883188418851886188718881889189018911892189318941895189618971898189919001901190219031904190519061907190819091910191119121913191419151916191719181919192019211922192319241925192619271928192919301931193219331934193519361937193819391940194119421943194419451946194719481949195019511952195319541955195619571958195919601961196219631964196519661967196819691970197119721973197419751976197719781979198019811982198319841985198619871988198919901991199219931994199519961997199819992000200120022003200420052006200720082009201020112012201320142015201620172018201920202021202220232024202520262027202820292030203120322033203420352036203720382039204020412042204320442045204620472048204920502051205220532054205520562057205820592060206120622063206420652066206720682069207020712072207320742075207620772078207920802081208220832084208520862087208820892090209120922093209420952096209720982099210021012102210321042105210621072108210921102111211221132114211521162117211821192120212121222123212421252126212721282129213021312132213321342135213621372138213921402141214221432144214521462147214821492150215121522153215421552156215721582159216021612162216321642165216621672168216921702171217221732174217521762177217821792180218121822183218421852186218721882189219021912192219321942195219621972198219922002201220222032204220522062207220822092210221122122213221422152216221722182219222022212222222322242225222622272228222922302231223222332234223522362237223822392240224122422243224422452246224722482249225022512252225322542255225622572258225922602261226222632264226522662267226822692270227122722273227422752276227722782279228022812282228322842285228622872288228922902291229222932294229522962297229822992300230123022303230423052306230723082309231023112312231323142315231623172318231923202321232223232324232523262327232823292330233123322333233423352336233723382339234023412342234323442345234623472348234923502351235223532354235523562357235823592360236123622363236423652366236723682369237023712372237323742375237623772378237923802381238223832384238523862387238823892390239123922393239423952396239723982399240024012402240324042405240624072408240924102411241224132414241524162417241824192420242124222423242424252426242724282429243024312432243324342435243624372438243924402441244224432444244524462447244824492450245124522453245424552456245724582459246024612462246324642465246624672468246924702471247224732474247524762477247824792480248124822483248424852486248724882489249024912492249324942495249624972498249925002501250225032504250525062507250825092510251125122513251425152516251725182519252025212522252325242525252625272528252925302531253225332534253525362537253825392540254125422543254425452546254725482549255025512552255325542555255625572558255925602561256225632564256525662567256825692570257125722573257425752576257725782579258025812582258325842585258625872588258925902591259225932594259525962597259825992600260126022603260426052606260726082609261026112612261326142615261626172618261926202621262226232624262526262627262826292630263126322633263426352636263726382639264026412642264326442645264626472648264926502651265226532654265526562657265826592660266126622663266426652666266726682669267026712672267326742675267626772678267926802681268226832684268526862687268826892690269126922693269426952696269726982699270027012702270327042705270627072708270927102711271227132714271527162717271827192720272127222723272427252726272727282729273027312732273327342735273627372738273927402741274227432744274527462747274827492750275127522753275427552756275727582759276027612762276327642765276627672768276927702771277227732774277527762777277827792780278127822783278427852786278727882789279027912792279327942795279627972798279928002801280228032804280528062807280828092810281128122813281428152816281728182819282028212822282328242825282628272828282928302831283228332834283528362837283828392840284128422843284428452846284728482849285028512852285328542855285628572858285928602861286228632864286528662867286828692870287128722873287428752876287728782879288028812882288328842885288628872888288928902891289228932894289528962897289828992900290129022903290429052906290729082909291029112912291329142915291629172918291929202921292229232924292529262927292829292930293129322933293429352936293729382939294029412942294329442945294629472948294929502951295229532954295529562957295829592960296129622963296429652966296729682969297029712972297329742975297629772978297929802981298229832984298529862987298829892990299129922993299429952996299729982999300030013002300330043005300630073008300930103011301230133014301530163017301830193020302130223023302430253026302730283029303030313032303330343035303630373038303930403041304230433044304530463047304830493050305130523053305430553056305730583059306030613062306330643065306630673068306930703071307230733074307530763077307830793080308130823083308430853086308730883089309030913092309330943095309630973098309931003101310231033104310531063107310831093110311131123113311431153116311731183119312031213122312331243125312631273128312931303131313231333134313531363137313831393140314131423143314431453146314731483149315031513152315331543155315631573158315931603161316231633164316531663167316831693170317131723173317431753176317731783179318031813182318331843185318631873188318931903191319231933194319531963197319831993200320132023203320432053206320732083209321032113212321332143215321632173218321932203221322232233224322532263227322832293230323132323233323432353236323732383239324032413242324332443245324632473248324932503251325232533254325532563257325832593260326132623263326432653266326732683269327032713272327332743275327632773278327932803281328232833284328532863287328832893290329132923293329432953296329732983299330033013302330333043305330633073308330933103311331233133314331533163317331833193320332133223323332433253326332733283329333033313332333333343335333633373338333933403341334233433344334533463347334833493350335133523353335433553356335733583359336033613362336333643365336633673368336933703371337233733374337533763377337833793380338133823383338433853386338733883389339033913392339333943395339633973398339934003401340234033404340534063407340834093410341134123413341434153416341734183419342034213422342334243425342634273428342934303431343234333434343534363437343834393440344134423443344434453446344734483449345034513452345334543455345634573458345934603461346234633464346534663467346834693470347134723473347434753476347734783479348034813482348334843485348634873488348934903491349234933494349534963497349834993500350135023503350435053506350735083509351035113512351335143515351635173518351935203521352235233524352535263527352835293530353135323533353435353536353735383539354035413542354335443545354635473548354935503551355235533554355535563557355835593560356135623563356435653566356735683569357035713572357335743575357635773578357935803581358235833584358535863587358835893590359135923593359435953596359735983599360036013602360336043605360636073608360936103611361236133614361536163617361836193620362136223623362436253626362736283629363036313632363336343635363636373638363936403641364236433644364536463647364836493650365136523653365436553656365736583659366036613662366336643665366636673668366936703671367236733674367536763677367836793680368136823683368436853686368736883689369036913692369336943695369636973698369937003701370237033704370537063707370837093710371137123713371437153716371737183719372037213722372337243725372637273728372937303731373237333734373537363737373837393740374137423743374437453746374737483749375037513752375337543755375637573758375937603761376237633764376537663767376837693770377137723773377437753776377737783779378037813782378337843785378637873788378937903791379237933794379537963797379837993800380138023803380438053806380738083809381038113812381338143815381638173818381938203821382238233824382538263827382838293830383138323833383438353836383738383839384038413842384338443845384638473848384938503851385238533854385538563857385838593860386138623863386438653866386738683869387038713872387338743875387638773878387938803881388238833884388538863887388838893890389138923893389438953896389738983899390039013902390339043905390639073908390939103911391239133914391539163917391839193920392139223923392439253926392739283929393039313932393339343935393639373938393939403941394239433944394539463947394839493950395139523953395439553956395739583959396039613962396339643965396639673968396939703971397239733974397539763977397839793980398139823983398439853986398739883989399039913992399339943995399639973998399940004001400240034004400540064007400840094010401140124013401440154016401740184019402040214022402340244025402640274028402940304031403240334034403540364037403840394040404140424043404440454046404740484049405040514052405340544055405640574058405940604061406240634064406540664067406840694070407140724073407440754076407740784079408040814082408340844085408640874088408940904091409240934094409540964097409840994100410141024103410441054106410741084109411041114112411341144115411641174118411941204121412241234124412541264127412841294130413141324133413441354136413741384139414041414142414341444145414641474148414941504151415241534154415541564157415841594160416141624163416441654166416741684169417041714172417341744175417641774178417941804181418241834184418541864187418841894190419141924193419441954196419741984199420042014202420342044205420642074208420942104211421242134214421542164217421842194220422142224223422442254226422742284229423042314232423342344235423642374238423942404241424242434244424542464247424842494250425142524253425442554256425742584259426042614262426342644265426642674268426942704271427242734274427542764277427842794280428142824283428442854286428742884289429042914292429342944295429642974298429943004301430243034304430543064307430843094310431143124313431443154316431743184319432043214322432343244325432643274328432943304331433243334334433543364337433843394340434143424343434443454346434743484349435043514352435343544355435643574358435943604361436243634364436543664367436843694370437143724373437443754376437743784379438043814382438343844385438643874388438943904391439243934394439543964397439843994400440144024403440444054406440744084409441044114412441344144415441644174418441944204421442244234424442544264427442844294430443144324433443444354436443744384439444044414442444344444445444644474448444944504451445244534454445544564457445844594460446144624463446444654466446744684469447044714472447344744475447644774478447944804481448244834484448544864487448844894490449144924493449444954496449744984499450045014502450345044505450645074508450945104511451245134514451545164517451845194520452145224523452445254526452745284529453045314532453345344535453645374538453945404541454245434544454545464547454845494550455145524553455445554556455745584559456045614562456345644565456645674568456945704571457245734574457545764577457845794580458145824583458445854586458745884589459045914592459345944595459645974598459946004601460246034604460546064607460846094610461146124613461446154616461746184619462046214622462346244625462646274628462946304631463246334634463546364637463846394640464146424643464446454646464746484649465046514652465346544655465646574658465946604661466246634664466546664667466846694670467146724673467446754676467746784679468046814682468346844685468646874688468946904691469246934694469546964697469846994700470147024703470447054706470747084709471047114712471347144715471647174718471947204721472247234724472547264727472847294730473147324733473447354736473747384739474047414742474347444745474647474748474947504751475247534754475547564757475847594760476147624763476447654766476747684769477047714772477347744775477647774778477947804781478247834784478547864787
  1. using System.IO;
  2. using System.Data;
  3. using NPOI.HSSF.UserModel;
  4. using NPOI.SS.UserModel;
  5. using NPOI.HSSF.Util;
  6. using NPOI.XSSF.UserModel;
  7. using System;
  8. using UAS_LabelMachine.Entity;
  9. using UAS_LabelMachine.PublicMethod;
  10. using System.Collections.Generic;
  11. using System.Windows.Forms;
  12. using System.Text.RegularExpressions;
  13. namespace UAS_LabelMachine
  14. {
  15. class ExcelHandler
  16. {
  17. DataHelper dh = SystemInf.dh;
  18. //展示抬头日期的索引
  19. int ShowDataIndex = 7;
  20. //展示芯片尺寸的索引
  21. int ShowSizeIndex = 5;
  22. //显示计数的索引
  23. int ShowCountIndex = 2;
  24. //显示片数的索引
  25. int ShowPieceIndex = 1;
  26. //显示备注的片
  27. int ShowRemarkPiece = 5;
  28. int NumIndex = 0;
  29. /// <summary>
  30. /// 导出Excel,返回文件在客户端的路径
  31. /// </summary>
  32. public string ExportExcel(DataTable dt, string FolderPath, string FileName)
  33. {
  34. //创建一个内存流,用来接收转换成Excel的内容
  35. MemoryStream ms;
  36. ms = DataTableToExcel(dt);
  37. //以系统当前时间命名文件,FileMode.Create表示创建文件,FileAccess.Write表示拥有写的权限
  38. string filePath = @FolderPath + "\\" + FileName + ".xls";
  39. FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write);
  40. byte[] data = ms.ToArray();
  41. fs.Write(data, 0, data.Length);
  42. fs.Flush();
  43. //释放当前Excel文件,否则打开文件的时候会显示文件被占用
  44. ms.Dispose();
  45. fs.Dispose();
  46. return filePath;
  47. }
  48. /// <summary>
  49. /// 导出Excel,返回文件在客户端的路径
  50. /// </summary>
  51. public string ExportExcel_Normal(DataTable firstsdt, DataTable dt, string FolderPath, string FileName, string Type, int PageSize, List<CheckBox> conditionbox)
  52. {
  53. //创建一个内存流,用来接收转换成Excel的内容
  54. MemoryStream ms;
  55. ms = DataTableToExcel_Normal(firstsdt, dt, Type, FileName, PageSize, conditionbox);
  56. //以系统当前时间命名文件,FileMode.Create表示创建文件,FileAccess.Write表示拥有写的权限
  57. string filePath = @FolderPath + "\\" + FileName + ".xls";
  58. int counter = 1;
  59. string filename = FileName + ".xls";
  60. while (File.Exists(filePath))
  61. {
  62. filename = string.Format("{0}({1}){2}", FileName, counter, ".xls"); //文件名+(count)+后缀
  63. filePath = Path.Combine(FolderPath, filename); //保存路径
  64. counter++; //count+1
  65. }
  66. FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write);
  67. byte[] data = ms.ToArray();
  68. fs.Write(data, 0, data.Length);
  69. fs.Flush();
  70. //释放当前Excel文件,否则打开文件的时候会显示文件被占用
  71. ms.Dispose();
  72. fs.Dispose();
  73. return filePath;
  74. }
  75. /// <summary>
  76. /// 晶源客户特殊方案
  77. /// </summary>
  78. public string JINYUAN(DataTable firstsdt, DataTable dt, string FolderPath, string FileName, string Type, int PageSize, List<CheckBox> conditionbox)
  79. {
  80. //创建一个内存流,用来接收转换成Excel的内容
  81. MemoryStream ms;
  82. ms = DataTableToExcel_JINYUAN(firstsdt, dt, Type, FileName, PageSize, conditionbox);
  83. //以系统当前时间命名文件,FileMode.Create表示创建文件,FileAccess.Write表示拥有写的权限
  84. string filePath = @FolderPath + "\\" + FileName + ".xls";
  85. int counter = 1;
  86. string filename = FileName + ".xls";
  87. while (File.Exists(filePath))
  88. {
  89. filename = string.Format("{0}({1}){2}", FileName, counter, ".xls"); //文件名+(count)+后缀
  90. filePath = Path.Combine(FolderPath, filename); //保存路径
  91. counter++; //count+1
  92. }
  93. FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write);
  94. byte[] data = ms.ToArray();
  95. fs.Write(data, 0, data.Length);
  96. fs.Flush();
  97. //释放当前Excel文件,否则打开文件的时候会显示文件被占用
  98. ms.Dispose();
  99. fs.Dispose();
  100. return filePath;
  101. }
  102. /// <summary>
  103. /// 利普芯客户特殊方案
  104. /// </summary>
  105. public string LIPIUXIN(DataTable firstsdt, DataTable dt, string FolderPath, string FileName, string Type, int PageSize, List<CheckBox> conditionbox)
  106. {
  107. //创建一个内存流,用来接收转换成Excel的内容
  108. MemoryStream ms;
  109. ms = DataTableToExcel_LIPUXIN(firstsdt, dt, Type, FileName, PageSize, conditionbox);
  110. //以系统当前时间命名文件,FileMode.Create表示创建文件,FileAccess.Write表示拥有写的权限
  111. string filePath = @FolderPath + "\\" + FileName + ".xls";
  112. int counter = 1;
  113. string filename = FileName + ".xls";
  114. while (File.Exists(filePath))
  115. {
  116. filename = string.Format("{0}({1}){2}", FileName, counter, ".xls"); //文件名+(count)+后缀
  117. filePath = Path.Combine(FolderPath, filename); //保存路径
  118. counter++; //count+1
  119. }
  120. FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write);
  121. byte[] data = ms.ToArray();
  122. fs.Write(data, 0, data.Length);
  123. fs.Flush();
  124. //释放当前Excel文件,否则打开文件的时候会显示文件被占用
  125. ms.Dispose();
  126. fs.Dispose();
  127. return filePath;
  128. }
  129. /// <summary>
  130. /// 导入Excel
  131. /// </summary>
  132. public void ImportExcel(DataTable DataTable, string TableName)
  133. {
  134. int columnNum = DataTable.Columns.Count;
  135. int rowNum = DataTable.Columns.Count;
  136. string[] field = new string[columnNum];
  137. for (int i = 0; i < columnNum; i++)
  138. {
  139. field[i] = DataTable.Rows[0][i].ToString();
  140. }
  141. }
  142. public static DataTable ExcelToDataTable(string filePath, bool isColumnName)
  143. {
  144. DataTable dataTable = null;
  145. FileStream fs = null;
  146. DataColumn column = null;
  147. DataRow dataRow = null;
  148. IWorkbook workbook = null;
  149. ISheet sheet = null;
  150. IRow row = null;
  151. ICell cell = null;
  152. int startRow = 0;
  153. try
  154. {
  155. using (fs = File.OpenRead(filePath))
  156. {
  157. // 2007版本
  158. if (filePath.IndexOf(".xlsx") > 0)
  159. {
  160. workbook = new XSSFWorkbook(fs);
  161. }
  162. // 2003版本
  163. else if (filePath.IndexOf(".xls") > 0)
  164. {
  165. workbook = new HSSFWorkbook(fs);
  166. }
  167. if (workbook != null)
  168. {
  169. sheet = workbook.GetSheetAt(0);//读取第一个sheet,当然也可以循环读取每个sheet
  170. dataTable = new DataTable();
  171. if (sheet != null)
  172. {
  173. int rowCount = sheet.LastRowNum;//总行数
  174. if (rowCount > 0)
  175. {
  176. IRow firstRow = sheet.GetRow(0);//第一行
  177. int cellCount = firstRow.LastCellNum;//列数
  178. //构建datatable的列
  179. if (isColumnName)
  180. {
  181. startRow = 1;//如果第一行是列名,则从第二行开始读取
  182. for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
  183. {
  184. cell = firstRow.GetCell(i);
  185. if (cell != null)
  186. {
  187. if (cell.StringCellValue != null)
  188. {
  189. column = new DataColumn(cell.StringCellValue);
  190. dataTable.Columns.Add(column);
  191. }
  192. }
  193. }
  194. }
  195. else
  196. {
  197. for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
  198. {
  199. column = new DataColumn("column" + (i + 1));
  200. dataTable.Columns.Add(column);
  201. }
  202. }
  203. //填充行
  204. for (int i = startRow; i <= rowCount; ++i)
  205. {
  206. row = sheet.GetRow(i);
  207. if (row == null) continue;
  208. dataRow = dataTable.NewRow();
  209. for (int j = row.FirstCellNum; j < cellCount; ++j)
  210. {
  211. cell = row.GetCell(j);
  212. if (cell == null)
  213. {
  214. dataRow[j] = "";
  215. }
  216. else
  217. {
  218. //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
  219. switch (cell.CellType)
  220. {
  221. case CellType.Blank:
  222. dataRow[j] = "";
  223. break;
  224. case CellType.Numeric:
  225. short format = cell.CellStyle.DataFormat;
  226. //对时间格式(2015.12.5、2015/12/5、2015-12-5等)的处理
  227. if (format == 14 || format == 31 || format == 57 || format == 58)
  228. dataRow[j] = cell.DateCellValue;
  229. else
  230. dataRow[j] = cell.NumericCellValue;
  231. break;
  232. case CellType.String:
  233. dataRow[j] = cell.StringCellValue;
  234. break;
  235. }
  236. }
  237. }
  238. dataTable.Rows.Add(dataRow);
  239. }
  240. }
  241. }
  242. }
  243. }
  244. return dataTable;
  245. }
  246. catch (Exception)
  247. {
  248. if (fs != null)
  249. {
  250. fs.Close();
  251. }
  252. return null;
  253. }
  254. }
  255. int RowHeight = 12;
  256. /// <summary>
  257. /// 将DataTable形式的数据转成Excel格式的,然后用字节流的形式写入文件
  258. /// </summary>
  259. /// <param name="DataTable"></param>
  260. /// <returns></returns>
  261. public MemoryStream DataTableToExcel(DataTable DataTable)
  262. {
  263. //创建内存流
  264. MemoryStream ms = new MemoryStream();
  265. //创建一个Book,相当于一个Excel文件
  266. HSSFWorkbook book = new HSSFWorkbook();
  267. //Excel中的Sheet
  268. ISheet sheet = book.CreateSheet("sheet1");
  269. //获取行数量和列数量
  270. int rowNum = DataTable.Rows.Count;
  271. int columnNum = DataTable.Columns.Count;
  272. //设置列的宽度,根据首行的列的内容的长度来设置
  273. for (int i = 0; i < columnNum; i++)
  274. {
  275. int dataLength;
  276. //如果内容比标题短则取标题长度
  277. if (DataTable.Rows[0][i].ToString().Length < DataTable.Columns[i].ColumnName.Length)
  278. {
  279. dataLength = DataTable.Columns[i].ColumnName.Length;
  280. dataLength = dataLength * 300;
  281. }
  282. else
  283. {
  284. dataLength = DataTable.Rows[0][i].ToString().Length;
  285. dataLength = dataLength * 300;
  286. }
  287. sheet.SetColumnWidth(i, dataLength);
  288. }
  289. //首先画好第一行带颜色的,单独写出来,避免写在循环里面
  290. IRow row = sheet.CreateRow(0);
  291. //冻结第一行
  292. sheet.CreateFreezePane(0, 1, 0, 1);
  293. ICellStyle style = book.CreateCellStyle();
  294. style.FillForegroundColor = HSSFColor.PaleBlue.Index;
  295. style.FillPattern = FillPattern.BigSpots;
  296. style.FillBackgroundColor = HSSFColor.LightGreen.Index;
  297. //设置边框
  298. style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
  299. style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
  300. style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
  301. style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
  302. row.HeightInPoints = 20;
  303. //固定第一行
  304. //row.RowStyle.IsLocked=true;
  305. //给第一行的标签赋值样式和值
  306. for (int j = 0; j < columnNum; j++)
  307. {
  308. row.CreateCell(j);
  309. row.Cells[j].CellStyle = style;
  310. row.Cells[j].CellStyle.VerticalAlignment = VerticalAlignment.Center;
  311. row.Cells[j].CellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
  312. row.Cells[j].SetCellValue(DataTable.Columns[j].Caption);
  313. }
  314. //将DataTable的值循环赋值给book,Aligment设置居中
  315. //之前已经画了带颜色的第一行,所以从i=1开始画
  316. for (int i = 0; i < rowNum; i++)
  317. {
  318. IRow row1 = sheet.CreateRow(i + 1);
  319. row1.HeightInPoints = 20;
  320. for (int j = 0; j < columnNum; j++)
  321. {
  322. row1.CreateCell(j);
  323. row1.Cells[j].SetCellValue(DataTable.Rows[i][j].ToString());
  324. row1.GetCell(j).CellStyle.VerticalAlignment = VerticalAlignment.Center;
  325. }
  326. }
  327. //将book的内容写入内存流中返回
  328. book.Write(ms);
  329. return ms;
  330. }
  331. /// <summary>
  332. /// 将DataTable形式的数据转成Excel格式的,然后用字节流的形式写入文件
  333. /// </summary>
  334. /// <param name="DataTable"></param>
  335. /// <returns></returns>
  336. public MemoryStream DataTableToExcel_Normal(DataTable FirstDT, DataTable DataTable, string Type, string Inoutno, int PageSize, List<CheckBox> conditionbox)
  337. {
  338. string 小计 = "";
  339. string 总计 = "";
  340. string 片 = "";
  341. string companyname = "";
  342. Dictionary<int, double> PercentSum = new Dictionary<int, double>();
  343. if (dh.getFieldDataByCondition("ProdInout", "pi_exporttype", "pi_inoutno='" + Inoutno + "'").ToString() == "Chinese")
  344. {
  345. 小计 = "小计";
  346. 总计 = "总计";
  347. companyname = "深爱半导体股份有限公司芯片出货清单";
  348. }
  349. else
  350. {
  351. 小计 = "total";
  352. 总计 = "total";
  353. companyname = "SHENZHEN SI SEMICONDUCTORS CO.,LTD";
  354. }
  355. bool CountQty = false;
  356. if (dh.getFieldDataByCondition("ProdInout", "pi_exporttype1", "pi_inoutno='" + Inoutno + "'").ToString() == "QTY")
  357. {
  358. CountQty = true;
  359. }
  360. //转换为序列
  361. CheckBox[] box = conditionbox.ToArray();
  362. //创建内存流
  363. MemoryStream ms = new MemoryStream();
  364. //创建一个Book,相当于一个Excel文件
  365. HSSFWorkbook book = new HSSFWorkbook();
  366. //Excel中的Sheet
  367. ISheet sheet = book.CreateSheet("分页");
  368. sheet.SetMargin(MarginType.TopMargin, 0.4);
  369. sheet.SetMargin(MarginType.BottomMargin, 0.4);
  370. sheet.SetMargin(MarginType.LeftMargin, 0.4);
  371. sheet.SetMargin(MarginType.RightMargin, 0.4);
  372. sheet.FitToPage = true;
  373. sheet.PrintSetup.FitHeight = 200;
  374. //芯片号需要作为更新盒号的条件
  375. HSSFFont ffont = (HSSFFont)book.CreateFont();
  376. ffont.FontName = "宋体";
  377. //更新箱号
  378. List<string> pib_id = new List<string>();
  379. //系统打印箱号
  380. List<string> pib_outboxcode1 = new List<string>();
  381. int BoxCode = 1;
  382. ICellStyle style = book.CreateCellStyle();
  383. style.VerticalAlignment = VerticalAlignment.Center;
  384. style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
  385. style.SetFont(ffont);
  386. ICellStyle styleborder = book.CreateCellStyle();
  387. styleborder.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
  388. styleborder.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
  389. styleborder.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
  390. styleborder.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
  391. styleborder.VerticalAlignment = VerticalAlignment.Center;
  392. styleborder.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
  393. styleborder.SetFont(ffont);
  394. string pi_inoutno = "";
  395. HSSFFont ColumnTitle = (HSSFFont)book.CreateFont();
  396. ColumnTitle.FontName = "宋体";
  397. ColumnTitle.Boldweight = (short)FontBoldWeight.Bold;
  398. ICellStyle ColumnTitleStyle = book.CreateCellStyle();
  399. ColumnTitleStyle.SetFont(ColumnTitle);
  400. ColumnTitleStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
  401. ColumnTitleStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
  402. ColumnTitleStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
  403. ColumnTitleStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
  404. List<string> NotShowColumn = new List<string>();
  405. NotShowColumn.Add("ch_level");
  406. NotShowColumn.Add("pib_id");
  407. NotShowColumn.Add("chw_itemname1");
  408. NotShowColumn.Add("chw_itemname2");
  409. NotShowColumn.Add("chw_itemname3");
  410. NotShowColumn.Add("chw_itemname4");
  411. NotShowColumn.Add("chw_itemname5");
  412. NotShowColumn.Add("pd_ordercode");
  413. NotShowColumn.Add("pr_size");
  414. NotShowColumn.Add("me_desc");
  415. NotShowColumn.Add("pr_orispeccode");
  416. NotShowColumn.Add("pr_orispeccode1");
  417. NotShowColumn.Add("pi_title");
  418. NotShowColumn.Add("pi_chipouttype");
  419. NotShowColumn.Add("pi_date");
  420. int ShowColumnCount = 0;
  421. bool ShowSplitBatch = true;
  422. //展示的内容列
  423. //设置列的宽度,根据首行的列的内容的长度来设置
  424. for (int i = DataTable.Columns.Count - 1; i > 0; i--)
  425. {
  426. for (int j = 0; j < box.Length; j++)
  427. {
  428. if (box[j].Name.ToLower() == "ch_bluefilm" && !box[j].Checked)
  429. {
  430. if (DataTable.Columns[i].ColumnName.ToLower().Contains("ch_bluefilm"))
  431. {
  432. DataTable.Columns.RemoveAt(i);
  433. break;
  434. }
  435. }
  436. if (box[j].Name.ToLower() == "ch_code" && !box[j].Checked)
  437. {
  438. if (DataTable.Columns[i].ColumnName.ToLower().Contains("ch_code"))
  439. {
  440. DataTable.Columns.RemoveAt(i);
  441. break;
  442. }
  443. }
  444. if (box[j].Name.ToLower() == "ch_splitbatch" && !box[j].Checked)
  445. {
  446. if (DataTable.Columns[i].ColumnName.ToLower().Contains("ch_splitbatch"))
  447. {
  448. DataTable.Columns.RemoveAt(i);
  449. break;
  450. }
  451. }
  452. if (box[j].Name.ToLower() == "ch_waterid" && !box[j].Checked)
  453. {
  454. if (DataTable.Columns[i].ColumnName.ToLower().Contains("ch_waterid"))
  455. {
  456. DataTable.Columns.RemoveAt(i);
  457. break;
  458. }
  459. }
  460. if (box[j].Name.ToLower() == "ch_pbcode" && !box[j].Checked)
  461. {
  462. if (DataTable.Columns[i].ColumnName.ToLower().Contains("ch_pbcode"))
  463. {
  464. DataTable.Columns.RemoveAt(i);
  465. break;
  466. }
  467. }
  468. if (box[j].Name.ToLower() == "ch_remark" && !box[j].Checked)
  469. {
  470. if (DataTable.Columns[i].ColumnName.ToLower().Contains("ch_remark"))
  471. {
  472. DataTable.Columns.RemoveAt(i);
  473. break;
  474. }
  475. }
  476. if (box[j].Name.ToLower() == "ch_yield" && !box[j].Checked)
  477. {
  478. if (DataTable.Columns[i].ColumnName.ToLower().Contains("ch_yield"))
  479. {
  480. DataTable.Columns.RemoveAt(i);
  481. break;
  482. }
  483. }
  484. }
  485. }
  486. //获取行数量和列数量
  487. int rowNum = DataTable.Rows.Count;
  488. int columnNum = DataTable.Columns.Count;
  489. //首先画好第一行带颜色的,单独写出来,避免写在循环里面
  490. IRow row = sheet.CreateRow(0);
  491. //冻结第一行
  492. sheet.CreateFreezePane(0, 1, 0, 1);
  493. row.HeightInPoints = RowHeight;
  494. //固定第一行
  495. //row.RowStyle.IsLocked=true;
  496. //给第一行的标签赋值样式和值
  497. // ffont.FontHeight = 13;
  498. row.CreateCell(0);
  499. row.Cells[0].SetCellValue(" " + companyname);
  500. row.GetCell(0).CellStyle.SetFont((ffont));
  501. //ffont.FontHeight = 10;
  502. //开始绘制的Index
  503. int PaintIndex = 1;
  504. int sumCount = 0;
  505. int totalCount = 0;
  506. switch (Type)
  507. {
  508. case "FixRow":
  509. //清理系统取出来的数据
  510. BaseUtil.CleanDataTableData(FirstDT);
  511. //首页参数拼接
  512. string First_OrderCode = "";
  513. string First_Prspec = "";
  514. string First_Batch = "";
  515. ArrayList<string> First_WID = new ArrayList<string>();
  516. for (int i = 0; i < rowNum; i++)
  517. {
  518. IRow row1 = sheet.CreateRow(PaintIndex);
  519. PaintIndex = PaintIndex + 1;
  520. row1.HeightInPoints = RowHeight;
  521. //不包含的订单号
  522. if (DataTable.Columns.Contains("pd_ordercode") && !First_OrderCode.Contains(DataTable.Rows[i]["pd_ordercode"].ToString()))
  523. {
  524. First_OrderCode += DataTable.Rows[i]["pd_ordercode"].ToString() + " ";
  525. }
  526. //不包含的物料型号
  527. if (DataTable.Columns.Contains("pr_orispeccode") && !First_Prspec.Contains(DataTable.Rows[i]["pr_orispeccode"].ToString()))
  528. {
  529. First_Prspec += DataTable.Rows[i]["pr_orispeccode"].ToString() + " ";
  530. }
  531. //不包含扩撒批号
  532. if (DataTable.Columns.Contains("ch_splitbatch") && !First_Batch.Contains(DataTable.Rows[i]["ch_splitbatch"].ToString()))
  533. {
  534. First_Batch += DataTable.Rows[i]["ch_splitbatch"].ToString() + " ";
  535. }
  536. //不包含Wafer_id
  537. if (DataTable.Columns.Contains("Wafer_ID") && !First_WID.Contains(DataTable.Rows[i]["Wafer_ID"].ToString()))
  538. {
  539. First_WID.Add(DataTable.Rows[i]["Wafer_ID"].ToString());
  540. }
  541. if (i / PageSize >= 1 && i % PageSize == 0)
  542. {
  543. DataRow dr = FirstDT.NewRow();
  544. dr["me_desc"] = DataTable.Rows[i]["me_desc"].ToString();
  545. dr["pr_size"] = DataTable.Rows[i]["pr_size"].ToString();
  546. dr["pi_chipouttype"] = DataTable.Rows[i]["pi_chipouttype"].ToString();
  547. dr["ch_level"] = DataTable.Rows[i]["ch_level"].ToString();
  548. dr["pr_orispeccode"] = DataTable.Rows[i]["pr_orispeccode"].ToString();
  549. dr["pi_inoutno"] = DataTable.Rows[i]["pi_inoutno"].ToString();
  550. pi_inoutno = DataTable.Rows[i]["pi_inoutno"].ToString();
  551. dr["pi_title"] = DataTable.Rows[i]["pi_title"].ToString();
  552. dr["pi_date"] = DataTable.Rows[i]["pi_date"].ToString();
  553. dr["pd_ordercode"] = First_OrderCode;
  554. dr["pr_orispeccode"] = First_Prspec;
  555. dr["ch_splitbatch"] = First_Batch;
  556. dr["ch_waterid"] = BaseUtil.GetArrStr(First_WID, " ");
  557. dr["num"] = PageSize;
  558. dr["io_qty"] = sumCount;
  559. FirstDT.Rows.Add(dr);
  560. First_OrderCode = "";
  561. First_Prspec = "";
  562. First_Batch = "";
  563. First_WID.Clear();
  564. BoxCode = BoxCode + 1;
  565. for (int j = 0; j < ShowColumnCount; j++)
  566. {
  567. row1.CreateCell(j);
  568. if (j == 0)
  569. {
  570. row1.Cells[j].SetCellValue(小计);
  571. }
  572. else if (j == ShowPieceIndex)
  573. {
  574. row1.Cells[ShowPieceIndex].SetCellValue((i % PageSize == 0 ? PageSize : i));
  575. row1.Cells[j].CellStyle = styleborder;
  576. }
  577. else if (j == ShowCountIndex)
  578. {
  579. row1.Cells[j].SetCellValue(sumCount);
  580. }
  581. if (CountQty)
  582. {
  583. for (int k = 0; k < PercentSum.Count; k++)
  584. {
  585. if (PercentSum.ContainsKey(j))
  586. {
  587. if (PercentSum[j] != 0)
  588. {
  589. row1.Cells[j].SetCellValue(PercentSum[j] / PercentSum.Count);
  590. PercentSum[j] = 0;
  591. }
  592. }
  593. }
  594. }
  595. row1.Cells[j].CellStyle = styleborder;
  596. }
  597. sumCount = 0;
  598. row1 = sheet.CreateRow(PaintIndex);
  599. sheet.SetRowBreak(PaintIndex - 1);
  600. sheet.Footer.Center = "第&P页,共&N页";
  601. PaintIndex = PaintIndex + 1;
  602. }
  603. //每次到了页数开始分页
  604. if (i % PageSize == 0 || i == rowNum - 1)
  605. {
  606. //第一行添加客户信息 rownum只有一行的情
  607. if (i != rowNum - 1 || rowNum == 1 || (rowNum - i == rowNum % PageSize))
  608. {
  609. for (int j = 0; j < columnNum - 3; j++)
  610. {
  611. if (j == 0)
  612. {
  613. row1.CreateCell(j);
  614. row1.Cells[j].SetCellValue(DataTable.Columns["pi_title"].Caption + ":" + DataTable.Rows[i]["pi_title"].ToString() + " "
  615. + DataTable.Columns["ch_level"].Caption + ":" + DataTable.Rows[i]["ch_level"].ToString());
  616. }
  617. else if (j == ShowDataIndex)
  618. {
  619. row1.CreateCell(j);
  620. row1.Cells[j].SetCellValue(DataTable.Columns["pi_inoutno"].Caption + ":" + DataTable.Rows[i]["pi_inoutno"].ToString());
  621. }
  622. else
  623. {
  624. row1.CreateCell(j);
  625. }
  626. row1.GetCell(j).CellStyle = style;
  627. }
  628. row1 = sheet.CreateRow(PaintIndex);
  629. PaintIndex = PaintIndex + 1;
  630. //第二行添加型号
  631. for (int j = 0; j < columnNum - 3; j++)
  632. {
  633. if (j == 0)
  634. {
  635. row1.CreateCell(j).SetCellValue(DataTable.Columns["pr_orispeccode"].Caption + ":" + DataTable.Rows[i]["pr_orispeccode"].ToString() + " "
  636. + "(" + DataTable.Rows[i]["me_desc"].ToString() + ")" + DataTable.Columns["pi_chipouttype"].Caption + ":" + DataTable.Rows[i]["pi_chipouttype"].ToString());
  637. }
  638. else if (j == ShowSizeIndex)
  639. {
  640. row1.CreateCell(j).SetCellValue(DataTable.Columns["pr_size"].Caption + ":" + DataTable.Rows[i]["pr_size"].ToString());
  641. }
  642. else if (j == ShowDataIndex)
  643. {
  644. row1.CreateCell(j).SetCellValue(DataTable.Columns["pi_date"].Caption + ":" + DataTable.Rows[i]["pi_date"].ToString());
  645. }
  646. }
  647. //添加列名
  648. row1 = sheet.CreateRow(PaintIndex);
  649. PaintIndex = PaintIndex + 1;
  650. //计数列所在的索引
  651. int showcount = 0;
  652. for (int j = 4; j < columnNum; j++)
  653. {
  654. if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent1")
  655. {
  656. if (!PercentSum.ContainsKey(j - 4))
  657. PercentSum.Add(j - 4, 0);
  658. showcount = showcount + 1;
  659. row1.CreateCell(j - 4);
  660. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  661. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname1"].ToString());
  662. }
  663. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent2")
  664. {
  665. if (!PercentSum.ContainsKey(j - 4))
  666. PercentSum.Add(j - 4, 0);
  667. showcount = showcount + 1;
  668. row1.CreateCell(j - 4);
  669. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  670. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname2"].ToString());
  671. }
  672. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent3")
  673. {
  674. if (!PercentSum.ContainsKey(j - 4))
  675. PercentSum.Add(j - 4, 0);
  676. showcount = showcount + 1;
  677. row1.CreateCell(j - 4);
  678. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  679. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname3"].ToString());
  680. }
  681. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent4")
  682. {
  683. if (!PercentSum.ContainsKey(j - 4))
  684. PercentSum.Add(j - 4, 0);
  685. showcount = showcount + 1;
  686. row1.CreateCell(j - 4);
  687. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  688. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname4"].ToString());
  689. }
  690. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent5")
  691. {
  692. if (!PercentSum.ContainsKey(j - 4))
  693. PercentSum.Add(j - 4, 0);
  694. showcount = showcount + 1;
  695. row1.CreateCell(j - 4);
  696. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  697. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname5"].ToString());
  698. }
  699. else if ((NotShowColumn.Contains(DataTable.Columns[j].ColumnName.ToLower())))
  700. {
  701. }
  702. else
  703. {
  704. showcount = showcount + 1;
  705. row1.CreateCell(j - 4);
  706. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  707. row1.Cells[j - 4].SetCellValue(DataTable.Columns[j].Caption);
  708. }
  709. if (DataTable.Columns[j].ColumnName.ToString() == "io_qty")
  710. {
  711. NumIndex = j;
  712. if (ShowCountIndex == NumIndex - 4)
  713. {
  714. ShowCountIndex = ShowCountIndex + 1;
  715. }
  716. }
  717. }
  718. ShowColumnCount = showcount;
  719. row1 = sheet.CreateRow(PaintIndex);
  720. PaintIndex = PaintIndex + 1;
  721. }
  722. }
  723. //添加数据内容
  724. for (int j = 4; j < columnNum; j++)
  725. {
  726. string Data = DataTable.Rows[i][j].ToString();
  727. row1.CreateCell(j - 4);
  728. if (DataTable.Columns[j].ColumnName.ToLower() == "ch_bluefilm")
  729. {
  730. row1.CreateCell(j - 4, CellType.String);
  731. }
  732. if ((NotShowColumn.Contains(DataTable.Columns[j].ColumnName.ToLower())))
  733. {
  734. }
  735. else
  736. {
  737. double Num = 0;
  738. if (double.TryParse(Data, out Num)&& DataTable.Columns[j].ColumnName.ToLower() != "ch_bluefilm")
  739. {
  740. row1.Cells[j - 4].SetCellValue(Num);
  741. for (int k = 0; k < PercentSum.Count; k++)
  742. {
  743. if (PercentSum.ContainsKey(j - 4))
  744. {
  745. PercentSum[j - 4] += Num;
  746. }
  747. }
  748. }
  749. else
  750. {
  751. row1.Cells[j - 4].SetCellValue(Data);
  752. }
  753. row1.Cells[j - 4].CellStyle = styleborder;
  754. }
  755. if (DataTable.Columns[j].ColumnName == "io_qty")
  756. {
  757. row1.Cells[j - 4].SetCellValue(int.Parse(Data));
  758. sumCount += int.Parse(DataTable.Rows[i][j].ToString());
  759. totalCount += int.Parse(DataTable.Rows[i][j].ToString());
  760. }
  761. if (DataTable.Columns[j].ColumnName == "rownum")
  762. {
  763. row1.Cells[j - 4].SetCellValue(i + 1);
  764. }
  765. if (DataTable.Columns[j].ColumnName == "ch_code")
  766. {
  767. sheet.AutoSizeColumn(j - 4);
  768. sheet.SetColumnWidth(j - 4, sheet.GetColumnWidth(j - 4) + 1000);
  769. }
  770. }
  771. //固定行号分组的时候自动拼接新的DataTable
  772. if (i == rowNum - 1)
  773. {
  774. DataRow dr = FirstDT.NewRow();
  775. dr["me_desc"] = DataTable.Rows[i]["me_desc"].ToString();
  776. dr["pr_size"] = DataTable.Rows[i]["pr_size"].ToString();
  777. dr["pi_chipouttype"] = DataTable.Rows[i]["pi_chipouttype"].ToString();
  778. dr["ch_level"] = DataTable.Rows[i]["ch_level"].ToString();
  779. dr["pr_orispeccode"] = DataTable.Rows[i]["pr_orispeccode"].ToString();
  780. dr["pi_inoutno"] = DataTable.Rows[i]["pi_inoutno"].ToString();
  781. dr["pi_title"] = DataTable.Rows[i]["pi_title"].ToString();
  782. dr["pi_date"] = DataTable.Rows[i]["pi_date"].ToString();
  783. dr["pd_ordercode"] = First_OrderCode;
  784. dr["pr_orispeccode"] = First_Prspec;
  785. dr["ch_splitbatch"] = First_Batch;
  786. dr["ch_waterid"] = BaseUtil.GetArrStr(First_WID, " ");
  787. dr["num"] = (i % PageSize) + 1;
  788. dr["io_qty"] = sumCount;
  789. FirstDT.Rows.Add(dr);
  790. row1 = sheet.CreateRow(PaintIndex);
  791. PaintIndex = PaintIndex + 1;
  792. for (int j = 0; j < ShowColumnCount; j++)
  793. {
  794. row1.CreateCell(j);
  795. if (j == 0)
  796. {
  797. row1.Cells[j].SetCellValue(小计);
  798. }
  799. else if (j == ShowPieceIndex)
  800. {
  801. row1.Cells[ShowPieceIndex].SetCellValue((rowNum % PageSize == 0 ? PageSize : (rowNum % PageSize)));
  802. row1.Cells[j].CellStyle = styleborder;
  803. }
  804. else if (j == ShowCountIndex)
  805. {
  806. row1.Cells[j].SetCellValue(sumCount);
  807. }
  808. if (CountQty)
  809. {
  810. for (int k = 0; k < PercentSum.Count; k++)
  811. {
  812. if (PercentSum.ContainsKey(j))
  813. {
  814. if (PercentSum[j] != 0)
  815. {
  816. row1.Cells[j].SetCellValue(PercentSum[j] / PercentSum.Count);
  817. PercentSum[j] = 0;
  818. }
  819. }
  820. }
  821. }
  822. row1.Cells[j].CellStyle = styleborder;
  823. }
  824. row1 = sheet.CreateRow(PaintIndex);
  825. for (int j = 0; j < columnNum - 3; j++)
  826. {
  827. if (j == 0)
  828. {
  829. row1.CreateCell(j);
  830. row1.Cells[j].SetCellValue("备注");
  831. }
  832. else if (j == ShowCountIndex)
  833. {
  834. row1.CreateCell(j);
  835. row1.Cells[j].SetCellValue(totalCount);
  836. }
  837. else if (j == ShowRemarkPiece)
  838. {
  839. row1.CreateCell(j);
  840. row1.Cells[j].SetCellValue(rowNum + "片");
  841. }
  842. else
  843. {
  844. row1.CreateCell(j);
  845. }
  846. row1.Cells[j].CellStyle = style;
  847. }
  848. sheet.SetRowBreak(PaintIndex);
  849. sheet.Footer.Center = "第&P页,共&N页";
  850. PaintIndex = PaintIndex + 1;
  851. }
  852. pib_id.Add(DataTable.Rows[i]["pib_id"].ToString());
  853. pib_outboxcode1.Add(BoxCode.ToString());
  854. }
  855. break;
  856. case "BatchCode":
  857. string LastBatchCode = "";
  858. int PageNum = 0;
  859. for (int i = 0; i < rowNum; i++)
  860. {
  861. IRow row1 = sheet.CreateRow(PaintIndex);
  862. PaintIndex = PaintIndex + 1;
  863. row1.HeightInPoints = RowHeight;
  864. //如果批号不相等的时候
  865. if (LastBatchCode != "" && LastBatchCode != DataTable.Rows[i]["ch_splitbatch"].ToString())
  866. {
  867. BoxCode = BoxCode + 1;
  868. for (int j = 0; j < ShowColumnCount; j++)
  869. {
  870. row1.CreateCell(j);
  871. if (j == 0)
  872. {
  873. row1.Cells[j].SetCellValue(小计);
  874. row1.Cells[j].CellStyle = styleborder;
  875. }
  876. else if (j == ShowPieceIndex)
  877. {
  878. row1.Cells[ShowPieceIndex].SetCellValue(PageNum);
  879. row1.Cells[j].CellStyle = styleborder;
  880. }
  881. else if (j == ShowCountIndex)
  882. {
  883. row1.Cells[j].SetCellValue(sumCount);
  884. row1.Cells[j].CellStyle = styleborder;
  885. }
  886. else
  887. {
  888. row1.Cells[j].CellStyle = styleborder;
  889. }
  890. if (CountQty)
  891. {
  892. for (int k = 0; k < PercentSum.Count; k++)
  893. {
  894. if (PercentSum.ContainsKey(j))
  895. {
  896. if (PercentSum[j] != 0)
  897. {
  898. row1.Cells[j].SetCellValue(PercentSum[j] / PercentSum.Count);
  899. PercentSum[j] = 0;
  900. }
  901. }
  902. }
  903. }
  904. }
  905. sumCount = 0;
  906. row1 = sheet.CreateRow(PaintIndex);
  907. sheet.SetRowBreak(PaintIndex - 1);
  908. sheet.Footer.Center = "第&P页,共&N页";
  909. PaintIndex = PaintIndex + 1;
  910. PageNum = 0;
  911. }
  912. int SplitBatchIndex = 0;
  913. //每次到了页数开始分页
  914. if (LastBatchCode == "" || (LastBatchCode != "" && LastBatchCode != DataTable.Rows[i]["ch_splitbatch"].ToString()) || (i == rowNum - 1 && (LastBatchCode != "" && LastBatchCode != DataTable.Rows[i]["ch_splitbatch"].ToString())))
  915. {
  916. LastBatchCode = DataTable.Rows[i]["ch_splitbatch"].ToString();
  917. //第一行添加客户信息
  918. //if (i != rowNum - 1)
  919. {
  920. for (int j = 0; j < columnNum - 3; j++)
  921. {
  922. if (j == 0)
  923. {
  924. row1.CreateCell(j);
  925. row1.Cells[j].SetCellValue(DataTable.Columns["pi_title"].Caption + ":" + DataTable.Rows[i]["pi_title"].ToString() + " "
  926. + DataTable.Columns["ch_level"].Caption + ":" + DataTable.Rows[i]["ch_level"].ToString());
  927. }
  928. else if (j == ShowDataIndex)
  929. {
  930. row1.CreateCell(j);
  931. row1.Cells[j].SetCellValue(DataTable.Columns["pi_inoutno"].Caption + ":" + DataTable.Rows[i]["pi_inoutno"].ToString());
  932. }
  933. else
  934. {
  935. row1.CreateCell(j);
  936. }
  937. row1.GetCell(j).CellStyle = style;
  938. }
  939. row1 = sheet.CreateRow(PaintIndex);
  940. PaintIndex = PaintIndex + 1;
  941. //第二行添加型号
  942. for (int j = 0; j < columnNum - 3; j++)
  943. {
  944. if (j == 0)
  945. {
  946. row1.CreateCell(j).SetCellValue(DataTable.Columns["pr_orispeccode"].Caption + ":" + DataTable.Rows[i]["pr_orispeccode"].ToString() + " "
  947. + "(" + DataTable.Rows[i]["me_desc"].ToString() + ")" + DataTable.Columns["pi_chipouttype"].Caption + ":" + DataTable.Rows[i]["pi_chipouttype"].ToString());
  948. }
  949. else if (j == ShowSizeIndex)
  950. {
  951. row1.CreateCell(j).SetCellValue(DataTable.Columns["pr_size"].Caption + ":" + DataTable.Rows[i]["pr_size"].ToString());
  952. }
  953. else if (j == ShowDataIndex)
  954. {
  955. row1.CreateCell(j).SetCellValue(DataTable.Columns["pi_date"].Caption + ":" + DataTable.Rows[i]["pi_date"].ToString());
  956. }
  957. }
  958. //添加列名
  959. row1 = sheet.CreateRow(PaintIndex);
  960. PaintIndex = PaintIndex + 1;
  961. int showcount = 0;
  962. for (int j = 4; j < columnNum; j++)
  963. {
  964. if (DataTable.Columns[j].ColumnName.ToLower() == "ch_splitbatch")
  965. {
  966. SplitBatchIndex = j - 4;
  967. }
  968. if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent1")
  969. {
  970. if (!PercentSum.ContainsKey(j - 4))
  971. PercentSum.Add(j - 4, 0);
  972. showcount = showcount + 1;
  973. row1.CreateCell(j - 4);
  974. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  975. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname1"].ToString());
  976. }
  977. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent2")
  978. {
  979. if (!PercentSum.ContainsKey(j - 4))
  980. PercentSum.Add(j - 4, 0);
  981. showcount = showcount + 1;
  982. row1.CreateCell(j - 4);
  983. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  984. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname2"].ToString());
  985. }
  986. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent3")
  987. {
  988. if (!PercentSum.ContainsKey(j - 4))
  989. PercentSum.Add(j - 4, 0);
  990. showcount = showcount + 1;
  991. row1.CreateCell(j - 4);
  992. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  993. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname3"].ToString());
  994. }
  995. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent4")
  996. {
  997. if (!PercentSum.ContainsKey(j - 4))
  998. PercentSum.Add(j - 4, 0);
  999. showcount = showcount + 1;
  1000. row1.CreateCell(j - 4);
  1001. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  1002. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname4"].ToString());
  1003. }
  1004. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent5")
  1005. {
  1006. if (!PercentSum.ContainsKey(j - 4))
  1007. PercentSum.Add(j - 4, 0);
  1008. showcount = showcount + 1;
  1009. row1.CreateCell(j - 4);
  1010. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  1011. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname5"].ToString());
  1012. }
  1013. else if ((NotShowColumn.Contains(DataTable.Columns[j].ColumnName.ToLower())))
  1014. {
  1015. }
  1016. else
  1017. {
  1018. showcount = showcount + 1;
  1019. row1.CreateCell(j - 4);
  1020. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  1021. row1.Cells[j - 4].SetCellValue(DataTable.Columns[j].Caption);
  1022. }
  1023. if (DataTable.Columns[j].ColumnName.ToString() == "io_qty")
  1024. {
  1025. NumIndex = j;
  1026. if (ShowCountIndex == NumIndex - 4)
  1027. {
  1028. ShowCountIndex = ShowCountIndex + 1;
  1029. }
  1030. }
  1031. }
  1032. ShowColumnCount = showcount;
  1033. row1 = sheet.CreateRow(PaintIndex);
  1034. PaintIndex = PaintIndex + 1;
  1035. }
  1036. }
  1037. //添加数据内容
  1038. for (int j = 4; j < columnNum; j++)
  1039. {
  1040. string Data = DataTable.Rows[i][j].ToString();
  1041. row1.CreateCell(j - 4);
  1042. if (DataTable.Columns[j].ColumnName.ToLower() == "ch_bluefilm")
  1043. {
  1044. row1.CreateCell(j - 4, CellType.String);
  1045. }
  1046. if ((NotShowColumn.Contains(DataTable.Columns[j].ColumnName.ToLower())))
  1047. {
  1048. }
  1049. else
  1050. {
  1051. double Num = 0;
  1052. if (double.TryParse(Data, out Num)&& DataTable.Columns[j].ColumnName.ToLower() != "ch_bluefilm")
  1053. {
  1054. row1.Cells[j - 4].SetCellValue(Num);
  1055. for (int k = 0; k < PercentSum.Count; k++)
  1056. {
  1057. if (PercentSum.ContainsKey(j - 4))
  1058. {
  1059. PercentSum[j - 4] += Num;
  1060. }
  1061. }
  1062. }
  1063. else
  1064. {
  1065. row1.Cells[j - 4].SetCellValue(Data);
  1066. }
  1067. row1.Cells[j - 4].CellStyle = styleborder;
  1068. }
  1069. if (DataTable.Columns[j].ColumnName == "io_qty")
  1070. {
  1071. row1.Cells[j - 4].SetCellValue(int.Parse(Data));
  1072. sumCount += int.Parse(DataTable.Rows[i][j].ToString());
  1073. totalCount += int.Parse(DataTable.Rows[i][j].ToString());
  1074. }
  1075. if (DataTable.Columns[j].ColumnName == "rownum")
  1076. {
  1077. row1.Cells[j - 4].SetCellValue(i + 1);
  1078. }
  1079. if (DataTable.Columns[j].ColumnName == "ch_code")
  1080. {
  1081. sheet.AutoSizeColumn(j - 4);
  1082. sheet.SetColumnWidth(j - 4, sheet.GetColumnWidth(j - 4) + 1000);
  1083. }
  1084. }
  1085. if (i == rowNum - 1)
  1086. {
  1087. PageNum = PageNum + 1;
  1088. row1 = sheet.CreateRow(PaintIndex);
  1089. PaintIndex = PaintIndex + 1;
  1090. for (int j = 0; j < ShowColumnCount; j++)
  1091. {
  1092. row1.CreateCell(j);
  1093. if (j == 0)
  1094. {
  1095. row1.Cells[j].SetCellValue(小计);
  1096. row1.Cells[j].CellStyle = styleborder;
  1097. }
  1098. else if (j == ShowPieceIndex)
  1099. {
  1100. row1.Cells[ShowPieceIndex].SetCellValue(PageNum);
  1101. row1.Cells[j].CellStyle = styleborder;
  1102. }
  1103. else if (j == ShowCountIndex)
  1104. {
  1105. if (ShowCountIndex == SplitBatchIndex)
  1106. {
  1107. row1.CreateCell(j+1);
  1108. row1.Cells[j + 1].SetCellValue(sumCount);
  1109. row1.Cells[j + 1].CellStyle = styleborder;
  1110. }
  1111. else
  1112. {
  1113. row1.Cells[j].SetCellValue(sumCount);
  1114. row1.Cells[j].CellStyle = styleborder;
  1115. }
  1116. }
  1117. else
  1118. {
  1119. row1.Cells[j].CellStyle = styleborder;
  1120. }
  1121. if (CountQty)
  1122. {
  1123. for (int k = 0; k < PercentSum.Count; k++)
  1124. {
  1125. if (PercentSum.ContainsKey(j))
  1126. {
  1127. if (PercentSum[j] != 0)
  1128. {
  1129. row1.Cells[j].SetCellValue(PercentSum[j] / PercentSum.Count);
  1130. PercentSum[j] = 0;
  1131. }
  1132. }
  1133. }
  1134. }
  1135. }
  1136. //创建备注内容
  1137. row1 = sheet.CreateRow(PaintIndex);
  1138. for (int j = 0; j < columnNum - 3; j++)
  1139. {
  1140. if (j == 0)
  1141. {
  1142. row1.CreateCell(j);
  1143. row1.Cells[j].SetCellValue("备注");
  1144. }
  1145. else if (j == ShowCountIndex)
  1146. {
  1147. row1.CreateCell(j);
  1148. row1.Cells[j].SetCellValue(totalCount);
  1149. }
  1150. else if (j == ShowRemarkPiece)
  1151. {
  1152. row1.CreateCell(j);
  1153. row1.Cells[j].SetCellValue(rowNum + "片");
  1154. }
  1155. else if (j == 4)
  1156. {
  1157. row1.CreateCell(j);
  1158. row1.Cells[j].SetCellValue(片);
  1159. }
  1160. else
  1161. {
  1162. row1.CreateCell(j);
  1163. }
  1164. row1.Cells[j].CellStyle = style;
  1165. }
  1166. sheet.SetRowBreak(PaintIndex);
  1167. sheet.Footer.Center = "第&P页,共&N页";
  1168. PaintIndex = PaintIndex + 1;
  1169. PageNum = 0;
  1170. }
  1171. PageNum = PageNum + 1;
  1172. pib_id.Add(DataTable.Rows[i]["pib_id"].ToString());
  1173. pib_outboxcode1.Add(BoxCode.ToString());
  1174. }
  1175. break;
  1176. case "BoxCode":
  1177. string LastBoxCode = "";
  1178. int PageNum1 = 0;
  1179. for (int i = 0; i < rowNum; i++)
  1180. {
  1181. IRow row1 = sheet.CreateRow(PaintIndex);
  1182. PaintIndex = PaintIndex + 1;
  1183. row1.HeightInPoints = RowHeight;
  1184. //如果批号不相等的时候
  1185. if (LastBoxCode != "" && LastBoxCode != DataTable.Rows[i]["CH_PBCODE"].ToString())
  1186. {
  1187. BoxCode = BoxCode + 1;
  1188. for (int j = 0; j < ShowColumnCount; j++)
  1189. {
  1190. row1.CreateCell(j);
  1191. if (j == 0)
  1192. {
  1193. row1.Cells[j].SetCellValue(小计);
  1194. row1.Cells[j].CellStyle = styleborder;
  1195. }
  1196. else if (j == ShowPieceIndex)
  1197. {
  1198. row1.Cells[ShowPieceIndex].SetCellValue(PageNum1);
  1199. row1.Cells[j].CellStyle = styleborder;
  1200. }
  1201. else if (j == ShowCountIndex)
  1202. {
  1203. row1.Cells[j].SetCellValue(sumCount);
  1204. row1.Cells[j].CellStyle = styleborder;
  1205. }
  1206. else
  1207. {
  1208. row1.Cells[j].CellStyle = styleborder;
  1209. }
  1210. if (CountQty)
  1211. {
  1212. for (int k = 0; k < PercentSum.Count; k++)
  1213. {
  1214. if (PercentSum.ContainsKey(j))
  1215. {
  1216. if (PercentSum[j] != 0)
  1217. {
  1218. row1.Cells[j].SetCellValue(PercentSum[j] / PercentSum.Count);
  1219. PercentSum[j] = 0;
  1220. }
  1221. }
  1222. }
  1223. }
  1224. }
  1225. sumCount = 0;
  1226. row1 = sheet.CreateRow(PaintIndex);
  1227. sheet.SetRowBreak(PaintIndex - 1);
  1228. sheet.Footer.Center = "第&P页,共&N页";
  1229. PaintIndex = PaintIndex + 1;
  1230. PageNum1 = 0;
  1231. }
  1232. //每次到了页数开始分页
  1233. if (LastBoxCode == "" || (LastBoxCode != "" && LastBoxCode != DataTable.Rows[i]["ch_pbcode"].ToString()) || ((i == rowNum - 1) && (LastBoxCode != "" && LastBoxCode != DataTable.Rows[i]["ch_pbcode"].ToString())))
  1234. {
  1235. LastBoxCode = DataTable.Rows[i]["CH_PBCODE"].ToString();
  1236. //第一行添加客户信息
  1237. //if (i != rowNum - 1)
  1238. {
  1239. for (int j = 0; j < columnNum - 3; j++)
  1240. {
  1241. if (j == 0)
  1242. {
  1243. row1.CreateCell(j);
  1244. row1.Cells[j].SetCellValue(DataTable.Columns["pi_title"].Caption + ":" + DataTable.Rows[i]["pi_title"].ToString() + " "
  1245. + DataTable.Columns["ch_level"].Caption + ":" + DataTable.Rows[i]["ch_level"].ToString());
  1246. }
  1247. else if (j == ShowDataIndex)
  1248. {
  1249. row1.CreateCell(j);
  1250. row1.Cells[j].SetCellValue(DataTable.Columns["pi_inoutno"].Caption + ":" + DataTable.Rows[i]["pi_inoutno"].ToString());
  1251. }
  1252. else
  1253. {
  1254. row1.CreateCell(j);
  1255. }
  1256. row1.GetCell(j).CellStyle = style;
  1257. }
  1258. row1 = sheet.CreateRow(PaintIndex);
  1259. PaintIndex = PaintIndex + 1;
  1260. //第二行添加型号
  1261. for (int j = 0; j < columnNum - 3; j++)
  1262. {
  1263. if (j == 0)
  1264. {
  1265. row1.CreateCell(j).SetCellValue(DataTable.Columns["pr_orispeccode"].Caption + ":" + DataTable.Rows[i]["pr_orispeccode"].ToString() + " "
  1266. + "(" + DataTable.Rows[i]["me_desc"].ToString() + ")" + DataTable.Columns["pi_chipouttype"].Caption + ":" + DataTable.Rows[i]["pi_chipouttype"].ToString());
  1267. }
  1268. else if (j == ShowSizeIndex)
  1269. {
  1270. row1.CreateCell(j).SetCellValue(DataTable.Columns["pr_size"].Caption + ":" + DataTable.Rows[i]["pr_size"].ToString());
  1271. }
  1272. else if (j == ShowDataIndex)
  1273. {
  1274. row1.CreateCell(j).SetCellValue(DataTable.Columns["pi_date"].Caption + ":" + DataTable.Rows[i]["pi_date"].ToString());
  1275. }
  1276. }
  1277. //添加列名
  1278. row1 = sheet.CreateRow(PaintIndex);
  1279. PaintIndex = PaintIndex + 1;
  1280. int showcount = 0;
  1281. for (int j = 4; j < columnNum; j++)
  1282. {
  1283. if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent1")
  1284. {
  1285. if (!PercentSum.ContainsKey(j - 4))
  1286. PercentSum.Add(j - 4, 0);
  1287. showcount = showcount + 1;
  1288. row1.CreateCell(j - 4);
  1289. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  1290. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname1"].ToString());
  1291. }
  1292. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent2")
  1293. {
  1294. if (!PercentSum.ContainsKey(j - 4))
  1295. PercentSum.Add(j - 4, 0);
  1296. showcount = showcount + 1;
  1297. row1.CreateCell(j - 4);
  1298. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  1299. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname2"].ToString());
  1300. }
  1301. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent3")
  1302. {
  1303. if (!PercentSum.ContainsKey(j - 4))
  1304. PercentSum.Add(j - 4, 0);
  1305. showcount = showcount + 1;
  1306. row1.CreateCell(j - 4);
  1307. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  1308. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname3"].ToString());
  1309. }
  1310. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent4")
  1311. {
  1312. if (!PercentSum.ContainsKey(j - 4))
  1313. PercentSum.Add(j - 4, 0);
  1314. showcount = showcount + 1;
  1315. row1.CreateCell(j - 4);
  1316. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  1317. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname4"].ToString());
  1318. }
  1319. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent5")
  1320. {
  1321. if (!PercentSum.ContainsKey(j - 4))
  1322. PercentSum.Add(j - 4, 0);
  1323. showcount = showcount + 1;
  1324. row1.CreateCell(j - 4);
  1325. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  1326. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname5"].ToString());
  1327. }
  1328. else if ((NotShowColumn.Contains(DataTable.Columns[j].ColumnName.ToLower())))
  1329. {
  1330. }
  1331. else
  1332. {
  1333. showcount = showcount + 1;
  1334. row1.CreateCell(j - 4);
  1335. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  1336. row1.Cells[j - 4].SetCellValue(DataTable.Columns[j].Caption);
  1337. }
  1338. if (DataTable.Columns[j].ColumnName.ToString() == "io_qty")
  1339. {
  1340. NumIndex = j;
  1341. if (ShowCountIndex == NumIndex - 4)
  1342. {
  1343. ShowCountIndex = ShowCountIndex + 1;
  1344. }
  1345. }
  1346. }
  1347. ShowColumnCount = showcount;
  1348. row1 = sheet.CreateRow(PaintIndex);
  1349. PaintIndex = PaintIndex + 1;
  1350. }
  1351. }
  1352. //添加数据内容
  1353. for (int j = 4; j < columnNum; j++)
  1354. {
  1355. string Data = DataTable.Rows[i][j].ToString();
  1356. row1.CreateCell(j - 4);
  1357. if (DataTable.Columns[j].ColumnName.ToLower() == "ch_bluefilm")
  1358. {
  1359. row1.CreateCell(j - 4, CellType.String);
  1360. }
  1361. if ((NotShowColumn.Contains(DataTable.Columns[j].ColumnName.ToLower())))
  1362. {
  1363. }
  1364. else
  1365. {
  1366. double Num = 0;
  1367. if (double.TryParse(Data, out Num)&& DataTable.Columns[j].ColumnName.ToLower() != "ch_bluefilm")
  1368. {
  1369. row1.Cells[j - 4].SetCellValue(Num);
  1370. for (int k = 0; k < PercentSum.Count; k++)
  1371. {
  1372. if (PercentSum.ContainsKey(j - 4))
  1373. {
  1374. PercentSum[j - 4] += Num;
  1375. }
  1376. }
  1377. }
  1378. else
  1379. {
  1380. row1.Cells[j - 4].SetCellValue(Data);
  1381. }
  1382. row1.Cells[j - 4].CellStyle = styleborder;
  1383. }
  1384. if (DataTable.Columns[j].ColumnName == "io_qty")
  1385. {
  1386. row1.Cells[j - 4].SetCellValue(int.Parse(Data));
  1387. sumCount += int.Parse(DataTable.Rows[i][j].ToString());
  1388. totalCount += int.Parse(DataTable.Rows[i][j].ToString());
  1389. }
  1390. if (DataTable.Columns[j].ColumnName == "rownum")
  1391. {
  1392. row1.Cells[j - 4].SetCellValue(i + 1);
  1393. }
  1394. if (DataTable.Columns[j].ColumnName == "ch_code")
  1395. {
  1396. sheet.AutoSizeColumn(j - 4);
  1397. sheet.SetColumnWidth(j - 4, sheet.GetColumnWidth(j - 4) + 1000);
  1398. }
  1399. }
  1400. if (i == rowNum - 1)
  1401. {
  1402. PageNum1 = PageNum1 + 1;
  1403. row1 = sheet.CreateRow(PaintIndex);
  1404. PaintIndex = PaintIndex + 1;
  1405. for (int j = 0; j < ShowColumnCount; j++)
  1406. {
  1407. row1.CreateCell(j);
  1408. if (j == 0)
  1409. {
  1410. row1.Cells[j].SetCellValue(小计);
  1411. row1.Cells[j].CellStyle = styleborder;
  1412. }
  1413. else if (j == ShowPieceIndex)
  1414. {
  1415. row1.Cells[ShowPieceIndex].SetCellValue(PageNum1);
  1416. row1.Cells[j].CellStyle = styleborder;
  1417. }
  1418. else if (j == ShowCountIndex)
  1419. {
  1420. row1.Cells[j].SetCellValue(sumCount);
  1421. row1.Cells[j].CellStyle = styleborder;
  1422. }
  1423. else
  1424. {
  1425. row1.Cells[j].CellStyle = styleborder;
  1426. }
  1427. if (CountQty)
  1428. {
  1429. for (int k = 0; k < PercentSum.Count; k++)
  1430. {
  1431. if (PercentSum.ContainsKey(j))
  1432. {
  1433. if (PercentSum[j] != 0)
  1434. {
  1435. row1.Cells[j].SetCellValue(PercentSum[j] / PercentSum.Count);
  1436. PercentSum[j] = 0;
  1437. }
  1438. }
  1439. }
  1440. }
  1441. }
  1442. row1 = sheet.CreateRow(PaintIndex);
  1443. for (int j = 0; j < columnNum - 3; j++)
  1444. {
  1445. if (j == 0)
  1446. {
  1447. row1.CreateCell(j);
  1448. row1.Cells[j].SetCellValue("备注");
  1449. }
  1450. else if (j == ShowCountIndex)
  1451. {
  1452. row1.CreateCell(j);
  1453. row1.Cells[j].SetCellValue(totalCount);
  1454. }
  1455. else if (j == ShowRemarkPiece)
  1456. {
  1457. row1.CreateCell(j);
  1458. row1.Cells[j].SetCellValue(rowNum + "片");
  1459. }
  1460. else
  1461. {
  1462. row1.CreateCell(j);
  1463. }
  1464. row1.Cells[j].CellStyle = style;
  1465. }
  1466. sheet.SetRowBreak(PaintIndex);
  1467. sheet.Footer.Center = "第&P页,共&N页";
  1468. PaintIndex = PaintIndex + 1;
  1469. PageNum1 = 0;
  1470. }
  1471. PageNum1 = PageNum1 + 1;
  1472. pib_id.Add(DataTable.Rows[i]["pib_id"].ToString());
  1473. pib_outboxcode1.Add(BoxCode.ToString());
  1474. }
  1475. //for (int i = 0; i < sheet.LastRowNum; i++)
  1476. //{
  1477. // if (i != 0)
  1478. // {
  1479. // sheet.AutoSizeColumn(i);
  1480. // sheet.SetColumnWidth(i, sheet.GetColumnWidth(i) + 1000);
  1481. // }
  1482. //}
  1483. break;
  1484. default:
  1485. break;
  1486. }
  1487. dh.BatchInsert("update prodiobarcode set pib_outboxcode1=:pib_outboxcode1 where pib_inoutno='" + Inoutno + "' and pib_id=:pib_id", new string[] { "pib_outboxcode1", "pib_id" }, pib_outboxcode1.ToArray(), pib_id.ToArray());
  1488. //删除下载链接再重新插入
  1489. HttpHandler.GenDownLoadLinK(Inoutno);
  1490. //填充首页
  1491. sumCount = 0;
  1492. totalCount = 0;
  1493. PaintIndex = 1;
  1494. ISheet sheet2 = book.CreateSheet("首页");
  1495. row = sheet2.CreateRow(0);
  1496. row.CreateCell(0);
  1497. row.Cells[0].SetCellValue(" " + companyname);
  1498. row.GetCell(0).CellStyle = style;
  1499. rowNum = FirstDT.Rows.Count;
  1500. //不需要显示的列移除
  1501. for (int i = FirstDT.Columns.Count - 1; i > 0; i--)
  1502. {
  1503. for (int j = 0; j < box.Length; j++)
  1504. {
  1505. if (box[j].Name == "FirstPage_WID" && !box[j].Checked)
  1506. {
  1507. if (FirstDT.Columns[i].ColumnName.ToLower().Contains("ch_waterid"))
  1508. {
  1509. FirstDT.Columns.RemoveAt(i);
  1510. }
  1511. }
  1512. if (box[j].Name == "FirstPage_YIELD" && !box[j].Checked)
  1513. {
  1514. if (FirstDT.Columns[i].ColumnName.ToLower().Contains("ch_yeild"))
  1515. {
  1516. FirstDT.Columns.RemoveAt(i);
  1517. }
  1518. }
  1519. if (box[j].Name == "FirstPage_REMARK" && !box[j].Checked)
  1520. {
  1521. if (FirstDT.Columns[i].ColumnName.ToLower().Contains("ch_remark"))
  1522. {
  1523. FirstDT.Columns.RemoveAt(i);
  1524. }
  1525. }
  1526. if (box[j].Name == "FirstPage_BOXCODE" && !box[j].Checked)
  1527. {
  1528. if (FirstDT.Columns[i].ColumnName.ToLower().Contains("ch_pbcode"))
  1529. {
  1530. FirstDT.Columns.RemoveAt(i);
  1531. }
  1532. }
  1533. }
  1534. }
  1535. columnNum = FirstDT.Columns.Count;
  1536. for (int i = 0; i < rowNum; i++)
  1537. {
  1538. IRow row1 = sheet2.CreateRow(PaintIndex);
  1539. PaintIndex = PaintIndex + 1;
  1540. row1.HeightInPoints = RowHeight;
  1541. //只需要绘制一行
  1542. if (i == 0)
  1543. {
  1544. for (int j = 0; j < columnNum - 3; j++)
  1545. {
  1546. if (j == 0)
  1547. {
  1548. row1.CreateCell(j);
  1549. row1.Cells[j].SetCellValue(FirstDT.Columns["pi_title"].Caption + ":" + FirstDT.Rows[i]["pi_title"].ToString() + " "
  1550. + FirstDT.Columns["ch_level"].Caption + ":" + FirstDT.Rows[i]["ch_level"].ToString());
  1551. }
  1552. else if (j > 5 && j == columnNum - 5)
  1553. {
  1554. row1.CreateCell(j);
  1555. row1.Cells[j].SetCellValue(FirstDT.Columns["pi_inoutno"].Caption + ":" + FirstDT.Rows[i]["pi_inoutno"].ToString());
  1556. }
  1557. else if (columnNum > 5 && j == columnNum - 5)
  1558. {
  1559. row1.CreateCell(j);
  1560. row1.Cells[j].SetCellValue(FirstDT.Columns["pi_inoutno"].Caption + ":" + FirstDT.Rows[i]["pi_inoutno"].ToString());
  1561. }
  1562. else
  1563. {
  1564. row1.CreateCell(j);
  1565. }
  1566. row1.GetCell(j).CellStyle = style;
  1567. }
  1568. row1 = sheet2.CreateRow(PaintIndex);
  1569. PaintIndex = PaintIndex + 1;
  1570. //第二行添加型号
  1571. for (int j = 0; j < columnNum - 3; j++)
  1572. {
  1573. if (j == 0)
  1574. {
  1575. row1.CreateCell(j).SetCellValue(FirstDT.Columns["pr_orispeccode"].Caption + ":" + FirstDT.Rows[i]["pr_orispeccode"].ToString() + " "
  1576. + "(" + FirstDT.Rows[i]["me_desc"].ToString() + ")" + FirstDT.Columns["pi_chipouttype"].Caption + ":" + FirstDT.Rows[i]["pi_chipouttype"].ToString());
  1577. }
  1578. else if (j == ShowSizeIndex)
  1579. {
  1580. row1.CreateCell(j);
  1581. row1.Cells[j].SetCellValue(FirstDT.Columns["pr_size"].Caption + ":" + FirstDT.Rows[i]["pr_size"].ToString());
  1582. }
  1583. else if (j > 5 && j == columnNum - 5)
  1584. {
  1585. row1.CreateCell(j);
  1586. row1.Cells[j].SetCellValue(FirstDT.Columns["pi_date"].Caption + ":" + FirstDT.Rows[i]["pi_date"].ToString());
  1587. }
  1588. else if (columnNum > 5 && j == columnNum - 5)
  1589. {
  1590. row1.CreateCell(j);
  1591. row1.Cells[j].SetCellValue(FirstDT.Columns["pi_date"].Caption + ":" + FirstDT.Rows[i]["pi_date"].ToString());
  1592. }
  1593. else
  1594. {
  1595. row1.CreateCell(j);
  1596. }
  1597. row1.GetCell(j).CellStyle = style;
  1598. }
  1599. row1 = sheet2.CreateRow(PaintIndex);
  1600. PaintIndex = PaintIndex + 1;
  1601. //添加列名
  1602. for (int j = 4; j < columnNum; j++)
  1603. {
  1604. row1.CreateCell(j - 4);
  1605. if (NotShowColumn.Contains(FirstDT.Columns[j].ColumnName.ToLower()))
  1606. {
  1607. }
  1608. else
  1609. {
  1610. row1.Cells[j - 4].CellStyle = styleborder;
  1611. row1.Cells[j - 4].SetCellValue(FirstDT.Columns[j].Caption);
  1612. }
  1613. }
  1614. row1 = sheet2.CreateRow(PaintIndex);
  1615. PaintIndex = PaintIndex + 1;
  1616. }
  1617. //添加数据内容
  1618. ShowColumnCount = 0;
  1619. for (int j = 4; j < columnNum; j++)
  1620. {
  1621. row1.CreateCell(j - 4);
  1622. if (FirstDT.Columns[j].ColumnName == "ch_bluefilm")
  1623. {
  1624. row1.CreateCell(j - 4, CellType.String);
  1625. }
  1626. if (!NotShowColumn.Contains(FirstDT.Columns[j].ColumnName.ToLower()))
  1627. {
  1628. string Data = FirstDT.Rows[i][j].ToString();
  1629. row1.Cells[j - 4].SetCellValue(Data);
  1630. row1.GetCell(j - 4).CellStyle = styleborder;
  1631. if (FirstDT.Columns[j].ColumnName == "num")
  1632. {
  1633. row1.Cells[j - 4].SetCellValue(int.Parse(Data));
  1634. sumCount += int.Parse(Data);
  1635. }
  1636. if (FirstDT.Columns[j].ColumnName == "io_qty")
  1637. {
  1638. totalCount += int.Parse(Data);
  1639. row1.Cells[j - 4].SetCellValue(int.Parse(Data));
  1640. }
  1641. ShowColumnCount = ShowColumnCount + 1;
  1642. }
  1643. else
  1644. {
  1645. }
  1646. }
  1647. //添加总计行
  1648. if (i == rowNum - 1)
  1649. {
  1650. row1 = sheet2.CreateRow(PaintIndex);
  1651. PaintIndex = PaintIndex + 1;
  1652. for (int j = 0; j < ShowColumnCount; j++)
  1653. {
  1654. if (j == 0)
  1655. {
  1656. row1.CreateCell(j);
  1657. row1.Cells[j].CellStyle = styleborder;
  1658. row1.Cells[j].SetCellValue(总计);
  1659. }
  1660. else if (j == ShowColumnCount - 2)
  1661. {
  1662. row1.CreateCell(j);
  1663. row1.Cells[j].CellStyle = styleborder;
  1664. row1.Cells[j].SetCellValue(sumCount);
  1665. }
  1666. else if (j == ShowColumnCount - 1)
  1667. {
  1668. row1.CreateCell(j);
  1669. row1.Cells[j].CellStyle = styleborder;
  1670. row1.Cells[j].SetCellValue(totalCount);
  1671. }
  1672. else
  1673. {
  1674. row1.CreateCell(j);
  1675. row1.Cells[j].CellStyle = styleborder;
  1676. }
  1677. }
  1678. }
  1679. }
  1680. for (int i = 0; i < sheet2.LastRowNum; i++)
  1681. {
  1682. if (i != 0)
  1683. {
  1684. sheet2.AutoSizeColumn(i);
  1685. sheet2.SetColumnWidth(i, sheet2.GetColumnWidth(i) + 1000);
  1686. }
  1687. }
  1688. //将book的内容写入内存流中返回
  1689. book.Write(ms);
  1690. return ms;
  1691. }
  1692. /// <summary>
  1693. /// 将DataTable形式的数据转成Excel格式的,然后用字节流的形式写入文件
  1694. /// </summary>
  1695. /// <param name="DataTable"></param>
  1696. /// <returns></returns>
  1697. private MemoryStream DataTableToExcel_JINYUAN(DataTable FirstDT, DataTable DataTable, string Type, string Inoutno, int PageSize, List<CheckBox> conditionbox)
  1698. {
  1699. string 小计 = "";
  1700. string 总计 = "";
  1701. string 片 = "";
  1702. string companyname = "";
  1703. //晶源的需要显示在数量列下面
  1704. int ShowCountIndex = 2;
  1705. if (dh.getFieldDataByCondition("ProdInout", "pi_exporttype", "pi_inoutno='" + Inoutno + "'").ToString() == "Chinese")
  1706. {
  1707. 小计 = "小计";
  1708. 总计 = "总计";
  1709. 片 = "片";
  1710. companyname = "深爱半导体股份有限公司芯片出货清单";
  1711. }
  1712. else
  1713. {
  1714. 小计 = "total";
  1715. 总计 = "total";
  1716. 片 = "slice";
  1717. companyname = "SHENZHEN SI SEMICONDUCTORS CO.,LTD";
  1718. }
  1719. //转换为序列
  1720. CheckBox[] box = conditionbox.ToArray();
  1721. //创建内存流
  1722. MemoryStream ms = new MemoryStream();
  1723. //创建一个Book,相当于一个Excel文件
  1724. HSSFWorkbook book = new HSSFWorkbook();
  1725. //Excel中的Sheet
  1726. ISheet sheet = book.CreateSheet("分页");
  1727. sheet.SetMargin(MarginType.TopMargin, 0.4);
  1728. sheet.SetMargin(MarginType.BottomMargin, 0.1);
  1729. sheet.SetMargin(MarginType.LeftMargin, 0.4);
  1730. sheet.SetMargin(MarginType.RightMargin, 0.4);
  1731. sheet.PrintSetup.NoColor = true;
  1732. sheet.PrintSetup.Landscape = true;
  1733. //芯片号需要作为更新盒号的条件
  1734. HSSFFont ffont = (HSSFFont)book.CreateFont();
  1735. ffont.FontName = "宋体";
  1736. //更新箱号
  1737. List<string> pib_id = new List<string>();
  1738. //系统打印箱号
  1739. List<string> pib_outboxcode1 = new List<string>();
  1740. int BoxCode = 1;
  1741. ICellStyle style = book.CreateCellStyle();
  1742. style.VerticalAlignment = VerticalAlignment.Center;
  1743. style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
  1744. style.SetFont(ffont);
  1745. ICellStyle styleborder = book.CreateCellStyle();
  1746. styleborder.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
  1747. styleborder.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
  1748. styleborder.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
  1749. styleborder.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
  1750. styleborder.VerticalAlignment = VerticalAlignment.Center;
  1751. styleborder.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
  1752. styleborder.SetFont(ffont);
  1753. string pi_inoutno = "";
  1754. List<string> NotShowColumn = new List<string>();
  1755. NotShowColumn.Add("ch_level");
  1756. NotShowColumn.Add("pib_id");
  1757. NotShowColumn.Add("chw_itemname1");
  1758. NotShowColumn.Add("chw_itemname2");
  1759. NotShowColumn.Add("chw_itemname3");
  1760. NotShowColumn.Add("chw_itemname4");
  1761. NotShowColumn.Add("chw_itemname5");
  1762. NotShowColumn.Add("pd_ordercode");
  1763. NotShowColumn.Add("pr_size");
  1764. NotShowColumn.Add("me_desc");
  1765. NotShowColumn.Add("pr_orispeccode");
  1766. NotShowColumn.Add("pr_orispeccode1");
  1767. NotShowColumn.Add("pi_title");
  1768. NotShowColumn.Add("pi_date");
  1769. NotShowColumn.Add("pi_chipouttype");
  1770. //设置列的宽度,根据首行的列的内容的长度来设置
  1771. for (int i = DataTable.Columns.Count - 1; i > 0; i--)
  1772. {
  1773. for (int j = 0; j < box.Length; j++)
  1774. {
  1775. if (box[j].Name.ToLower() == "ch_bluefilm" && !box[j].Checked)
  1776. {
  1777. if (DataTable.Columns[i].ColumnName.ToLower().Contains("ch_bluefilm"))
  1778. {
  1779. DataTable.Columns.RemoveAt(i);
  1780. break;
  1781. }
  1782. }
  1783. if (box[j].Name.ToLower() == "ch_code" && !box[j].Checked)
  1784. {
  1785. if (DataTable.Columns[i].ColumnName.ToLower().Contains("ch_code"))
  1786. {
  1787. DataTable.Columns.RemoveAt(i);
  1788. break;
  1789. }
  1790. }
  1791. if (box[j].Name.ToLower() == "ch_splitbatch" && !box[j].Checked)
  1792. {
  1793. if (DataTable.Columns[i].ColumnName.ToLower().Contains("ch_splitbatch"))
  1794. {
  1795. DataTable.Columns.RemoveAt(i);
  1796. break;
  1797. }
  1798. }
  1799. if (box[j].Name.ToLower() == "ch_waterid" && !box[j].Checked)
  1800. {
  1801. if (DataTable.Columns[i].ColumnName.ToLower().Contains("ch_waterid"))
  1802. {
  1803. DataTable.Columns.RemoveAt(i);
  1804. break;
  1805. }
  1806. }
  1807. if (box[j].Name.ToLower() == "ch_pbcode" && !box[j].Checked)
  1808. {
  1809. if (DataTable.Columns[i].ColumnName.ToLower().Contains("ch_pbcode"))
  1810. {
  1811. DataTable.Columns.RemoveAt(i);
  1812. break;
  1813. }
  1814. }
  1815. if (box[j].Name.ToLower() == "ch_remark" && !box[j].Checked)
  1816. {
  1817. if (DataTable.Columns[i].ColumnName.ToLower().Contains("ch_remark"))
  1818. {
  1819. DataTable.Columns.RemoveAt(i);
  1820. break;
  1821. }
  1822. }
  1823. if (box[j].Name.ToLower() == "ch_yield" && !box[j].Checked)
  1824. {
  1825. if (DataTable.Columns[i].ColumnName.ToLower().Contains("ch_yield"))
  1826. {
  1827. DataTable.Columns.RemoveAt(i);
  1828. break;
  1829. }
  1830. }
  1831. }
  1832. }
  1833. //获取行数量和列数量
  1834. int rowNum = DataTable.Rows.Count;
  1835. int columnNum = DataTable.Columns.Count;
  1836. //首先画好第一行带颜色的,单独写出来,避免写在循环里面
  1837. //ffont.FontHeight = 10;
  1838. //开始绘制的Index
  1839. int PaintIndex = 0;
  1840. int sumCount = 0;
  1841. int totalCount = 0;
  1842. //Title的字体
  1843. HSSFFont titlefont = (HSSFFont)book.CreateFont();
  1844. titlefont.FontName = "宋体";
  1845. titlefont.FontHeight = 270;
  1846. titlefont.Boldweight = (short)FontBoldWeight.Bold;
  1847. HSSFFont ColumnTitle = (HSSFFont)book.CreateFont();
  1848. ColumnTitle.FontName = "宋体";
  1849. ColumnTitle.Boldweight = (short)FontBoldWeight.Bold;
  1850. ICellStyle ColumnTitleStyle = book.CreateCellStyle();
  1851. ColumnTitleStyle.SetFont(ColumnTitle);
  1852. ColumnTitleStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
  1853. ColumnTitleStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
  1854. ColumnTitleStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
  1855. ColumnTitleStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
  1856. ICellStyle TitleStyle = book.CreateCellStyle();
  1857. TitleStyle.SetFont(titlefont);
  1858. int ShowColumnCount = 0;
  1859. IRow row1 = null;
  1860. switch (Type)
  1861. {
  1862. case "FixRow":
  1863. //清理系统取出来的数据
  1864. BaseUtil.CleanDataTableData(FirstDT);
  1865. //首页参数拼接
  1866. string First_OrderCode = "";
  1867. string First_Prspec = "";
  1868. string First_Batch = "";
  1869. ArrayList<string> First_WID = new ArrayList<string>();
  1870. for (int i = 0; i < rowNum; i++)
  1871. {
  1872. if (PaintIndex != 0)
  1873. {
  1874. row1 = sheet.CreateRow(PaintIndex);
  1875. PaintIndex = PaintIndex + 1;
  1876. row1.HeightInPoints = RowHeight;
  1877. }
  1878. //不包含的订单号
  1879. if (DataTable.Columns.Contains("pd_ordercode") && !First_OrderCode.Contains(DataTable.Rows[i]["pd_ordercode"].ToString()))
  1880. {
  1881. First_OrderCode += DataTable.Rows[i]["pd_ordercode"].ToString() + " ";
  1882. }
  1883. //不包含的物料型号
  1884. if (DataTable.Columns.Contains("pr_orispeccode") && !First_Prspec.Contains(DataTable.Rows[i]["pr_orispeccode"].ToString()))
  1885. {
  1886. First_Prspec += DataTable.Rows[i]["pr_orispeccode"].ToString() + " ";
  1887. }
  1888. //不包含扩撒批号
  1889. if (DataTable.Columns.Contains("ch_splitbatch") && !First_Batch.Contains(DataTable.Rows[i]["ch_splitbatch"].ToString()))
  1890. {
  1891. First_Batch += DataTable.Rows[i]["ch_splitbatch"].ToString() + " ";
  1892. }
  1893. //不包含Wafer_id
  1894. if (DataTable.Columns.Contains("Wafer_ID") && !First_WID.Contains(DataTable.Rows[i]["Wafer_ID"].ToString()))
  1895. {
  1896. First_WID.Add(DataTable.Rows[i]["Wafer_ID"].ToString());
  1897. }
  1898. if (i / PageSize >= 1 && i % PageSize == 0)
  1899. {
  1900. DataRow dr = FirstDT.NewRow();
  1901. dr["me_desc"] = DataTable.Rows[i]["me_desc"].ToString();
  1902. dr["pr_size"] = DataTable.Rows[i]["pr_size"].ToString();
  1903. dr["pi_chipouttype"] = DataTable.Rows[i]["pi_chipouttype"].ToString();
  1904. dr["ch_level"] = DataTable.Rows[i]["ch_level"].ToString();
  1905. dr["pr_orispeccode"] = DataTable.Rows[i]["pr_orispeccode"].ToString();
  1906. dr["pi_inoutno"] = DataTable.Rows[i]["pi_inoutno"].ToString();
  1907. pi_inoutno = DataTable.Rows[i]["pi_inoutno"].ToString();
  1908. dr["pi_title"] = DataTable.Rows[i]["pi_title"].ToString();
  1909. dr["pi_date"] = DataTable.Rows[i]["pi_date"].ToString();
  1910. dr["pd_ordercode"] = First_OrderCode;
  1911. dr["pr_orispeccode"] = First_Prspec;
  1912. dr["ch_splitbatch"] = First_Batch;
  1913. dr["ch_waterid"] = BaseUtil.GetArrStr(First_WID, " ");
  1914. dr["num"] = PageSize;
  1915. dr["io_qty"] = sumCount;
  1916. FirstDT.Rows.Add(dr);
  1917. First_OrderCode = "";
  1918. First_Prspec = "";
  1919. First_Batch = "";
  1920. First_WID.Clear();
  1921. BoxCode = BoxCode + 1;
  1922. for (int j = 0; j < ShowColumnCount; j++)
  1923. {
  1924. row1.CreateCell(j);
  1925. if (j == 0)
  1926. {
  1927. row1.Cells[j].SetCellValue(小计);
  1928. }
  1929. else if (j == ShowPieceIndex)
  1930. {
  1931. row1.Cells[ShowPieceIndex].SetCellValue((i % PageSize == 0 ? PageSize : i) + 片);
  1932. row1.Cells[1].CellStyle = ColumnTitleStyle;
  1933. }
  1934. else if (j == ShowCountIndex)
  1935. {
  1936. row1.Cells[j].SetCellValue(sumCount);
  1937. }
  1938. row1.Cells[j].CellStyle = styleborder;
  1939. }
  1940. sumCount = 0;
  1941. row1 = sheet.CreateRow(PaintIndex);
  1942. sheet.SetRowBreak(PaintIndex - 1);
  1943. sheet.Footer.Center = "第&P页,共&N页";
  1944. }
  1945. //每次到了页数开始分页
  1946. if (i % PageSize == 0 || i == rowNum - 1)
  1947. {
  1948. //第一行添加客户信息 rownum只有一行的情
  1949. if (i != rowNum - 1 || rowNum == 1 || (rowNum - i == rowNum % PageSize))
  1950. {
  1951. //抬头设置
  1952. row1 = sheet.CreateRow(PaintIndex);
  1953. PaintIndex = PaintIndex + 1;
  1954. row1.CreateCell(0).SetCellValue(" " + companyname);
  1955. row1.GetCell(0).CellStyle = TitleStyle;
  1956. row1 = sheet.CreateRow(PaintIndex);
  1957. PaintIndex = PaintIndex + 1;
  1958. for (int j = 0; j < columnNum - 3; j++)
  1959. {
  1960. if (j == 0)
  1961. {
  1962. row1.CreateCell(j);
  1963. row1.Cells[j].SetCellValue(DataTable.Columns["pi_title"].Caption + ":" + DataTable.Rows[i]["pi_title"].ToString() + " "
  1964. + DataTable.Columns["ch_level"].Caption + ":" + DataTable.Rows[i]["ch_level"].ToString());
  1965. }
  1966. else if (j == ShowDataIndex)
  1967. {
  1968. row1.CreateCell(j);
  1969. row1.Cells[j].SetCellValue(DataTable.Columns["pi_inoutno"].Caption + ":" + DataTable.Rows[i]["pi_inoutno"].ToString());
  1970. }
  1971. else
  1972. {
  1973. row1.CreateCell(j);
  1974. }
  1975. row1.GetCell(j).CellStyle = style;
  1976. }
  1977. row1 = sheet.CreateRow(PaintIndex);
  1978. PaintIndex = PaintIndex + 1;
  1979. //第二行添加型号
  1980. for (int j = 0; j < columnNum - 3; j++)
  1981. {
  1982. if (j == 0)
  1983. {
  1984. row1.CreateCell(j);
  1985. row1.Cells[j].SetCellValue(DataTable.Columns["pr_orispeccode"].Caption + ":" + DataTable.Rows[i]["pr_orispeccode"].ToString() + " "
  1986. + DataTable.Columns["pi_chipouttype"].Caption + ":" + DataTable.Rows[i]["pi_chipouttype"].ToString());
  1987. }
  1988. else if (j == ShowSizeIndex)
  1989. {
  1990. row1.CreateCell(j);
  1991. row1.Cells[j].SetCellValue(DataTable.Columns["pr_size"].Caption + ":" + DataTable.Rows[i]["pr_size"].ToString());
  1992. }
  1993. else if (j == ShowDataIndex)
  1994. {
  1995. row1.CreateCell(j);
  1996. row1.Cells[j].SetCellValue(DataTable.Columns["pi_date"].Caption + ":" + DataTable.Rows[i]["pi_date"].ToString());
  1997. }
  1998. else
  1999. {
  2000. row1.CreateCell(j);
  2001. }
  2002. row1.GetCell(j).CellStyle = style;
  2003. }
  2004. //特殊客户添加一列空行
  2005. row1 = sheet.CreateRow(PaintIndex);
  2006. PaintIndex = PaintIndex + 1;
  2007. for (int j = 0; j < columnNum; j++)
  2008. {
  2009. row1.CreateCell(j).SetCellValue("");
  2010. }
  2011. //添加列名
  2012. row1 = sheet.CreateRow(PaintIndex);
  2013. PaintIndex = PaintIndex + 1;
  2014. //计数列所在的索引
  2015. int showcount = 0;
  2016. //前面有4列标题内容,数据库设置的,需要从4开始索引
  2017. for (int j = 4; j < columnNum; j++)
  2018. {
  2019. if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent1")
  2020. {
  2021. showcount = showcount + 1;
  2022. row1.CreateCell(j - 4);
  2023. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  2024. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname1"].ToString());
  2025. }
  2026. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent2")
  2027. {
  2028. showcount = showcount + 1;
  2029. row1.CreateCell(j - 4);
  2030. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  2031. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname2"].ToString());
  2032. }
  2033. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent3")
  2034. {
  2035. showcount = showcount + 1;
  2036. row1.CreateCell(j - 4);
  2037. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  2038. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname3"].ToString());
  2039. }
  2040. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent4")
  2041. {
  2042. showcount = showcount + 1;
  2043. row1.CreateCell(j - 4);
  2044. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  2045. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname4"].ToString());
  2046. }
  2047. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent5")
  2048. {
  2049. showcount = showcount + 1;
  2050. row1.CreateCell(j - 4);
  2051. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  2052. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname5"].ToString());
  2053. }
  2054. else if ((NotShowColumn.Contains(DataTable.Columns[j].ColumnName.ToLower())))
  2055. {
  2056. }
  2057. else
  2058. {
  2059. showcount = showcount + 1;
  2060. row1.CreateCell(j - 4);
  2061. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  2062. row1.Cells[j - 4].SetCellValue(DataTable.Columns[j].Caption);
  2063. }
  2064. if (DataTable.Columns[j].ColumnName.ToString() == "io_qty")
  2065. {
  2066. NumIndex = j - 4;
  2067. }
  2068. }
  2069. ShowColumnCount = showcount;
  2070. row1 = sheet.CreateRow(PaintIndex);
  2071. PaintIndex = PaintIndex + 1;
  2072. }
  2073. }
  2074. //添加数据内容
  2075. for (int j = 4; j < columnNum; j++)
  2076. {
  2077. string Data = DataTable.Rows[i][j].ToString();
  2078. row1.CreateCell(j - 4);
  2079. if (DataTable.Columns[j].ColumnName.ToLower() == "ch_bluefilm")
  2080. {
  2081. row1.CreateCell(j - 4, CellType.String);
  2082. }
  2083. if ((NotShowColumn.Contains(DataTable.Columns[j].ColumnName.ToLower())))
  2084. {
  2085. }
  2086. else
  2087. {
  2088. double Num = 0;
  2089. if (double.TryParse(Data, out Num)&& DataTable.Columns[j].ColumnName.ToLower() != "ch_bluefilm")
  2090. {
  2091. row1.Cells[j - 4].SetCellValue(Num);
  2092. }
  2093. else
  2094. {
  2095. row1.Cells[j - 4].SetCellValue(Data);
  2096. }
  2097. row1.Cells[j - 4].CellStyle = styleborder;
  2098. }
  2099. if (DataTable.Columns[j].ColumnName == "io_qty")
  2100. {
  2101. row1.Cells[j - 4].SetCellValue(int.Parse(Data));
  2102. sumCount += int.Parse(DataTable.Rows[i][j].ToString());
  2103. totalCount += int.Parse(DataTable.Rows[i][j].ToString());
  2104. }
  2105. if (DataTable.Columns[j].ColumnName == "rownum")
  2106. {
  2107. row1.Cells[j - 4].SetCellValue(i + 1);
  2108. }
  2109. if (DataTable.Columns[j].ColumnName == "ch_code")
  2110. {
  2111. sheet.AutoSizeColumn(j - 4);
  2112. sheet.SetColumnWidth(j - 4, sheet.GetColumnWidth(j - 4) + 1000);
  2113. }
  2114. }
  2115. //固定行号分组的时候自动拼接新的DataTable
  2116. if (i == rowNum - 1)
  2117. {
  2118. DataRow dr = FirstDT.NewRow();
  2119. dr["me_desc"] = DataTable.Rows[i]["me_desc"].ToString();
  2120. dr["pr_size"] = DataTable.Rows[i]["pr_size"].ToString();
  2121. dr["pi_chipouttype"] = DataTable.Rows[i]["pi_chipouttype"].ToString();
  2122. dr["ch_level"] = DataTable.Rows[i]["ch_level"].ToString();
  2123. dr["pr_orispeccode"] = DataTable.Rows[i]["pr_orispeccode"].ToString();
  2124. dr["pi_inoutno"] = DataTable.Rows[i]["pi_inoutno"].ToString();
  2125. dr["pi_title"] = DataTable.Rows[i]["pi_title"].ToString();
  2126. dr["pi_date"] = DataTable.Rows[i]["pi_date"].ToString();
  2127. dr["pd_ordercode"] = First_OrderCode;
  2128. dr["pr_orispeccode"] = First_Prspec;
  2129. dr["ch_splitbatch"] = First_Batch;
  2130. dr["ch_waterid"] = BaseUtil.GetArrStr(First_WID, " ");
  2131. dr["num"] = (i % PageSize) + 1;
  2132. dr["io_qty"] = sumCount;
  2133. FirstDT.Rows.Add(dr);
  2134. row1 = sheet.CreateRow(PaintIndex);
  2135. PaintIndex = PaintIndex + 1;
  2136. for (int j = 0; j < ShowColumnCount; j++)
  2137. {
  2138. row1.CreateCell(j);
  2139. if (j == 0)
  2140. {
  2141. row1.Cells[j].SetCellValue(小计);
  2142. }
  2143. else if (j == ShowPieceIndex)
  2144. {
  2145. row1.Cells[ShowPieceIndex].SetCellValue((rowNum % PageSize == 0 ? PageSize : (rowNum % PageSize)) + 片);
  2146. row1.Cells[1].CellStyle = ColumnTitleStyle;
  2147. }
  2148. else if (j == ShowCountIndex)
  2149. {
  2150. row1.Cells[j].SetCellValue(sumCount);
  2151. }
  2152. row1.Cells[j].CellStyle = styleborder;
  2153. }
  2154. row1 = sheet.CreateRow(PaintIndex);
  2155. for (int j = 0; j < columnNum - 3; j++)
  2156. {
  2157. if (j == 0)
  2158. {
  2159. row1.CreateCell(j);
  2160. row1.Cells[j].SetCellValue("备注");
  2161. }
  2162. else if (j == ShowCountIndex)
  2163. {
  2164. row1.CreateCell(j);
  2165. row1.Cells[j].SetCellValue(totalCount);
  2166. }
  2167. else if (j == ShowRemarkPiece)
  2168. {
  2169. row1.CreateCell(j);
  2170. row1.Cells[j].SetCellValue(rowNum + 片);
  2171. }
  2172. else
  2173. {
  2174. row1.CreateCell(j);
  2175. }
  2176. row1.Cells[j].CellStyle = style;
  2177. }
  2178. sheet.SetRowBreak(PaintIndex);
  2179. sheet.Footer.Center = "第&P页,共&N页";
  2180. }
  2181. pib_id.Add(DataTable.Rows[i]["pib_id"].ToString());
  2182. pib_outboxcode1.Add(BoxCode.ToString());
  2183. }
  2184. break;
  2185. case "BatchCode":
  2186. string LastBatchCode = "";
  2187. int PageNum = 0;
  2188. for (int i = 0; i < rowNum; i++)
  2189. {
  2190. row1 = sheet.CreateRow(PaintIndex);
  2191. if (i != 0)
  2192. PaintIndex = PaintIndex + 1;
  2193. row1.HeightInPoints = RowHeight;
  2194. //如果批号不相等的时候
  2195. if (LastBatchCode != "" && LastBatchCode != DataTable.Rows[i]["ch_splitbatch"].ToString())
  2196. {
  2197. BoxCode = BoxCode + 1;
  2198. for (int j = 0; j < ShowColumnCount; j++)
  2199. {
  2200. row1.CreateCell(j);
  2201. if (j == 0)
  2202. {
  2203. row1.Cells[j].SetCellValue(小计);
  2204. row1.Cells[j].CellStyle = styleborder;
  2205. }
  2206. else if (j == ShowPieceIndex)
  2207. {
  2208. row1.Cells[ShowPieceIndex].SetCellValue(PageNum + 片);
  2209. row1.Cells[1].CellStyle = ColumnTitleStyle;
  2210. }
  2211. else if (j == ShowCountIndex)
  2212. {
  2213. row1.Cells[j].SetCellValue(sumCount);
  2214. row1.Cells[j].CellStyle = styleborder;
  2215. }
  2216. else
  2217. {
  2218. row1.Cells[j].CellStyle = styleborder;
  2219. }
  2220. }
  2221. sumCount = 0;
  2222. //row1 = sheet.CreateRow(PaintIndex);
  2223. sheet.SetRowBreak(PaintIndex - 1);
  2224. sheet.Footer.Center = "第&P页,共&N页";
  2225. PageNum = 0;
  2226. }
  2227. //每次到了页数开始分页
  2228. if (LastBatchCode == "" || (LastBatchCode != "" && LastBatchCode != DataTable.Rows[i]["ch_splitbatch"].ToString()))
  2229. {
  2230. LastBatchCode = DataTable.Rows[i]["ch_splitbatch"].ToString();
  2231. //第一行添加客户信息
  2232. row1 = sheet.CreateRow(PaintIndex);
  2233. PaintIndex = PaintIndex + 1;
  2234. row1.CreateCell(0).SetCellValue(" " + companyname);
  2235. row1.GetCell(0).CellStyle = TitleStyle;
  2236. row1 = sheet.CreateRow(PaintIndex);
  2237. PaintIndex = PaintIndex + 1;
  2238. //if (i != rowNum - 1)
  2239. {
  2240. for (int j = 0; j < columnNum - 3; j++)
  2241. {
  2242. if (j == 0)
  2243. {
  2244. row1.CreateCell(j);
  2245. row1.Cells[j].SetCellValue(DataTable.Columns["pi_title"].Caption + ":" + DataTable.Rows[i]["pi_title"].ToString() + " "
  2246. + DataTable.Columns["ch_level"].Caption + ":" + DataTable.Rows[i]["ch_level"].ToString());
  2247. }
  2248. else if (j == ShowDataIndex)
  2249. {
  2250. row1.CreateCell(j);
  2251. row1.Cells[j].SetCellValue(DataTable.Columns["pi_inoutno"].Caption + ":" + DataTable.Rows[i]["pi_inoutno"].ToString());
  2252. }
  2253. else
  2254. {
  2255. row1.CreateCell(j);
  2256. }
  2257. row1.GetCell(j).CellStyle = style;
  2258. }
  2259. row1 = sheet.CreateRow(PaintIndex);
  2260. PaintIndex = PaintIndex + 1;
  2261. //第二行添加型号
  2262. for (int j = 0; j < columnNum - 3; j++)
  2263. {
  2264. if (j == 0)
  2265. {
  2266. row1.CreateCell(j);
  2267. row1.Cells[j].SetCellValue(DataTable.Columns["pr_orispeccode"].Caption + ":" + DataTable.Rows[i]["pr_orispeccode"].ToString() + " "
  2268. + DataTable.Columns["pi_chipouttype"].Caption + ":" + DataTable.Rows[i]["pi_chipouttype"].ToString());
  2269. }
  2270. else if (j == ShowSizeIndex)
  2271. {
  2272. row1.CreateCell(j);
  2273. row1.Cells[j].SetCellValue(DataTable.Columns["pr_size"].Caption + ":" + DataTable.Rows[i]["pr_size"].ToString());
  2274. }
  2275. else if (j == ShowDataIndex)
  2276. {
  2277. row1.CreateCell(j);
  2278. row1.Cells[j].SetCellValue(DataTable.Columns["pi_date"].Caption + ":" + DataTable.Rows[i]["pi_date"].ToString());
  2279. }
  2280. else
  2281. {
  2282. row1.CreateCell(j);
  2283. }
  2284. row1.GetCell(j).CellStyle = style;
  2285. }
  2286. //特殊客户添加一列空行
  2287. row1 = sheet.CreateRow(PaintIndex);
  2288. PaintIndex = PaintIndex + 1;
  2289. for (int j = 0; j < columnNum; j++)
  2290. {
  2291. row1.CreateCell(j).SetCellValue("");
  2292. }
  2293. //添加列名
  2294. row1 = sheet.CreateRow(PaintIndex);
  2295. PaintIndex = PaintIndex + 1;
  2296. int showcount = 0;
  2297. for (int j = 4; j < columnNum; j++)
  2298. {
  2299. if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent1")
  2300. {
  2301. showcount = showcount + 1;
  2302. row1.CreateCell(j - 4);
  2303. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  2304. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname1"].ToString());
  2305. }
  2306. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent2")
  2307. {
  2308. showcount = showcount + 1;
  2309. row1.CreateCell(j - 4);
  2310. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  2311. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname2"].ToString());
  2312. }
  2313. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent3")
  2314. {
  2315. showcount = showcount + 1;
  2316. row1.CreateCell(j - 4);
  2317. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  2318. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname3"].ToString());
  2319. }
  2320. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent4")
  2321. {
  2322. showcount = showcount + 1;
  2323. row1.CreateCell(j - 4);
  2324. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  2325. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname4"].ToString());
  2326. }
  2327. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent5")
  2328. {
  2329. showcount = showcount + 1;
  2330. row1.CreateCell(j - 4);
  2331. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  2332. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname5"].ToString());
  2333. }
  2334. else if ((NotShowColumn.Contains(DataTable.Columns[j].ColumnName.ToLower())))
  2335. {
  2336. }
  2337. else
  2338. {
  2339. showcount = showcount + 1;
  2340. row1.CreateCell(j - 4);
  2341. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  2342. row1.Cells[j - 4].SetCellValue(DataTable.Columns[j].Caption);
  2343. }
  2344. if (DataTable.Columns[j].ColumnName.ToString() == "io_qty")
  2345. {
  2346. NumIndex = j - 4;
  2347. }
  2348. }
  2349. ShowColumnCount = showcount;
  2350. row1 = sheet.CreateRow(PaintIndex);
  2351. PaintIndex = PaintIndex + 1;
  2352. }
  2353. }
  2354. //添加数据内容
  2355. for (int j = 4; j < columnNum; j++)
  2356. {
  2357. string Data = DataTable.Rows[i][j].ToString();
  2358. row1.CreateCell(j - 4);
  2359. if (DataTable.Columns[j].ColumnName.ToLower() == "ch_bluefilm")
  2360. {
  2361. row1.CreateCell(j - 4, CellType.String);
  2362. }
  2363. if ((NotShowColumn.Contains(DataTable.Columns[j].ColumnName.ToLower())))
  2364. {
  2365. }
  2366. else
  2367. {
  2368. double Num = 0;
  2369. if (double.TryParse(Data, out Num)&& DataTable.Columns[j].ColumnName.ToLower() != "ch_bluefilm")
  2370. {
  2371. row1.Cells[j - 4].SetCellValue(Num);
  2372. }
  2373. else
  2374. {
  2375. row1.Cells[j - 4].SetCellValue(Data);
  2376. }
  2377. row1.Cells[j - 4].CellStyle = styleborder;
  2378. }
  2379. if (DataTable.Columns[j].ColumnName == "io_qty")
  2380. {
  2381. row1.Cells[j - 4].SetCellValue(int.Parse(Data));
  2382. sumCount += int.Parse(DataTable.Rows[i][j].ToString());
  2383. totalCount += int.Parse(DataTable.Rows[i][j].ToString());
  2384. }
  2385. if (DataTable.Columns[j].ColumnName == "rownum")
  2386. {
  2387. row1.Cells[j - 4].SetCellValue(i + 1);
  2388. }
  2389. if (DataTable.Columns[j].ColumnName == "ch_code")
  2390. {
  2391. sheet.AutoSizeColumn(j - 4);
  2392. sheet.SetColumnWidth(j - 4, sheet.GetColumnWidth(j - 4) + 1000);
  2393. }
  2394. }
  2395. if (i == rowNum - 1)
  2396. {
  2397. row1 = sheet.CreateRow(PaintIndex);
  2398. PaintIndex = PaintIndex + 1;
  2399. for (int j = 0; j < ShowColumnCount; j++)
  2400. {
  2401. row1.CreateCell(j);
  2402. if (j == 0)
  2403. {
  2404. row1.Cells[j].SetCellValue(小计);
  2405. row1.Cells[j].CellStyle = styleborder;
  2406. }
  2407. else if (j == ShowPieceIndex)
  2408. {
  2409. row1.Cells[ShowPieceIndex].SetCellValue(PageNum + 片);
  2410. row1.Cells[1].CellStyle = ColumnTitleStyle;
  2411. }
  2412. else if (j == ShowCountIndex)
  2413. {
  2414. row1.Cells[j].SetCellValue(sumCount);
  2415. row1.Cells[j].CellStyle = styleborder;
  2416. }
  2417. else
  2418. {
  2419. row1.Cells[j].CellStyle = styleborder;
  2420. }
  2421. }
  2422. //创建备注内容
  2423. row1 = sheet.CreateRow(PaintIndex);
  2424. for (int j = 0; j < columnNum - 3; j++)
  2425. {
  2426. if (j == 0)
  2427. {
  2428. row1.CreateCell(j);
  2429. row1.Cells[j].SetCellValue("备注");
  2430. }
  2431. else if (j == ShowCountIndex)
  2432. {
  2433. row1.CreateCell(j);
  2434. row1.Cells[j].SetCellValue(totalCount);
  2435. }
  2436. else if (j == ShowRemarkPiece)
  2437. {
  2438. row1.CreateCell(j);
  2439. row1.Cells[j].SetCellValue(rowNum + 片);
  2440. }
  2441. else
  2442. {
  2443. row1.CreateCell(j);
  2444. }
  2445. row1.Cells[j].CellStyle = style;
  2446. }
  2447. sheet.SetRowBreak(PaintIndex);
  2448. sheet.Footer.Center = "第&P页,共&N页";
  2449. PageNum = 0;
  2450. }
  2451. PageNum = PageNum + 1;
  2452. pib_id.Add(DataTable.Rows[i]["pib_id"].ToString());
  2453. pib_outboxcode1.Add(BoxCode.ToString());
  2454. }
  2455. break;
  2456. case "BoxCode":
  2457. string LastBoxCode = "";
  2458. int PageNum1 = 0;
  2459. for (int i = 0; i < rowNum; i++)
  2460. {
  2461. row1 = sheet.CreateRow(PaintIndex);
  2462. if (i != 0)
  2463. PaintIndex = PaintIndex + 1;
  2464. row1.HeightInPoints = RowHeight;
  2465. //如果箱号不相等的时候
  2466. if (LastBoxCode != "" && LastBoxCode != DataTable.Rows[i]["CH_PBCODE"].ToString())
  2467. {
  2468. BoxCode = BoxCode + 1;
  2469. for (int j = 0; j < ShowColumnCount; j++)
  2470. {
  2471. row1.CreateCell(j);
  2472. if (j == 0)
  2473. {
  2474. row1.Cells[j].SetCellValue(小计);
  2475. row1.Cells[j].CellStyle = styleborder;
  2476. }
  2477. else if (j == ShowPieceIndex)
  2478. {
  2479. row1.Cells[ShowPieceIndex].SetCellValue(PageNum1 + 片);
  2480. row1.Cells[1].CellStyle = ColumnTitleStyle;
  2481. }
  2482. else if (j == ShowCountIndex)
  2483. {
  2484. row1.Cells[j].SetCellValue(sumCount);
  2485. row1.Cells[j].CellStyle = styleborder;
  2486. }
  2487. else
  2488. {
  2489. row1.Cells[j].CellStyle = styleborder;
  2490. }
  2491. }
  2492. sumCount = 0;
  2493. //row1 = sheet.CreateRow(PaintIndex);
  2494. sheet.SetRowBreak(PaintIndex - 1);
  2495. sheet.Footer.Center = "第&P页,共&N页";
  2496. PageNum1 = 0;
  2497. }
  2498. //每次到了页数开始分页
  2499. if (LastBoxCode == "" || (LastBoxCode != "" && LastBoxCode != DataTable.Rows[i]["ch_pbcode"].ToString()) || ((i == rowNum - 1) && (LastBoxCode != "" && LastBoxCode != DataTable.Rows[i]["ch_pbcode"].ToString())))
  2500. {
  2501. LastBoxCode = DataTable.Rows[i]["CH_PBCODE"].ToString();
  2502. row1 = sheet.CreateRow(PaintIndex);
  2503. PaintIndex = PaintIndex + 1;
  2504. row1.CreateCell(0).SetCellValue(" " + companyname);
  2505. row1.GetCell(0).CellStyle = TitleStyle;
  2506. row1 = sheet.CreateRow(PaintIndex);
  2507. PaintIndex = PaintIndex + 1;
  2508. //第一行添加客户信息
  2509. //if (i != rowNum - 1)
  2510. {
  2511. for (int j = 0; j < columnNum - 3; j++)
  2512. {
  2513. if (j == 0)
  2514. {
  2515. row1.CreateCell(j);
  2516. row1.Cells[j].SetCellValue(DataTable.Columns["pi_title"].Caption + ":" + DataTable.Rows[i]["pi_title"].ToString() + " "
  2517. + DataTable.Columns["ch_level"].Caption + ":" + DataTable.Rows[i]["ch_level"].ToString());
  2518. }
  2519. else if (j == ShowDataIndex)
  2520. {
  2521. row1.CreateCell(j);
  2522. row1.Cells[j].SetCellValue(DataTable.Columns["pi_inoutno"].Caption + ":" + DataTable.Rows[i]["pi_inoutno"].ToString());
  2523. }
  2524. else
  2525. {
  2526. row1.CreateCell(j);
  2527. }
  2528. row1.GetCell(j).CellStyle = style;
  2529. }
  2530. row1 = sheet.CreateRow(PaintIndex);
  2531. PaintIndex = PaintIndex + 1;
  2532. //第二行添加型号
  2533. for (int j = 0; j < columnNum - 3; j++)
  2534. {
  2535. if (j == 0)
  2536. {
  2537. row1.CreateCell(j);
  2538. row1.Cells[j].SetCellValue(DataTable.Columns["pr_orispeccode"].Caption + ":" + DataTable.Rows[i]["pr_orispeccode"].ToString() + " "
  2539. + DataTable.Columns["pi_chipouttype"].Caption + ":" + DataTable.Rows[i]["pi_chipouttype"].ToString());
  2540. }
  2541. else if (j == ShowSizeIndex)
  2542. {
  2543. row1.CreateCell(j);
  2544. row1.Cells[j].SetCellValue(DataTable.Columns["pr_size"].Caption + ":" + DataTable.Rows[i]["pr_size"].ToString());
  2545. }
  2546. else if (j == ShowDataIndex)
  2547. {
  2548. row1.CreateCell(j);
  2549. row1.Cells[j].SetCellValue(DataTable.Columns["pi_date"].Caption + ":" + DataTable.Rows[i]["pi_date"].ToString());
  2550. }
  2551. else
  2552. {
  2553. row1.CreateCell(j);
  2554. }
  2555. row1.GetCell(j).CellStyle = style;
  2556. }
  2557. //特殊客户添加一列空行
  2558. row1 = sheet.CreateRow(PaintIndex);
  2559. PaintIndex = PaintIndex + 1;
  2560. for (int j = 0; j < columnNum; j++)
  2561. {
  2562. row1.CreateCell(j).SetCellValue("");
  2563. }
  2564. //添加列名
  2565. row1 = sheet.CreateRow(PaintIndex);
  2566. PaintIndex = PaintIndex + 1;
  2567. int showcount = 0;
  2568. for (int j = 4; j < columnNum; j++)
  2569. {
  2570. if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent1")
  2571. {
  2572. showcount = showcount + 1;
  2573. row1.CreateCell(j - 4);
  2574. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  2575. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname1"].ToString());
  2576. }
  2577. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent2")
  2578. {
  2579. showcount = showcount + 1;
  2580. row1.CreateCell(j - 4);
  2581. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  2582. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname2"].ToString());
  2583. }
  2584. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent3")
  2585. {
  2586. showcount = showcount + 1;
  2587. row1.CreateCell(j - 4);
  2588. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  2589. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname3"].ToString());
  2590. }
  2591. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent4")
  2592. {
  2593. showcount = showcount + 1;
  2594. row1.CreateCell(j - 4);
  2595. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  2596. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname4"].ToString());
  2597. }
  2598. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent5")
  2599. {
  2600. showcount = showcount + 1;
  2601. row1.CreateCell(j - 4);
  2602. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  2603. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname5"].ToString());
  2604. }
  2605. else if ((NotShowColumn.Contains(DataTable.Columns[j].ColumnName.ToLower())))
  2606. {
  2607. }
  2608. else
  2609. {
  2610. showcount = showcount + 1;
  2611. row1.CreateCell(j - 4);
  2612. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  2613. row1.Cells[j - 4].SetCellValue(DataTable.Columns[j].Caption);
  2614. }
  2615. if (DataTable.Columns[j].ColumnName.ToString() == "io_qty")
  2616. {
  2617. NumIndex = j - 4;
  2618. }
  2619. }
  2620. ShowColumnCount = showcount;
  2621. row1 = sheet.CreateRow(PaintIndex);
  2622. PaintIndex = PaintIndex + 1;
  2623. }
  2624. }
  2625. //添加数据内容
  2626. for (int j = 4; j < columnNum; j++)
  2627. {
  2628. string Data = DataTable.Rows[i][j].ToString();
  2629. row1.CreateCell(j - 4);
  2630. if (DataTable.Columns[j].ColumnName.ToLower() == "ch_bluefilm")
  2631. {
  2632. row1.CreateCell(j - 4, CellType.String);
  2633. }
  2634. if ((NotShowColumn.Contains(DataTable.Columns[j].ColumnName.ToLower())))
  2635. {
  2636. }
  2637. else
  2638. {
  2639. double Num = 0;
  2640. if (double.TryParse(Data, out Num)&& DataTable.Columns[j].ColumnName.ToLower() != "ch_bluefilm")
  2641. {
  2642. row1.Cells[j - 4].SetCellValue(Num);
  2643. }
  2644. else
  2645. {
  2646. row1.Cells[j - 4].SetCellValue(Data);
  2647. }
  2648. row1.Cells[j - 4].CellStyle = styleborder;
  2649. }
  2650. if (DataTable.Columns[j].ColumnName == "io_qty")
  2651. {
  2652. row1.Cells[j - 4].SetCellValue(int.Parse(Data));
  2653. sumCount += int.Parse(DataTable.Rows[i][j].ToString());
  2654. totalCount += int.Parse(DataTable.Rows[i][j].ToString());
  2655. }
  2656. if (DataTable.Columns[j].ColumnName == "rownum")
  2657. {
  2658. row1.Cells[j - 4].SetCellValue(i + 1);
  2659. }
  2660. if (DataTable.Columns[j].ColumnName == "ch_code")
  2661. {
  2662. sheet.AutoSizeColumn(j - 4);
  2663. sheet.SetColumnWidth(j - 4, sheet.GetColumnWidth(j - 4) + 1000);
  2664. }
  2665. }
  2666. if (i == rowNum - 1)
  2667. {
  2668. PageNum1 = PageNum1 + 1;
  2669. row1 = sheet.CreateRow(PaintIndex);
  2670. PaintIndex = PaintIndex + 1;
  2671. for (int j = 0; j < ShowColumnCount; j++)
  2672. {
  2673. row1.CreateCell(j);
  2674. if (j == 0)
  2675. {
  2676. row1.Cells[j].SetCellValue(小计);
  2677. row1.Cells[j].CellStyle = styleborder;
  2678. }
  2679. else if (j == ShowPieceIndex)
  2680. {
  2681. row1.Cells[ShowPieceIndex].SetCellValue(PageNum1 + 片);
  2682. row1.Cells[1].CellStyle = ColumnTitleStyle;
  2683. }
  2684. else if (j == ShowCountIndex)
  2685. {
  2686. row1.Cells[j].SetCellValue(sumCount);
  2687. row1.Cells[j].CellStyle = styleborder;
  2688. }
  2689. else
  2690. {
  2691. row1.Cells[j].CellStyle = styleborder;
  2692. }
  2693. }
  2694. row1 = sheet.CreateRow(PaintIndex);
  2695. for (int j = 0; j < columnNum - 3; j++)
  2696. {
  2697. if (j == 0)
  2698. {
  2699. row1.CreateCell(j);
  2700. row1.Cells[j].SetCellValue("备注");
  2701. }
  2702. else if (j == ShowCountIndex)
  2703. {
  2704. row1.CreateCell(j);
  2705. row1.Cells[j].SetCellValue(totalCount);
  2706. }
  2707. else if (j == ShowRemarkPiece)
  2708. {
  2709. row1.CreateCell(j);
  2710. row1.Cells[j].SetCellValue(rowNum + 片);
  2711. }
  2712. else
  2713. {
  2714. row1.CreateCell(j);
  2715. }
  2716. row1.Cells[j].CellStyle = style;
  2717. }
  2718. sheet.SetRowBreak(PaintIndex);
  2719. sheet.Footer.Center = "第&P页,共&N页";
  2720. PageNum1 = 0;
  2721. }
  2722. PageNum1 = PageNum1 + 1;
  2723. pib_id.Add(DataTable.Rows[i]["pib_id"].ToString());
  2724. pib_outboxcode1.Add(BoxCode.ToString());
  2725. }
  2726. break;
  2727. default:
  2728. break;
  2729. }
  2730. dh.BatchInsert("update prodiobarcode set pib_outboxcode1=:pib_outboxcode1 where pib_inoutno='" + Inoutno + "' and pib_id=:pib_id", new string[] { "pib_outboxcode1", "pib_id" }, pib_outboxcode1.ToArray(), pib_id.ToArray());
  2731. //删除下载链接再重新插入
  2732. HttpHandler.GenDownLoadLinK(Inoutno);
  2733. //填充首页
  2734. sumCount = 0;
  2735. totalCount = 0;
  2736. PaintIndex = 1;
  2737. ISheet sheet2 = book.CreateSheet("首页");
  2738. IRow row = sheet2.CreateRow(0);
  2739. row.CreateCell(0);
  2740. row.Cells[0].SetCellValue(" " + companyname);
  2741. row.GetCell(0).CellStyle = style;
  2742. rowNum = FirstDT.Rows.Count;
  2743. //不需要显示的列移除
  2744. for (int i = FirstDT.Columns.Count - 1; i > 0; i--)
  2745. {
  2746. for (int j = 0; j < box.Length; j++)
  2747. {
  2748. if (box[j].Name == "FirstPage_WID" && !box[j].Checked)
  2749. {
  2750. if (FirstDT.Columns[i].ColumnName.ToLower().Contains("ch_waterid"))
  2751. {
  2752. FirstDT.Columns.RemoveAt(i);
  2753. }
  2754. }
  2755. if (box[j].Name == "FirstPage_YIELD" && !box[j].Checked)
  2756. {
  2757. if (FirstDT.Columns[i].ColumnName.ToLower().Contains("ch_yeild"))
  2758. {
  2759. FirstDT.Columns.RemoveAt(i);
  2760. }
  2761. }
  2762. if (box[j].Name == "FirstPage_REMARK" && !box[j].Checked)
  2763. {
  2764. if (FirstDT.Columns[i].ColumnName.ToLower().Contains("ch_remark"))
  2765. {
  2766. FirstDT.Columns.RemoveAt(i);
  2767. }
  2768. }
  2769. if (box[j].Name == "FirstPage_BOXCODE" && !box[j].Checked)
  2770. {
  2771. if (FirstDT.Columns[i].ColumnName.ToLower().Contains("ch_pbcode"))
  2772. {
  2773. FirstDT.Columns.RemoveAt(i);
  2774. }
  2775. }
  2776. }
  2777. }
  2778. columnNum = FirstDT.Columns.Count;
  2779. for (int i = 0; i < rowNum; i++)
  2780. {
  2781. row1 = sheet2.CreateRow(PaintIndex);
  2782. PaintIndex = PaintIndex + 1;
  2783. row1.HeightInPoints = RowHeight;
  2784. //只需要绘制一行
  2785. if (i == 0)
  2786. {
  2787. for (int j = 0; j < columnNum - 3; j++)
  2788. {
  2789. if (j == 0)
  2790. {
  2791. row1.CreateCell(j);
  2792. row1.Cells[j].SetCellValue(FirstDT.Columns["pi_title"].Caption + ":" + FirstDT.Rows[i]["pi_title"].ToString() + " "
  2793. + FirstDT.Columns["ch_level"].Caption + ":" + FirstDT.Rows[i]["ch_level"].ToString());
  2794. }
  2795. else if (j > 5 && j == columnNum - 5)
  2796. {
  2797. row1.CreateCell(j);
  2798. row1.Cells[j].SetCellValue(FirstDT.Columns["pi_inoutno"].Caption + ":" + FirstDT.Rows[i]["pi_inoutno"].ToString());
  2799. }
  2800. else if (columnNum > 5 && j == columnNum - 5)
  2801. {
  2802. row1.CreateCell(j);
  2803. row1.Cells[j].SetCellValue(FirstDT.Columns["pi_inoutno"].Caption + ":" + FirstDT.Rows[i]["pi_inoutno"].ToString());
  2804. }
  2805. else
  2806. {
  2807. row1.CreateCell(j);
  2808. }
  2809. row1.GetCell(j).CellStyle = style;
  2810. }
  2811. row1 = sheet2.CreateRow(PaintIndex);
  2812. PaintIndex = PaintIndex + 1;
  2813. //第二行添加型号
  2814. for (int j = 0; j < columnNum - 3; j++)
  2815. {
  2816. if (j == 0)
  2817. {
  2818. row1.CreateCell(j).SetCellValue(DataTable.Columns["pr_orispeccode"].Caption + ":" + DataTable.Rows[i]["pr_orispeccode"].ToString() + " "
  2819. + DataTable.Columns["pi_chipouttype"].Caption + ":" + DataTable.Rows[i]["pi_chipouttype"].ToString());
  2820. }
  2821. else if (j == ShowSizeIndex)
  2822. {
  2823. row1.CreateCell(j);
  2824. row1.Cells[j].SetCellValue(FirstDT.Columns["pr_size"].Caption + ":" + FirstDT.Rows[i]["pr_size"].ToString());
  2825. }
  2826. else if (j > 5 && j == columnNum - 5)
  2827. {
  2828. row1.CreateCell(j);
  2829. row1.Cells[j].SetCellValue(FirstDT.Columns["pi_date"].Caption + ":" + FirstDT.Rows[i]["pi_date"].ToString());
  2830. }
  2831. else if (columnNum > 5 && j == columnNum - 5)
  2832. {
  2833. row1.CreateCell(j);
  2834. row1.Cells[j].SetCellValue(FirstDT.Columns["pi_date"].Caption + ":" + FirstDT.Rows[i]["pi_date"].ToString());
  2835. }
  2836. else
  2837. {
  2838. row1.CreateCell(j);
  2839. }
  2840. row1.GetCell(j).CellStyle = style;
  2841. }
  2842. row1 = sheet2.CreateRow(PaintIndex);
  2843. PaintIndex = PaintIndex + 1;
  2844. //添加列名
  2845. for (int j = 4; j < columnNum; j++)
  2846. {
  2847. row1.CreateCell(j - 4);
  2848. if (NotShowColumn.Contains(FirstDT.Columns[j].ColumnName.ToLower()))
  2849. {
  2850. }
  2851. else
  2852. {
  2853. row1.Cells[j - 4].CellStyle = styleborder;
  2854. row1.Cells[j - 4].SetCellValue(FirstDT.Columns[j].Caption);
  2855. }
  2856. }
  2857. row1 = sheet2.CreateRow(PaintIndex);
  2858. PaintIndex = PaintIndex + 1;
  2859. }
  2860. //添加数据内容
  2861. ShowColumnCount = 0;
  2862. for (int j = 4; j < columnNum; j++)
  2863. {
  2864. row1.CreateCell(j - 4);
  2865. if (FirstDT.Columns[j].ColumnName == "ch_bluefilm")
  2866. {
  2867. row1.CreateCell(j - 4, CellType.String);
  2868. }
  2869. if (!NotShowColumn.Contains(FirstDT.Columns[j].ColumnName.ToLower()))
  2870. {
  2871. string Data = FirstDT.Rows[i][j].ToString();
  2872. row1.Cells[j - 4].SetCellValue(Data);
  2873. row1.GetCell(j - 4).CellStyle = styleborder;
  2874. if (FirstDT.Columns[j].ColumnName == "num")
  2875. {
  2876. row1.Cells[j - 4].SetCellValue(int.Parse(Data));
  2877. sumCount += int.Parse(Data);
  2878. }
  2879. if (FirstDT.Columns[j].ColumnName == "io_qty")
  2880. {
  2881. totalCount += int.Parse(Data);
  2882. row1.Cells[j - 4].SetCellValue(int.Parse(Data));
  2883. }
  2884. ShowColumnCount = ShowColumnCount + 1;
  2885. }
  2886. else
  2887. {
  2888. }
  2889. }
  2890. //添加总计行
  2891. if (i == rowNum - 1)
  2892. {
  2893. row1 = sheet2.CreateRow(PaintIndex);
  2894. PaintIndex = PaintIndex + 1;
  2895. for (int j = 0; j < ShowColumnCount; j++)
  2896. {
  2897. if (j == 0)
  2898. {
  2899. row1.CreateCell(j);
  2900. row1.Cells[j].CellStyle = styleborder;
  2901. row1.Cells[j].SetCellValue(总计);
  2902. }
  2903. else if (j == ShowColumnCount - 2)
  2904. {
  2905. row1.CreateCell(j);
  2906. row1.Cells[j].CellStyle = styleborder;
  2907. row1.Cells[j].SetCellValue(sumCount);
  2908. }
  2909. else if (j == ShowColumnCount - 1)
  2910. {
  2911. row1.CreateCell(j);
  2912. row1.Cells[j].CellStyle = styleborder;
  2913. row1.Cells[j].SetCellValue(totalCount);
  2914. }
  2915. else
  2916. {
  2917. row1.CreateCell(j);
  2918. row1.Cells[j].CellStyle = styleborder;
  2919. }
  2920. }
  2921. }
  2922. }
  2923. for (int i = 0; i < sheet2.LastRowNum; i++)
  2924. {
  2925. if (i != 0)
  2926. {
  2927. sheet2.AutoSizeColumn(i);
  2928. sheet2.SetColumnWidth(i, sheet2.GetColumnWidth(i) + 1000);
  2929. }
  2930. }
  2931. //将book的内容写入内存流中返回
  2932. book.Write(ms);
  2933. return ms;
  2934. }
  2935. /// <summary>
  2936. /// 将DataTable形式的数据转成Excel格式的,然后用字节流的形式写入文件
  2937. /// </summary>
  2938. /// <param name="DataTable"></param>
  2939. /// <returns></returns>
  2940. private MemoryStream DataTableToExcel_LIPUXIN(DataTable FirstDT, DataTable DataTable, string Type, string Inoutno, int PageSize, List<CheckBox> conditionbox)
  2941. {
  2942. string 小计 = "";
  2943. string 批小计 = "";
  2944. string 总计 = "";
  2945. string 片 = "";
  2946. string companyname = "";
  2947. int ShowBoxIndex = 0;
  2948. //用于小计的盒号
  2949. string LastBox = "";
  2950. if (dh.getFieldDataByCondition("ProdInout", "pi_exporttype", "pi_inoutno='" + Inoutno + "'").ToString() == "Chinese")
  2951. {
  2952. 小计 = "小计";
  2953. 总计 = "总计";
  2954. 批小计 = "批小计";
  2955. 片 = "片";
  2956. companyname = "深爱半导体股份有限公司芯片出货清单";
  2957. }
  2958. else
  2959. {
  2960. 批小计 = "BatchTotal";
  2961. 小计 = "total";
  2962. 总计 = "total";
  2963. companyname = "SHENZHEN SI SEMICONDUCTORS CO.,LTD";
  2964. }
  2965. //转换为序列
  2966. CheckBox[] box = conditionbox.ToArray();
  2967. //创建内存流
  2968. MemoryStream ms = new MemoryStream();
  2969. //创建一个Book,相当于一个Excel文件
  2970. HSSFWorkbook book = new HSSFWorkbook();
  2971. //Excel中的Sheet
  2972. ISheet sheet = book.CreateSheet("分页");
  2973. sheet.SetMargin(MarginType.TopMargin, 0.4);
  2974. sheet.SetMargin(MarginType.BottomMargin, 0.4);
  2975. sheet.SetMargin(MarginType.LeftMargin, 0.4);
  2976. sheet.SetMargin(MarginType.RightMargin, 0.4);
  2977. sheet.FitToPage = true;
  2978. sheet.PrintSetup.FitHeight = 200;
  2979. //芯片号需要作为更新盒号的条件
  2980. HSSFFont ffont = (HSSFFont)book.CreateFont();
  2981. ffont.FontName = "宋体";
  2982. //更新箱号
  2983. List<string> pib_id = new List<string>();
  2984. //系统打印箱号
  2985. List<string> pib_outboxcode1 = new List<string>();
  2986. int BoxCode = 1;
  2987. ICellStyle style = book.CreateCellStyle();
  2988. style.VerticalAlignment = VerticalAlignment.Center;
  2989. style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
  2990. style.SetFont(ffont);
  2991. ICellStyle styleborder = book.CreateCellStyle();
  2992. styleborder.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
  2993. styleborder.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
  2994. styleborder.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
  2995. styleborder.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
  2996. styleborder.VerticalAlignment = VerticalAlignment.Center;
  2997. styleborder.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
  2998. styleborder.SetFont(ffont);
  2999. string pi_inoutno = "";
  3000. HSSFFont ColumnTitle = (HSSFFont)book.CreateFont();
  3001. ColumnTitle.FontName = "宋体";
  3002. ColumnTitle.Boldweight = (short)FontBoldWeight.Bold;
  3003. ICellStyle ColumnTitleStyle = book.CreateCellStyle();
  3004. ColumnTitleStyle.SetFont(ColumnTitle);
  3005. ColumnTitleStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
  3006. ColumnTitleStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
  3007. ColumnTitleStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
  3008. ColumnTitleStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
  3009. List<string> NotShowColumn = new List<string>();
  3010. NotShowColumn.Add("ch_level");
  3011. NotShowColumn.Add("pib_id");
  3012. NotShowColumn.Add("chw_itemname1");
  3013. NotShowColumn.Add("chw_itemname2");
  3014. NotShowColumn.Add("chw_itemname3");
  3015. NotShowColumn.Add("chw_itemname4");
  3016. NotShowColumn.Add("chw_itemname5");
  3017. NotShowColumn.Add("pd_ordercode");
  3018. NotShowColumn.Add("pr_size");
  3019. NotShowColumn.Add("me_desc");
  3020. NotShowColumn.Add("pr_orispeccode");
  3021. NotShowColumn.Add("pr_orispeccode1");
  3022. NotShowColumn.Add("pi_title");
  3023. NotShowColumn.Add("pi_chipouttype");
  3024. NotShowColumn.Add("pi_date");
  3025. int ShowColumnCount = 0;
  3026. //展示的内容列
  3027. //设置列的宽度,根据首行的列的内容的长度来设置
  3028. for (int i = DataTable.Columns.Count - 1; i > 0; i--)
  3029. {
  3030. for (int j = 0; j < box.Length; j++)
  3031. {
  3032. if (box[j].Name.ToLower() == "ch_bluefilm" && !box[j].Checked)
  3033. {
  3034. if (DataTable.Columns[i].ColumnName.ToLower().Contains("ch_bluefilm"))
  3035. {
  3036. DataTable.Columns.RemoveAt(i);
  3037. break;
  3038. }
  3039. }
  3040. if (box[j].Name.ToLower() == "ch_code" && !box[j].Checked)
  3041. {
  3042. if (DataTable.Columns[i].ColumnName.ToLower().Contains("ch_code"))
  3043. {
  3044. DataTable.Columns.RemoveAt(i);
  3045. break;
  3046. }
  3047. }
  3048. if (box[j].Name.ToLower() == "ch_splitbatch" && !box[j].Checked)
  3049. {
  3050. if (DataTable.Columns[i].ColumnName.ToLower().Contains("ch_splitbatch"))
  3051. {
  3052. DataTable.Columns.RemoveAt(i);
  3053. break;
  3054. }
  3055. }
  3056. if (box[j].Name.ToLower() == "ch_waterid" && !box[j].Checked)
  3057. {
  3058. if (DataTable.Columns[i].ColumnName.ToLower().Contains("ch_waterid"))
  3059. {
  3060. DataTable.Columns.RemoveAt(i);
  3061. break;
  3062. }
  3063. }
  3064. if (box[j].Name.ToLower() == "ch_pbcode" && !box[j].Checked)
  3065. {
  3066. if (DataTable.Columns[i].ColumnName.ToLower().Contains("ch_pbcode"))
  3067. {
  3068. DataTable.Columns.RemoveAt(i);
  3069. break;
  3070. }
  3071. }
  3072. if (box[j].Name.ToLower() == "ch_remark" && !box[j].Checked)
  3073. {
  3074. if (DataTable.Columns[i].ColumnName.ToLower().Contains("ch_remark"))
  3075. {
  3076. DataTable.Columns.RemoveAt(i);
  3077. break;
  3078. }
  3079. }
  3080. if (box[j].Name.ToLower() == "ch_yield" && !box[j].Checked)
  3081. {
  3082. if (DataTable.Columns[i].ColumnName.ToLower().Contains("ch_yield"))
  3083. {
  3084. DataTable.Columns.RemoveAt(i);
  3085. break;
  3086. }
  3087. }
  3088. }
  3089. }
  3090. //获取行数量和列数量
  3091. int rowNum = DataTable.Rows.Count;
  3092. int columnNum = DataTable.Columns.Count;
  3093. //首先画好第一行带颜色的,单独写出来,避免写在循环里面
  3094. IRow row = sheet.CreateRow(0);
  3095. //冻结第一行
  3096. sheet.CreateFreezePane(0, 1, 0, 1);
  3097. row.HeightInPoints = RowHeight;
  3098. //固定第一行
  3099. //row.RowStyle.IsLocked=true;
  3100. //给第一行的标签赋值样式和值
  3101. // ffont.FontHeight = 13;
  3102. row.CreateCell(0);
  3103. row.Cells[0].SetCellValue(" " + companyname);
  3104. row.GetCell(0).CellStyle.SetFont((ffont));
  3105. //ffont.FontHeight = 10;
  3106. //开始绘制的Index
  3107. int PaintIndex = 1;
  3108. int sumCount = 0;
  3109. int totalCount = 0;
  3110. switch (Type)
  3111. {
  3112. case "FixRow":
  3113. //清理系统取出来的数据
  3114. BaseUtil.CleanDataTableData(FirstDT);
  3115. //首页参数拼接
  3116. string First_OrderCode = "";
  3117. string First_Prspec = "";
  3118. string First_Batch = "";
  3119. int NumIndex = 0;
  3120. ArrayList<string> First_WID = new ArrayList<string>();
  3121. for (int i = 0; i < rowNum; i++)
  3122. {
  3123. IRow row1 = sheet.CreateRow(PaintIndex);
  3124. PaintIndex = PaintIndex + 1;
  3125. row1.HeightInPoints = RowHeight;
  3126. //不包含的订单号
  3127. if (DataTable.Columns.Contains("pd_ordercode") && !First_OrderCode.Contains(DataTable.Rows[i]["pd_ordercode"].ToString()))
  3128. {
  3129. First_OrderCode += DataTable.Rows[i]["pd_ordercode"].ToString() + " ";
  3130. }
  3131. //不包含的物料型号
  3132. if (DataTable.Columns.Contains("pr_orispeccode") && !First_Prspec.Contains(DataTable.Rows[i]["pr_orispeccode"].ToString()))
  3133. {
  3134. First_Prspec += DataTable.Rows[i]["pr_orispeccode"].ToString() + " ";
  3135. }
  3136. //不包含扩撒批号
  3137. if (DataTable.Columns.Contains("ch_splitbatch") && !First_Batch.Contains(DataTable.Rows[i]["ch_splitbatch"].ToString()))
  3138. {
  3139. First_Batch += DataTable.Rows[i]["ch_splitbatch"].ToString() + " ";
  3140. }
  3141. //不包含Wafer_id
  3142. if (DataTable.Columns.Contains("Wafer_ID") && !First_WID.Contains(DataTable.Rows[i]["Wafer_ID"].ToString()))
  3143. {
  3144. First_WID.Add(DataTable.Rows[i]["Wafer_ID"].ToString());
  3145. }
  3146. if (i / PageSize >= 1 && i % PageSize == 0)
  3147. {
  3148. DataRow dr = FirstDT.NewRow();
  3149. dr["me_desc"] = DataTable.Rows[i]["me_desc"].ToString();
  3150. dr["pr_size"] = DataTable.Rows[i]["pr_size"].ToString();
  3151. dr["pi_chipouttype"] = DataTable.Rows[i]["pi_chipouttype"].ToString();
  3152. dr["ch_level"] = DataTable.Rows[i]["ch_level"].ToString();
  3153. dr["pr_orispeccode"] = DataTable.Rows[i]["pr_orispeccode"].ToString();
  3154. dr["pi_inoutno"] = DataTable.Rows[i]["pi_inoutno"].ToString();
  3155. pi_inoutno = DataTable.Rows[i]["pi_inoutno"].ToString();
  3156. dr["pi_title"] = DataTable.Rows[i]["pi_title"].ToString();
  3157. dr["pi_date"] = DataTable.Rows[i]["pi_date"].ToString();
  3158. dr["pd_ordercode"] = First_OrderCode;
  3159. dr["pr_orispeccode"] = First_Prspec;
  3160. dr["ch_splitbatch"] = First_Batch;
  3161. dr["ch_waterid"] = BaseUtil.GetArrStr(First_WID, " ");
  3162. dr["num"] = PageSize;
  3163. dr["io_qty"] = sumCount;
  3164. FirstDT.Rows.Add(dr);
  3165. First_OrderCode = "";
  3166. First_Prspec = "";
  3167. First_Batch = "";
  3168. First_WID.Clear();
  3169. BoxCode = BoxCode + 1;
  3170. for (int j = 0; j < ShowColumnCount; j++)
  3171. {
  3172. row1.CreateCell(j);
  3173. if (j == 0)
  3174. {
  3175. row1.Cells[j].SetCellValue(批小计);
  3176. }
  3177. else if (j == ShowPieceIndex)
  3178. {
  3179. row1.Cells[ShowPieceIndex].SetCellValue((i % PageSize == 0 ? PageSize : i));
  3180. row1.Cells[j].CellStyle = styleborder;
  3181. }
  3182. else if (j == ShowCountIndex)
  3183. {
  3184. row1.Cells[j].SetCellValue(sumCount);
  3185. }
  3186. else if (j == ShowBoxIndex - 4)
  3187. {
  3188. row1.Cells[j].SetCellValue(LastBox);
  3189. row1.Cells[j].CellStyle = styleborder;
  3190. }
  3191. row1.Cells[j].CellStyle = styleborder;
  3192. }
  3193. row1 = sheet.CreateRow(PaintIndex);
  3194. PaintIndex = PaintIndex + 1;
  3195. for (int j = 0; j < ShowColumnCount; j++)
  3196. {
  3197. row1.CreateCell(j);
  3198. if (j == 0)
  3199. {
  3200. row1.Cells[j].SetCellValue(小计);
  3201. }
  3202. else if (j == ShowPieceIndex)
  3203. {
  3204. row1.Cells[ShowPieceIndex].SetCellValue((i % PageSize == 0 ? PageSize : i));
  3205. }
  3206. else if (j == ShowCountIndex)
  3207. {
  3208. row1.Cells[j].SetCellValue(sumCount);
  3209. }
  3210. else if (j == ShowBoxIndex - 4)
  3211. {
  3212. row1.Cells[j].SetCellValue(LastBox);
  3213. row1.Cells[j].CellStyle = styleborder;
  3214. }
  3215. row1.Cells[j].CellStyle = styleborder;
  3216. }
  3217. sumCount = 0;
  3218. row1 = sheet.CreateRow(PaintIndex);
  3219. sheet.SetRowBreak(PaintIndex - 1);
  3220. sheet.Footer.Center = "第&P页,共&N页";
  3221. PaintIndex = PaintIndex + 1;
  3222. }
  3223. //每次到了页数开始分页
  3224. if (i % PageSize == 0 || i == rowNum - 1)
  3225. {
  3226. //第一行添加客户信息 rownum只有一行的情
  3227. if (i != rowNum - 1 || rowNum == 1 || (rowNum - i == rowNum % PageSize))
  3228. {
  3229. for (int j = 0; j < columnNum - 3; j++)
  3230. {
  3231. if (j == 0)
  3232. {
  3233. row1.CreateCell(j);
  3234. row1.Cells[j].SetCellValue(DataTable.Columns["pi_title"].Caption + ":" + DataTable.Rows[i]["pi_title"].ToString() + " "
  3235. + DataTable.Columns["ch_level"].Caption + ":" + DataTable.Rows[i]["ch_level"].ToString());
  3236. }
  3237. else if (j == ShowDataIndex)
  3238. {
  3239. row1.CreateCell(j);
  3240. row1.Cells[j].SetCellValue(DataTable.Columns["pi_inoutno"].Caption + ":" + DataTable.Rows[i]["pi_inoutno"].ToString());
  3241. }
  3242. else
  3243. {
  3244. row1.CreateCell(j);
  3245. }
  3246. row1.GetCell(j).CellStyle = style;
  3247. }
  3248. row1 = sheet.CreateRow(PaintIndex);
  3249. PaintIndex = PaintIndex + 1;
  3250. //第二行添加型号
  3251. for (int j = 0; j < columnNum - 3; j++)
  3252. {
  3253. if (j == 0)
  3254. {
  3255. row1.CreateCell(j);
  3256. row1.Cells[j].SetCellValue(DataTable.Columns["pr_orispeccode"].Caption + ":" + DataTable.Rows[i]["pr_orispeccode"].ToString()
  3257. + "_(" + DataTable.Rows[i]["me_desc"].ToString() + ") " + DataTable.Columns["pi_chipouttype"].Caption + ":" + DataTable.Rows[i]["pi_chipouttype"].ToString());
  3258. }
  3259. else if (j == ShowSizeIndex)
  3260. {
  3261. row1.CreateCell(j);
  3262. row1.Cells[j].SetCellValue(DataTable.Columns["pr_size"].Caption + ":" + DataTable.Rows[i]["pr_size"].ToString());
  3263. }
  3264. else if (j == ShowDataIndex)
  3265. {
  3266. row1.CreateCell(j);
  3267. row1.Cells[j].SetCellValue(DataTable.Columns["pi_date"].Caption + ":" + DataTable.Rows[i]["pi_date"].ToString());
  3268. }
  3269. else
  3270. {
  3271. row1.CreateCell(j);
  3272. }
  3273. row1.GetCell(j).CellStyle = style;
  3274. }
  3275. //添加列名
  3276. row1 = sheet.CreateRow(PaintIndex);
  3277. PaintIndex = PaintIndex + 1;
  3278. //计数列所在的索引
  3279. int showcount = 0;
  3280. for (int j = 4; j < columnNum; j++)
  3281. {
  3282. if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent1")
  3283. {
  3284. showcount = showcount + 1;
  3285. row1.CreateCell(j - 4);
  3286. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  3287. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname1"].ToString());
  3288. }
  3289. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent2")
  3290. {
  3291. showcount = showcount + 1;
  3292. row1.CreateCell(j - 4);
  3293. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  3294. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname2"].ToString());
  3295. }
  3296. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent3")
  3297. {
  3298. showcount = showcount + 1;
  3299. row1.CreateCell(j - 4);
  3300. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  3301. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname3"].ToString());
  3302. }
  3303. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent4")
  3304. {
  3305. showcount = showcount + 1;
  3306. row1.CreateCell(j - 4);
  3307. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  3308. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname4"].ToString());
  3309. }
  3310. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent5")
  3311. {
  3312. showcount = showcount + 1;
  3313. row1.CreateCell(j - 4);
  3314. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  3315. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname5"].ToString());
  3316. }
  3317. else if ((NotShowColumn.Contains(DataTable.Columns[j].ColumnName.ToLower())))
  3318. {
  3319. }
  3320. else
  3321. {
  3322. showcount = showcount + 1;
  3323. row1.CreateCell(j - 4);
  3324. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  3325. row1.Cells[j - 4].SetCellValue(DataTable.Columns[j].Caption);
  3326. }
  3327. if (DataTable.Columns[j].ColumnName.ToLower().ToString() == "ch_pbcode")
  3328. {
  3329. ShowBoxIndex = j;
  3330. }
  3331. if (DataTable.Columns[j].ColumnName.ToString() == "io_qty")
  3332. {
  3333. NumIndex = j;
  3334. if (ShowCountIndex == NumIndex - 4)
  3335. {
  3336. ShowCountIndex = ShowCountIndex + 1;
  3337. }
  3338. }
  3339. }
  3340. ShowColumnCount = showcount;
  3341. row1 = sheet.CreateRow(PaintIndex);
  3342. PaintIndex = PaintIndex + 1;
  3343. }
  3344. }
  3345. //添加数据内容
  3346. for (int j = 4; j < columnNum; j++)
  3347. {
  3348. string Data = DataTable.Rows[i][j].ToString();
  3349. row1.CreateCell(j - 4);
  3350. if (DataTable.Columns[j].ColumnName.ToLower() == "ch_bluefilm")
  3351. {
  3352. row1.CreateCell(j - 4, CellType.String);
  3353. }
  3354. if ((NotShowColumn.Contains(DataTable.Columns[j].ColumnName.ToLower())))
  3355. {
  3356. }
  3357. else
  3358. {
  3359. double Num = 0;
  3360. if (double.TryParse(Data, out Num)&& DataTable.Columns[j].ColumnName.ToLower() != "ch_bluefilm")
  3361. {
  3362. row1.Cells[j - 4].SetCellValue(Num);
  3363. }
  3364. else
  3365. {
  3366. row1.Cells[j - 4].SetCellValue(Data);
  3367. }
  3368. row1.GetCell(j - 4).CellStyle = styleborder;
  3369. }
  3370. if (DataTable.Columns[j].ColumnName == "io_qty")
  3371. {
  3372. row1.Cells[j - 4].SetCellValue(int.Parse(Data));
  3373. sumCount += int.Parse(DataTable.Rows[i][j].ToString());
  3374. totalCount += int.Parse(DataTable.Rows[i][j].ToString());
  3375. row1.Cells[j - 4].CellStyle = styleborder;
  3376. }
  3377. if (DataTable.Columns[j].ColumnName == "rownum")
  3378. {
  3379. row1.Cells[j - 4].SetCellValue(i + 1);
  3380. row1.Cells[j - 4].CellStyle = styleborder;
  3381. }
  3382. if (DataTable.Columns[j].ColumnName == "ch_code")
  3383. {
  3384. sheet.AutoSizeColumn(j - 4);
  3385. sheet.SetColumnWidth(j - 4, sheet.GetColumnWidth(j - 4) + 1000);
  3386. }
  3387. if (DataTable.Columns[j].ColumnName.ToLower() == "ch_pbcode")
  3388. {
  3389. LastBox = Data;
  3390. }
  3391. }
  3392. //固定行号分组的时候自动拼接新的DataTable
  3393. if (i == rowNum - 1)
  3394. {
  3395. DataRow dr = FirstDT.NewRow();
  3396. dr["me_desc"] = DataTable.Rows[i]["me_desc"].ToString();
  3397. dr["pr_size"] = DataTable.Rows[i]["pr_size"].ToString();
  3398. dr["pi_chipouttype"] = DataTable.Rows[i]["pi_chipouttype"].ToString();
  3399. dr["ch_level"] = DataTable.Rows[i]["ch_level"].ToString();
  3400. dr["pr_orispeccode"] = DataTable.Rows[i]["pr_orispeccode"].ToString();
  3401. dr["pi_inoutno"] = DataTable.Rows[i]["pi_inoutno"].ToString();
  3402. dr["pi_title"] = DataTable.Rows[i]["pi_title"].ToString();
  3403. dr["pi_date"] = DataTable.Rows[i]["pi_date"].ToString();
  3404. dr["pd_ordercode"] = First_OrderCode;
  3405. dr["pr_orispeccode"] = First_Prspec;
  3406. dr["ch_splitbatch"] = First_Batch;
  3407. dr["ch_waterid"] = BaseUtil.GetArrStr(First_WID, " ");
  3408. dr["num"] = (i % PageSize) + 1;
  3409. dr["io_qty"] = sumCount;
  3410. FirstDT.Rows.Add(dr);
  3411. row1 = sheet.CreateRow(PaintIndex);
  3412. PaintIndex = PaintIndex + 1;
  3413. for (int j = 0; j < ShowColumnCount; j++)
  3414. {
  3415. row1.CreateCell(j);
  3416. if (j == 0)
  3417. {
  3418. row1.Cells[j].SetCellValue(批小计);
  3419. }
  3420. else if (j == ShowPieceIndex)
  3421. {
  3422. row1.Cells[ShowPieceIndex].SetCellValue((rowNum % PageSize == 0 ? PageSize : (rowNum % PageSize)));
  3423. row1.Cells[j].CellStyle = styleborder;
  3424. }
  3425. else if (j == ShowBoxIndex - 4)
  3426. {
  3427. row1.Cells[j].SetCellValue(LastBox);
  3428. row1.Cells[j].CellStyle = styleborder;
  3429. }
  3430. else if (j == ShowCountIndex)
  3431. {
  3432. row1.Cells[j].SetCellValue(sumCount);
  3433. }
  3434. row1.Cells[j].CellStyle = styleborder;
  3435. }
  3436. row1 = sheet.CreateRow(PaintIndex);
  3437. PaintIndex = PaintIndex + 1;
  3438. for (int j = 0; j < ShowColumnCount; j++)
  3439. {
  3440. row1.CreateCell(j);
  3441. if (j == 0)
  3442. {
  3443. row1.Cells[j].SetCellValue(小计);
  3444. }
  3445. else if (j == ShowPieceIndex)
  3446. {
  3447. row1.Cells[ShowPieceIndex].SetCellValue(rowNum % PageSize);
  3448. row1.Cells[j].CellStyle = styleborder;
  3449. }
  3450. else if (j == ShowBoxIndex - 4)
  3451. {
  3452. row1.Cells[j].SetCellValue(LastBox);
  3453. row1.Cells[j].CellStyle = styleborder;
  3454. }
  3455. else if (j == ShowCountIndex)
  3456. {
  3457. row1.Cells[j].SetCellValue(sumCount);
  3458. }
  3459. row1.Cells[j].CellStyle = styleborder;
  3460. }
  3461. row1 = sheet.CreateRow(PaintIndex);
  3462. for (int j = 0; j < columnNum - 3; j++)
  3463. {
  3464. if (j == 0)
  3465. {
  3466. row1.CreateCell(j);
  3467. row1.Cells[j].SetCellValue("备注");
  3468. }
  3469. else if (j == ShowCountIndex)
  3470. {
  3471. row1.CreateCell(j);
  3472. row1.Cells[j].SetCellValue(totalCount);
  3473. }
  3474. else if (j == ShowRemarkPiece)
  3475. {
  3476. row1.CreateCell(j);
  3477. row1.Cells[j].SetCellValue(rowNum + 片);
  3478. }
  3479. else
  3480. {
  3481. row1.CreateCell(j);
  3482. }
  3483. row1.Cells[j].CellStyle = style;
  3484. }
  3485. sheet.SetRowBreak(PaintIndex);
  3486. sheet.Footer.Center = "第&P页,共&N页";
  3487. PaintIndex = PaintIndex + 1;
  3488. }
  3489. pib_id.Add(DataTable.Rows[i]["pib_id"].ToString());
  3490. pib_outboxcode1.Add(BoxCode.ToString());
  3491. }
  3492. break;
  3493. case "BatchCode":
  3494. string LastBatchCode = "";
  3495. int PageNum = 0;
  3496. for (int i = 0; i < rowNum; i++)
  3497. {
  3498. IRow row1 = sheet.CreateRow(PaintIndex);
  3499. PaintIndex = PaintIndex + 1;
  3500. row1.HeightInPoints = RowHeight;
  3501. //如果批号不相等的时候
  3502. if (LastBatchCode != "" && LastBatchCode != DataTable.Rows[i]["ch_splitbatch"].ToString())
  3503. {
  3504. BoxCode = BoxCode + 1;
  3505. for (int j = 0; j < ShowColumnCount; j++)
  3506. {
  3507. row1.CreateCell(j);
  3508. if (j == 0)
  3509. {
  3510. row1.Cells[j].SetCellValue(批小计);
  3511. row1.Cells[j].CellStyle = styleborder;
  3512. }
  3513. else if (j == ShowPieceIndex)
  3514. {
  3515. row1.Cells[ShowPieceIndex].SetCellValue(PageNum);
  3516. row1.Cells[j].CellStyle = styleborder;
  3517. }
  3518. else if (j == ShowCountIndex)
  3519. {
  3520. row1.Cells[j].SetCellValue(sumCount);
  3521. row1.Cells[j].CellStyle = styleborder;
  3522. }
  3523. else if (j == ShowBoxIndex - 4)
  3524. {
  3525. row1.Cells[j].SetCellValue(LastBox);
  3526. row1.Cells[j].CellStyle = styleborder;
  3527. }
  3528. else
  3529. {
  3530. row1.Cells[j].CellStyle = styleborder;
  3531. }
  3532. }
  3533. row1 = sheet.CreateRow(PaintIndex);
  3534. PaintIndex = PaintIndex + 1;
  3535. for (int j = 0; j < ShowColumnCount; j++)
  3536. {
  3537. row1.CreateCell(j);
  3538. if (j == 0)
  3539. {
  3540. row1.Cells[j].SetCellValue(小计);
  3541. row1.Cells[j].CellStyle = styleborder;
  3542. }
  3543. else if (j == ShowPieceIndex)
  3544. {
  3545. row1.Cells[ShowPieceIndex].SetCellValue(PageNum);
  3546. row1.Cells[j].CellStyle = styleborder;
  3547. }
  3548. else if (j == ShowCountIndex)
  3549. {
  3550. row1.Cells[j].SetCellValue(sumCount);
  3551. row1.Cells[j].CellStyle = styleborder;
  3552. }
  3553. else if (j == ShowBoxIndex - 4)
  3554. {
  3555. row1.Cells[j].SetCellValue(LastBox);
  3556. row1.Cells[j].CellStyle = styleborder;
  3557. }
  3558. else
  3559. {
  3560. row1.Cells[j].CellStyle = styleborder;
  3561. }
  3562. }
  3563. sumCount = 0;
  3564. row1 = sheet.CreateRow(PaintIndex);
  3565. sheet.SetRowBreak(PaintIndex - 1);
  3566. sheet.Footer.Center = "第&P页,共&N页";
  3567. PaintIndex = PaintIndex + 1;
  3568. PageNum = 0;
  3569. }
  3570. //每次到了页数开始分页
  3571. if (LastBatchCode == "" || (LastBatchCode != "" && LastBatchCode != DataTable.Rows[i]["ch_splitbatch"].ToString()) || (i == rowNum - 1 && (LastBatchCode != "" && LastBatchCode != DataTable.Rows[i]["ch_splitbatch"].ToString())))
  3572. {
  3573. Console.WriteLine(DataTable.Rows[i]["ch_splitbatch"].ToString());
  3574. LastBatchCode = DataTable.Rows[i]["ch_splitbatch"].ToString();
  3575. //第一行添加客户信息
  3576. //if (i != rowNum - 1)
  3577. {
  3578. for (int j = 0; j < columnNum - 3; j++)
  3579. {
  3580. if (j == 0)
  3581. {
  3582. row1.CreateCell(j);
  3583. row1.Cells[j].SetCellValue(DataTable.Columns["pi_title"].Caption + ":" + DataTable.Rows[i]["pi_title"].ToString() + " "
  3584. + DataTable.Columns["ch_level"].Caption + ":" + DataTable.Rows[i]["ch_level"].ToString());
  3585. }
  3586. else if (j == ShowDataIndex)
  3587. {
  3588. row1.CreateCell(j);
  3589. row1.Cells[j].SetCellValue(DataTable.Columns["pi_inoutno"].Caption + ":" + DataTable.Rows[i]["pi_inoutno"].ToString());
  3590. }
  3591. else
  3592. {
  3593. row1.CreateCell(j);
  3594. }
  3595. row1.GetCell(j).CellStyle = style;
  3596. }
  3597. row1 = sheet.CreateRow(PaintIndex);
  3598. PaintIndex = PaintIndex + 1;
  3599. //第二行添加型号
  3600. for (int j = 0; j < columnNum - 3; j++)
  3601. {
  3602. if (j == 0)
  3603. {
  3604. row1.CreateCell(j);
  3605. row1.Cells[j].SetCellValue(DataTable.Columns["pr_orispeccode"].Caption + ":" + DataTable.Rows[i]["pr_orispeccode"].ToString() + "_"
  3606. + "(" + DataTable.Rows[i]["me_desc"].ToString() + ") " + DataTable.Columns["pi_chipouttype"].Caption + ":" + DataTable.Rows[i]["pi_chipouttype"].ToString());
  3607. }
  3608. else if (j == ShowSizeIndex)
  3609. {
  3610. row1.CreateCell(j);
  3611. row1.Cells[j].SetCellValue(DataTable.Columns["pr_size"].Caption + ":" + DataTable.Rows[i]["pr_size"].ToString());
  3612. }
  3613. else if (j == ShowDataIndex)
  3614. {
  3615. row1.CreateCell(j);
  3616. row1.Cells[j].SetCellValue(DataTable.Columns["pi_date"].Caption + ":" + DataTable.Rows[i]["pi_date"].ToString());
  3617. }
  3618. else
  3619. {
  3620. row1.CreateCell(j);
  3621. }
  3622. row1.GetCell(j).CellStyle = style;
  3623. }
  3624. //添加列名
  3625. row1 = sheet.CreateRow(PaintIndex);
  3626. PaintIndex = PaintIndex + 1;
  3627. int showcount = 0;
  3628. for (int j = 4; j < columnNum; j++)
  3629. {
  3630. if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent1")
  3631. {
  3632. showcount = showcount + 1;
  3633. row1.CreateCell(j - 4);
  3634. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  3635. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname1"].ToString());
  3636. }
  3637. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent2")
  3638. {
  3639. showcount = showcount + 1;
  3640. row1.CreateCell(j - 4);
  3641. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  3642. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname2"].ToString());
  3643. }
  3644. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent3")
  3645. {
  3646. showcount = showcount + 1;
  3647. row1.CreateCell(j - 4);
  3648. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  3649. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname3"].ToString());
  3650. }
  3651. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent4")
  3652. {
  3653. showcount = showcount + 1;
  3654. row1.CreateCell(j - 4);
  3655. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  3656. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname4"].ToString());
  3657. }
  3658. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent5")
  3659. {
  3660. showcount = showcount + 1;
  3661. row1.CreateCell(j - 4);
  3662. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  3663. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname5"].ToString());
  3664. }
  3665. else if ((NotShowColumn.Contains(DataTable.Columns[j].ColumnName.ToLower())))
  3666. {
  3667. }
  3668. else
  3669. {
  3670. showcount = showcount + 1;
  3671. row1.CreateCell(j - 4);
  3672. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  3673. row1.Cells[j - 4].SetCellValue(DataTable.Columns[j].Caption);
  3674. }
  3675. if (DataTable.Columns[j].ColumnName.ToLower().ToString() == "ch_pbcode")
  3676. {
  3677. ShowBoxIndex = j;
  3678. }
  3679. if (DataTable.Columns[j].ColumnName.ToString() == "io_qty")
  3680. {
  3681. NumIndex = j;
  3682. if (ShowCountIndex == NumIndex - 4)
  3683. {
  3684. ShowCountIndex = ShowCountIndex + 1;
  3685. }
  3686. }
  3687. }
  3688. ShowColumnCount = showcount;
  3689. row1 = sheet.CreateRow(PaintIndex);
  3690. PaintIndex = PaintIndex + 1;
  3691. }
  3692. }
  3693. //添加数据内容
  3694. for (int j = 4; j < columnNum; j++)
  3695. {
  3696. string Data = DataTable.Rows[i][j].ToString();
  3697. row1.CreateCell(j - 4);
  3698. if (DataTable.Columns[j].ColumnName.ToLower() == "ch_bluefilm")
  3699. {
  3700. row1.CreateCell(j - 4, CellType.String);
  3701. }
  3702. if ((NotShowColumn.Contains(DataTable.Columns[j].ColumnName.ToLower())))
  3703. {
  3704. }
  3705. else
  3706. {
  3707. double Num = 0;
  3708. if (double.TryParse(Data, out Num)&& DataTable.Columns[j].ColumnName.ToLower() != "ch_bluefilm")
  3709. {
  3710. row1.Cells[j - 4].SetCellValue(Num);
  3711. }
  3712. else
  3713. {
  3714. row1.Cells[j - 4].SetCellValue(Data);
  3715. }
  3716. row1.Cells[j - 4].CellStyle = styleborder;
  3717. }
  3718. if (DataTable.Columns[j].ColumnName == "io_qty")
  3719. {
  3720. row1.Cells[j - 4].SetCellValue(int.Parse(Data));
  3721. sumCount += int.Parse(DataTable.Rows[i][j].ToString());
  3722. totalCount += int.Parse(DataTable.Rows[i][j].ToString());
  3723. }
  3724. if (DataTable.Columns[j].ColumnName == "rownum")
  3725. {
  3726. row1.Cells[j - 4].SetCellValue(i + 1);
  3727. row1.Cells[j - 4].CellStyle = styleborder;
  3728. }
  3729. if (DataTable.Columns[j].ColumnName == "ch_code")
  3730. {
  3731. sheet.AutoSizeColumn(j - 4);
  3732. sheet.SetColumnWidth(j - 4, sheet.GetColumnWidth(j - 4) + 1000);
  3733. }
  3734. if (DataTable.Columns[j].ColumnName.ToLower() == "ch_pbcode")
  3735. {
  3736. LastBox = Data;
  3737. }
  3738. }
  3739. if (i == rowNum - 1)
  3740. {
  3741. row1 = sheet.CreateRow(PaintIndex);
  3742. PaintIndex = PaintIndex + 1;
  3743. for (int j = 0; j < ShowColumnCount; j++)
  3744. {
  3745. row1.CreateCell(j);
  3746. if (j == 0)
  3747. {
  3748. row1.Cells[j].SetCellValue(批小计);
  3749. row1.Cells[j].CellStyle = styleborder;
  3750. }
  3751. else if (j == ShowPieceIndex)
  3752. {
  3753. row1.Cells[ShowPieceIndex].SetCellValue(PageNum+1);
  3754. row1.Cells[j].CellStyle = styleborder;
  3755. }
  3756. else if (j == ShowCountIndex)
  3757. {
  3758. row1.Cells[j].SetCellValue(sumCount);
  3759. row1.Cells[j].CellStyle = styleborder;
  3760. }
  3761. else
  3762. {
  3763. row1.Cells[j].CellStyle = styleborder;
  3764. }
  3765. }
  3766. row1 = sheet.CreateRow(PaintIndex);
  3767. PaintIndex = PaintIndex + 1;
  3768. for (int j = 0; j < ShowColumnCount; j++)
  3769. {
  3770. row1.CreateCell(j);
  3771. if (j == 0)
  3772. {
  3773. row1.Cells[j].SetCellValue(小计);
  3774. row1.Cells[j].CellStyle = styleborder;
  3775. }
  3776. else if (j == ShowPieceIndex)
  3777. {
  3778. row1.Cells[ShowPieceIndex].SetCellValue(PageNum+1);
  3779. row1.Cells[j].CellStyle = styleborder;
  3780. }
  3781. else if (j == ShowCountIndex)
  3782. {
  3783. row1.Cells[j].SetCellValue(sumCount);
  3784. row1.Cells[j].CellStyle = styleborder;
  3785. }
  3786. else
  3787. {
  3788. row1.Cells[j].CellStyle = styleborder;
  3789. }
  3790. }
  3791. //创建备注内容
  3792. row1 = sheet.CreateRow(PaintIndex);
  3793. for (int j = 0; j < columnNum - 3; j++)
  3794. {
  3795. if (j == 0)
  3796. {
  3797. row1.CreateCell(j);
  3798. row1.Cells[j].SetCellValue("备注");
  3799. }
  3800. else if (j == ShowCountIndex)
  3801. {
  3802. row1.CreateCell(j);
  3803. row1.Cells[j].SetCellValue(totalCount);
  3804. }
  3805. else if (j == ShowRemarkPiece)
  3806. {
  3807. row1.CreateCell(j);
  3808. row1.Cells[j].SetCellValue(rowNum + 片);
  3809. }
  3810. else
  3811. {
  3812. row1.CreateCell(j);
  3813. }
  3814. row1.Cells[j].CellStyle = style;
  3815. }
  3816. sheet.SetRowBreak(PaintIndex);
  3817. sheet.Footer.Center = "第&P页,共&N页";
  3818. PaintIndex = PaintIndex + 1;
  3819. PageNum = 0;
  3820. }
  3821. PageNum = PageNum + 1;
  3822. pib_id.Add(DataTable.Rows[i]["pib_id"].ToString());
  3823. pib_outboxcode1.Add(BoxCode.ToString());
  3824. }
  3825. break;
  3826. case "BoxCode":
  3827. string LastBoxCode = "";
  3828. int PageNum1 = 0;
  3829. for (int i = 0; i < rowNum; i++)
  3830. {
  3831. IRow row1 = sheet.CreateRow(PaintIndex);
  3832. PaintIndex = PaintIndex + 1;
  3833. row1.HeightInPoints = RowHeight;
  3834. //如果批号不相等的时候
  3835. if (LastBoxCode != "" && LastBoxCode != DataTable.Rows[i]["CH_PBCODE"].ToString())
  3836. {
  3837. BoxCode = BoxCode + 1;
  3838. for (int j = 0; j < ShowColumnCount; j++)
  3839. {
  3840. row1.CreateCell(j);
  3841. if (j == 0)
  3842. {
  3843. row1.Cells[j].SetCellValue(批小计);
  3844. row1.Cells[j].CellStyle = styleborder;
  3845. }
  3846. else if (j == ShowPieceIndex)
  3847. {
  3848. row1.Cells[ShowPieceIndex].SetCellValue(PageNum1);
  3849. row1.Cells[j].CellStyle = styleborder;
  3850. }
  3851. else if (j == ShowCountIndex)
  3852. {
  3853. row1.Cells[j].SetCellValue(sumCount);
  3854. row1.Cells[j].CellStyle = styleborder;
  3855. }
  3856. else if (j == ShowBoxIndex - 4)
  3857. {
  3858. row1.Cells[j].SetCellValue(LastBox);
  3859. row1.Cells[j].CellStyle = styleborder;
  3860. }
  3861. else
  3862. {
  3863. row1.Cells[j].CellStyle = styleborder;
  3864. }
  3865. }
  3866. row1 = sheet.CreateRow(PaintIndex);
  3867. PaintIndex = PaintIndex + 1;
  3868. for (int j = 0; j < ShowColumnCount; j++)
  3869. {
  3870. row1.CreateCell(j);
  3871. if (j == 0)
  3872. {
  3873. row1.Cells[j].SetCellValue(小计);
  3874. row1.Cells[j].CellStyle = styleborder;
  3875. }
  3876. else if (j == ShowPieceIndex)
  3877. {
  3878. row1.Cells[ShowPieceIndex].SetCellValue(PageNum1);
  3879. row1.Cells[j].CellStyle = styleborder;
  3880. }
  3881. else if (j == ShowCountIndex)
  3882. {
  3883. row1.Cells[j].SetCellValue(sumCount);
  3884. row1.Cells[j].CellStyle = styleborder;
  3885. }
  3886. else if (j == ShowBoxIndex - 4)
  3887. {
  3888. row1.Cells[j].SetCellValue(LastBox);
  3889. row1.Cells[j].CellStyle = styleborder;
  3890. }
  3891. else
  3892. {
  3893. row1.Cells[j].CellStyle = styleborder;
  3894. }
  3895. }
  3896. sumCount = 0;
  3897. row1 = sheet.CreateRow(PaintIndex);
  3898. sheet.SetRowBreak(PaintIndex - 1);
  3899. sheet.Footer.Center = "第&P页,共&N页";
  3900. PaintIndex = PaintIndex + 1;
  3901. PageNum1 = 0;
  3902. }
  3903. //每次到了页数开始分页
  3904. if (LastBoxCode == "" || (LastBoxCode != "" && LastBoxCode != DataTable.Rows[i]["ch_pbcode"].ToString()) || ((i == rowNum - 1) && (LastBoxCode != "" && LastBoxCode != DataTable.Rows[i]["ch_pbcode"].ToString())))
  3905. {
  3906. LastBoxCode = DataTable.Rows[i]["CH_PBCODE"].ToString();
  3907. //第一行添加客户信息
  3908. //if (i != rowNum - 1)
  3909. {
  3910. for (int j = 0; j < columnNum - 3; j++)
  3911. {
  3912. if (j == 0)
  3913. {
  3914. row1.CreateCell(j);
  3915. row1.Cells[j].SetCellValue(DataTable.Columns["pi_title"].Caption + ":" + DataTable.Rows[i]["pi_title"].ToString() + " "
  3916. + DataTable.Columns["ch_level"].Caption + ":" + DataTable.Rows[i]["ch_level"].ToString());
  3917. }
  3918. else if (j == ShowDataIndex)
  3919. {
  3920. row1.CreateCell(j);
  3921. row1.Cells[j].SetCellValue(DataTable.Columns["pi_inoutno"].Caption + ":" + DataTable.Rows[i]["pi_inoutno"].ToString());
  3922. }
  3923. else
  3924. {
  3925. row1.CreateCell(j);
  3926. }
  3927. row1.GetCell(j).CellStyle = style;
  3928. }
  3929. row1 = sheet.CreateRow(PaintIndex);
  3930. PaintIndex = PaintIndex + 1;
  3931. //第二行添加型号
  3932. for (int j = 0; j < columnNum - 3; j++)
  3933. {
  3934. if (j == 0)
  3935. {
  3936. row1.CreateCell(j);
  3937. row1.Cells[j].SetCellValue(DataTable.Columns["pr_orispeccode"].Caption + ":" + DataTable.Rows[i]["pr_orispeccode"].ToString() + "_"
  3938. + "(" + DataTable.Rows[i]["me_desc"].ToString() + ") " + DataTable.Columns["pi_chipouttype"].Caption + ":" + DataTable.Rows[i]["pi_chipouttype"].ToString());
  3939. }
  3940. else if (j == ShowSizeIndex)
  3941. {
  3942. row1.CreateCell(j);
  3943. row1.Cells[j].SetCellValue(DataTable.Columns["pr_size"].Caption + ":" + DataTable.Rows[i]["pr_size"].ToString());
  3944. }
  3945. else if (j == ShowDataIndex)
  3946. {
  3947. row1.CreateCell(j);
  3948. row1.Cells[j].SetCellValue(DataTable.Columns["pi_date"].Caption + ":" + DataTable.Rows[i]["pi_date"].ToString());
  3949. }
  3950. else
  3951. {
  3952. row1.CreateCell(j);
  3953. }
  3954. }
  3955. //添加列名
  3956. row1 = sheet.CreateRow(PaintIndex);
  3957. PaintIndex = PaintIndex + 1;
  3958. int showcount = 0;
  3959. for (int j = 4; j < columnNum; j++)
  3960. {
  3961. if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent1")
  3962. {
  3963. showcount = showcount + 1;
  3964. row1.CreateCell(j - 4);
  3965. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  3966. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname1"].ToString());
  3967. }
  3968. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent2")
  3969. {
  3970. showcount = showcount + 1;
  3971. row1.CreateCell(j - 4);
  3972. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  3973. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname2"].ToString());
  3974. }
  3975. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent3")
  3976. {
  3977. showcount = showcount + 1;
  3978. row1.CreateCell(j - 4);
  3979. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  3980. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname3"].ToString());
  3981. }
  3982. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent4")
  3983. {
  3984. showcount = showcount + 1;
  3985. row1.CreateCell(j - 4);
  3986. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  3987. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname4"].ToString());
  3988. }
  3989. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent5")
  3990. {
  3991. showcount = showcount + 1;
  3992. row1.CreateCell(j - 4);
  3993. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  3994. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname5"].ToString());
  3995. }
  3996. else if ((NotShowColumn.Contains(DataTable.Columns[j].ColumnName.ToLower())))
  3997. {
  3998. }
  3999. else
  4000. {
  4001. showcount = showcount + 1;
  4002. row1.CreateCell(j - 4);
  4003. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  4004. row1.Cells[j - 4].SetCellValue(DataTable.Columns[j].Caption);
  4005. }
  4006. if (DataTable.Columns[j].ColumnName.ToLower().ToString() == "ch_pbcode")
  4007. {
  4008. ShowBoxIndex = j;
  4009. }
  4010. if (DataTable.Columns[j].ColumnName.ToString() == "io_qty")
  4011. {
  4012. NumIndex = j;
  4013. if (ShowCountIndex == NumIndex - 4)
  4014. {
  4015. ShowCountIndex = ShowCountIndex + 1;
  4016. }
  4017. }
  4018. }
  4019. ShowColumnCount = showcount;
  4020. row1 = sheet.CreateRow(PaintIndex);
  4021. PaintIndex = PaintIndex + 1;
  4022. }
  4023. }
  4024. //添加数据内容
  4025. for (int j = 4; j < columnNum; j++)
  4026. {
  4027. string Data = DataTable.Rows[i][j].ToString();
  4028. row1.CreateCell(j - 4);
  4029. if (DataTable.Columns[j].ColumnName.ToLower() == "ch_bluefilm")
  4030. {
  4031. row1.CreateCell(j - 4, CellType.String);
  4032. }
  4033. if ((NotShowColumn.Contains(DataTable.Columns[j].ColumnName.ToLower())))
  4034. {
  4035. }
  4036. else
  4037. {
  4038. double Num = 0;
  4039. if (double.TryParse(Data, out Num)&& DataTable.Columns[j].ColumnName.ToLower() != "ch_bluefilm")
  4040. {
  4041. row1.Cells[j - 4].SetCellValue(Num);
  4042. }
  4043. else
  4044. {
  4045. row1.Cells[j - 4].SetCellValue(Data);
  4046. }
  4047. row1.Cells[j - 4].CellStyle = styleborder;
  4048. }
  4049. if (DataTable.Columns[j].ColumnName == "io_qty")
  4050. {
  4051. row1.Cells[j - 4].SetCellValue(int.Parse(Data));
  4052. sumCount += int.Parse(DataTable.Rows[i][j].ToString());
  4053. totalCount += int.Parse(DataTable.Rows[i][j].ToString());
  4054. row1.Cells[j - 4].CellStyle = styleborder;
  4055. }
  4056. if (DataTable.Columns[j].ColumnName == "rownum")
  4057. {
  4058. row1.Cells[j - 4].SetCellValue(i + 1);
  4059. row1.Cells[j - 4].CellStyle = styleborder;
  4060. }
  4061. if (DataTable.Columns[j].ColumnName == "ch_code")
  4062. {
  4063. sheet.AutoSizeColumn(j - 4);
  4064. sheet.SetColumnWidth(j - 4, sheet.GetColumnWidth(j - 4) + 1000);
  4065. }
  4066. if (DataTable.Columns[j].ColumnName.ToLower() == "ch_pbcode")
  4067. {
  4068. LastBox = Data;
  4069. }
  4070. }
  4071. if (i == rowNum - 1)
  4072. {
  4073. PageNum1 = PageNum1 + 1;
  4074. row1 = sheet.CreateRow(PaintIndex);
  4075. PaintIndex = PaintIndex + 1;
  4076. for (int j = 0; j < ShowColumnCount; j++)
  4077. {
  4078. row1.CreateCell(j);
  4079. if (j == 0)
  4080. {
  4081. row1.Cells[j].SetCellValue(批小计);
  4082. row1.Cells[j].CellStyle = styleborder;
  4083. }
  4084. else if (j == ShowPieceIndex)
  4085. {
  4086. row1.Cells[ShowPieceIndex].SetCellValue(PageNum1);
  4087. row1.Cells[j].CellStyle = styleborder;
  4088. }
  4089. else if (j == ShowCountIndex)
  4090. {
  4091. row1.Cells[j].SetCellValue(sumCount);
  4092. row1.Cells[j].CellStyle = styleborder;
  4093. }
  4094. else if (j == ShowBoxIndex - 4)
  4095. {
  4096. row1.Cells[j].SetCellValue(LastBox);
  4097. row1.Cells[j].CellStyle = styleborder;
  4098. }
  4099. else
  4100. {
  4101. row1.Cells[j].CellStyle = styleborder;
  4102. }
  4103. }
  4104. row1 = sheet.CreateRow(PaintIndex);
  4105. PaintIndex = PaintIndex + 1;
  4106. for (int j = 0; j < ShowColumnCount; j++)
  4107. {
  4108. row1.CreateCell(j);
  4109. if (j == 0)
  4110. {
  4111. row1.Cells[j].SetCellValue(小计);
  4112. row1.Cells[j].CellStyle = styleborder;
  4113. }
  4114. else if (j == ShowPieceIndex)
  4115. {
  4116. row1.Cells[ShowPieceIndex].SetCellValue(PageNum1);
  4117. row1.Cells[j].CellStyle = styleborder;
  4118. }
  4119. else if (j == ShowCountIndex)
  4120. {
  4121. row1.Cells[j].SetCellValue(sumCount);
  4122. row1.Cells[j].CellStyle = styleborder;
  4123. }
  4124. else if (j == ShowBoxIndex - 4)
  4125. {
  4126. row1.Cells[j].SetCellValue(LastBox);
  4127. row1.Cells[j].CellStyle = styleborder;
  4128. }
  4129. else
  4130. {
  4131. row1.Cells[j].CellStyle = styleborder;
  4132. }
  4133. }
  4134. row1 = sheet.CreateRow(PaintIndex);
  4135. for (int j = 0; j < columnNum - 3; j++)
  4136. {
  4137. if (j == 0)
  4138. {
  4139. row1.CreateCell(j);
  4140. row1.Cells[j].SetCellValue("备注");
  4141. }
  4142. else if (j == ShowCountIndex)
  4143. {
  4144. row1.CreateCell(j);
  4145. row1.Cells[j].SetCellValue(totalCount);
  4146. }
  4147. else if (j == ShowRemarkPiece)
  4148. {
  4149. row1.CreateCell(j);
  4150. row1.Cells[j].SetCellValue(rowNum + 片);
  4151. }
  4152. else
  4153. {
  4154. row1.CreateCell(j);
  4155. }
  4156. row1.Cells[j].CellStyle = style;
  4157. }
  4158. sheet.SetRowBreak(PaintIndex);
  4159. sheet.Footer.Center = "第&P页,共&N页";
  4160. PaintIndex = PaintIndex + 1;
  4161. PageNum1 = 0;
  4162. }
  4163. PageNum1 = PageNum1 + 1;
  4164. pib_id.Add(DataTable.Rows[i]["pib_id"].ToString());
  4165. pib_outboxcode1.Add(BoxCode.ToString());
  4166. }
  4167. break;
  4168. case "BoxSplit":
  4169. string LastBoxCode1 = "";
  4170. int PageNum2 = 0;
  4171. string LastBatch = "";
  4172. int MidCount = 0;
  4173. int SerialNum = 1;
  4174. for (int i = 0; i < rowNum; i++)
  4175. {
  4176. IRow row1 = sheet.CreateRow(PaintIndex);
  4177. PaintIndex = PaintIndex + 1;
  4178. row1.HeightInPoints = RowHeight;
  4179. //如果批号不相等的时候
  4180. if (LastBatch == "" || (LastBatch != "" && LastBatch != DataTable.Rows[i]["ch_splitbatch"].ToString()))
  4181. {
  4182. LastBatch = DataTable.Rows[i]["ch_splitbatch"].ToString();
  4183. for (int j = 0; j < ShowColumnCount; j++)
  4184. {
  4185. if (j == 0)
  4186. {
  4187. row1.CreateCell(j).SetCellValue(SerialNum - 1);
  4188. }
  4189. else if (j == 1)
  4190. {
  4191. row1.CreateCell(j).SetCellValue("批小计");
  4192. }
  4193. else if (j == 2)
  4194. {
  4195. row1.CreateCell(j).SetCellValue(MidCount);
  4196. }
  4197. else if (j == ShowBoxIndex - 4)
  4198. {
  4199. row1.CreateCell(j).SetCellValue(LastBox);
  4200. }
  4201. else
  4202. {
  4203. row1.CreateCell(j);
  4204. }
  4205. row1.Cells[j].CellStyle = styleborder;
  4206. }
  4207. SerialNum = 1;
  4208. MidCount = 0;
  4209. row1 = sheet.CreateRow(PaintIndex);
  4210. PaintIndex = PaintIndex + 1;
  4211. }
  4212. if (LastBoxCode1 != "" && LastBoxCode1 != DataTable.Rows[i]["CH_PBCODE"].ToString())
  4213. {
  4214. BoxCode = BoxCode + 1;
  4215. for (int j = 0; j < ShowColumnCount; j++)
  4216. {
  4217. row1.CreateCell(j);
  4218. if (j == 1)
  4219. {
  4220. row1.Cells[j].SetCellValue(小计);
  4221. row1.Cells[j].CellStyle = styleborder;
  4222. }
  4223. else if (j == 0)
  4224. {
  4225. row1.Cells[0].SetCellValue(PageNum2);
  4226. row1.Cells[j].CellStyle = styleborder;
  4227. }
  4228. else if (j == ShowCountIndex)
  4229. {
  4230. row1.Cells[j].SetCellValue(sumCount);
  4231. row1.Cells[j].CellStyle = styleborder;
  4232. }
  4233. else if (j == ShowBoxIndex - 4)
  4234. {
  4235. row1.Cells[j].SetCellValue(LastBox);
  4236. row1.Cells[j].CellStyle = styleborder;
  4237. }
  4238. else
  4239. {
  4240. row1.Cells[j].CellStyle = styleborder;
  4241. }
  4242. }
  4243. sumCount = 0;
  4244. row1 = sheet.CreateRow(PaintIndex);
  4245. sheet.SetRowBreak(PaintIndex - 1);
  4246. sheet.Footer.Center = "第&P页,共&N页";
  4247. PaintIndex = PaintIndex + 1;
  4248. PageNum2 = 0;
  4249. }
  4250. //每次到了页数开始分页
  4251. if (LastBoxCode1 == "" || (LastBoxCode1 != "" && LastBoxCode1 != DataTable.Rows[i]["ch_pbcode"].ToString()) || ((i == rowNum - 1) && (LastBoxCode1 != "" && LastBoxCode1 != DataTable.Rows[i]["ch_pbcode"].ToString())))
  4252. {
  4253. LastBoxCode1 = DataTable.Rows[i]["CH_PBCODE"].ToString();
  4254. //第一行添加客户信息
  4255. //if (i != rowNum - 1)
  4256. {
  4257. for (int j = 0; j < columnNum - 3; j++)
  4258. {
  4259. if (j == 0)
  4260. {
  4261. row1.CreateCell(j);
  4262. row1.Cells[j].SetCellValue(DataTable.Columns["pi_title"].Caption + ":" + DataTable.Rows[i]["pi_title"].ToString() + " "
  4263. + DataTable.Columns["ch_level"].Caption + ":" + DataTable.Rows[i]["ch_level"].ToString());
  4264. }
  4265. else if (j == ShowDataIndex)
  4266. {
  4267. row1.CreateCell(j);
  4268. row1.Cells[j].SetCellValue(DataTable.Columns["pi_inoutno"].Caption + ":" + DataTable.Rows[i]["pi_inoutno"].ToString());
  4269. }
  4270. else
  4271. {
  4272. row1.CreateCell(j);
  4273. }
  4274. row1.GetCell(j).CellStyle = style;
  4275. }
  4276. row1 = sheet.CreateRow(PaintIndex);
  4277. PaintIndex = PaintIndex + 1;
  4278. //第二行添加型号
  4279. for (int j = 0; j < columnNum - 3; j++)
  4280. {
  4281. if (j == 0)
  4282. {
  4283. row1.CreateCell(j);
  4284. row1.Cells[j].SetCellValue(DataTable.Columns["pr_orispeccode"].Caption + ":" + DataTable.Rows[i]["pr_orispeccode"].ToString() + "_"
  4285. + "(" + DataTable.Rows[i]["me_desc"].ToString() + ") " + DataTable.Columns["pi_chipouttype"].Caption + ":" + DataTable.Rows[i]["pi_chipouttype"].ToString());
  4286. }
  4287. else if (j == ShowSizeIndex)
  4288. {
  4289. row1.CreateCell(j);
  4290. row1.Cells[j].SetCellValue(DataTable.Columns["pr_size"].Caption + ":" + DataTable.Rows[i]["pr_size"].ToString());
  4291. }
  4292. else if (j == ShowDataIndex)
  4293. {
  4294. row1.CreateCell(j);
  4295. row1.Cells[j].SetCellValue(DataTable.Columns["pi_date"].Caption + ":" + DataTable.Rows[i]["pi_date"].ToString());
  4296. }
  4297. else
  4298. {
  4299. row1.CreateCell(j);
  4300. }
  4301. }
  4302. //添加列名
  4303. row1 = sheet.CreateRow(PaintIndex);
  4304. PaintIndex = PaintIndex + 1;
  4305. int showcount = 0;
  4306. for (int j = 4; j < columnNum; j++)
  4307. {
  4308. if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent1")
  4309. {
  4310. showcount = showcount + 1;
  4311. row1.CreateCell(j - 4);
  4312. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  4313. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname1"].ToString());
  4314. }
  4315. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent2")
  4316. {
  4317. showcount = showcount + 1;
  4318. row1.CreateCell(j - 4);
  4319. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  4320. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname2"].ToString());
  4321. }
  4322. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent3")
  4323. {
  4324. showcount = showcount + 1;
  4325. row1.CreateCell(j - 4);
  4326. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  4327. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname3"].ToString());
  4328. }
  4329. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent4")
  4330. {
  4331. showcount = showcount + 1;
  4332. row1.CreateCell(j - 4);
  4333. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  4334. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname4"].ToString());
  4335. }
  4336. else if (DataTable.Columns[j].ColumnName.ToLower() == "chw_percent5")
  4337. {
  4338. showcount = showcount + 1;
  4339. row1.CreateCell(j - 4);
  4340. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  4341. row1.Cells[j - 4].SetCellValue(DataTable.Rows[0]["chw_itemname5"].ToString());
  4342. }
  4343. else if ((NotShowColumn.Contains(DataTable.Columns[j].ColumnName.ToLower())))
  4344. {
  4345. }
  4346. else
  4347. {
  4348. showcount = showcount + 1;
  4349. row1.CreateCell(j - 4);
  4350. row1.Cells[j - 4].CellStyle = ColumnTitleStyle;
  4351. row1.Cells[j - 4].SetCellValue(DataTable.Columns[j].Caption);
  4352. }
  4353. if (DataTable.Columns[j].ColumnName.ToLower().ToString() == "ch_pbcode")
  4354. {
  4355. ShowBoxIndex = j;
  4356. }
  4357. if (DataTable.Columns[j].ColumnName.ToString() == "io_qty")
  4358. {
  4359. NumIndex = j;
  4360. if (ShowCountIndex == NumIndex - 4)
  4361. {
  4362. ShowCountIndex = ShowCountIndex + 1;
  4363. }
  4364. }
  4365. }
  4366. ShowColumnCount = showcount;
  4367. row1 = sheet.CreateRow(PaintIndex);
  4368. PaintIndex = PaintIndex + 1;
  4369. }
  4370. }
  4371. //添加数据内容
  4372. for (int j = 4; j < columnNum; j++)
  4373. {
  4374. string Data = DataTable.Rows[i][j].ToString();
  4375. row1.CreateCell(j - 4);
  4376. if (DataTable.Columns[j].ColumnName.ToLower() == "ch_bluefilm")
  4377. {
  4378. row1.CreateCell(j - 4, CellType.String);
  4379. }
  4380. if ((NotShowColumn.Contains(DataTable.Columns[j].ColumnName.ToLower())))
  4381. {
  4382. }
  4383. else
  4384. {
  4385. row1.Cells[j - 4].SetCellValue(Data);
  4386. row1.GetCell(j - 4).CellStyle = styleborder;
  4387. }
  4388. if (DataTable.Columns[j].ColumnName == "io_qty")
  4389. {
  4390. row1.Cells[j - 4].SetCellValue(int.Parse(Data));
  4391. sumCount += int.Parse(DataTable.Rows[i][j].ToString());
  4392. totalCount += int.Parse(DataTable.Rows[i][j].ToString());
  4393. MidCount += int.Parse(DataTable.Rows[i][j].ToString());
  4394. }
  4395. if (DataTable.Columns[j].ColumnName == "rownum")
  4396. {
  4397. row1.Cells[j - 4].SetCellValue(SerialNum);
  4398. }
  4399. if (DataTable.Columns[j].ColumnName == "ch_code")
  4400. {
  4401. sheet.AutoSizeColumn(j - 4);
  4402. sheet.SetColumnWidth(j - 4, sheet.GetColumnWidth(j - 4) + 1000);
  4403. }
  4404. if (DataTable.Columns[j].ColumnName.ToLower() == "ch_pbcode")
  4405. {
  4406. LastBox = Data;
  4407. }
  4408. }
  4409. if (i == rowNum - 1)
  4410. {
  4411. PageNum2 = PageNum2 + 1;
  4412. row1 = sheet.CreateRow(PaintIndex);
  4413. PaintIndex = PaintIndex + 1;
  4414. for (int j = 0; j < ShowColumnCount; j++)
  4415. {
  4416. row1.CreateCell(j);
  4417. if (j == 0)
  4418. {
  4419. row1.Cells[j].SetCellValue(批小计);
  4420. row1.Cells[j].CellStyle = styleborder;
  4421. }
  4422. else if (j == ShowPieceIndex)
  4423. {
  4424. row1.Cells[ShowPieceIndex].SetCellValue(PageNum2);
  4425. row1.Cells[j].CellStyle = styleborder;
  4426. }
  4427. else if (j == ShowCountIndex)
  4428. {
  4429. row1.Cells[j].SetCellValue(sumCount);
  4430. row1.Cells[j].CellStyle = styleborder;
  4431. }
  4432. else if (j == ShowBoxIndex - 4)
  4433. {
  4434. row1.Cells[j].SetCellValue(LastBox);
  4435. row1.Cells[j].CellStyle = styleborder;
  4436. }
  4437. else
  4438. {
  4439. row1.Cells[j].CellStyle = styleborder;
  4440. }
  4441. }
  4442. row1 = sheet.CreateRow(PaintIndex);
  4443. PaintIndex = PaintIndex + 1;
  4444. for (int j = 0; j < ShowColumnCount; j++)
  4445. {
  4446. row1.CreateCell(j);
  4447. if (j == 0)
  4448. {
  4449. row1.Cells[j].SetCellValue(小计);
  4450. row1.Cells[j].CellStyle = styleborder;
  4451. }
  4452. else if (j == ShowPieceIndex)
  4453. {
  4454. row1.Cells[ShowPieceIndex].SetCellValue(PageNum2);
  4455. row1.Cells[j].CellStyle = styleborder;
  4456. }
  4457. else if (j == ShowCountIndex)
  4458. {
  4459. row1.Cells[j].SetCellValue(sumCount);
  4460. row1.Cells[j].CellStyle = styleborder;
  4461. }
  4462. else if (j == ShowBoxIndex - 4)
  4463. {
  4464. row1.Cells[j].SetCellValue(LastBox);
  4465. row1.Cells[j].CellStyle = styleborder;
  4466. }
  4467. else
  4468. {
  4469. row1.Cells[j].CellStyle = styleborder;
  4470. }
  4471. }
  4472. row1 = sheet.CreateRow(PaintIndex);
  4473. for (int j = 0; j < columnNum - 3; j++)
  4474. {
  4475. if (j == 0)
  4476. {
  4477. row1.CreateCell(j);
  4478. row1.Cells[j].SetCellValue("备注");
  4479. }
  4480. else if (j == ShowCountIndex)
  4481. {
  4482. row1.CreateCell(j);
  4483. row1.Cells[j].SetCellValue(totalCount);
  4484. }
  4485. else if (j == ShowRemarkPiece)
  4486. {
  4487. row1.CreateCell(j);
  4488. row1.Cells[j].SetCellValue(rowNum + 片);
  4489. }
  4490. else
  4491. {
  4492. row1.CreateCell(j);
  4493. }
  4494. row1.Cells[j].CellStyle = style;
  4495. }
  4496. sheet.SetRowBreak(PaintIndex);
  4497. sheet.Footer.Center = "第&P页,共&N页";
  4498. PaintIndex = PaintIndex + 1;
  4499. PageNum1 = 0;
  4500. }
  4501. PageNum2 = PageNum2 + 1;
  4502. pib_id.Add(DataTable.Rows[i]["pib_id"].ToString());
  4503. pib_outboxcode1.Add(BoxCode.ToString());
  4504. SerialNum = SerialNum + 1;
  4505. }
  4506. break;
  4507. default:
  4508. break;
  4509. }
  4510. dh.BatchInsert("update prodiobarcode set pib_outboxcode1=:pib_outboxcode1 where pib_inoutno='" + Inoutno + "' and pib_id=:pib_id", new string[] { "pib_outboxcode1", "pib_id" }, pib_outboxcode1.ToArray(), pib_id.ToArray());
  4511. //删除下载链接再重新插入
  4512. HttpHandler.GenDownLoadLinK(Inoutno);
  4513. //填充首页
  4514. sumCount = 0;
  4515. totalCount = 0;
  4516. PaintIndex = 1;
  4517. ISheet sheet2 = book.CreateSheet("首页");
  4518. row = sheet2.CreateRow(0);
  4519. row.CreateCell(0);
  4520. row.Cells[0].SetCellValue(" " + companyname);
  4521. row.GetCell(0).CellStyle = style;
  4522. rowNum = FirstDT.Rows.Count;
  4523. //不需要显示的列移除
  4524. for (int i = FirstDT.Columns.Count - 1; i > 0; i--)
  4525. {
  4526. for (int j = 0; j < box.Length; j++)
  4527. {
  4528. if (box[j].Name == "FirstPage_WID" && !box[j].Checked)
  4529. {
  4530. if (FirstDT.Columns[i].ColumnName.ToLower().Contains("ch_waterid"))
  4531. {
  4532. FirstDT.Columns.RemoveAt(i);
  4533. }
  4534. }
  4535. if (box[j].Name == "FirstPage_YIELD" && !box[j].Checked)
  4536. {
  4537. if (FirstDT.Columns[i].ColumnName.ToLower().Contains("ch_yeild"))
  4538. {
  4539. FirstDT.Columns.RemoveAt(i);
  4540. }
  4541. }
  4542. if (box[j].Name == "FirstPage_REMARK" && !box[j].Checked)
  4543. {
  4544. if (FirstDT.Columns[i].ColumnName.ToLower().Contains("ch_remark"))
  4545. {
  4546. FirstDT.Columns.RemoveAt(i);
  4547. }
  4548. }
  4549. if (box[j].Name == "FirstPage_BOXCODE" && !box[j].Checked)
  4550. {
  4551. if (FirstDT.Columns[i].ColumnName.ToLower().Contains("ch_pbcode"))
  4552. {
  4553. FirstDT.Columns.RemoveAt(i);
  4554. }
  4555. }
  4556. }
  4557. }
  4558. columnNum = FirstDT.Columns.Count;
  4559. for (int i = 0; i < rowNum; i++)
  4560. {
  4561. IRow row1 = sheet2.CreateRow(PaintIndex);
  4562. PaintIndex = PaintIndex + 1;
  4563. row1.HeightInPoints = RowHeight;
  4564. //只需要绘制一行
  4565. if (i == 0)
  4566. {
  4567. for (int j = 0; j < columnNum - 3; j++)
  4568. {
  4569. if (j == 0)
  4570. {
  4571. row1.CreateCell(j);
  4572. row1.Cells[j].SetCellValue(FirstDT.Columns["pi_title"].Caption + ":" + FirstDT.Rows[i]["pi_title"].ToString() + " "
  4573. + FirstDT.Columns["ch_level"].Caption + ":" + FirstDT.Rows[i]["ch_level"].ToString());
  4574. }
  4575. else if (j > 5 && j == columnNum - 5)
  4576. {
  4577. row1.CreateCell(j);
  4578. row1.Cells[j].SetCellValue(FirstDT.Columns["pi_inoutno"].Caption + ":" + FirstDT.Rows[i]["pi_inoutno"].ToString());
  4579. }
  4580. else if (columnNum > 5 && j == columnNum - 5)
  4581. {
  4582. row1.CreateCell(j);
  4583. row1.Cells[j].SetCellValue(FirstDT.Columns["pi_inoutno"].Caption + ":" + FirstDT.Rows[i]["pi_inoutno"].ToString());
  4584. }
  4585. else
  4586. {
  4587. row1.CreateCell(j);
  4588. }
  4589. row1.GetCell(j).CellStyle = style;
  4590. }
  4591. row1 = sheet2.CreateRow(PaintIndex);
  4592. PaintIndex = PaintIndex + 1;
  4593. //第二行添加型号
  4594. for (int j = 0; j < columnNum - 3; j++)
  4595. {
  4596. if (j == 0)
  4597. {
  4598. row1.CreateCell(j).SetCellValue(FirstDT.Columns["pr_orispeccode"].Caption + ":" + FirstDT.Rows[i]["pr_orispeccode"].ToString() + "_"
  4599. + "(" + FirstDT.Rows[i]["me_desc"].ToString() + ")" + FirstDT.Columns["pi_chipouttype"].Caption + ":" + FirstDT.Rows[i]["pi_chipouttype"].ToString());
  4600. }
  4601. else if (j == ShowSizeIndex)
  4602. {
  4603. row1.CreateCell(j);
  4604. row1.Cells[j].SetCellValue(FirstDT.Columns["pr_size"].Caption + ":" + FirstDT.Rows[i]["pr_size"].ToString());
  4605. }
  4606. else if (j > 5 && j == columnNum - 5)
  4607. {
  4608. row1.CreateCell(j);
  4609. row1.Cells[j].SetCellValue(FirstDT.Columns["pi_date"].Caption + ":" + FirstDT.Rows[i]["pi_date"].ToString());
  4610. }
  4611. else if (columnNum > 5 && j == columnNum - 5)
  4612. {
  4613. row1.CreateCell(j);
  4614. row1.Cells[j].SetCellValue(FirstDT.Columns["pi_date"].Caption + ":" + FirstDT.Rows[i]["pi_date"].ToString());
  4615. }
  4616. else
  4617. {
  4618. row1.CreateCell(j);
  4619. }
  4620. row1.GetCell(j).CellStyle = style;
  4621. }
  4622. row1 = sheet2.CreateRow(PaintIndex);
  4623. PaintIndex = PaintIndex + 1;
  4624. //添加列名
  4625. for (int j = 4; j < columnNum; j++)
  4626. {
  4627. row1.CreateCell(j - 4);
  4628. if (NotShowColumn.Contains(FirstDT.Columns[j].ColumnName.ToLower()))
  4629. {
  4630. }
  4631. else
  4632. {
  4633. row1.Cells[j - 4].CellStyle = styleborder;
  4634. row1.Cells[j - 4].SetCellValue(FirstDT.Columns[j].Caption);
  4635. }
  4636. }
  4637. row1 = sheet2.CreateRow(PaintIndex);
  4638. PaintIndex = PaintIndex + 1;
  4639. }
  4640. //添加数据内容
  4641. ShowColumnCount = 0;
  4642. for (int j = 4; j < columnNum; j++)
  4643. {
  4644. row1.CreateCell(j - 4);
  4645. if (FirstDT.Columns[j].ColumnName == "ch_bluefilm")
  4646. {
  4647. row1.CreateCell(j - 4, CellType.String);
  4648. }
  4649. if (!NotShowColumn.Contains(FirstDT.Columns[j].ColumnName.ToLower()))
  4650. {
  4651. string Data = FirstDT.Rows[i][j].ToString();
  4652. row1.Cells[j - 4].SetCellValue(Data);
  4653. row1.GetCell(j - 4).CellStyle = styleborder;
  4654. if (FirstDT.Columns[j].ColumnName == "num")
  4655. {
  4656. row1.Cells[j - 4].SetCellValue(int.Parse(Data));
  4657. sumCount += int.Parse(Data);
  4658. }
  4659. if (FirstDT.Columns[j].ColumnName == "io_qty")
  4660. {
  4661. totalCount += int.Parse(Data);
  4662. row1.Cells[j - 4].SetCellValue(int.Parse(Data));
  4663. }
  4664. ShowColumnCount = ShowColumnCount + 1;
  4665. }
  4666. else
  4667. {
  4668. }
  4669. }
  4670. //添加总计行
  4671. if (i == rowNum - 1)
  4672. {
  4673. row1 = sheet2.CreateRow(PaintIndex);
  4674. PaintIndex = PaintIndex + 1;
  4675. for (int j = 0; j < ShowColumnCount; j++)
  4676. {
  4677. if (j == 0)
  4678. {
  4679. row1.CreateCell(j);
  4680. row1.Cells[j].CellStyle = styleborder;
  4681. row1.Cells[j].SetCellValue(总计);
  4682. }
  4683. else if (j == ShowColumnCount - 2)
  4684. {
  4685. row1.CreateCell(j);
  4686. row1.Cells[j].CellStyle = styleborder;
  4687. row1.Cells[j].SetCellValue(sumCount);
  4688. }
  4689. else if (j == ShowColumnCount - 1)
  4690. {
  4691. row1.CreateCell(j);
  4692. row1.Cells[j].CellStyle = styleborder;
  4693. row1.Cells[j].SetCellValue(totalCount);
  4694. }
  4695. else
  4696. {
  4697. row1.CreateCell(j);
  4698. row1.Cells[j].CellStyle = styleborder;
  4699. }
  4700. }
  4701. }
  4702. }
  4703. //将book的内容写入内存流中返回
  4704. book.Write(ms);
  4705. return ms;
  4706. }
  4707. }
  4708. }