NPOI version: 2.2.1.0
Recently, the company has had this demand:
Statistics the usage of multiple expenses under each department. There are multiple levels of departments, but when stating, you only need to count to level 2. Those below level 2 will be classified as below level 2. It is also required that the second-level department has a subtotal and the first-level department needs a total. Finally, a total is required.
Originally, the research on NPOI was not in-depth enough. In the past, they directly exported all the data in DataTable through the code provided by others, but they did not contain merging and totaling functions, which did not meet the current needs. As a last resort, I had to study it carefully. Fortunately, the requirements were finally met.
Here, I would like to thank other people who provide relevant information for allowing me to implement this function.
Briefly explain the use:
1. Use the Export2Template2 method directly. DataTable raw data must be data that has been sorted as required. All are processed line by line.
2. The column name to be exported is taken from cellKeys. The column name must be that exists in the source.
3. The first column with the same value is cellKeys[0], and the second column of the merge is cellKeys[1]. If you need to merge other columns, you can adjust it according to your own needs based on this. (When merging, only up and down data content will be compared)
4. In the data to be exported, the numerical type will automatically be on the right. Other types, automatically centered.
5. Subtotal, total, total fonts, all black
6. Subtotal, total, total, automatically summarize the numerical types. All other types of data are empty.
7. Number of merged columns: mergeColumns. If >2, only the first 2 columns are processed automatically. If <1, no merge process is done.
Directly available code：
/// <summary>
/// Export Excel according to the template -- special processing, each group has a total/// </summary>
/// <param name="source">source DataTable</param>/// <param name="cellKeys">The corresponding column fields that need to be exported Example: string[] cellKeys = { "Date","Remarks" };</param>/// <param name="strFileName">The file name to be saved (including the suffix) Example: "The file name to be saved.xls"</param>/// <param name="templateFile">Template file name (including path suffix) Example: "Template file name.xls"</param>/// <param name="rowIndex">Create the data row from which row, the first row is 0</param>/// <param name="mergeColumns">When the values are the same, the first few columns that can be merged can be supported at most 2 columns. 1=Only merge the first column, 2=Judge the first 2 columns</param>/// <param name="isConver">Whether to overwrite the data, =false, will move the original data down. =true, will overwrite the data behind the insertion row</param>/// <param name="isTotal">Whether subtotal/total items</param>/// <param name="addAllTotal">Whether to add total items</param>/// <returns> Whether the export is successful</returns>public static bool Export2Template2(DataTable source, string[] cellKeys, string strFileName, string templateFile, int rowIndex, int mergeColumns, bool isConver, bool isTotal, bool addAllTotal)
{
bool bn = false;
int cellCount = ; //Total number of columns, the first column is 0 // IWorkbook workbook = null;
HSSFWorkbook workbook = null;
string temp0 = "", temp1 = "";
int start0 = 0, start1 = 0; // Record the start sequence number with the same value of 1 and 2 columns int end0 = 0, end1 = 0;// Record the end number with the same value of 1 and 2 columns
try
{
using (FileStream file = new FileStream(templateFile, , ))
{
workbook = new HSSFWorkbook(file);
}
#region Defines the cell style of four types of data // Content data format -- value ICellStyle styleNum = ();
= ;
= ;
= ;
= ;
// = ;
// = ;
// Content data format -- string (centered) ICellStyle styleStr = ();
= ;
= ;
= ;
= ;
= ;
= ;
// Summary data format -- value ICellStyle styleTotalNum = ();
= ;
= ;
= ;
= ;
= .;
= ;
= ;
// Set font color HSSFFont ffont0 = (HSSFFont)();
// = 14 * 14;
// = "Songyi"; = true;
// = ;
(ffont0);
// Summary data format -- string (centered) ICellStyle styleTotalStr = ();
= ;
= ;
= ;
= ;
= ;
= ;
= .;
= ;
// Set font color HSSFFont ffont1 = (HSSFFont)();
// = 14 * 14;
// = "Songyi"; = true;
// = ;
(ffont1);
#endregion
ISheet sheet = (0); // Open the first sheet page if (sheet != null && source != null && > 0) // The template content is empty and will not be processed {
IRow row;
for (int i = 0, len = ; i < len; i++)
{
if (!isConver) (rowIndex, , 1, true, false); // No overwrite, data is moved downward
#region The first line, after writing the data, assign the initial value to the variable if (i == 0) // The first line, assign the initial value {
row = (rowIndex);
#region Create columns and insert data //Create column and insert data for (int index = 0; index < cellCount; index++)
{
ICell cell = (index);
string strValue = !([i][cellKeys[index]] is DBNull) ? [i][cellKeys[index]].ToString() : ;
// Other columns of data, values are summarized switch ([cellKeys[index]].())
{
case "System.Int16": //Integral case "System.Int32":
case "System.Int64":
case "":
int intV = 0;
(strValue, out intV);
= styleNum; // Set the format (intV);
break;
case "": //Floating point type case "":
case "":
double doubV = 0;
(strValue, out doubV);
= styleNum; // Set the format (doubV);
break;
default:
= styleStr; // Set the format (strValue);
break;
}
}
#endregion
if (mergeColumns > 0)
{
temp0 = [i][cellKeys[0]].ToString(); // Save the value of the first column start0 = rowIndex;
end0 = rowIndex;
}
if (mergeColumns > 1)
{
temp1 = [i][cellKeys[1]].ToString(); // Save the value of the second column start1 = rowIndex;
end1 = rowIndex;
}
rowIndex++;
continue;
}
#endregion
// Not the first line of data processing // Determine whether the value of column 1 has changed string cellText0 = [i][cellKeys[0]].ToString();
if (temp0 != cellText0) // The value of column 1 has changed {
#region Column 2 is to be merged if (mergeColumns > 1) // Column 2 is to be merged {
if (start1 != end1) // The start line and the end line are different, so the merge is performed only {
CellRangeAddress region1 = new CellRangeAddress(start1, end1, 1, 1); // Merge the second column (region1);
}
#region Column 2 plus subtotal if (isTotal) // Add subtotal {
if (!isConver) (rowIndex, , 1, true, false); // No overwrite, data is moved downward
IRow rowTotal1 = (rowIndex);
//Create column and insert data #region Insert subtotal data for (int index = 0; index < cellCount; index++)
{
object obj1;
ICell newcell = (index);
if (index == 0) //Column 1 {
= styleTotalStr;
(temp0);
continue;
}
if (index == 1) // Column 2 {
= styleTotalStr;
("Subtotal");
continue;
}
// Other columns of data, values are summarized switch ([cellKeys[index]].())
{
case "System.Int16": //Integral case "System.Int32":
case "System.Int64":
case "":
obj1 = (("sum({0})", cellKeys[index]), ("{0} = '{1}' and {2} = '{3}' ", cellKeys[0], temp0, cellKeys[1], temp1));
int intV = 0;
((), out intV);
= styleTotalNum;
(intV);
break;
case "": //Floating point type case "":
case "":
obj1 = (("sum({0})", cellKeys[index]), ("{0} = '{1}' and {2} = '{3}' ", cellKeys[0], temp0, cellKeys[1], temp1));
double doubV = 0;
((), out doubV);
= styleTotalNum;
(doubV);
break;
default:
= styleTotalStr;
("");
break;
}
}
#endregion
// Merge subtotal CellRangeAddress region0 = new CellRangeAddress(rowIndex, rowIndex, 1, 2); // Merge subtotal (region0);
}
#endregion
temp1 = [i][cellKeys[1]].ToString();
end0++;
rowIndex++;
}
#endregion
#region Column 1 is to be merged if (mergeColumns > 0) // Column 1 is to be merged {
if (start0 != end0) // The start line and the end line are different, so the merge is performed only {
CellRangeAddress region0 = new CellRangeAddress(start0, end0, 0, 0); // Merge the second column (region0);
}
#region Column 1 plus total if (isTotal) // Add total {
if (!isConver) (rowIndex, , 1, true, false); // No overwrite, data is moved downward
IRow rowTotal0 = (rowIndex);
//Create column and insert data #region Add total column for (int index = 0; index < cellCount; index++)
{
object obj1;
ICell newcell = (index);
if (index == 0)
{
= styleTotalStr;
("total"); //Column 1 continue;
}
if (index == 1)
{
= styleTotalStr;
(""); // Column 2 continue;
}
switch ([cellKeys[index]].())
{
case "System.Int16": //Integral case "System.Int32":
case "System.Int64":
case "":
obj1 = (("sum({0})", cellKeys[index]), ("{0} = '{1}' ", cellKeys[0], temp0));
int intV = 0;
((), out intV);
= styleTotalNum;
(intV);
break;
case "": //Floating point type case "":
case "":
obj1 = (("sum({0})", cellKeys[index]), ("{0} = '{1}' ", cellKeys[0], temp0));
double doubV = 0;
((), out doubV);
= styleTotalNum;
(doubV);
break;
default:
= styleTotalStr;
("");
break;
}
}
#endregion
// Merge Total CellRangeAddress region0 = new CellRangeAddress(rowIndex, rowIndex, 0, 2); // Merge Total (region0);
end0++;
rowIndex++;
}
#endregion
temp0 = cellText0;
}
#endregion
// Reassign value start0 = rowIndex;
end0 = rowIndex;
start1 = rowIndex;
end1 = rowIndex;
}
else // The value of column 1 has not changed {
end0++;
// Determine whether there is any change in column 2 string cellText1 = [i][cellKeys[1]].ToString();
if (cellText1 != temp1) // Column 1 has not changed, column 2 has changed {
#region Column 2 is to be merged if (mergeColumns > 1) // Column 2 is to be merged {
if (start1 != end1) // The start line and the end line are different, so the merge is performed only {
CellRangeAddress region1 = new CellRangeAddress(start1, end1, 1, 1); // Merge the second column (region1);
}
#region Column 2 plus subtotal if (isTotal) // Add subtotal {
if (!isConver) (rowIndex, , 1, true, false); // No overwrite, data is moved downward
IRow rowTotal1 = (rowIndex);
//Create column and insert data #region Insert subtotal data for (int index = 0; index < cellCount; index++)
{
object obj1;
ICell newcell = (index);
if (index == 0) //Column 1 {
= styleTotalStr;
(temp0);
continue;
}
if (index == 1) // Column 2 {
= styleTotalStr;
("Subtotal");
continue;
}
// Other columns of data, values are summarized switch ([cellKeys[index]].())
{
case "System.Int16": //Integral case "System.Int32":
case "System.Int64":
case "":
obj1 = (("sum({0})", cellKeys[index]), ("{0} = '{1}' and {2} = '{3}' ", cellKeys[0], temp0, cellKeys[1], temp1));
int intV = 0;
((), out intV);
= styleTotalNum;
(intV);
break;
case "": //Floating point type case "":
case "":
obj1 = (("sum({0})", cellKeys[index]), ("{0} = '{1}' and {2} = '{3}' ", cellKeys[0], temp0, cellKeys[1], temp1));
double doubV = 0;
((), out doubV);
= styleTotalNum;
(doubV);
break;
default:
= styleTotalStr;
("");
break;
}
}
#endregion
// Merge subtotal CellRangeAddress region0 = new CellRangeAddress(rowIndex, rowIndex, 1, 2); // Merge subtotal (region0);
end0++;
rowIndex++;
}
temp1 = cellText1; // Reassign it only if you want to merge start1 = rowIndex;
end1 = rowIndex;
#endregion
}
#endregion
}
else // The value of column 1 has not changed, and the value of column 2 has not changed either. end1++;
}
// Insert the current data row = (rowIndex);
#region Create rows and insert data of the current record //Create row and insert the data of the current record for (int index = 0; index < cellCount; index++)
{
ICell cell = (index);<br>
string strValue = !([i][cellKeys[index]] is DBNull) ? [i][cellKeys[index]].ToString() : ; // Get the value switch ([cellKeys[index]].())
{
case "System.Int16": //Integral case "System.Int32":
case "System.Int64":
case "":
int intV = 0;
(strValue, out intV);
= styleNum;
(intV);
break;
case "": //Floating point type case "":
case "":
double doubV = 0;
(strValue, out doubV);
= styleNum;
(doubV);
break;
default:
= styleStr;
(strValue);
break;
}
}
#endregion
// Move down one line rowIndex++;
}
// Total of the last record #region Merge column 2 if (mergeColumns > 1) // Merge column 2 {
if (start1 != end1) // The start line and the end line are different, and merge it {
CellRangeAddress region1 = new CellRangeAddress(start1, end1, 1, 1); // Merge the second column (region1);
}
#region Column 2 plus subtotal if (isTotal) // Add subtotal {
if (!isConver) (rowIndex, , 1, true, false); // No overwrite, data is moved downward
IRow rowTotal1 = (rowIndex);
//Create column and insert data #region Insert subtotal data for (int index = 0; index < cellCount; index++)
{
object obj1;
ICell newcell = (index);
#region Column value processing if (index == 0) //Column 1 {
= styleTotalStr;
(temp0);
continue;
}
if (index == 1) // Column 2 {
= styleTotalStr;
("Subtotal");
continue;
}
// Other columns of data, values are summarized switch ([cellKeys[index]].())
{
case "System.Int16": //Integral case "System.Int32":
case "System.Int64":
case "":
obj1 = (("sum({0})", cellKeys[index]), ("{0} = '{1}' and {2} = '{3}' ", cellKeys[0], temp0, cellKeys[1], temp1));
int intV = 0;
((), out intV);
= styleTotalNum;
(intV);
break;
case "": //Floating point type case "":
case "":
obj1 = (("sum({0})", cellKeys[index]), ("{0} = '{1}' and {2} = '{3}' ", cellKeys[0], temp0, cellKeys[1], temp1));
double doubV = 0;
((), out doubV);
= styleTotalNum;
(doubV);
break;
default:
= styleTotalStr;
("");
break;
}
#endregion
}
#endregion
// Merge subtotal CellRangeAddress region0 = new CellRangeAddress(rowIndex, rowIndex, 1, 2); // Merge subtotal (region0);
rowIndex++;
end0++;
}
#endregion
}
#endregion
#region Merge column 1 if (mergeColumns > 0) // Merge column 1 {
if (start0 != end0) // The start line and the end line are different, and merge it {
CellRangeAddress region1 = new CellRangeAddress(start0, end0, 0, 0); // Merge the second column (region1);
}
#region Column 1 plus total if (isTotal) // Add total {
if (!isConver) (rowIndex, , 1, true, false); // No overwrite, data is moved downward
IRow rowTotal0 = (rowIndex);
//Create column and insert data #region Insert total data for (int index = 0; index < cellCount; index++)
{
object obj1;
ICell newcell = (index);
#region Column value processing if (index == 0) //Column 1 {
= styleTotalStr;
("total");
continue;
}
if (index == 1) // Column 2 {
= styleTotalStr;
("");
continue;
}
// Other columns of data, values are summarized switch ([cellKeys[index]].())
{
case "System.Int16": //Integral case "System.Int32":
case "System.Int64":
case "":
obj1 = (("sum({0})", cellKeys[index]), ("{0} = '{1}' ", cellKeys[0], temp0));
int intV = 0;
= styleTotalNum;
((), out intV);
(intV);
break;
case "": //Floating point type case "":
case "":
obj1 = (("sum({0})", cellKeys[index]), ("{0} = '{1}' ", cellKeys[0], temp0));
double doubV = 0;
((), out doubV);
= styleTotalNum;
(doubV);
break;
default:
= styleTotalStr;
("");
break;
}
#endregion
}
#endregion
// Merge Total CellRangeAddress region0 = new CellRangeAddress(rowIndex, rowIndex, 0, 2); // Merge Total (region0);
}
rowIndex++;
#endregion
}
#endregion
#region for summary - Add total if (addAllTotal) // Add total {
if (!isConver) (rowIndex, , 1, true, false); // No overwrite, data is moved downward
IRow rowTotal0 = (rowIndex);
//Create column and insert data #region Insert total data for (int index = 0; index < cellCount; index++)
{
object obj1;
ICell newcell = (index);
#region Column value processing if (index == 0) //Column 1 {
= styleTotalStr;
("total");
continue;
}
if (index == 1) // Column 2 {
= styleTotalStr;
("");
continue;
}
// Other columns of data, values are summarized switch ([cellKeys[index]].())
{
case "System.Int16": //Integral case "System.Int32":
case "System.Int64":
case "":
obj1 = (("sum({0})", cellKeys[index]), "");
int intV = 0;
((), out intV);
= styleTotalNum;
(intV);
break;
case "": //Floating point type case "":
case "":
obj1 = (("sum({0})", cellKeys[index]), "");
double doubV = 0;
((), out doubV);
= styleTotalNum;
(doubV);
break;
default:
= styleTotalStr;
("");
break;
}
#endregion
}
#endregion
// Merge Total CellRangeAddress region0 = new CellRangeAddress(rowIndex, rowIndex, 0, 2); // Merge Total (region0);
}
#endregion
}
return Save2Xls(strFileName, workbook); // Save as xls file }
catch (Exception ex)
{
// (logfile, "process data exception:" + ); // msg = ;
}
return bn;
}
Code to save the file:
public static bool Save2Xls(string fileName, IWorkbook workbook)
{
bool bn = false;
try
{
FileStream fs = new FileStream(fileName, );
MemoryStream ms = new MemoryStream();
(ms);
BinaryWriter w = new BinaryWriter(fs);
(());
();
();
bn = true;
}
catch(Exception ex)
{
//(logfile, "Save file exception: " + ); }
return bn;
}
The above article NPOI implements two-level combination and function (example explanation) is all the content I share with you. I hope you can give you a reference and I hope you can support me more.