//Cui: Nhap mang
public static void ExecuteProcedure(DataTable b_dt, string storeName)
{
SqlConnection cn = GetConnection();
for (int i = 0; i < b_dt.Rows.Count; i++)
{
object[] a_pra = new object[b_dt.Columns.Count];
for (int j = 0; j < b_dt.Columns.Count; j++)
{
a_pra[j] = LibConvert.ObjectToString(b_dt.Rows[i][j]);
}
SqlCommand cm = CreateCommand(cn, a_pra, storeName);
cm.ExecuteNonQuery();
cm.Parameters.Clear();
cm.Clone();
}
cn.Close();
}
/// <summary>
///Quannm: Đưa file excel theo group
/// </summary>
/// <param name="b_dt"></param>
/// <param name="b_truong"></param>
/// <returns></returns>
private static string getWorksheets(DataTable b_dt, string b_truong)
{
var b_qr = from p in b_dt.AsEnumerable()
group p by new
{
ma_dt = p.Field <string>("ma_dt"),
ten_dt = p.Field <string>("ten_dt")
};
StringWriter writer = new StringWriter();
if ((b_dt == null) || (b_dt.Rows.Count == 0))
{
writer.Write("<Worksheet ss:Name=\"Sheet1\">\r\n<Table>\r\n<Row><Cell><Data ss:Type=\"String\"></Data></Cell></Row>\r\n</Table>\r\n</Worksheet>");
return(writer.ToString());
}
int num = 0;
string b_gtri = string.Empty;
bool b_okie = true;
b_gtri = LibConvert.ObjectToString(b_dt.Rows[0]["ten_dvi"]);
for (int i = 0; i < b_dt.Rows.Count; i++)
{
if ((i % 65000) == 0)
{
if ((i / 65000) > num)
{
writer.Write("\r\n</Table>\r\n</Worksheet>");
num = i / 65000;
}
writer.Write("\r\n<Worksheet ss:Name=\"" + replaceXmlChar(b_dt.TableName) + (((i / 65000) == 0) ? "" : Convert.ToString((int)(i / 65000))) + "\">\r\n<Table>");
foreach (var items in b_qr)
{
writer.Write("<Column ss:StyleID=\"s62\" ss:AutoFitWidth=\"0\" ss:Width=\"100\" />");
}
writer.Write("\r\n<Row>");
//Lặp tiêu đề khi vượt quá 65000 dòng
writer.Write("<Cell ss:StyleID=\"m284322144\" ><Data ss:Type=\"String\">Tên đơn vị</Data></Cell>");
foreach (var items in b_qr)
{
writer.Write(string.Format("<Cell ss:StyleID=\"m284322144\" ><Data ss:Type=\"String\">{0}</Data></Cell>", MergeValue(items.Key.ten_dt, items.Key.ma_dt)));
}
writer.Write("</Row>");
}
//Ghi data
tiep:
if (b_okie)
{
writer.Write("\r\n<Row>");
writer.Write(getCell(b_dt.Rows[i]["ten_dvi"].GetType(), b_dt.Rows[i]["ten_dvi"]));
}
if (b_gtri == LibConvert.ObjectToString(b_dt.Rows[i]["ten_dvi"]))
{
if (b_qr.Select(o => o.Key.ma_dt == LibConvert.ObjectToString(b_dt.Rows[i]["ma_dt"])).Count() > 0)
{
b_okie = false;
writer.Write(getCell(b_dt.Rows[i][b_truong].GetType(), b_dt.Rows[i][b_truong]));
}
}
else
{
b_gtri = LibConvert.ObjectToString(b_dt.Rows[i]["ten_dvi"]);
writer.Write("</Row>");
b_okie = true;
goto tiep;
}
}
writer.Write("</Row>");
writer.Write("\r\n</Table>\r\n</Worksheet>");
return(writer.ToString());
}