Excelの取り込みをしてみます
※メモなので参考になりません
javascript
<script>
$(function () {
$("#input").on("change", function () {
var excelFile,
fileReader = new FileReader();
$("#result").hide();
fileReader.onload = function (e) {
var buffer = new Uint8Array(fileReader.result);
$.ig.excel.Workbook.load(buffer, function (workbook) {
var column, row, newRow, cellValue, columnIndex, i,
worksheet = workbook.worksheets(0),
columnsNumber = 0,
gridColumns = [],
data = [],
worksheetRowsCount;
// Both the columns and rows in the worksheet are lazily created and because of this most of the time worksheet.columns().count() will return 0
// So to get the number of columns we read the values in the first row and count. When value is null we stop counting columns:
while (worksheet.rows(0).getCellValue(columnsNumber)) {
columnsNumber++;
}
// Iterating through cells in first row and use the cell text as key and header text for the grid columns
for (columnIndex = 0; columnIndex < columnsNumber; columnIndex++) {
column = worksheet.rows(0).getCellText(columnIndex);
gridColumns.push({ headerText: column, key: column });
}
// We start iterating from 1, because we already read the first row to build the gridColumns array above
// We use each cell value and add it to json array, which will be used as dataSource for the grid
for (i = 1, worksheetRowsCount = worksheet.rows().count() ; i < worksheetRowsCount; i++) {
newRow = {};
row = worksheet.rows(i);
for (columnIndex = 0; columnIndex < columnsNumber; columnIndex++) {
cellValue = row.getCellText(columnIndex);
newRow[gridColumns[columnIndex].key] = cellValue;
}
data.push(newRow);
}
// we can also skip passing the gridColumns use autoGenerateColumns = true, or modify the gridColumns array
createGrid(data, gridColumns);
}, function (error) {
$("#result").text("Excel ファイルは破損しています。");
$("#result").show(1000);
});
}
if (this.files.length > 0) {
excelFile = this.files[0];
if (excelFile.type === "application/vnd.ms-excel" || excelFile.type === "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" || (excelFile.type === "" && (excelFile.name.endsWith("xls") || excelFile.name.endsWith("xlsx")))) {
fileReader.readAsArrayBuffer(excelFile);
} else {
$("#result").text("選択のファイル形式がサポートされていません。有効な Excel ファイルを選択してください ('.xls, *.xlsx')。");
$("#result").show(1000);
}
}
})
});
function createGrid(data, gridColumns) {
if ($("#grid1").data("igGrid") !== undefined) {
$("#grid1").igGrid("destroy");
}
$("#grid1").igGrid({
columns: gridColumns,
autoGenerateColumns: true,
dataSource: data,
width: "100%"
});
}
</script>
CoffeeScriptへコンパイル
XXX.coffee
createGrid = (data, gridColumns) ->
if $('#grid1').data('igGrid') != undefined
$('#grid1').igGrid 'destroy'
$('#grid1').igGrid
columns: gridColumns
autoGenerateColumns: true
dataSource: data
width: '100%'
return
$ ->
$('#input').on 'change', ->
excelFile = undefined
fileReader = new FileReader
$('#result').hide()
fileReader.onload = (e) ->
buffer = new Uint8Array(fileReader.result)
$.ig.excel.Workbook.load buffer, ((workbook) ->
column = undefined
row = undefined
newRow = undefined
cellValue = undefined
columnIndex = undefined
i = undefined
worksheet = workbook.worksheets(0)
columnsNumber = 0
gridColumns = []
data = []
worksheetRowsCount = undefined
# Both the columns and rows in the worksheet are lazily created and because of this most of the time worksheet.columns().count() will return 0
# So to get the number of columns we read the values in the first row and count. When value is null we stop counting columns:
while worksheet.rows(0).getCellValue(columnsNumber)
columnsNumber++
# Iterating through cells in first row and use the cell text as key and header text for the grid columns
columnIndex = 0
while columnIndex < columnsNumber
column = worksheet.rows(0).getCellText(columnIndex)
gridColumns.push
headerText: column
key: column
columnIndex++
# We start iterating from 1, because we already read the first row to build the gridColumns array above
# We use each cell value and add it to json array, which will be used as dataSource for the grid
i = 1
worksheetRowsCount = worksheet.rows().count()
while i < worksheetRowsCount
newRow = {}
row = worksheet.rows(i)
columnIndex = 0
while columnIndex < columnsNumber
cellValue = row.getCellText(columnIndex)
newRow[gridColumns[columnIndex].key] = cellValue
columnIndex++
data.push newRow
i++
# we can also skip passing the gridColumns use autoGenerateColumns = true, or modify the gridColumns array
createGrid data, gridColumns
return
), (error) ->
$('#result').text 'Excel ファイルは破損しています。'
$('#result').show 1000
return
return
if @files.length > 0
excelFile = @files[0]
if excelFile.type == 'application/vnd.ms-excel' or excelFile.type == 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' or excelFile.type == '' and (excelFile.name.endsWith('xls') or excelFile.name.endsWith('xlsx'))
fileReader.readAsArrayBuffer excelFile
else
$('#result').text '選択のファイル形式がサポートされていません。有効な Excel ファイルを選択してください (\'.xls, *.xlsx\')。'
$('#result').show 1000
return
return
↓これを直書きで記述すると使えることは確認
ただ、coffeescriptを使うと表示されなかったり、jsファイルに記述して上手く読み込めない
index.html.erb
<script src="http://ajax.aspnetcdn.com/ajax/modernizr/modernizr-2.8.3.js"></script>
<script src="http://code.jquery.com/jquery-1.11.3.min.js"></script>
<script src="http://code.jquery.com/ui/1.11.1/jquery-ui.min.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.1/latest/js/infragistics.core.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.1/latest/js/infragistics.lob.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.1/latest/js/modules/infragistics.ext_core.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.1/latest/js/modules/infragistics.ext_collections.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.1/latest/js/modules/infragistics.ext_text.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.1/latest/js/modules/infragistics.ext_io.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.1/latest/js/modules/infragistics.ext_ui.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.1/latest/js/modules/infragistics.documents.core_core.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.1/latest/js/modules/infragistics.ext_collectionsextended.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.1/latest/js/modules/infragistics.excel_core.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.1/latest/js/modules/infragistics.ext_threading.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.1/latest/js/modules/infragistics.ext_web.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.1/latest/js/modules/infragistics.xml.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.1/latest/js/modules/infragistics.documents.core_openxml.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.1/latest/js/modules/infragistics.excel_serialization_openxml.js"></script>
</head>
<body>
<div>
<ol>
<!-- <li>サンプル <a href="https://jp.igniteui.com/HtmlSamples/javascript-excel-library/report.xlsx" download>Excel ファイルのダウンロード</a></li> -->
<li>[ファイルを選択]/[参照...] ボタンをクリックして、サンプル Excel ファイルを選択します。</li>
</ol>
<input type="file" id="input" accept="application/vnd.ms-excel, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"/>
<div id="result"></div>
<table id="grid1"></table>
</div>
<script>
$(function () {
$("#input").on("change", function () {
var excelFile,
fileReader = new FileReader();
$("#result").hide();
fileReader.onload = function (e) {
var buffer = new Uint8Array(fileReader.result);
$.ig.excel.Workbook.load(buffer, function (workbook) {
var column, row, newRow, cellValue, columnIndex, i,
worksheet = workbook.worksheets(0),
columnsNumber = 0,
gridColumns = [],
data = [],
worksheetRowsCount;
// 最初の行とcountの値を読み取る列の数を取得。
// valueがnullの場合、列のカウントを停止します。
while (worksheet.rows(0).getCellValue(columnsNumber)) {
columnsNumber++;
}
// 最初の行のセルを反復し、セルテキストをグリッド列のキーとヘッダーテキストとして使用する
for (columnIndex = 0; columnIndex < columnsNumber; columnIndex++) {
column = worksheet.rows(0).getCellText(columnIndex);
gridColumns.push({ headerText: column, key: column });
}
//上のgridColumns配列を構築するために最初の行をすでに読み込んでいるので、1から反復を開始
//各セル値を使用してjson配列に追加します.json配列はグリッドのdataSourceとして使用
for (i = 1, worksheetRowsCount = worksheet.rows().count() ; i < worksheetRowsCount; i++) {
newRow = {};
row = worksheet.rows(i);
for (columnIndex = 0; columnIndex < columnsNumber; columnIndex++) {
cellValue = row.getCellText(columnIndex);
newRow[gridColumns[columnIndex].key] = cellValue;
}
data.push(newRow);
}
// gridColumnsを渡すのをスキップすることもできます。
// autoGenerateColumns = trueを使用するか、gridColumns配列を変更します
createGrid(data, gridColumns);
}, function (error) {
$("#result").text("Excel ファイルは破損しています。");
$("#result").show(1000);
});
}
if (this.files.length > 0) {
excelFile = this.files[0];
if (excelFile.type === "application/vnd.ms-excel" || excelFile.type === "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" || (excelFile.type === "" && (excelFile.name.endsWith("xls") || excelFile.name.endsWith("xlsx")))) {
fileReader.readAsArrayBuffer(excelFile);
} else {
$("#result").text("選択のファイル形式がサポートされていません。有効な Excel ファイルを選択してください ('.xls, *.xlsx')。");
$("#result").show(1000);
}
}
})
});
function createGrid(data, gridColumns) {
if ($("#grid1").data("igGrid") !== undefined) {
$("#grid1").igGrid("destroy");
}
$("#grid1").igGrid({
columns: gridColumns,
autoGenerateColumns: true,
dataSource: data,
width: "100%"
});
}
</script>
</body>
参考」:https://jp.igniteui.com/javascript-excel-library/excel-import-data
:http://js2.coffee/