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, ...


17

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 ...


14

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 ...


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: "file-...


13

From a very pragmatic point of view, it might be easier to use an external tool such as xlsx2csv (Python script, but other alternatives exist). Then simply import the comma-separated values: ImportString[StringJoin @Riffle[ReadList[OpenRead["!./xlsx2csv.py test.xlsx"], "String"], "\n"], "CSV"]; On a 21 MB XLSX file on my Mac Book Pro, the above takes ...


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 ...


11

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"]; worksheet=workbook@...


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

I see there are no accepted answers for this question after more than 10 months so I thought I'd have a go at it. Although I have been using Mathematica for since V8, I am only an occasional user and hence not at all an expert like the others who have chimed in so far - but I'll give it a shot. Rather than using the Java based import to open and import the ...


9

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 = Import["myfile.xls",{"...


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 Import["c:\\...


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 ...


9

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 ...


8

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" ...


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 = Table[...


8

You can also try to increase the Java Virtual Machine Heap space using the following code Needs["JLink`"]; ReinstallJava[JVMArguments -> "-Xmx1536m"];


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

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

I know this is an old question, but I ran into this problem myself. The permanent solution for me was to alter the settings of JLink directly. As for most commenters too, increasing the heap-space by giving commandline options to ReinstallJava and friends didn't work. Specifically, I tried to increase the heap-space by specifying the complete CommandLine ...


7

I don't have a solution to your problem, but maybe some more ideas what you can try. I understand that you actually want a solution which would allow you to import the large excel file directly without workarounds, but as you have seen that might not be possible. You might want to contact WRI in the hope they will improve the import in future versions. For ...


7

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}).


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

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


7

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 ...


7

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 ...


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 = CurrentValue[...



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