Tag Info

Hot answers tagged

19

You don't need the initial InstallNET[]. That should come after Needs["NETLink"]. I made a post on this topic a while back, here: http://forums.wolfram.com/mathgroup/archive/2011/Oct/msg00386.html Some code to illustrate the method: Needs["NETLink`"] ReadFromExcel[file_String, cell_String, rows_Integer, cols_Integer] := Module[{excel, workbook, ...


16

Excel VBA enumeration values cannot be accessed symbolically through COM. We must use the corresponding numeric values found by consulting the Microsoft Excel object model enumeration reference. The relevant enumerations in this case are XlBordersIndex (xlDiagonalDown = 5) and XlBorderWeight (xlThick = 4). Once we know the enumeration values, the code is ...


13

In the answer linked here, I did the following: out = FileNameJoin @ {$TemporaryDirectory, "MathematicaOutput" <> ToString /@ Date[] <> ".rtf"} /tmp/MathematicaOutput2012519111731.900549.rtf Then you would say Export[out, ...]. If you want to have the date in a more readable and less detailed form, you could use this for the name: ...


12

Overall, your data is just badly formatted. For instance, later in the list your dates look similar to "3-Mar" which is interpreted as the third of March not March 2003, as you intended. For the most part, this is not your fault, but Excel arbitrarily formats data, and you have to be vigilant that it doesn't misinterpret it. Towards that end, I've rewritten ...


12

If your Excel file test.xls is very simple: Then the code is a one-liner (if I understand correctly what is needed): Set@@@Transpose[{ToExpression[First[#]], Transpose[Rest[#]]}&@Import["test.xls"][[1]]] To check: {Paris, Moscow} {{1., 2., 3., 4., 5., 6., 7., 8.}, {12., 23., 34., 45., 56., 67., 78., 89.}} The rest is more complex cases. ...


11

You can easily implement it in two steps. You create an Excel macro in your personal.xlsb that you can use to execute some keybord shortcut to copy your selected data. I use CTRL+SHIFT+C. Second you can create a Mathematica function to import this clipboard data (optional, but very usefull) More information on how to handle your personal.xlsb here How ...


10

You can export both data and the images using one of several syntax patterns that you find in the docs on XLS format: For example: g = CompleteGraph[7]; Export["output1.xls", {g, {"mySheet1" -> Normal@AdjacencyMatrix[g]}}, {{"Images", "Sheets"}}] gives EDIT: Exporting multiple images: It seems you need at least one sheet (which could be ...


10

Using SE as my rubber duck to organize ideas worked just nice. I found the answer in NETLinkUserGuide. To get current Excel session use GetActiveCOMObject instead of CreateCOMObject. Here is a code example. Needs["NETLink`"] InstallNET[] excel=GetActiveCOMObject["Excel.Application"] workbook=excel@Workbooks["MySpreadsheet.xlsx"]; ...


9

It seems there are some troubles with the XLSX format, whereas using the old XLS it works fine. Look the following tests: Import["c:\\tempmath\\test.xlsx", "Sheets"] {"SheetWithArrayFormula"} Import["c:\\tempmath\\test.xlsx", {"Sheets", "SheetWithArrayFormula"}] Import::fmterr: Cannot import data as XLSX format. >> $Failed ...


9

Let's say you've managed to import the data from excel and you want to transform list: {{strength, angle}...} so it fits the methods from the Q&A: How to create a wind rose with Mathematica?. There are couple of operations you must do: r = Table[{RandomReal[{0, 20}], RandomReal[{0, 360}]}, {t, 0, 15}]; (*sample data*) Your list is not sorted with ...


8

If you know the number of header rows, you can always just Drop that number of rows as part of the code to import the data. For example, suppose your file is called "myfile.xls" and the worksheet is called "Data". Then your code would be: data = Drop[Import["myfile.xls",{"Sheets","Data"}],1] or using Part with Span (;;): data = ...


8

I see several ways how you could achieve what you want. Any of them either needs extra "non-mathematica" software, efforts or knowledge. I think the best way is to learn how to interact with excel via .Net/COM as is described e.g. in the documentation, but you have mentioned that you want to avoid that. Here are the alternatives that I can think of: Use ...


8

If we define your parameters as: tstep = .5; tstop = 10; nss = StateSpaceModel[{ {{0, 1, 0, 0}, {5.3572, 7.602, 56.6571,18.102}, {0, 0, 0, 1}, {-5.3572, -7.602, -46.8571, -18.102}}, {{0}, {0.5}, {0}, \{-0.5}}}, SamplingPeriod -> None, SystemsModelLabels -> None]; Then store the response as a list: response = ...


8

There are a few different syntaxes for exporting formulas, all giving the same results. For example: Export["test1.xlsx", {"Data" -> {1, 2, 3, 4, 5}, "Formulas" -> {{{"", "A1*27", ""}, {"", "A2/3", "B1*B2"}}}}, "Rules"] Export["test2.xlsx", {{1, 2, 3, 4, 5}, {{{"", "A1*27", ""}, {"", "A2/3", "B1*B2"}}}}, {{"Data","Formulas"}}] someData = {1, 2, 3, ...


8

The following syntax works for exporting the two sheets: data1 = Table[{t, 9.8*t^2}, {t, 0, 3, .1}]; data2 = Table[{t, 9.8*t^2}, {t, 0, 5, .2}]; Export["datasets.xlsx", {"Experiment 1" -> data1, "Experiment 2" -> data2}] It creates an XLSX file with two seperate sheets holding the datasets: However, i agree with the comments above that QA for ...


7

Here's a variation on Jens' answer: "file-" <> DateString[Riffle[{"Year", "Month", "Day"}, "-"]] <> ".pdf" (* ==> "file-2012-05-19.pdf" *) which has the advantage of automatically padding the month and day with zeros so that the file names will sort nicely.


7

Per request, I'm posting this as an answer: The same problem is mentioned in the following support article: http://support.microsoft.com/kb/320369 The problem appears if the language of Excel differs from the locale setting of the operating system. One workaround is to set the system locale to match with the language of Excel (probably US English for ...


7

For example: (* some data *) pts = Range[10]^4; f1 = Interpolation[pts, InterpolationOrder -> 1]; f2 = Interpolation[pts, InterpolationOrder -> 3]; t1 = Join[{{"x", "f1[]"}}, Table[{x, f1@x}, {x, 1.5, 4.5, 1}]]; t2 = Join[{{"x", "f2[]"}}, Table[{x, f2@x}, {x, 1.5, 4.5, 1}]]; data = Join @@@ Transpose@{t1, t2}; (* Transformation from Excel "A1" ...


7

I've got VBA calling Mathematica functions. It's not without issues, but maybe some other smart people here can help with the hiccups. First things first: The .dll that Mathematica includes with its installation for .NETLink is not COM-compatible, meaning that VBA cannot find entry points into the dll functions. To get around this, .NET must be installed ...


7

a = {{{1}}, {{2}}}; q = {"one", "two"}; Export["c:\\test.xlsx", Rule @@@ Transpose@{q, a}]


7

it is not etirely clear for what you need that, but I think the best way to store data within a Notebook so that it is available in the next session is the TaggingRules option. This is how you could store data for a there: CurrentValue[EvaluationNotebook[],{TaggingRules,"dataset-name"}] = data; And this is how you can read it: data = ...


6

A simple solution is to use Rationalize on the result. If your original data is all integers, but with the head Real (e.g. {1., 2., 3.}) then they'll have the head Integer now (i.e., {1, 2, 3}).


6

If you need a basic data behind the graph, the AdjacencyMatrix I think is good for tabular formats. This will work: Export["graph_data.xls", AdjacencyMatrix[CompleteGraph[13]]] "graph_data.xls" If you need the image of the graph object, in addition to programmatic way (see @kguler answer) there is an interactive way I personally use often. The steps ...


6

With syntax errors fixed: Import[" appropriate path /Desktop/stproj.xls", "xls", "Data", 1] should import the file. Regarding population counts you are getting, the = sign at the beginning of an input cell invokes Wolfram Alpha query which allows free-form input (hence you get no syntax error warnings). Interestingly, Wolfram Alpha interprets the query ...


6

You can use Apply and StringSplit to help out: rawData = {{"a,b"}, {"c,d"}, {"e,f"}}; Apply[StringSplit[#, ","] &, rawData, {1}] (*output in InputForm to show structure more clearly*) {{"a", "b"}, {"c", "d"}, {"e", "f"}}


6

Unfortunately, it does not appear that this is possible without the Enterprise edition of Mathematica (or, perhaps, an alternative conclusion is that Mathematica is not the best tool for this job). As Mr. Wizard points out: I'm afraid you are in for disappointment. Apart from the Enterprise Edition or Wolfram Player Pro Mathematica does not have this ...


6

I don't think SemanticImport has been designed to deal with multiple Excel sheets. I work around this problem using Import to get the number of sheets and their names, and to import them and ExportString to export them as "TSV" which, on its turn, can be imported by SemanticImportString. An intermediate conversion of DateObject/TimeObject to DateString is ...


6

Following discussion with WRI tech support, setting Import's “Numeric” -> False has no effect on Excel files (.xls, .xlsx), but works when importing comma or tab delimited text files (.csv, .tsv). WRI understands that "this could be very frustrating and inconvenient for you" and apologizes for this but lacks the resources to fix. So either: (1) convert ...


5

Here is a way to invoke System.DateTime.FromOADate using NETLink: Needs["NETLink`"] InstallNET[]; LoadNETType["System.DateTime"]; fromOADate[d_] := DateList @ NETBlock @ System`DateTime`FromOADate[d]@ToString[] Note, however, that FromOADate does not share Excel's backward-compatible implementation of Lotus 1-2-3's date bug. To see this, we introduce ...


5

You can copy a tabulated data to clipboard and then paste it in a sheet with formatted cells without loss of formatting: CopyToClipboard@ Cell[StringReplace[ ExportString[myTable, "TSV"], "\r\n" -> "\n"], TextClipboardType -> "PlainText"] Alternatively, you could save your Excel file as XML file, Import this file in Mathematica and ...



Only top voted, non community-wiki answers of a minimum length are eligible