日付と時間の管理方法
Contents
[
Hide
]
Excelでの日付と時間の格納方法
日付と時刻はセル内に数値として格納されます。したがって、日付や時刻を含むセルの値は数値型です。日付と時刻を指定する数値は、日付（整数部分）と時刻（小数部分）から構成されています。Cell.doubleValueプロパティは、この数値を返します。
Aspose.Cellsでの日付と時間の表示方法
数値を日付と時刻として表示するには、Style.numberまたはStyle.Customプロパティを使用してセルに必要な日付と時刻の形式を適用します。CellValue.dateTimeValueプロパティは、この数値で表される日付と時刻を示すDateTimeオブジェクトを返します。
Aspose.Cellsでの2つの日付システムの切り替え方法
MS-Excelは、日付をシリアル値と呼ばれる数値として格納します。シリアル値は、日付システムの最初の日から経過した日数を示す整数です。Excelは、シリアル値のために以下の日付システムをサポートしています。
- 1900年の日付システム。最初の日付は1900年1月1日で、そのシリアル値は1です。最後の日付は9999年12月31日で、そのシリアル値は2,958,465です。この日付システムはワークブックのデフォルトで使用されます。
- 1904年日付システム。最初の日付は1904年1月1日で、そのシリアル値は0です。最後の日付は9999年12月31日で、そのシリアル値は2,957,003です。この日付システムをワークブックで使用するには、WorkbookSettings.date1904をtrueに設定します。
この例は、異なる日付システムで同じ日付に格納されたシリアル値が異なることを示しています。
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Aspose.Cells Date System Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat, Utils } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
// Creating a new Workbook object
const workbook = new Workbook();
// Set 1900/1904 date system to false (1900 system)
workbook.settings.date1904 = false;
// Obtaining the reference of the newly added worksheet (first worksheet)
const ws = workbook.worksheets.get(0);
const cells = ws.cells;
const dateData = new Date(2023, 10, 23); // JavaScript months are 0-based
// Setting the DateTime value to the cells (A1)
const a1 = cells.get("A1");
a1.value = dateData;
let resultHtml = '';
// Check if the cell contains a numeric value
if (a1.type === AsposeCells.CellValueType.IsNumeric) {
resultHtml += `<p>A1 is Numeric Value: ${a1.doubleValue}</p>`;
console.log("A1 is Numeric Value: " + a1.doubleValue);
} else {
resultHtml += `<p>A1 is not numeric. Cell type: ${a1.type}</p>`;
}
// Use the 1904 date system now
workbook.settings.date1904 = true;
console.log("use The 1904 date system====================");
// Setting the DateTime value to the cells (A2) using setter conversion
const a2 = cells.get("A2");
a2.value = dateData;
// Check if the cell contains a numeric value
if (a2.type === AsposeCells.CellValueType.IsNumeric) {
resultHtml += `<p>A2 is Numeric Value: ${a2.doubleValue}</p>`;
console.log("A2 is Numeric Value: " + a2.doubleValue);
} else {
resultHtml += `<p>A2 is not numeric. Cell type: ${a2.type}</p>`;
}
// Save the workbook to a downloadable file
const outputData = workbook.save(SaveFormat.Xlsx);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'output.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Excel File';
document.getElementById('result').innerHTML = `<p style="color: green;">Operation completed successfully!</p>${resultHtml}`;
});
</script>
</html>
出力結果：
A1 is Numeric Value: 45253
use The 1904 date system====================
A2 is Numeric Value: 43791
Aspose.Cellsでの日時値の設定方法
この例では、セルA1とA2に日時値を設定し、A1のカスタム形式とA2の数値形式を設定し、値のタイプを出力します。
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Example Title</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat, Utils } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
const fileInput = document.getElementById('fileInput');
if (!fileInput.files.length) {
// No file selected - proceed with a new blank workbook (matches original JavaScript behavior)
document.getElementById('result').innerHTML = '<p>No file selected. A new workbook will be created and modified.</p>';
}
// Instantiate or load workbook
let workbook;
if (fileInput.files.length) {
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
workbook = new Workbook(new Uint8Array(arrayBuffer));
} else {
workbook = new Workbook();
}
// Obtaining the reference of the first worksheet
let ws = workbook.worksheets.get(0);
let cells = ws.cells;
// Setting the DateTime value to the cell A1
let a1 = cells.get("A1");
a1.putValue(new Date());
// Check if the cell contains a numeric value
if (a1.type === AsposeCells.CellValueType.IsNumeric) {
console.log("A1 is Numeric Value: " + a1.isNumericValue());
}
let a1Style = a1.style;
// Set custom Datetime style
a1Style.custom = "mm-dd-yy hh:mm:ss";
a1.style = a1Style;
// Check if the cell contains a DateTime value
if (a1.type === AsposeCells.CellValueType.IsDateTime) {
console.log("Cell A1 contains a DateTime value.");
} else {
console.log("Cell A1 does not contain a DateTime value.");
}
// Setting the DateTime value to the cell A2
let a2 = cells.get("A2");
a2.putValue(new Date());
// Check if the cell contains a numeric value
if (a2.type === AsposeCells.CellValueType.IsNumeric) {
console.log("A2 is Numeric Value: " + a2.isNumericValue());
}
let a2Style = a2.style;
// Set the display format of numbers and dates.
a2Style.number = 22;
a2.style = a2Style;
// Check if the cell contains a DateTime value
if (a2.type === AsposeCells.CellValueType.IsDateTime) {
console.log("Cell A2 contains a DateTime value.");
} else {
console.log("Cell A2 does not contain a DateTime value.");
}
// Saving the modified Excel file
const outputData = workbook.save(SaveFormat.Xlsx);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'output.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Operation completed successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
出力結果：
A1 is Numeric Value: True
Cell A1 contains a DateTime value.
A2 is Numeric Value: True
Cell A2 contains a DateTime value.
Aspose.Cellsでの日時値の取得方法
この例では、セルA1とA2に日時値を設定し、A1のカスタム形式とA2の数値形式を設定し、2つのセルの値のタイプをチェックし、日時値とフォーマットされた文字列を出力します。
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
<meta charset="utf-8" />
<style>
body { font-family: Arial, sans-serif; margin: 20px; }
#downloadLink { margin-top: 10px; display: inline-block; }
#result p { margin: 8px 0; }
</style>
</head>
<body>
<h1>DateTime Cells Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat, Utils } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
const resultDiv = document.getElementById('result');
resultDiv.innerHTML = '';
const fileInput = document.getElementById('fileInput');
let workbook;
if (fileInput.files.length) {
const arrayBuffer = await fileInput.files[0].arrayBuffer();
workbook = new Workbook(new Uint8Array(arrayBuffer));
} else {
workbook = new Workbook();
}
// Obtaining the reference of the first worksheet
const ws = workbook.worksheets.get(0);
const cells = ws.cells;
// Setting the DateTime value to cell A1
const a1 = cells.get("A1");
a1.putValue(new Date());
// Check if the cell contains a numeric value
if (a1.type === AsposeCells.CellValueType.IsNumeric) {
console.log("A1 is Numeric Value: " + a1.isNumericValue);
resultDiv.innerHTML += `<p>A1 is Numeric Value: ${a1.isNumericValue}</p>`;
}
let a1Style = a1.style;
// Set custom Datetime style
a1Style.custom = "mm-dd-yy hh:mm:ss";
a1.style = a1Style;
// Check if the cell contains a DateTime value
if (a1.type === AsposeCells.CellValueType.IsDateTime) {
console.log("Cell A1 contains a DateTime value.");
const dateTimeValue = a1.dateTimeValue;
console.log("A1 DateTime Value: " + dateTimeValue);
console.log("A1 DateTime String Value: " + a1.stringValue);
resultDiv.innerHTML += `<p>Cell A1 contains a DateTime value: ${a1.stringValue}</p>`;
} else {
console.log("Cell A1 does not contain a DateTime value.");
resultDiv.innerHTML += `<p>Cell A1 does not contain a DateTime value.</p>`;
}
// Setting the DateTime value to cell A2
const a2 = cells.get("A2");
a2.putValue(new Date());
// Check if the cell contains a numeric value
if (a2.type === AsposeCells.CellValueType.IsNumeric) {
console.log("A2 is Numeric Value: " + a2.isNumericValue);
resultDiv.innerHTML += `<p>A2 is Numeric Value: ${a2.isNumericValue}</p>`;
}
let a2Style = a2.style;
// Set the display format of numbers and dates.
a2Style.number = 22;
a2.style = a2Style;
// Check if the cell contains a DateTime value
if (a2.type === AsposeCells.CellValueType.IsDateTime) {
console.log("Cell A2 contains a DateTime value.");
const dateTimeValue = a2.dateTimeValue;
console.log("A2 DateTime Value: " + dateTimeValue);
console.log("A2 DateTime String Value: " + a2.stringValue);
resultDiv.innerHTML += `<p>Cell A2 contains a DateTime value: ${a2.stringValue}</p>`;
} else {
console.log("Cell A2 does not contain a DateTime value.");
resultDiv.innerHTML += `<p>Cell A2 does not contain a DateTime value.</p>`;
}
// Saving the modified Excel file
const outputData = workbook.save(SaveFormat.Xlsx);
const blob = new Blob([outputData], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'output.xlsx';
downloadLink.style.display = 'inline-block';
downloadLink.textContent = 'Download Excel File';
resultDiv.innerHTML += '<p style="color: green;">Operation completed successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
出力結果：
A1 is Numeric Value: True
Cell A1 contains a DateTime value.
A1 DateTime Value: 11/23/2023 5:59:09 PM
A1 DateTime String Value: 11-23-23 17:59:09
A2 is Numeric Value: True
Cell A2 contains a DateTime value.
A2 DateTime Value: 11/23/2023 5:59:09 PM
A2 DateTime String Value: 11/23/2023 17:59