using System;
using ;
using ;
using ;
using ;
using ;
using ;
using ;
using ;
using ;
namespace XFormular
{
class OpenXmlSDKExporter
{
private static string[] Level = {"A", "B", "C", "D", "E", "F", "G",
"H", "I", "G", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T",
"U", "V", "W", "X", "Y", "Z" };
public static List<DataTable> Import(string path)
{
List<DataTable> tables = new List<DataTable>();
if ((ExcelHelper.POSTFIX_SVN))
return tables;
using (MemoryStream stream = (path))
{
using (SpreadsheetDocument doc = (stream, true))
{
foreach (Sheet sheet in <Sheet>())
{
DataTable table = new DataTable();
WorksheetPart worksheet = (WorksheetPart)();
List<string> columnsNames = new List<string>();
foreach (Row row in <Row>())
{
foreach (Cell cell in row)
{
string columnName = (, "[a-zA-Z]+").Value;
if (!(columnName))
{
(columnName);
}
}
}
(CompareColumn);
foreach (string columnName in columnsNames)
{
(columnName);
}
foreach (Row row in <Row>())
{
DataRow tableRow = ();
(tableRow);
foreach (Cell cell in row)
{
string columnName = (, "[a-zA-Z]+").Value;
tableRow[columnName] = GetValue(cell, );
}
}
if ( <= 0)
continue;
if ( <= 0)
continue;
(table);
}
}
}
return tables;
}
public static String GetValue(Cell cell, SharedStringTablePart stringTablePart)
{
if ( == 0)
return null;
//get cell value
String value = ;
//Look up real value from shared string table
if (( != null) && ( == ))
value =
.ChildElements[(value)]
.InnerText;
return value;
}
public static void Export(string path, List<DataTable> tables)
{
using (MemoryStream stream = ())
{
using (SpreadsheetDocument doc = (stream, true))
{
(doc, "Sheet1");
(doc, "Sheet2");
(doc, "Sheet3");
foreach (DataTable table in tables)
{
WorksheetPart sheet = (doc, );
WorksheetWriter writer = new WorksheetWriter(doc, sheet);
SpreadsheetStyle style = (doc);
foreach (DataRow row in )
{
for (int i = 0; i < ; i++)
{
string columnName = ("A", i);
string location = columnName + ((row) + 1);
(location, row[i].ToString(), style);
}
}
();
}
(path, stream);//Save to file
}
}
}
private static int CompareColumn(string x, string y)
{
int xIndex = Letter_to_num(x);
int yIndex = Letter_to_num(y);
return (yIndex);
}
/// <summary>
/// The numbers are converted into letters and used recursive algorithms
/// </summary>
/// <param name="value"></param>
/// <returns></returns>
private static string Num_to_letter(int value)
{
// Here we determine whether the input is the correct number, omitted (resentation judgment)
int remainder = value % 26;
//remainder = (remainder == 0) ? 26 : remainder;
int front = (value - remainder) / 26;
if (front < 26)
{
return Level[front - 1] + Level[remainder];
}
else
{
return Num_to_letter(front) + Level[remainder];
}
//return "";
}
/// <summary>
/// Convert hexadecimal letters to numbers
/// </summary>
/// <param name="letter"></param>
/// <returns></returns>
private static int Letter_to_num(string str)
{
//This is a string composed of A-Z letters, omitted (expression fragment)
char[] letter = (); //Split the string
int reNum = 0;
int power = 1; // used to calculate the value to the power
int times = 1; //The highest bit needs to be added 1
int num = ;//Get the number of strings
//Get the mantissa value of the last letter
reNum += Char_num(letter[num - 1]);
//Get the value of the last letter, which is more than two digits before this function is executed
if (num >= 2)
{
for (int i = num - 1; i > 0; i--)
{
power = 1;//To 1, used for the next cycle power calculation
for (int j = 0; j < i; j++)
{
power *= 26;
}
reNum += (power * (Char_num(letter[num - i - 1]) + times)); //The highest digit needs to be added 1, and the middle digit does not need to be added
times = 0;
}
}
//();
return reNum;
}
/// <summary>
/// Enter characters to get the corresponding number. This is the stupidest method and can also be encoded by ASIICK;
/// </summary>
/// <param name="ch"></param>
/// <returns></returns>
private static int Char_num(char ch)
{
switch (ch)
{
case 'A':
return 0;
case 'B':
return 1;
case 'C':
return 2;
case 'D':
return 3;
case 'E':
return 4;
case 'F':
return 5;
case 'G':
return 6;
case 'H':
return 7;
case 'I':
return 8;
case 'J':
return 9;
case 'K':
return 10;
case 'L':
return 11;
case 'M':
return 12;
case 'N':
return 13;
case 'O':
return 14;
case 'P':
return 15;
case 'Q':
return 16;
case 'R':
return 17;
case 'S':
return 18;
case 'T':
return 19;
case 'U':
return 20;
case 'V':
return 21;
case 'W':
return 22;
case 'X':
return 23;
case 'Y':
return 24;
case 'Z':
return 25;
}
return -1;
}
}
}