# Tag Info

## Hot answers tagged excel

18

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

14

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

6

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

5

Here is a way to invoke System.DateTime.FromOADate using NETLink: Needs["NETLink"] InstallNET[]; LoadNETType["System.DateTime"]; fromOADate[d_] := DateList @ NETBlock @ SystemDateTimeFromOADate[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 ...

5

I have found the solution. From the Documentation, Some documents use names in a non-namespace-compliant fashion, because the XML namespace recommendation, which extends XML, was made after the initial XML recommendation. "IncludeNamespaces"->"Unparsed" is provided to allow parsing of these documents. The name is always represented as the ...

5

I Think what you want is something like (Evaluate[{Symbol@#[[1, 1]], Symbol@#[[1, 2]]}] = Transpose@##[[2 ;;]]) &@ Import["C:\\test1.xls"][[1]]); {Test1, test2} (* {{1., 2., 3., 4., "", "", "", "", "", ""}, {2., 4., 5., 7., "", "", "", "", "", ""}} *) Which assigns the columns to Mathematica ...

5

Assuming I understand correctly your question, here's what I would do: First, let's assign the second column to a variable a=BankShadowFed[[All,2]]. Now let's remove every , from the numbers in the list, using aa=StringReplace[a, "," -> ""]. We have a list of strings, which we can now turn into a list of expressions using aaa=Table[ToExpression[aa[[i]]], ...

5

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

5

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

5

Here's a recursion method: toexcelcolumn[col_?IntegerQ] /; col < 27 := FromCharacterCode[col + 64] toexcelcolumn[0] = "Z"; toexcelcolumn[col_?IntegerQ] := toexcelcolumn[Quotient[col - 1, 26]] <> toexcelcolumn[Mod[col, 26]] toexcelcolumn[13935] (*TOY*)

5

Ok, well, I only needed the inverse function so far and have implemented it as: FromExcelCol[col_String] := FromDigits[ToCharacterCode[col] - 64, 26] It runs fine because FromDigits does not complain about characters larger than base-1. However, the other way round seems to be more tricky. The leading digit runs from 0 to 26 (1 to 27 if you want -> base ...

5

How about using the basic definition. δβ/δρ = (β2-β1)/(ρ2-ρ1) Since you have 5 data points, β = {0.324, 0.322, 0.319, 0.317, 0.316}; ρ = {0.687, 0.695, 0.721, 0.759, 0.798}; ndata = 5; grad = Table[(β[[i + 1]] - β[[i]])/(ρ[[i + 1]] - ρ[[i]]), {i, 1, ndata - 1}]; (* # of data for δβ/δρ will be ndata-1 *) ...

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