public static Object[] Data(DataTable dtGrid)
{
var rows = new object[dtGrid.Rows.Count];
for (int i = 0; i < dtGrid.Rows.Count; i++)
{
var fields = new object[dtGrid.Columns.Count];
int colIndex = 0;
for (int j = 0; j < dtGrid.Columns.Count; j++)
{
fields[colIndex] = DataConvert.ToString(dtGrid.Rows[i][j]);
colIndex += 1;
}
rows[i] = new
{
id = i + 1,
cell = fields
};
}
return(rows);
}
/// <summary>
/// 汉字转化为拼音
/// </summary>
/// <param name="str">汉字</param>
/// <returns>全拼</returns>
public static string GetPinyin(string str)
{
string returnStr = string.Empty;
List <string> py = new List <string>();
py.Add("");
foreach (char obj in str)
{
try
{
ChineseChar chineseChar = new ChineseChar(obj);
ReadOnlyCollection <string> pinyin = chineseChar.Pinyins;
List <string> dypy = new List <string>();
foreach (string pin in pinyin)
{
if (pin != null)
{
string r = DataConvert.ToString(pin.Substring(0, pin.Length - 1));
if (!dypy.Contains(r))
{
dypy.Add(r);
}
}
}
if (dypy.Count < 2)
{
if (chineseChar.Pinyins[0] != null)
{
string t = DataConvert.ToString(chineseChar.Pinyins[0].ToString());
string r = DataConvert.ToString(t.Substring(0, t.Length - 1));
for (int i = 0; i < py.Count; i++)
{
py[i] += r;
}
}
}
else
{
List <string> prePY = new List <string>();
foreach (string strpy in py)
{
prePY.Add(strpy);
}
int pyIndex = 0;
foreach (string prepy in prePY)
{
for (int i = 0; i < dypy.Count; i++)
{
if (i > 0)
{
py.Add(prepy);
}
py[pyIndex] += dypy[i];
pyIndex++;
}
}
}
}
catch
{
returnStr += obj.ToString();
}
}
foreach (string rs in py)
{
returnStr += rs + ",";
}
returnStr = returnStr.Substring(0, returnStr.Length - 1);
return(returnStr);
}
public static Dictionary <string, GridInfo> GetGridLayout(string areaName, string layoutName, string formMode)
{
XmlDocument doc = new XmlDocument();
doc.Load(AppMember.GridPath + areaName + "\\" + layoutName + ".xml");
Dictionary <string, GridInfo> gridHeadersInfoCollection = new Dictionary <string, GridInfo>();
XmlNode xn = doc.SelectSingleNode("/gridLayout/columnLayout");
XmlNodeList xnl = xn.ChildNodes;
foreach (XmlNode cnode in xnl)
{
GridInfo gridHeadersInfo = new GridInfo();
gridHeadersInfo.Name = cnode.Attributes["name"].Value;
if (cnode.Attributes["caption"] != null)
{
gridHeadersInfo.Caption = cnode.Attributes["caption"].Value;
}
if (cnode.Attributes["align"] != null)
{
gridHeadersInfo.Align = cnode.Attributes["align"].Value;
}
if (cnode.Attributes["hidden"] != null)
{
gridHeadersInfo.Hidden = DataConvert.ToString(cnode.Attributes["hidden"].Value);
}
if (cnode.Attributes["width"] != null)
{
gridHeadersInfo.Width = DataConvert.ToString(cnode.Attributes["width"].Value);
}
if (cnode.Attributes["index"] != null)
{
gridHeadersInfo.Index = DataConvert.ToString(cnode.Attributes["index"].Value);
}
//if (formMode != "approve" && !formMode.Contains("view"))
if (!formMode.Contains("view"))
{
if (cnode.Attributes["editable"] != null)
{
gridHeadersInfo.Editable = DataConvert.ToString(cnode.Attributes["editable"].Value);
}
}
if (cnode.Attributes["edittype"] != null)
{
gridHeadersInfo.Edittype = DataConvert.ToString(cnode.Attributes["edittype"].Value);
}
if (cnode.Attributes["formatter"] != null)
{
gridHeadersInfo.Formatter = DataConvert.ToString(cnode.Attributes["formatter"].Value);
}
if (cnode.Attributes["datasourceurl"] != null)
{
gridHeadersInfo.Datasourceurl = DataConvert.ToString(cnode.Attributes["datasourceurl"].Value);
}
if (cnode.Attributes["isRowspan"] != null)
{
gridHeadersInfo.IsRowspan = DataConvert.ToString(cnode.Attributes["isRowspan"].Value);
}
gridHeadersInfoCollection.Add(gridHeadersInfo.Name, gridHeadersInfo);
}
return(gridHeadersInfoCollection);
}
/// <summary>
/// 导出到Execl
/// </summary>
/// <param name="dt">数据集</param>
/// <param name="strSheetName">工作部名称</param>
/// <param name="pathloading">保存路径</param>
/// <param name="title">标题名</param>
public static void PrintExcel(System.Data.DataTable dt, AdvanceGridLayout layout, string strSheetName)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //Execl的操作类
Workbook bookDest = (Workbook)excel.Workbooks.Add(Missing.Value);
Worksheet sheetDest = bookDest.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value) as Worksheet; //给工作薄添加一个Sheet
sheetDest.Name = strSheetName;
for (int i = bookDest.Worksheets.Count; i > 1; i--)
{
Worksheet wt = (Worksheet)bookDest.Worksheets[i];
if (wt.Name != strSheetName)
{
wt.Delete();
}
}
//Range rngRow = (Microsoft.Office.Interop.Excel.Range)sheetDest.Columns[1, Type.Missing];
////rngRow.UseStandardWidth = 70;
//Range rngA = (Range)sheetDest.Columns["A", Type.Missing];//设置单元格格式
//rngA.NumberFormatLocal = "@";//字符型格式
//Range rngJ = (Range)sheetDest.Columns["J", Type.Missing];
//rngJ.NumberFormatLocal = "@";
//Range rngQ = (Range)sheetDest.Columns["Q", Type.Missing];
//rngQ.NumberFormatLocal = "@";
//Range rngE = (Range)sheetDest.Columns["E", Type.Missing];
//rngE.NumberFormatLocal = @"yyyy-mm-dd";//日期型格式
excel.Application.Workbooks.Add(true);
try
{
int rowIndex = 2;
int colIndex = 1;
if (layout.GridGroupHeader != null)
{
////导入group头
foreach (KeyValuePair <string, GirdGroupHeaderInfo> dic in layout.GridGroupHeader)
{
Range rng;
if (DataConvert.ToString(dic.Value.NumberOfRows) != "")
{
rng = sheetDest.Range[sheetDest.Cells[1, colIndex], sheetDest.Cells[1 + DataConvert.ToInt32(dic.Value.NumberOfRows) - 1, colIndex]];
rng.Merge(rng.MergeCells);
sheetDest.Cells[1, colIndex] = dic.Value.TitleText;
SetHeaderRang(rng);
colIndex++;
}
else
{
rng = sheetDest.Range[sheetDest.Cells[1, colIndex], sheetDest.Cells[1, colIndex + DataConvert.ToInt32(dic.Value.NumberOfColumns) - 1]];
rng.Merge(rng.MergeCells);
sheetDest.Cells[1, colIndex] = dic.Value.TitleText;
SetHeaderRang(rng);
colIndex += DataConvert.ToInt32(dic.Value.NumberOfColumns);
}
}
}
colIndex = 0;
//导入列头
foreach (KeyValuePair <string, GridInfo> dic in layout.GridLayouts)
{
if (dic.Value.Hidden == "false")
{
colIndex++;
Range rng = (Range)sheetDest.Cells[2, colIndex];
sheetDest.Cells[2, colIndex] = dic.Value.Caption;//Execl中的第一列把DataTable的列名先导进去
SetHeaderRang(rng);
rng.ColumnWidth = DataConvert.ToDouble(dic.Value.Width) / 7.647;
}
}
//导入数据行
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 0;
foreach (KeyValuePair <string, GridInfo> dic in layout.GridLayouts)
{
if (dic.Value.Hidden == "false")
{
colIndex++;
sheetDest.Cells[rowIndex, colIndex] = row[dic.Key].ToString();
Range rng01 = (Range)sheetDest.Cells[rowIndex, colIndex];
SetDataRang(rng01);
}
}
}
}
catch
{
throw new Exception();
}
bookDest.PrintOutEx();
//excel.Quit();
//excel = null;
//GC.Collect();
}